1 ### t/log_state.test ###
3 # This test suffers from server
4 # Bug#38124 "general_log_file" variable silently unset when using expression
6 # SET GLOBAL general_log_file = @<whatever>
7 # SET GLOBAL slow_query_log = @<whatever>
8 # cause that the value of these server system variables is set to default
9 # instead of the assigned values. There comes no error message or warning.
10 # If this bug is fixed please
11 # 1. try this test with "let $fixed_bug38124 = 0;"
12 # 2. remove all workarounds if 1. was successful.
13 let $fixed_bug38124 = 0;
15 --source include/not_embedded.inc
16 --source include/have_csv.inc
18 # Several subtests modify global variables. Save the initial values only here,
19 # but reset to the initial values per subtest.
20 SET @old_general_log= @@global.general_log;
21 SET @old_general_log_file= @@global.general_log_file;
22 SET @old_slow_query_log= @@global.slow_query_log;
23 SET @old_slow_query_log_file= @@global.slow_query_log_file;
28 set global general_log= OFF;
29 truncate table mysql.general_log;
30 truncate table mysql.slow_log;
32 where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
33 Variable_name = 'general_log' or Variable_name = 'slow_query_log';
35 set global general_log= ON;
36 create table t1(f1 int);
37 --replace_column 1 TIMESTAMP 2 USER_HOST 3 #
38 select * from mysql.general_log;
39 set global general_log= OFF;
41 --replace_column 1 TIMESTAMP 2 USER_HOST 3 #
42 select * from mysql.general_log;
43 set global general_log= ON;
46 where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
47 Variable_name = 'general_log' or Variable_name = 'slow_query_log';
49 --echo # Establish connection con1 (user=root)
50 connect (con1,localhost,root,,);
51 --echo # Switch to connection con1
53 # Please increase @long_query_time if the corresponding selects show an
54 # additional unexpected entry like
55 # start_time ... sql_text
56 # TIMESTAMP ... set session long_query_time=...
57 # (Bug#40377 sporadic pushbuild failure in log_state: result mismatch)
58 --replace_result 2 <long_query_time>
59 set @long_query_time = 2;
60 set session long_query_time = @long_query_time;
61 select sleep(@long_query_time + 1);
62 --replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME
63 select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%';
64 --echo # Switch to connection default
67 set global slow_query_log= ON;
68 --echo # Switch to connection con1
70 set session long_query_time = @long_query_time;
71 select sleep(@long_query_time + 1);
72 --replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME
73 select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%';
74 --echo # Switch to connection default
77 where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
78 Variable_name = 'general_log' or Variable_name = 'slow_query_log';
80 set global general_log= ON;
81 set global general_log= OFF;
82 set global general_log= OFF;
83 set global slow_query_log= ON;
84 set global slow_query_log= OFF;
85 set global slow_query_log= OFF;
87 set global general_log= ON;
88 truncate table mysql.general_log;
89 create table t1(f1 int);
91 --replace_column 1 TIMESTAMP 2 USER_HOST 3 #
92 select * from mysql.general_log;
93 set global general_log= OFF;
94 truncate table mysql.general_log;
95 --replace_column 1 TIMESTAMP 2 USER_HOST 3 #
96 select * from mysql.general_log;
97 set global general_log= ON;
99 where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
100 Variable_name = 'general_log' or Variable_name = 'slow_query_log';
103 show variables like 'general_log_file';
105 show variables like 'slow_query_log_file';
106 show variables like 'log_output';
108 # Can't set general_log_file to a non existing file
109 --error ER_WRONG_VALUE_FOR_VAR
110 set global general_log_file='/not existing path/log.master';
112 # Can't set general_log_file to a directory
113 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
114 --error ER_WRONG_VALUE_FOR_VAR
115 eval set global general_log_file='$MYSQLTEST_VARDIR';
117 # Can't set general_log_file to empty string
118 --error ER_WRONG_VALUE_FOR_VAR
119 set global general_log_file='';
122 show variables like 'general_log_file';
123 set global general_log= OFF;
124 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
125 eval set global general_log_file='$MYSQLTEST_VARDIR/tmp/log.master';
126 set global general_log= ON;
127 create table t1(f1 int);
129 set global general_log= OFF;
130 set global general_log_file=default;
131 set global general_log= ON;
132 create table t1(f1 int);
135 show variables like 'general_log_file';
137 show variables like 'slow_query_log_file';
139 set global general_log= default;
140 set global slow_query_log= default;
141 set global general_log_file= default;
142 set global slow_query_log_file= default;
143 show variables like 'general_log';
144 show variables like 'slow_query_log';
145 set global general_log=ON;
146 set global log_output=default;
147 show variables like 'log_output';
148 set global general_log=OFF;
149 set global log_output=FILE;
150 truncate table mysql.general_log;
151 show variables like 'log_output';
152 set global general_log=ON;
153 create table t1(f1 int);
154 --replace_column 1 TIMESTAMP 2 USER_HOST 3 #
155 select * from mysql.general_log;
156 set global general_log=OFF;
157 set global log_output="FILE,TABLE";
158 show variables like 'log_output';
159 set global general_log=ON;
161 --replace_column 1 TIMESTAMP 2 USER_HOST 3 #
162 select * from mysql.general_log;
164 # Reset to initial values
165 SET @@global.general_log = @old_general_log;
166 SET @@global.general_log_file = @old_general_log_file;
167 SET @@global.slow_query_log = @old_slow_query_log;
168 SET @@global.slow_query_log_file = @old_slow_query_log_file;
172 let $my_var = `SELECT @old_general_log_file`;
173 eval SET @@global.general_log_file = '$my_var';
174 let $my_var = `SELECT @old_slow_query_log_file`;
175 eval SET @@global.slow_query_log_file = '$my_var';
180 ###########################################################################
183 # Bug#29129 (Resetting general_log while the GLOBAL READ LOCK is set causes
187 # Test ON->OFF transition under a GLOBAL READ LOCK
188 SET GLOBAL general_log = ON;
189 SET GLOBAL slow_query_log = ON;
191 FLUSH TABLES WITH READ LOCK;
193 SET GLOBAL general_log = OFF;
194 SET GLOBAL slow_query_log = OFF;
198 # Test OFF->ON transition under a GLOBAL READ LOCK
199 FLUSH TABLES WITH READ LOCK;
201 SET GLOBAL general_log = ON;
202 SET GLOBAL slow_query_log = ON;
206 # Test ON->OFF transition under a GLOBAL READ_ONLY
207 SET GLOBAL READ_ONLY = ON;
209 SET GLOBAL general_log = OFF;
210 SET GLOBAL slow_query_log = OFF;
212 SET GLOBAL READ_ONLY = OFF;
214 # Test OFF->ON transition under a GLOBAL READ_ONLY
215 SET GLOBAL READ_ONLY = ON;
217 SET GLOBAL general_log = ON;
218 SET GLOBAL slow_query_log = ON;
220 SET GLOBAL READ_ONLY = OFF;
222 # Reset to initial values
223 SET GLOBAL general_log = @old_general_log;
224 SET GLOBAL slow_query_log = @old_slow_query_log;
227 ###########################################################################
230 # Bug#29131: SHOW VARIABLES reports variable 'log' but SET doesn't recognize it
233 SET GLOBAL general_log = ON;
235 SHOW VARIABLES LIKE 'general_log';
236 SHOW VARIABLES LIKE 'log';
237 SELECT @@general_log, @@log;
239 SHOW VARIABLES LIKE 'general_log';
240 SHOW VARIABLES LIKE 'log';
241 SELECT @@general_log, @@log;
242 SET GLOBAL general_log = 1;
243 SHOW VARIABLES LIKE 'general_log';
244 SHOW VARIABLES LIKE 'log';
245 SELECT @@general_log, @@log;
247 SHOW VARIABLES LIKE 'slow_query_log';
248 SHOW VARIABLES LIKE 'log_slow_queries';
249 SELECT @@slow_query_log, @@log_slow_queries;
250 SET GLOBAL log_slow_queries = 0;
251 SHOW VARIABLES LIKE 'slow_query_log';
252 SHOW VARIABLES LIKE 'log_slow_queries';
253 SELECT @@slow_query_log, @@log_slow_queries;
254 SET GLOBAL slow_query_log = 1;
255 SHOW VARIABLES LIKE 'slow_query_log';
256 SHOW VARIABLES LIKE 'log_slow_queries';
257 SELECT @@slow_query_log, @@log_slow_queries;
259 SET GLOBAL general_log = @old_general_log;
260 SET GLOBAL slow_query_log = @old_slow_query_log;
263 ###########################################################################
266 # Bug#31604: server crash when setting slow_query_log_file/general_log_file
269 --error ER_WRONG_VALUE_FOR_VAR
270 SET GLOBAL general_log_file= CONCAT('/not existing path/log.maste', 'r');
271 --error ER_WRONG_VALUE_FOR_VAR
272 SET GLOBAL general_log_file= NULL;
273 --error ER_WRONG_VALUE_FOR_VAR
274 SET GLOBAL slow_query_log_file= CONCAT('/not existing path/log.maste', 'r');
275 --error ER_WRONG_VALUE_FOR_VAR
276 SET GLOBAL slow_query_log_file= NULL;
278 # Reset to initial values in case a setting above was successful.
279 SET GLOBAL general_log_file= @old_general_log_file;
280 SET GLOBAL slow_query_log_file= @old_slow_query_log_file;
284 let $my_var = `SELECT @old_general_log_file`;
285 eval SET @@global.general_log_file = '$my_var';
286 let $my_var = `SELECT @old_slow_query_log_file`;
287 eval SET @@global.slow_query_log_file = '$my_var';
291 ###########################################################################
295 --echo # -- Bug#32748: Inconsistent handling of assignments to
296 --echo # -- general_log_file/slow_query_log_file.
300 SET GLOBAL general_log_file = 'bug32748.query.log';
301 SET GLOBAL slow_query_log_file = 'bug32748.slow.log';
304 SHOW VARIABLES LIKE '%log_file';
306 # Reset to initial values
308 SET GLOBAL general_log_file = @old_general_log_file;
309 SET GLOBAL slow_query_log_file = @old_slow_query_log_file;
313 let $my_var = `SELECT @old_general_log_file`;
314 eval SET @@global.general_log_file = '$my_var';
315 let $my_var = `SELECT @old_slow_query_log_file`;
316 eval SET @@global.slow_query_log_file = '$my_var';
321 --echo # -- End of Bug#32748.
324 ###########################################################################
326 # WL#4403 - deprecate @log and @slow_log_queries variables
328 # These server system variables are all deprecated
329 # -- show for command-line as well!
332 SET GLOBAL log_slow_queries = 0;
333 SET GLOBAL log = DEFAULT;
334 SET GLOBAL log_slow_queries = DEFAULT;
336 # These server system variables are NOT deprecated.
337 --echo not deprecated:
338 SELECT @@global.general_log_file INTO @my_glf;
339 SELECT @@global.slow_query_log_file INTO @my_sqlf;
340 SET GLOBAL general_log = 0;
341 SET GLOBAL slow_query_log = 0;
342 SET GLOBAL general_log_file = 'WL4403_G.log';
343 SET GLOBAL slow_query_log_file = 'WL4403_SQ.log';
344 SET GLOBAL general_log_file = @my_glf;
345 SET GLOBAL slow_query_log_file = @my_sqlf;
346 SET GLOBAL general_log = DEFAULT;
347 SET GLOBAL slow_query_log = DEFAULT;
349 ## Reset to initial values
350 SET @@global.general_log = @old_general_log;
351 SET @@global.general_log_file = @old_general_log_file;
352 SET @@global.slow_query_log = @old_slow_query_log;
353 SET @@global.slow_query_log_file = @old_slow_query_log_file;
357 let $my_var = `SELECT @old_general_log_file`;
358 eval SET @@global.general_log_file = '$my_var';
359 let $my_var = `SELECT @old_slow_query_log_file`;
360 eval SET @@global.slow_query_log_file = '$my_var';
365 ###########################################################################
368 --echo # Bug #49756 Rows_examined is always 0 in the slow query log
369 --echo # for update statements
372 SET @old_log_output = @@global.log_output;
373 SET GLOBAL log_output = "TABLE";
374 SET GLOBAL slow_query_log = ON;
375 SET GLOBAL long_query_time = 0.001;
377 # clear slow_log of any residual slow queries
378 TRUNCATE TABLE mysql.slow_log;
379 CREATE TABLE t1 (a INT);
380 CREATE TABLE t2 (b INT, PRIMARY KEY (b));
381 INSERT INTO t2 VALUES (3),(4);
383 connect (con2,localhost,root,,);
384 INSERT INTO t1 VALUES (1+sleep(.01)),(2);
385 INSERT INTO t1 SELECT b+sleep(.01) from t2;
386 UPDATE t1 SET a=a+sleep(.01) WHERE a>2;
387 UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC;
388 UPDATE t2 set b=b+sleep(.01) limit 1;
389 UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2);
390 DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2;
392 SELECT rows_examined,sql_text FROM mysql.slow_log;
396 TRUNCATE TABLE mysql.slow_log;
398 --echo # end of bug#49756
401 --echo End of 5.1 tests
408 # Disconnect must be done last to avoid delayed 'Quit' message in general log
409 --echo # Close connection con1
411 # set back the saved default values
414 # Reset global system variables to initial values if forgotten somewhere above.
415 SET GLOBAL long_query_time = DEFAULT;
416 SET GLOBAL log_output = @old_log_output;
417 SET global general_log = @old_general_log;
418 SET global general_log_file = @old_general_log_file;
419 SET global slow_query_log = @old_slow_query_log;
420 SET global slow_query_log_file = @old_slow_query_log_file;
424 let $my_var = `SELECT @old_general_log_file`;
425 eval SET @@global.general_log_file = '$my_var';
426 let $my_var = `SELECT @old_slow_query_log_file`;
427 eval SET @@global.slow_query_log_file = '$my_var';
431 # Remove the log files that was created in the "default location"
433 --remove_file $MYSQLTEST_VARDIR/run/mysqld.log
434 --remove_file $MYSQLTEST_VARDIR/tmp/log.master