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