1# Setting general_log content empty.
2TRUNCATE TABLE mysql.general_log;
3# Creating tables
4CREATE TABLE t_int (c1  TINYINT,
5c2  SMALLINT,
6c3  MEDIUMINT,
7c4  INT,
8c5  INTEGER,
9c6  SMALLINT UNSIGNED,
10c7  MEDIUMINT UNSIGNED,
11c8  INT UNSIGNED,
12c9  INTEGER);
13CREATE TABLE t_bigint (c1  BIGINT,
14c2  BIGINT UNSIGNED);
15CREATE TABLE t_real (c1 REAL UNSIGNED,
16c2 DOUBLE UNSIGNED,
17c3 FLOAT UNSIGNED,
18c4 DECIMAL UNSIGNED,
19c5 NUMERIC UNSIGNED,
20c6 DOUBLE,
21c7 FLOAT,
22c8 DECIMAL,
23c9 NUMERIC);
24CREATE TABLE t_date (c1 DATE,
25c2 TIME,
26c3 TIMESTAMP,
27c4 DATETIME,
28c5 YEAR);
29# Inserting values into the tables
30INSERT INTO t_int VALUES (91,92,93,94,95,96,97,98,99),(81,82,83,84,85,86,87,88,89);
31INSERT INTO t_bigint VALUES (91,92),(81,82);
32INSERT INTO t_real VALUES (8.51,8.52,8.53,8.54,8.55,8.56,8.57,8.58,8.59);
33Warnings:
34Note	1265	Data truncated for column 'c4' at row 1
35Note	1265	Data truncated for column 'c5' at row 1
36Note	1265	Data truncated for column 'c8' at row 1
37Note	1265	Data truncated for column 'c9' at row 1
38INSERT INTO t_real VALUES (9.51,9.52,9.53,9.54,9.55,9.56,9.57,9.58,9.59);
39Warnings:
40Note	1265	Data truncated for column 'c4' at row 1
41Note	1265	Data truncated for column 'c5' at row 1
42Note	1265	Data truncated for column 'c8' at row 1
43Note	1265	Data truncated for column 'c9' at row 1
44INSERT INTO t_date VALUES ('2015-07-06','20:15','2015-12-31 14:59:48','2014-11-30 13:58:47','2015');
45INSERT INTO t_date VALUES ('2015-07-07','20:17','2013-12-31 17:59:47','2012-10-31 15:57:47','2010');
46SET @old_log_output = @@global.log_output;
47SET @old_general_log = @@global.general_log;
48SET @old_general_log_file = @@global.general_log_file;
49##########################################
50# Start plugin with general log
51##########################################
52SET GLOBAL general_log_file = '.../log/test_sql_general.log';
53SET GLOBAL log_output = 'FILE,TABLE';
54SET GLOBAL general_log = 'ON';
55##########################################
56# Run plugin
57##########################################
58INSTALL PLUGIN test_sql_2_sessions SONAME 'TEST_SQL_2_SESSIONS';
59##########################################
60# Stop plugin
61##########################################
62UNINSTALL PLUGIN test_sql_2_sessions;
63##########################################
64# Plugin log
65##########################################
66========================================================================
67Test in a server thread
68Opening Session 1
69========================================================================
70Session 1 :
71========================================================================
72SELECT * FROM test.t_int
73num_cols: 9
74nb rows: 2
75c1  c2  c3  c4  c5  c6  c7  c8  c9
76TINY(1) SHORT(2) INT24(9) LONG(3) LONG(3) SHORT(2) INT24(9) LONG(3) LONG(3)
77() () () () () (UNSIGNED ) (UNSIGNED ) (UNSIGNED ) ()
78Write a string
7991  92  93  94  95  96  97  98  99
8081  82  83  84  85  86  87  88  89
81
82affected rows : 0
83server status : 34
84warn count    : 0
85
86Opening Session 2
87========================================================================
88Session 1 :
89========================================================================
90SELECT * FROM test.t_bigint
91num_cols: 2
92nb rows: 2
93c1  c2
94LONGLONG(8) LONGLONG(8)
95() (UNSIGNED )
96Write a string
9791  92
9881  82
99
100affected rows : 0
101server status : 34
102warn count    : 0
103========================================================================
104Session 2 :
105========================================================================
106SELECT * FROM test.t_real
107num_cols: 9
108nb rows: 2
109c1  c2  c3  c4  c5  c6  c7  c8  c9
110DOUBLE(5) DOUBLE(5) FLOAT(4) NEWDECIMAL(246) NEWDECIMAL(246) DOUBLE(5) FLOAT(4) NEWDECIMAL(246) NEWDECIMAL(246)
111(UNSIGNED ) (UNSIGNED ) (UNSIGNED ) (UNSIGNED ) (UNSIGNED ) () () () ()
112Write a string
1138.51  8.52  8.53  9  9  8.56  8.57  9  9
1149.51  9.52  9.53  10  10  9.56  9.57  10  10
115
116affected rows : 0
117server status : 34
118warn count    : 0
119
120Close Session 1
121========================================================================
122Session 2 :
123========================================================================
124SELECT * FROM test.t_date
125num_cols: 5
126nb rows: 2
127c1  c2  c3  c4  c5
128DATE(10) TIME(11) TIMESTAMP(7) DATETIME(12) YEAR(13)
129(BINARY ) (BINARY ) (NOT_NULL BINARY TIMESTAMP ON_UPDATE_NOW ) (BINARY ) (UNSIGNED ZEROFILL )
130Write a string
1312015-07-06  20:15:00  2015-12-31 14:59:48  2014-11-30 13:58:47  2015
1322015-07-07  20:17:00  2013-12-31 17:59:47  2012-10-31 15:57:47  2010
133
134affected rows : 0
135server status : 34
136warn count    : 0
137========================================================================
138Session 1 :
139========================================================================
140SELECT * FROM test.t_date
141
142Close Session 2
143Follows threaded run
144========================================================================
145init thread
146Opening Session 1
147========================================================================
148Session 1 :
149========================================================================
150SELECT * FROM test.t_int
151num_cols: 9
152nb rows: 2
153c1  c2  c3  c4  c5  c6  c7  c8  c9
154TINY(1) SHORT(2) INT24(9) LONG(3) LONG(3) SHORT(2) INT24(9) LONG(3) LONG(3)
155() () () () () (UNSIGNED ) (UNSIGNED ) (UNSIGNED ) ()
156Write a string
15791  92  93  94  95  96  97  98  99
15881  82  83  84  85  86  87  88  89
159
160affected rows : 0
161server status : 34
162warn count    : 0
163
164Opening Session 2
165========================================================================
166Session 1 :
167========================================================================
168SELECT * FROM test.t_bigint
169num_cols: 2
170nb rows: 2
171c1  c2
172LONGLONG(8) LONGLONG(8)
173() (UNSIGNED )
174Write a string
17591  92
17681  82
177
178affected rows : 0
179server status : 34
180warn count    : 0
181========================================================================
182Session 2 :
183========================================================================
184SELECT * FROM test.t_real
185num_cols: 9
186nb rows: 2
187c1  c2  c3  c4  c5  c6  c7  c8  c9
188DOUBLE(5) DOUBLE(5) FLOAT(4) NEWDECIMAL(246) NEWDECIMAL(246) DOUBLE(5) FLOAT(4) NEWDECIMAL(246) NEWDECIMAL(246)
189(UNSIGNED ) (UNSIGNED ) (UNSIGNED ) (UNSIGNED ) (UNSIGNED ) () () () ()
190Write a string
1918.51  8.52  8.53  9  9  8.56  8.57  9  9
1929.51  9.52  9.53  10  10  9.56  9.57  10  10
193
194affected rows : 0
195server status : 34
196warn count    : 0
197
198Close Session 1
199========================================================================
200Session 2 :
201========================================================================
202SELECT * FROM test.t_date
203num_cols: 5
204nb rows: 2
205c1  c2  c3  c4  c5
206DATE(10) TIME(11) TIMESTAMP(7) DATETIME(12) YEAR(13)
207(BINARY ) (BINARY ) (NOT_NULL BINARY TIMESTAMP ON_UPDATE_NOW ) (BINARY ) (UNSIGNED ZEROFILL )
208Write a string
2092015-07-06  20:15:00  2015-12-31 14:59:48  2014-11-30 13:58:47  2015
2102015-07-07  20:17:00  2013-12-31 17:59:47  2012-10-31 15:57:47  2010
211
212affected rows : 0
213server status : 34
214warn count    : 0
215========================================================================
216Session 1 :
217========================================================================
218SELECT * FROM test.t_date
219
220Close Session 2
221deinit thread
222##########################################
223# Check general log file
224##########################################
225CREATE TABLE t1 (c1 TEXT, c2 TEXT, c3 TEXT);
226LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/log/test_sql_general.log' INTO TABLE t1;
227SELECT c3 FROM t1 WHERE c3 LIKE '%FROM test.t_int%';
228c3
229SELECT * FROM test.t_int
230SELECT * FROM test.t_int
231SELECT c3 FROM t1 WHERE c3 LIKE '%FROM test.t_bigint%';
232c3
233SELECT * FROM test.t_bigint
234SELECT * FROM test.t_bigint
235SELECT count(*) FROM t1 WHERE c2 LIKE '%Connect%';
236count(*)
2374
238SELECT count(*) FROM t1 WHERE c2 LIKE '%Quit%';
239count(*)
2404
241SELECT argument FROM mysql.general_log WHERE command_type = 'Query' AND
242(argument LIKE '%FROM test.t_int%' OR
243argument LIKE '%FROM test.t_bigint%');
244argument
245SELECT * FROM test.t_int
246SELECT * FROM test.t_bigint
247SELECT * FROM test.t_int
248SELECT * FROM test.t_bigint
249SELECT c3 FROM t1 WHERE c3 LIKE '%FROM test.t_int%'
250SELECT c3 FROM t1 WHERE c3 LIKE '%FROM test.t_bigint%'
251SELECT argument FROM mysql.general_log WHERE command_type = 'Query' AND
252(argument LIKE '%FROM test.t_int%' OR
253argument LIKE '%FROM test.t_bigint%')
254SELECT count(*) FROM mysql.general_log WHERE command_type = 'Connect';
255count(*)
2564
257SELECT count(*) FROM mysql.general_log WHERE command_type = 'Quit';
258count(*)
2594
260##########################################
261# Cleanup
262##########################################
263# Dropping the created tables
264DROP TABLE t1;
265DROP TABLE t_int;
266DROP TABLE t_bigint;
267DROP TABLE t_real;
268DROP TABLE t_date;
269# Removing general_log content
270TRUNCATE TABLE mysql.general_log;
271# Deleting log file created
272SET GLOBAL log_output = @old_log_output;
273SET GLOBAL general_log = @old_general_log;
274SET GLOBAL general_log_file = @old_general_log_file;
275