summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/log_state.result24
-rw-r--r--mysql-test/r/log_tables.result297
-rw-r--r--mysql-test/r/ps.result54
-rw-r--r--mysql-test/r/show_check.result31
-rw-r--r--mysql-test/r/status.result12
-rw-r--r--mysql-test/t/log_state.test53
-rw-r--r--mysql-test/t/log_tables.test410
-rw-r--r--mysql-test/t/ps.test7
-rw-r--r--mysql-test/t/show_check.test7
9 files changed, 742 insertions, 153 deletions
diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result
index 946797aeec7..0c6be16b9b7 100644
--- a/mysql-test/r/log_state.result
+++ b/mysql-test/r/log_state.result
@@ -131,7 +131,7 @@ set global general_log=ON;
set global log_output=default;
show variables like 'log_output';
Variable_name Value
-log_output TABLE
+log_output FILE
set global general_log=OFF;
set global log_output=FILE;
truncate table mysql.general_log;
@@ -153,3 +153,25 @@ select * from mysql.general_log;
event_time user_host thread_id server_id command_type argument
TIMESTAMP USER_HOST # 1 Query drop table t1
TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log
+SET @old_general_log_state = @@global.general_log;
+SET @old_slow_log_state = @@global.slow_query_log;
+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;
+FLUSH TABLES WITH READ LOCK;
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+UNLOCK TABLES;
+SET GLOBAL READ_ONLY = ON;
+SET GLOBAL general_log = OFF;
+SET GLOBAL slow_query_log = OFF;
+SET GLOBAL READ_ONLY = OFF;
+SET GLOBAL READ_ONLY = ON;
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+SET GLOBAL READ_ONLY = OFF;
+SET GLOBAL general_log = @old_general_log_state;
+SET GLOBAL slow_query_log = @old_slow_log_state;
diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result
index ce3dabe3a56..39349183276 100644
--- a/mysql-test/r/log_tables.result
+++ b/mysql-test/r/log_tables.result
@@ -29,30 +29,83 @@ on (mysql.general_log.command_type = join_test.command_type)
drop table join_test;
flush logs;
lock tables mysql.general_log WRITE;
-ERROR HY000: You can't write-lock a log table. Only read access is possible
+ERROR HY000: You can't use locks with log tables.
lock tables mysql.slow_log WRITE;
-ERROR HY000: You can't write-lock a log table. Only read access is possible
+ERROR HY000: You can't use locks with log tables.
lock tables mysql.general_log READ;
-ERROR HY000: You can't use usual read lock with log tables. Try READ LOCAL instead
+ERROR HY000: You can't use locks with log tables.
lock tables mysql.slow_log READ;
-ERROR HY000: You can't use usual read lock with log tables. Try READ LOCAL instead
+ERROR HY000: You can't use locks with log tables.
lock tables mysql.slow_log READ LOCAL, mysql.general_log READ LOCAL;
-unlock tables;
-lock tables mysql.general_log READ LOCAL;
+ERROR HY000: You can't use locks with log tables.
+show create table mysql.general_log;
+Table Create Table
+general_log CREATE TABLE `general_log` (
+ `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ `user_host` mediumtext,
+ `thread_id` int(11) DEFAULT NULL,
+ `server_id` int(11) DEFAULT NULL,
+ `command_type` varchar(64) DEFAULT NULL,
+ `argument` mediumtext
+) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
+show fields from mysql.general_log;
+Field Type Null Key Default Extra
+event_time timestamp NO CURRENT_TIMESTAMP
+user_host mediumtext YES NULL
+thread_id int(11) YES NULL
+server_id int(11) YES NULL
+command_type varchar(64) YES NULL
+argument mediumtext YES NULL
+show create table mysql.slow_log;
+Table Create Table
+slow_log CREATE TABLE `slow_log` (
+ `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ `user_host` mediumtext NOT NULL,
+ `query_time` time NOT NULL,
+ `lock_time` time NOT NULL,
+ `rows_sent` int(11) NOT NULL,
+ `rows_examined` int(11) NOT NULL,
+ `db` varchar(512) DEFAULT NULL,
+ `last_insert_id` int(11) DEFAULT NULL,
+ `insert_id` int(11) DEFAULT NULL,
+ `server_id` int(11) DEFAULT NULL,
+ `sql_text` mediumtext NOT NULL
+) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
+show fields from mysql.slow_log;
+Field Type Null Key Default Extra
+start_time timestamp NO CURRENT_TIMESTAMP
+user_host mediumtext NO
+query_time time NO
+lock_time time NO
+rows_sent int(11) NO
+rows_examined int(11) NO
+db varchar(512) YES NULL
+last_insert_id int(11) YES NULL
+insert_id int(11) YES NULL
+server_id int(11) YES NULL
+sql_text mediumtext NO
flush logs;
-unlock tables;
-select "Mark that we woke up from flush logs in the test"
- as "test passed";
-test passed
-Mark that we woke up from flush logs in the test
-lock tables mysql.general_log READ LOCAL;
-truncate mysql.general_log;
-unlock tables;
-select "Mark that we woke up from TRUNCATE in the test"
- as "test passed";
-test passed
-Mark that we woke up from TRUNCATE in the test
-use test;
+flush tables;
+SET GLOBAL GENERAL_LOG=ON;
+SET GLOBAL SLOW_QUERY_LOG=ON;
+show open tables;
+Database Table In_use Name_locked
+mysql general_log 0 0
+flush logs;
+show open tables;
+Database Table In_use Name_locked
+mysql general_log 0 0
+flush tables;
+show open tables;
+Database Table In_use Name_locked
+mysql general_log 0 0
+SET GLOBAL GENERAL_LOG=OFF;
+SET GLOBAL SLOW_QUERY_LOG=OFF;
+flush tables;
+show open tables;
+Database Table In_use Name_locked
+SET GLOBAL GENERAL_LOG=ON;
+SET GLOBAL SLOW_QUERY_LOG=ON;
truncate table mysql.general_log;
set names utf8;
create table bug16905 (s char(15) character set utf8 default 'пусто');
@@ -71,7 +124,7 @@ sleep(2)
0
select * from mysql.slow_log;
start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text
-TIMESTAMP USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 select sleep(2)
+TIMESTAMP USER_HOST QUERY_TIME 00:00:00 1 0 mysql 0 0 1 select sleep(2)
alter table mysql.general_log engine=myisam;
ERROR HY000: You cannot 'ALTER' a log table if logging is enabled
alter table mysql.slow_log engine=myisam;
@@ -158,15 +211,13 @@ TIMESTAMP USER_HOST THREAD_ID 1 Query set global slow_query_log='ON'
TIMESTAMP USER_HOST THREAD_ID 1 Query select * from mysql.general_log
flush logs;
lock tables mysql.general_log WRITE;
-ERROR HY000: You can't write-lock a log table. Only read access is possible
+ERROR HY000: You can't use locks with log tables.
lock tables mysql.slow_log WRITE;
-ERROR HY000: You can't write-lock a log table. Only read access is possible
+ERROR HY000: You can't use locks with log tables.
lock tables mysql.general_log READ;
-ERROR HY000: You can't use usual read lock with log tables. Try READ LOCAL instead
+ERROR HY000: You can't use locks with log tables.
lock tables mysql.slow_log READ;
-ERROR HY000: You can't use usual read lock with log tables. Try READ LOCAL instead
-lock tables mysql.slow_log READ LOCAL, mysql.general_log READ LOCAL;
-unlock tables;
+ERROR HY000: You can't use locks with log tables.
set global general_log='OFF';
set global slow_query_log='OFF';
set @save_storage_engine= @@session.storage_engine;
@@ -217,6 +268,7 @@ flush tables with read lock;
unlock tables;
use mysql;
lock tables general_log read local, help_category read local;
+ERROR HY000: You can't use locks with log tables.
unlock tables;
use mysql;
RENAME TABLE general_log TO renamed_general_log;
@@ -258,9 +310,9 @@ RENAME TABLE general_log TO general_log2;
set global slow_query_log='OFF';
RENAME TABLE slow_log TO slow_log2;
set global general_log='ON';
-ERROR HY000: Cannot activate 'general' log
+ERROR 42S02: Table 'mysql.general_log' doesn't exist
set global slow_query_log='ON';
-ERROR HY000: Cannot activate 'slow query' log
+ERROR 42S02: Table 'mysql.slow_log' doesn't exist
RENAME TABLE general_log2 TO general_log;
RENAME TABLE slow_log2 TO slow_log;
set global general_log='ON';
@@ -355,3 +407,192 @@ SET SESSION long_query_time =@old_long_query_time;
FLUSH LOGS;
ALTER TABLE mysql.slow_log DROP COLUMN seq;
ALTER TABLE mysql.slow_log ENGINE = CSV;
+drop procedure if exists proc25422_truncate_slow;
+drop procedure if exists proc25422_truncate_general;
+drop procedure if exists proc25422_alter_slow;
+drop procedure if exists proc25422_alter_general;
+use test//
+create procedure proc25422_truncate_slow (loops int)
+begin
+declare v1 int default 0;
+while v1 < loops do
+truncate mysql.slow_log;
+set v1 = v1 + 1;
+end while;
+end//
+create procedure proc25422_truncate_general (loops int)
+begin
+declare v1 int default 0;
+while v1 < loops do
+truncate mysql.general_log;
+set v1 = v1 + 1;
+end while;
+end//
+create procedure proc25422_alter_slow (loops int)
+begin
+declare v1 int default 0;
+declare ER_BAD_LOG_STATEMENT condition for 1575;
+declare continue handler for ER_BAD_LOG_STATEMENT begin end;
+while v1 < loops do
+set @old_log_state = @@global.slow_query_log;
+set global slow_query_log = 'OFF';
+alter table mysql.slow_log engine = CSV;
+set global slow_query_log = @old_log_state;
+set v1 = v1 + 1;
+end while;
+end//
+create procedure proc25422_alter_general (loops int)
+begin
+declare v1 int default 0;
+declare ER_BAD_LOG_STATEMENT condition for 1575;
+declare continue handler for ER_BAD_LOG_STATEMENT begin end;
+while v1 < loops do
+set @old_log_state = @@global.general_log;
+set global general_log = 'OFF';
+alter table mysql.general_log engine = CSV;
+set global general_log = @old_log_state;
+set v1 = v1 + 1;
+end while;
+end//
+"Serial test (proc25422_truncate_slow)"
+call proc25422_truncate_slow(100);
+"Serial test (proc25422_truncate_general)"
+call proc25422_truncate_general(100);
+"Serial test (proc25422_alter_slow)"
+call proc25422_alter_slow(100);
+"Serial test (proc25422_alter_general)"
+call proc25422_alter_general(100);
+"Parallel test"
+call proc25422_truncate_slow(100);
+call proc25422_truncate_slow(100);
+call proc25422_truncate_general(100);
+call proc25422_truncate_general(100);
+call proc25422_alter_slow(100);
+call proc25422_alter_slow(100);
+call proc25422_alter_general(100);
+call proc25422_alter_general(100);
+drop procedure proc25422_truncate_slow;
+drop procedure proc25422_truncate_general;
+drop procedure proc25422_alter_slow;
+drop procedure proc25422_alter_general;
+FLUSH TABLE mysql.general_log;
+show warnings;
+Level Code Message
+FLUSH TABLE mysql.slow_log;
+show warnings;
+Level Code Message
+DROP TABLE IF EXISTS `db_17876.slow_log_data`;
+DROP TABLE IF EXISTS `db_17876.general_log_data`;
+DROP PROCEDURE IF EXISTS `db_17876.archiveSlowLog`;
+DROP PROCEDURE IF EXISTS `db_17876.archiveGeneralLog`;
+DROP DATABASE IF EXISTS `db_17876`;
+CREATE DATABASE db_17876;
+CREATE TABLE `db_17876.slow_log_data` (
+`start_time` timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+`user_host` mediumtext ,
+`query_time` time ,
+`lock_time` time ,
+`rows_sent` int(11) ,
+`rows_examined` int(11) ,
+`db` varchar(512) default NULL,
+`last_insert_id` int(11) default NULL,
+`insert_id` int(11) default NULL,
+`server_id` int(11) default NULL,
+`sql_text` mediumtext
+);
+CREATE TABLE `db_17876.general_log_data` (
+`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+`user_host` mediumtext,
+`thread_id` int(11) DEFAULT NULL,
+`server_id` int(11) DEFAULT NULL,
+`command_type` varchar(64) DEFAULT NULL,
+`argument` mediumtext
+);
+CREATE procedure `db_17876.archiveSlowLog`()
+BEGIN
+DECLARE start_time, query_time, lock_time CHAR(20);
+DECLARE user_host MEDIUMTEXT;
+DECLARE rows_set, rows_examined, last_insert_id, insert_id, server_id INT;
+DECLARE dbname MEDIUMTEXT;
+DECLARE sql_text BLOB;
+DECLARE done INT DEFAULT 0;
+DECLARE ER_SP_FETCH_NO_DATA CONDITION for 1329;
+DECLARE cur1 CURSOR FOR SELECT * FROM mysql.slow_log;
+OPEN cur1;
+REPEAT
+BEGIN
+BEGIN
+DECLARE CONTINUE HANDLER FOR ER_SP_FETCH_NO_DATA SET done = 1;
+FETCH cur1 INTO
+start_time, user_host, query_time, lock_time,
+rows_set, rows_examined, dbname, last_insert_id,
+insert_id, server_id, sql_text;
+END;
+IF NOT done THEN
+BEGIN
+INSERT INTO
+`db_17876.slow_log_data`
+ VALUES(start_time, user_host, query_time, lock_time, rows_set, rows_examined,
+dbname, last_insert_id, insert_id, server_id, sql_text);
+END;
+END IF;
+END;
+UNTIL done END REPEAT;
+CLOSE cur1;
+TRUNCATE mysql.slow_log;
+END //
+CREATE procedure `db_17876.archiveGeneralLog`()
+BEGIN
+DECLARE event_time CHAR(20);
+DECLARE user_host, argument MEDIUMTEXT;
+DECLARE thread_id, server_id INT;
+DECLARE sql_text BLOB;
+DECLARE done INT DEFAULT 0;
+DECLARE command_type VARCHAR(64);
+DECLARE ER_SP_FETCH_NO_DATA CONDITION for 1329;
+DECLARE cur1 CURSOR FOR SELECT * FROM mysql.general_log;
+OPEN cur1;
+REPEAT
+BEGIN
+BEGIN
+DECLARE CONTINUE HANDLER FOR ER_SP_FETCH_NO_DATA SET done = 1;
+FETCH cur1 INTO
+event_time, user_host, thread_id, server_id,
+command_type, argument;
+END;
+IF NOT done THEN
+BEGIN
+INSERT INTO
+`db_17876.general_log_data`
+ VALUES(event_time, user_host, thread_id, server_id,
+command_type, argument);
+END;
+END IF;
+END;
+UNTIL done END REPEAT;
+CLOSE cur1;
+TRUNCATE mysql.general_log;
+END //
+SET @old_general_log_state = @@global.general_log;
+SET @old_slow_log_state = @@global.slow_query_log;
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+select "put something into general_log";
+put something into general_log
+put something into general_log
+select "... and something more ...";
+... and something more ...
+... and something more ...
+call `db_17876.archiveSlowLog`();
+call `db_17876.archiveGeneralLog`();
+SET GLOBAL general_log = OFF;
+SET GLOBAL slow_query_log = OFF;
+call `db_17876.archiveSlowLog`();
+call `db_17876.archiveGeneralLog`();
+DROP TABLE `db_17876.slow_log_data`;
+DROP TABLE `db_17876.general_log_data`;
+DROP PROCEDURE IF EXISTS `db_17876.archiveSlowLog`;
+DROP PROCEDURE IF EXISTS `db_17876.archiveGeneralLog`;
+DROP DATABASE IF EXISTS `db_17876`;
+SET GLOBAL general_log = @old_general_log_state;
+SET GLOBAL slow_query_log = @old_slow_log_state;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 9214d470b37..341a6b7cd43 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -1850,61 +1850,57 @@ create procedure proc_1() flush tables;
flush tables;
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
select Host, User from mysql.user limit 0;
Host User
select Host, Db from mysql.host limit 0;
Host Db
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
+mysql slow_log 0 0
mysql host 0 0
mysql user 0 0
call proc_1();
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
select Host, User from mysql.user limit 0;
Host User
select Host, Db from mysql.host limit 0;
Host Db
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
+mysql slow_log 0 0
mysql host 0 0
mysql user 0 0
call proc_1();
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
select Host, User from mysql.user limit 0;
Host User
select Host, Db from mysql.host limit 0;
Host Db
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
+mysql slow_log 0 0
mysql host 0 0
mysql user 0 0
call proc_1();
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
select Host, User from mysql.user limit 0;
Host User
select Host, Db from mysql.host limit 0;
Host Db
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
+mysql slow_log 0 0
mysql host 0 0
mysql user 0 0
flush tables;
@@ -1922,54 +1918,50 @@ select Host, Db from mysql.host limit 0;
Host Db
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
-mysql host 0 0
mysql user 0 0
+mysql general_log 0 0
+mysql host 0 0
prepare abc from "flush tables";
execute abc;
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
select Host, User from mysql.user limit 0;
Host User
select Host, Db from mysql.host limit 0;
Host Db
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
+mysql slow_log 0 0
mysql host 0 0
mysql user 0 0
execute abc;
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
select Host, User from mysql.user limit 0;
Host User
select Host, Db from mysql.host limit 0;
Host Db
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
+mysql slow_log 0 0
mysql host 0 0
mysql user 0 0
execute abc;
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
select Host, User from mysql.user limit 0;
Host User
select Host, Db from mysql.host limit 0;
Host Db
show open tables from mysql;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
+mysql slow_log 0 0
mysql host 0 0
mysql user 0 0
flush tables;
diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
index 2bdd29602fb..9f79c34cd2c 100644
--- a/mysql-test/r/show_check.result
+++ b/mysql-test/r/show_check.result
@@ -251,14 +251,13 @@ drop table t1;
flush tables;
show open tables;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
create table t1(n int);
insert into t1 values (1);
show open tables;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
+mysql slow_log 0 0
test t1 0 0
drop table t1;
create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed;
@@ -672,24 +671,23 @@ SELECT 1 FROM mysql.db, mysql.proc, mysql.user, mysql.time_zone, mysql.time_zone
1
SHOW OPEN TABLES;
Database Table In_use Name_locked
-mysql proc 0 0
+mysql db 0 0
test urkunde 0 0
mysql time_zone 0 0
-mysql db 0 0
+mysql general_log 0 0
test txt1 0 0
-mysql slow_log 1 0
+mysql proc 0 0
test tyt2 0 0
-mysql general_log 1 0
mysql user 0 0
mysql time_zone_name 0 0
SHOW OPEN TABLES FROM mysql;
Database Table In_use Name_locked
-mysql proc 0 0
-mysql time_zone 0 0
mysql db 0 0
-mysql slow_log 1 0
-mysql general_log 1 0
+mysql time_zone 0 0
+mysql general_log 0 0
+mysql slow_log 0 0
mysql user 0 0
+mysql proc 0 0
mysql time_zone_name 0 0
SHOW OPEN TABLES FROM mysql LIKE 'u%';
Database Table In_use Name_locked
@@ -702,16 +700,15 @@ test tyt2 0 0
mysql time_zone_name 0 0
SHOW OPEN TABLES LIKE '%o%';
Database Table In_use Name_locked
-mysql proc 0 0
mysql time_zone 0 0
-mysql slow_log 1 0
-mysql general_log 1 0
+mysql general_log 0 0
+mysql slow_log 0 0
+mysql proc 0 0
mysql time_zone_name 0 0
FLUSH TABLES;
SHOW OPEN TABLES;
Database Table In_use Name_locked
-mysql general_log 1 0
-mysql slow_log 1 0
+mysql general_log 0 0
DROP TABLE txt1;
DROP TABLE tyt2;
DROP TABLE urkunde;
diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result
index b6dcbc251d7..8f10625744b 100644
--- a/mysql-test/r/status.result
+++ b/mysql-test/r/status.result
@@ -1,11 +1,11 @@
flush status;
show status like 'Table_lock%';
Variable_name Value
-Table_locks_immediate 0
+Table_locks_immediate 1
Table_locks_waited 0
select * from information_schema.session_status where variable_name like 'Table_lock%';
VARIABLE_NAME VARIABLE_VALUE
-TABLE_LOCKS_IMMEDIATE 0
+TABLE_LOCKS_IMMEDIATE 2
TABLE_LOCKS_WAITED 0
SET SQL_LOG_BIN=0;
drop table if exists t1;
@@ -18,11 +18,11 @@ update t1 set n = 3;
unlock tables;
show status like 'Table_lock%';
Variable_name Value
-Table_locks_immediate 3
+Table_locks_immediate 17
Table_locks_waited 1
select * from information_schema.session_status where variable_name like 'Table_lock%';
VARIABLE_NAME VARIABLE_VALUE
-TABLE_LOCKS_IMMEDIATE 3
+TABLE_LOCKS_IMMEDIATE 18
TABLE_LOCKS_WAITED 1
drop table t1;
select 1;
@@ -97,7 +97,7 @@ Variable_name Value
Com_show_status 3
show status like 'hand%write%';
Variable_name Value
-Handler_write 0
+Handler_write 5
show status like '%tmp%';
Variable_name Value
Created_tmp_disk_tables 0
@@ -105,7 +105,7 @@ Created_tmp_files 0
Created_tmp_tables 0
show status like 'hand%write%';
Variable_name Value
-Handler_write 0
+Handler_write 7
show status like '%tmp%';
Variable_name Value
Created_tmp_disk_tables 0
diff --git a/mysql-test/t/log_state.test b/mysql-test/t/log_state.test
index 16f466e9b54..b0bb818b783 100644
--- a/mysql-test/t/log_state.test
+++ b/mysql-test/t/log_state.test
@@ -126,6 +126,59 @@ drop table t1;
--replace_column 1 TIMESTAMP 2 USER_HOST 3 #
select * from mysql.general_log;
+#
+# 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;
+
--enable_ps_protocol
#
diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test
index e8eff7cba0a..8baa4e5a373 100644
--- a/mysql-test/t/log_tables.test
+++ b/mysql-test/t/log_tables.test
@@ -64,10 +64,10 @@ flush logs;
# check locking of the log tables
#
---error ER_CANT_WRITE_LOCK_LOG_TABLE
+--error ER_CANT_LOCK_LOG_TABLE
lock tables mysql.general_log WRITE;
---error ER_CANT_WRITE_LOCK_LOG_TABLE
+--error ER_CANT_LOCK_LOG_TABLE
lock tables mysql.slow_log WRITE;
#
@@ -76,78 +76,59 @@ lock tables mysql.slow_log WRITE;
# tables are always opened and locked by the logger.
#
---error ER_CANT_READ_LOCK_LOG_TABLE
+--error ER_CANT_LOCK_LOG_TABLE
lock tables mysql.general_log READ;
---error ER_CANT_READ_LOCK_LOG_TABLE
+--error ER_CANT_LOCK_LOG_TABLE
lock tables mysql.slow_log READ;
#
-# This call should result in TL_READ lock on the log table. This is ok and
-# should pass.
+# This call should result in TL_READ lock on the log table.
+# This is not ok and should fail.
#
+--error ER_CANT_LOCK_LOG_TABLE
lock tables mysql.slow_log READ LOCAL, mysql.general_log READ LOCAL;
-unlock tables;
-
-#
-# check that FLUSH LOGS waits for all readers of the log table to vanish
-#
-
-connect (con1,localhost,root,,);
-connect (con2,localhost,root,,);
-
-connection con1;
-
-lock tables mysql.general_log READ LOCAL;
-
-connection con2;
+# Misc locking tests
-# this should wait for log tables to unlock
-send flush logs;
-
-connection con1;
-
-unlock tables;
-
-# this connection should be alive by the time
-connection con2;
-
-reap;
+show create table mysql.general_log;
+show fields from mysql.general_log;
-select "Mark that we woke up from flush logs in the test"
- as "test passed";
+show create table mysql.slow_log;
+show fields from mysql.slow_log;
#
-# perform the same check for TRUNCATE: it should also wait for readers
-# to disappear
+# check that FLUSH LOGS does not flush the log tables
#
-connection con1;
-
-lock tables mysql.general_log READ LOCAL;
-
-connection con2;
-
-# this should wait for log tables to unlock
-send truncate mysql.general_log;
+flush logs;
+flush tables;
-connection con1;
+SET GLOBAL GENERAL_LOG=ON;
+SET GLOBAL SLOW_QUERY_LOG=ON;
-unlock tables;
+show open tables;
+flush logs;
+show open tables;
-# this connection should be alive by the time
-connection con2;
+#
+# check that FLUSH TABLES does flush the log tables
+#
-reap;
+flush tables;
+# Since the flush is logged, mysql.general_log will be in the cache
+show open tables;
-select "Mark that we woke up from TRUNCATE in the test"
- as "test passed";
+SET GLOBAL GENERAL_LOG=OFF;
+SET GLOBAL SLOW_QUERY_LOG=OFF;
-connection con1;
+flush tables;
+# Here the table cache is empty
+show open tables;
-use test;
+SET GLOBAL GENERAL_LOG=ON;
+SET GLOBAL SLOW_QUERY_LOG=ON;
#
# Bug #16905 Log tables: unicode statements are logged incorrectly
@@ -220,10 +201,10 @@ flush logs;
# check locking of myisam-based log tables
---error ER_CANT_WRITE_LOCK_LOG_TABLE
+--error ER_CANT_LOCK_LOG_TABLE
lock tables mysql.general_log WRITE;
---error ER_CANT_WRITE_LOCK_LOG_TABLE
+--error ER_CANT_LOCK_LOG_TABLE
lock tables mysql.slow_log WRITE;
#
@@ -232,21 +213,12 @@ lock tables mysql.slow_log WRITE;
# tables are always opened and locked by the logger.
#
---error ER_CANT_READ_LOCK_LOG_TABLE
+--error ER_CANT_LOCK_LOG_TABLE
lock tables mysql.general_log READ;
---error ER_CANT_READ_LOCK_LOG_TABLE
+--error ER_CANT_LOCK_LOG_TABLE
lock tables mysql.slow_log READ;
-#
-# This call should result in TL_READ lock on the log table. This is ok and
-# should pass.
-#
-
-lock tables mysql.slow_log READ LOCAL, mysql.general_log READ LOCAL;
-
-unlock tables;
-
# check that we can drop them
set global general_log='OFF';
set global slow_query_log='OFF';
@@ -314,6 +286,7 @@ use test;
flush tables with read lock;
unlock tables;
use mysql;
+--error ER_CANT_LOCK_LOG_TABLE
lock tables general_log read local, help_category read local;
unlock tables;
@@ -368,9 +341,9 @@ set global slow_query_log='OFF';
RENAME TABLE slow_log TO slow_log2;
# this should fail
---error ER_CANT_ACTIVATE_LOG
+--error ER_NO_SUCH_TABLE
set global general_log='ON';
---error ER_CANT_ACTIVATE_LOG
+--error ER_NO_SUCH_TABLE
set global slow_query_log='ON';
RENAME TABLE general_log2 TO general_log;
@@ -470,8 +443,305 @@ FLUSH LOGS;
ALTER TABLE mysql.slow_log DROP COLUMN seq;
ALTER TABLE mysql.slow_log ENGINE = CSV;
-# kill all connections
-disconnect con1;
-disconnect con2;
+#
+# Bug#25422 (Hang with log tables)
+#
+
+--disable_warnings
+drop procedure if exists proc25422_truncate_slow;
+drop procedure if exists proc25422_truncate_general;
+drop procedure if exists proc25422_alter_slow;
+drop procedure if exists proc25422_alter_general;
+--enable_warnings
+
+delimiter //;
+
+use test//
+create procedure proc25422_truncate_slow (loops int)
+begin
+ declare v1 int default 0;
+ while v1 < loops do
+ truncate mysql.slow_log;
+ set v1 = v1 + 1;
+ end while;
+end//
+
+create procedure proc25422_truncate_general (loops int)
+begin
+ declare v1 int default 0;
+ while v1 < loops do
+ truncate mysql.general_log;
+ set v1 = v1 + 1;
+ end while;
+end//
+
+create procedure proc25422_alter_slow (loops int)
+begin
+ declare v1 int default 0;
+ declare ER_BAD_LOG_STATEMENT condition for 1575;
+ declare continue handler for ER_BAD_LOG_STATEMENT begin end;
+
+ while v1 < loops do
+ set @old_log_state = @@global.slow_query_log;
+ set global slow_query_log = 'OFF';
+ alter table mysql.slow_log engine = CSV;
+ set global slow_query_log = @old_log_state;
+ set v1 = v1 + 1;
+ end while;
+end//
+
+create procedure proc25422_alter_general (loops int)
+begin
+ declare v1 int default 0;
+ declare ER_BAD_LOG_STATEMENT condition for 1575;
+ declare continue handler for ER_BAD_LOG_STATEMENT begin end;
+
+ while v1 < loops do
+ set @old_log_state = @@global.general_log;
+ set global general_log = 'OFF';
+ alter table mysql.general_log engine = CSV;
+ set global general_log = @old_log_state;
+ set v1 = v1 + 1;
+ end while;
+end//
+
+delimiter ;//
+
+--echo "Serial test (proc25422_truncate_slow)"
+call proc25422_truncate_slow(100);
+--echo "Serial test (proc25422_truncate_general)"
+call proc25422_truncate_general(100);
+--echo "Serial test (proc25422_alter_slow)"
+call proc25422_alter_slow(100);
+--echo "Serial test (proc25422_alter_general)"
+call proc25422_alter_general(100);
+
+--echo "Parallel test"
+
+# ER_BAD_LOG_STATEMENT errors will occur,
+# since concurrent threads do SET GLOBAL general_log= ...
+# This is silenced by handlers and will not affect the test
+
+connect (addconroot1, localhost, root,,);
+connect (addconroot2, localhost, root,,);
+connect (addconroot3, localhost, root,,);
+connect (addconroot4, localhost, root,,);
+connect (addconroot5, localhost, root,,);
+connect (addconroot6, localhost, root,,);
+connect (addconroot7, localhost, root,,);
+connect (addconroot8, localhost, root,,);
+
+connection addconroot1;
+send call proc25422_truncate_slow(100);
+connection addconroot2;
+send call proc25422_truncate_slow(100);
+
+connection addconroot3;
+send call proc25422_truncate_general(100);
+connection addconroot4;
+send call proc25422_truncate_general(100);
+
+connection addconroot5;
+send call proc25422_alter_slow(100);
+connection addconroot6;
+send call proc25422_alter_slow(100);
+
+connection addconroot7;
+send call proc25422_alter_general(100);
+connection addconroot8;
+send call proc25422_alter_general(100);
+
+connection addconroot1;
+reap;
+connection addconroot2;
+reap;
+connection addconroot3;
+reap;
+connection addconroot4;
+reap;
+connection addconroot5;
+reap;
+connection addconroot6;
+reap;
+connection addconroot7;
+reap;
+connection addconroot8;
+reap;
+
+connection default;
+
+disconnect addconroot1;
+disconnect addconroot2;
+disconnect addconroot3;
+disconnect addconroot4;
+disconnect addconroot5;
+disconnect addconroot6;
+disconnect addconroot7;
+disconnect addconroot8;
+
+drop procedure proc25422_truncate_slow;
+drop procedure proc25422_truncate_general;
+drop procedure proc25422_alter_slow;
+drop procedure proc25422_alter_general;
+
--enable_ps_protocol
+
+#
+# Bug#23044 (Warnings on flush of a log table)
+#
+
+FLUSH TABLE mysql.general_log;
+show warnings;
+
+FLUSH TABLE mysql.slow_log;
+show warnings;
+
+#
+# Bug#17876 (Truncating mysql.slow_log in a SP after using cursor locks the
+# thread)
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS `db_17876.slow_log_data`;
+DROP TABLE IF EXISTS `db_17876.general_log_data`;
+DROP PROCEDURE IF EXISTS `db_17876.archiveSlowLog`;
+DROP PROCEDURE IF EXISTS `db_17876.archiveGeneralLog`;
+DROP DATABASE IF EXISTS `db_17876`;
+--enable_warnings
+
+CREATE DATABASE db_17876;
+
+CREATE TABLE `db_17876.slow_log_data` (
+ `start_time` timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+ `user_host` mediumtext ,
+ `query_time` time ,
+ `lock_time` time ,
+ `rows_sent` int(11) ,
+ `rows_examined` int(11) ,
+ `db` varchar(512) default NULL,
+ `last_insert_id` int(11) default NULL,
+ `insert_id` int(11) default NULL,
+ `server_id` int(11) default NULL,
+ `sql_text` mediumtext
+);
+
+CREATE TABLE `db_17876.general_log_data` (
+ `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ `user_host` mediumtext,
+ `thread_id` int(11) DEFAULT NULL,
+ `server_id` int(11) DEFAULT NULL,
+ `command_type` varchar(64) DEFAULT NULL,
+ `argument` mediumtext
+);
+
+DELIMITER //;
+
+CREATE procedure `db_17876.archiveSlowLog`()
+BEGIN
+ DECLARE start_time, query_time, lock_time CHAR(20);
+ DECLARE user_host MEDIUMTEXT;
+ DECLARE rows_set, rows_examined, last_insert_id, insert_id, server_id INT;
+ DECLARE dbname MEDIUMTEXT;
+ DECLARE sql_text BLOB;
+ DECLARE done INT DEFAULT 0;
+ DECLARE ER_SP_FETCH_NO_DATA CONDITION for 1329;
+
+ DECLARE cur1 CURSOR FOR SELECT * FROM mysql.slow_log;
+
+ OPEN cur1;
+
+ REPEAT
+ BEGIN
+ BEGIN
+ DECLARE CONTINUE HANDLER FOR ER_SP_FETCH_NO_DATA SET done = 1;
+
+ FETCH cur1 INTO
+ start_time, user_host, query_time, lock_time,
+ rows_set, rows_examined, dbname, last_insert_id,
+ insert_id, server_id, sql_text;
+ END;
+
+ IF NOT done THEN
+ BEGIN
+ INSERT INTO
+ `db_17876.slow_log_data`
+ VALUES(start_time, user_host, query_time, lock_time, rows_set, rows_examined,
+ dbname, last_insert_id, insert_id, server_id, sql_text);
+ END;
+ END IF;
+ END;
+ UNTIL done END REPEAT;
+
+ CLOSE cur1;
+ TRUNCATE mysql.slow_log;
+END //
+
+CREATE procedure `db_17876.archiveGeneralLog`()
+BEGIN
+ DECLARE event_time CHAR(20);
+ DECLARE user_host, argument MEDIUMTEXT;
+ DECLARE thread_id, server_id INT;
+ DECLARE sql_text BLOB;
+ DECLARE done INT DEFAULT 0;
+ DECLARE command_type VARCHAR(64);
+ DECLARE ER_SP_FETCH_NO_DATA CONDITION for 1329;
+
+ DECLARE cur1 CURSOR FOR SELECT * FROM mysql.general_log;
+
+ OPEN cur1;
+
+ REPEAT
+ BEGIN
+ BEGIN
+ DECLARE CONTINUE HANDLER FOR ER_SP_FETCH_NO_DATA SET done = 1;
+
+ FETCH cur1 INTO
+ event_time, user_host, thread_id, server_id,
+ command_type, argument;
+ END;
+
+ IF NOT done THEN
+ BEGIN
+ INSERT INTO
+ `db_17876.general_log_data`
+ VALUES(event_time, user_host, thread_id, server_id,
+ command_type, argument);
+ END;
+ END IF;
+ END;
+ UNTIL done END REPEAT;
+
+ CLOSE cur1;
+ TRUNCATE mysql.general_log;
+END //
+
+DELIMITER ;//
+
+SET @old_general_log_state = @@global.general_log;
+SET @old_slow_log_state = @@global.slow_query_log;
+
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+
+select "put something into general_log";
+select "... and something more ...";
+
+call `db_17876.archiveSlowLog`();
+call `db_17876.archiveGeneralLog`();
+
+SET GLOBAL general_log = OFF;
+SET GLOBAL slow_query_log = OFF;
+
+call `db_17876.archiveSlowLog`();
+call `db_17876.archiveGeneralLog`();
+
+DROP TABLE `db_17876.slow_log_data`;
+DROP TABLE `db_17876.general_log_data`;
+DROP PROCEDURE IF EXISTS `db_17876.archiveSlowLog`;
+DROP PROCEDURE IF EXISTS `db_17876.archiveGeneralLog`;
+DROP DATABASE IF EXISTS `db_17876`;
+
+SET GLOBAL general_log = @old_general_log_state;
+SET GLOBAL slow_query_log = @old_slow_log_state;
+
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 394f44bbc83..5a4d30fdd50 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -2018,10 +2018,17 @@ delimiter ;|
select func_1(), func_1(), func_1() from dual;
drop function func_1;
drop procedure proc_1;
+
+# make the output deterministic:
+# the order used in SHOW OPEN TABLES
+# is too much implementation dependent
+--disable_ps_protocol
flush tables;
select Host, User from mysql.user limit 0;
select Host, Db from mysql.host limit 0;
show open tables from mysql;
+--enable_ps_protocol
+
prepare abc from "flush tables";
execute abc;
show open tables from mysql;
diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test
index 09c0b08a3cd..5d7d8d3130d 100644
--- a/mysql-test/t/show_check.test
+++ b/mysql-test/t/show_check.test
@@ -438,6 +438,11 @@ drop table if exists t1;
CREATE TABLE txt1(a int);
CREATE TABLE tyt2(a int);
CREATE TABLE urkunde(a int);
+
+# make the output deterministic:
+# the order used in SHOW OPEN TABLES
+# is too much implementation dependent
+--disable_ps_protocol
FLUSH TABLES;
SELECT 1 FROM mysql.db, mysql.proc, mysql.user, mysql.time_zone, mysql.time_zone_name, txt1, tyt2, urkunde LIMIT 0;
SHOW OPEN TABLES;
@@ -447,6 +452,8 @@ SHOW OPEN TABLES LIKE 't%';
SHOW OPEN TABLES LIKE '%o%';
FLUSH TABLES;
SHOW OPEN TABLES;
+--enable_ps_protocol
+
DROP TABLE txt1;
DROP TABLE tyt2;
DROP TABLE urkunde;
View Lorry Controller Status