log_state.test 8.93 KB
Newer Older
1 2 3
# hhunger 08.22.2008: with check-testcases differences due to bug#38889 using 
# slow_query_log_file or general_log_file

4 5 6 7
-- source include/not_embedded.inc
--source include/have_csv.inc

--disable_ps_protocol
8 9 10 11 12
# save default value to set them back at the end of the test
set @start_general_log= @@global.general_log;
set @start_slow_query_log= @@global.slow_query_log;
set @start_general_log_file= @@global.general_log_file;

13 14 15 16 17 18 19 20 21
set global general_log= OFF;
truncate table mysql.general_log;
truncate table mysql.slow_log;
show global variables
where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
Variable_name = 'general_log' or Variable_name = 'slow_query_log';
flush logs;
set global general_log= ON;
create table t1(f1 int);
22
--replace_column 1 TIMESTAMP 2 USER_HOST 3 #
23 24 25
select * from mysql.general_log;
set global general_log= OFF;
drop table t1;
26
--replace_column 1 TIMESTAMP 2 USER_HOST 3 #
27 28 29 30 31 32 33 34 35 36 37
select * from mysql.general_log;
set global general_log= ON;
flush logs;
show global variables
where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
Variable_name = 'general_log' or Variable_name = 'slow_query_log';

connect (con1,localhost,root,,);
connection con1;
set session long_query_time=1;
select sleep(2);
38
--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME
39
select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%';
40 41 42 43 44 45
connection default;

set global slow_query_log= ON;
connection con1;
set session long_query_time=1;
select sleep(2);
46
--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME
47
select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%';
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
connection default;
show global variables
where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
Variable_name = 'general_log' or Variable_name = 'slow_query_log';

set global general_log= ON;
set global general_log= OFF;
set global general_log= OFF;
set global slow_query_log= ON;
set global slow_query_log= OFF;
set global slow_query_log= OFF;

set global general_log= ON;
truncate table mysql.general_log;
create table t1(f1 int);
drop table t1;
64
--replace_column 1 TIMESTAMP 2 USER_HOST 3 #
65 66 67
select * from mysql.general_log;
set global general_log= OFF;
truncate table mysql.general_log;
68
--replace_column 1 TIMESTAMP 2 USER_HOST 3 #
69 70 71 72 73 74 75 76 77 78 79 80
select * from mysql.general_log;
set global general_log= ON;
show global variables
where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
Variable_name = 'general_log' or Variable_name = 'slow_query_log';

--replace_column 2 #
show variables like 'general_log_file';
--replace_column 2 #
show variables like 'slow_query_log_file';
show variables like 'log_output';

81
# Can't set general_log_file to a non existing file
82
--error ER_WRONG_VALUE_FOR_VAR
83
set global general_log_file='/not exiting path/log.master';
84 85 86

# Can't set general_log_file to a directory
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
87
--error ER_WRONG_VALUE_FOR_VAR
88 89 90
eval set global general_log_file='$MYSQLTEST_VARDIR';

# Can't set general_log_file to empty string
91
--error ER_WRONG_VALUE_FOR_VAR
92
set global general_log_file='';
93

94 95 96
--replace_column 2 #
show variables like 'general_log_file';
set global general_log= OFF;
97 98
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval set global general_log_file='$MYSQLTEST_VARDIR/tmp/log.master';
99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126
set global general_log= ON;
create table t1(f1 int);
drop table t1;
set global general_log= OFF;
set global general_log_file=default;
set global general_log= ON;
create table t1(f1 int);
drop table t1;
--replace_column 2 #
show variables like 'general_log_file';
--replace_column 2 #
show variables like 'slow_query_log_file';

set global general_log= default;
set global slow_query_log= default;
set global general_log_file= default;
set global slow_query_log_file= default;
show variables like 'general_log';
show variables like 'slow_query_log';
set global general_log=ON;
set global log_output=default;
show variables like 'log_output';
set global general_log=OFF;
set global log_output=FILE;
truncate table mysql.general_log;
show variables like 'log_output';
set global general_log=ON;
create table t1(f1 int);
127
--replace_column 1 TIMESTAMP 2 USER_HOST 3 #
128 129 130 131 132 133
select * from mysql.general_log;
set global general_log=OFF;
set global log_output="FILE,TABLE";
show variables like 'log_output';
set global general_log=ON;
drop table t1;
134
--replace_column 1 TIMESTAMP 2 USER_HOST 3 #
135 136
select * from mysql.general_log;

137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189
#
# Bug#29129 (Resetting general_log while the GLOBAL READ LOCK is set causes
# a deadlock)

# save state

SET @old_general_log_state = @@global.general_log;
SET @old_slow_log_state = @@global.slow_query_log;

# Test ON->OFF transition under a GLOBAL READ LOCK

SET GLOBAL general_log = ON;
SET GLOBAL slow_query_log = ON;

FLUSH TABLES WITH READ LOCK;

SET GLOBAL general_log = OFF;
SET GLOBAL slow_query_log = OFF;

UNLOCK TABLES;

# Test OFF->ON transition under a GLOBAL READ LOCK

FLUSH TABLES WITH READ LOCK;

SET GLOBAL general_log = ON;
SET GLOBAL slow_query_log = ON;

UNLOCK TABLES;

# Test ON->OFF transition under a GLOBAL READ_ONLY

SET GLOBAL READ_ONLY = ON;

SET GLOBAL general_log = OFF;
SET GLOBAL slow_query_log = OFF;

SET GLOBAL READ_ONLY = OFF;

# Test OFF->ON transition under a GLOBAL READ_ONLY

SET GLOBAL READ_ONLY = ON;

SET GLOBAL general_log = ON;
SET GLOBAL slow_query_log = ON;

SET GLOBAL READ_ONLY = OFF;

# Restore state

SET GLOBAL general_log = @old_general_log_state;
SET GLOBAL slow_query_log = @old_slow_log_state;

190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223
#
# Bug #29131: SHOW VARIABLES reports variable 'log' but SET doesn't recognize it
#

SET @old_general_log_state = @@global.general_log;
SET @old_slow_log_state = @@global.slow_query_log;

SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'log';
SELECT @@general_log, @@log;
SET GLOBAL log = 0;
SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'log';
SELECT @@general_log, @@log;
SET GLOBAL general_log = 1;
SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'log';
SELECT @@general_log, @@log;

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'log_slow_queries';
SELECT @@slow_query_log, @@log_slow_queries;
SET GLOBAL log_slow_queries = 0;
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'log_slow_queries';
SELECT @@slow_query_log, @@log_slow_queries;
SET GLOBAL slow_query_log = 1;
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'log_slow_queries';
SELECT @@slow_query_log, @@log_slow_queries;

SET GLOBAL general_log = @old_general_log_state;
SET GLOBAL slow_query_log = @old_slow_log_state;

224 225 226 227 228 229
#
# Bug #31604: server crash when setting slow_query_log_file/general_log_file
#
set @old_general_log_file= @@global.general_log_file;
set @old_slow_query_log_file= @@global.slow_query_log_file;

230
--error ER_WRONG_VALUE_FOR_VAR
231
set global general_log_file= concat('/not exiting path/log.maste', 'r');
232
--error ER_WRONG_VALUE_FOR_VAR
233
set global general_log_file= NULL;
234
--error ER_WRONG_VALUE_FOR_VAR
235
set global slow_query_log_file= concat('/not exiting path/log.maste', 'r');
236
--error ER_WRONG_VALUE_FOR_VAR
237 238 239 240 241
set global slow_query_log_file= NULL;

set global general_log_file= @old_general_log_file;
set global slow_query_log_file= @old_slow_query_log_file;

242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269
###########################################################################

--echo
--echo # --
--echo # -- Bug#32748: Inconsistent handling of assignments to
--echo # -- general_log_file/slow_query_log_file.
--echo # --

--echo
SET @general_log_file_saved = @@global.general_log_file;
SET @slow_query_log_file_saved = @@global.slow_query_log_file;

--echo
SET GLOBAL general_log_file = 'bug32748.query.log';
SET GLOBAL slow_query_log_file = 'bug32748.slow.log';

--echo
SHOW VARIABLES LIKE '%log_file';

--echo
SET GLOBAL general_log_file = @general_log_file_saved;
SET GLOBAL slow_query_log_file = @slow_query_log_file_saved;

--echo
--echo # -- End of Bug#32748.

###########################################################################

270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295


## WL#4403 - deprecate @log and @slow_log_queries variables

## these are all deprecated -- show for command-line as well!
--echo deprecated:
SET GLOBAL log = 0;
SET GLOBAL log_slow_queries = 0;
SET GLOBAL log = DEFAULT;
SET GLOBAL log_slow_queries = DEFAULT;

## these are NOT deprecated
--echo not deprecated:
SELECT @@global.general_log_file INTO @my_glf;
SELECT @@global.slow_query_log_file INTO @my_sqlf;
SET GLOBAL general_log = 0;
SET GLOBAL slow_query_log = 0;
SET GLOBAL general_log_file = 'WL4403_G.log';
SET GLOBAL slow_query_log_file = 'WL4403_SQ.log';
SET GLOBAL general_log_file = @my_glf;
SET GLOBAL slow_query_log_file = @my_sqlf;
SET GLOBAL general_log = DEFAULT;
SET GLOBAL slow_query_log = DEFAULT;



296 297
--echo End of 5.1 tests

298
--enable_ps_protocol
299 300 301 302 303

#
# Cleanup (must be done last to avoid delayed 'Quit' message in general log)
#
disconnect con1;
304 305 306 307 308
# set back the saved default values
connection default;
set @@global.general_log= @start_general_log;
set @@global.slow_query_log= @start_slow_query_log;
set @@global.general_log_file= @start_general_log_file;
309 310 311

# Remove the log files that was created in the "default location"
# i.e var/run
msvensson@pilot.mysql.com's avatar
msvensson@pilot.mysql.com committed
312 313
--remove_file $MYSQLTEST_VARDIR/run/mysqld.log
--remove_file $MYSQLTEST_VARDIR/run/mysqld-slow.log
314