summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/log_tables.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/log_tables.test
parentInitial commit. (diff)
downloadmariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz
mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/log_tables.test')
-rw-r--r--mysql-test/main/log_tables.test1091
1 files changed, 1091 insertions, 0 deletions
diff --git a/mysql-test/main/log_tables.test b/mysql-test/main/log_tables.test
new file mode 100644
index 00000000..1eee6be0
--- /dev/null
+++ b/mysql-test/main/log_tables.test
@@ -0,0 +1,1091 @@
+# this test needs multithreaded mysqltest
+-- source include/not_embedded.inc
+#
+# Basic log tables test
+#
+# check that CSV engine was compiled in
+--source include/have_csv.inc
+
+SET SQL_MODE="";
+SET @old_general_log_state = @@global.general_log;
+SET @old_log_output= @@global.log_output;
+SET @old_slow_query_log= @@global.slow_query_log;
+SET @old_general_log= @@global.general_log;
+SET @old_long_query_time= @@session.long_query_time;
+
+--disable_ps_protocol
+use mysql;
+
+# Capture initial settings of system variables
+# so that we can revert to old state after manipulation for testing
+# NOTE: PLEASE USE THESE VALUES TO 'RESET' SYSTEM VARIABLES
+# Capturing old values within the tests results in loss of values
+# due to people not paying attention to previous tests' changes, captures
+# or improper cleanup
+SET @saved_long_query_time = @@long_query_time;
+SET @saved_log_output = @@log_output;
+SET @saved_general_log = @@GLOBAL.general_log;
+SET @saved_slow_query_log = @@GLOBAL.slow_query_log;
+
+#
+# Check that log tables work and we can do basic selects. This also
+# tests truncate, which works in a special mode with the log tables
+#
+
+truncate table general_log;
+--replace_column 1 TIMESTAMP 2 USER_HOST 3 THREAD_ID
+select * from general_log;
+truncate table slow_log;
+--replace_column 1 TIMESTAMP 2 USER_HOST
+select * from slow_log;
+
+#
+# We want to check that a record newly written to a log table shows up for
+# the query: since log tables use concurrent insert machinery and log tables
+# are always locked by artificial THD, this feature requires additional
+# check in ha_tina::write_row. This simple test should prove that the
+# log table flag in the table handler is triggered and working.
+#
+
+truncate table general_log;
+--replace_column 1 TIMESTAMP 2 USER_HOST 3 THREAD_ID
+select * from general_log where argument like '%general_log%';
+
+
+#
+# Check some basic queries interfering with the log tables.
+# In our test we'll use a table with verbose comments to the short
+# command type names, used in the tables
+#
+
+create table join_test (verbose_comment varchar (80), command_type varchar(64));
+
+insert into join_test values ("User performed a usual SQL query", "Query");
+insert into join_test values ("New DB connection was registered", "Connect");
+insert into join_test values ("Get the table info", "Field List");
+
+--replace_column 2 USER_HOST
+select verbose_comment, user_host, argument
+ from mysql.general_log join join_test
+ on (mysql.general_log.command_type = join_test.command_type);
+
+drop table join_test;
+
+#
+# check that flush of the log table work fine
+#
+
+flush logs;
+
+#
+# check locking of the log tables
+#
+
+--error ER_CANT_LOCK_LOG_TABLE
+lock tables mysql.general_log WRITE;
+
+--error ER_CANT_LOCK_LOG_TABLE
+lock tables mysql.slow_log WRITE;
+
+#
+# This attemts to get TL_READ_NO_INSERT lock, which is incompatible with
+# TL_WRITE_CONCURRENT_INSERT. This should fail. We issue this error as log
+# tables are always opened and locked by the logger.
+#
+
+--error ER_CANT_LOCK_LOG_TABLE
+lock tables mysql.general_log READ;
+
+--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 not ok and should fail.
+#
+
+--error ER_CANT_LOCK_LOG_TABLE
+lock tables mysql.slow_log READ LOCAL, mysql.general_log READ LOCAL;
+
+# Misc locking tests
+
+show create table mysql.general_log;
+show fields from mysql.general_log;
+
+show create table mysql.slow_log;
+show fields from mysql.slow_log;
+
+#
+# check that FLUSH LOGS does not flush the log tables
+#
+
+flush logs;
+flush tables;
+
+SET GLOBAL GENERAL_LOG=ON;
+SET GLOBAL SLOW_QUERY_LOG=ON;
+
+show open tables;
+flush logs;
+show open tables;
+
+#
+# check that FLUSH TABLES does flush the log tables
+#
+
+flush tables;
+# Since the flush is logged, mysql.general_log will be in the cache
+show open tables;
+
+SET GLOBAL GENERAL_LOG=OFF;
+SET GLOBAL SLOW_QUERY_LOG=OFF;
+
+flush tables;
+# Here the table cache is empty
+show open tables;
+
+SET GLOBAL GENERAL_LOG=ON;
+SET GLOBAL SLOW_QUERY_LOG=ON;
+
+#
+# Bug#23924 general_log truncates queries with character set introducers.
+#
+truncate table mysql.general_log;
+set names binary;
+select _koi8r'ΤΕΣΤ' as test;
+--replace_column 1 TIMESTAMP 2 USER_HOST 3 THREAD_ID
+select * from mysql.general_log;
+set names utf8;
+
+#
+# Bug #16905 Log tables: unicode statements are logged incorrectly
+#
+
+truncate table mysql.general_log;
+set names utf8;
+create table bug16905 (s char(15) character set utf8 default 'пусто');
+insert into bug16905 values ('Π½ΠΎΠ²ΠΎΠ΅');
+--replace_column 1 TIMESTAMP 2 USER_HOST 3 THREAD_ID
+select * from mysql.general_log;
+drop table bug16905;
+
+#
+# Bug #17600: Invalid data logged into mysql.slow_log
+#
+
+truncate table mysql.slow_log;
+set session long_query_time=1;
+select sleep(2);
+--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME 12 THREAD_ID
+select * from mysql.slow_log;
+set @@session.long_query_time = @saved_long_query_time;
+
+#
+# Bug #18559 log tables cannot change engine, and gets deadlocked when
+# dropping w/ log on
+#
+
+# check that appropriate error messages are given when one attempts to alter
+# or drop a log tables, while corresponding logs are enabled
+--error ER_BAD_LOG_STATEMENT
+alter table mysql.general_log engine=myisam;
+--error ER_BAD_LOG_STATEMENT
+alter table mysql.slow_log engine=myisam;
+
+--error ER_BAD_LOG_STATEMENT
+drop table mysql.general_log;
+--error ER_BAD_LOG_STATEMENT
+drop table mysql.slow_log;
+
+# check that one can alter log tables to MyISAM
+set global general_log='OFF';
+
+# cannot convert another log table
+--error ER_BAD_LOG_STATEMENT
+alter table mysql.slow_log engine=myisam;
+
+# alter both tables
+set global slow_query_log='OFF';
+# check that both tables use CSV engine
+show create table mysql.general_log;
+show create table mysql.slow_log;
+
+alter table mysql.general_log engine=myisam;
+alter table mysql.slow_log engine=myisam;
+
+# check that the tables were converted
+show create table mysql.general_log;
+show create table mysql.slow_log;
+
+# enable log tables and chek that new tables indeed work
+set global general_log='ON';
+set global slow_query_log='ON';
+
+--replace_column 1 TIMESTAMP 2 USER_HOST 3 THREAD_ID
+select * from mysql.general_log;
+
+# check that flush of myisam-based log tables work fine
+flush logs;
+
+# check locking of myisam-based log tables
+
+--error ER_CANT_LOCK_LOG_TABLE
+lock tables mysql.general_log WRITE;
+
+--error ER_CANT_LOCK_LOG_TABLE
+lock tables mysql.slow_log WRITE;
+
+#
+# This attemts to get TL_READ_NO_INSERT lock, which is incompatible with
+# TL_WRITE_CONCURRENT_INSERT. This should fail. We issue this error as log
+# tables are always opened and locked by the logger.
+#
+
+--error ER_CANT_LOCK_LOG_TABLE
+lock tables mysql.general_log READ;
+
+--error ER_CANT_LOCK_LOG_TABLE
+lock tables mysql.slow_log READ;
+
+# check that we can drop them
+set global general_log='OFF';
+set global slow_query_log='OFF';
+
+# check that alter table doesn't work for other engines
+set @save_storage_engine= @@session.default_storage_engine;
+set default_storage_engine= MEMORY;
+# After fixing bug#35765 the error behaivor changed:
+# If compiled in/enabled ER_UNSUPORTED_LOG_ENGINE
+# If not (i.e. not existant) it will show a warning
+# and use the current one.
+alter table mysql.slow_log engine=NonExistentEngine;
+--error ER_UNSUPORTED_LOG_ENGINE
+alter table mysql.slow_log engine=memory;
+#--error ER_UNSUPORTED_LOG_ENGINE
+#alter table mysql.slow_log engine=innodb;
+#--error ER_UNSUPORTED_LOG_ENGINE
+#alter table mysql.slow_log engine=archive;
+#--error ER_UNSUPORTED_LOG_ENGINE
+#alter table mysql.slow_log engine=blackhole;
+set default_storage_engine= @save_storage_engine;
+
+# Make sure only non-transactional Aria table can be used for logging
+--error ER_UNSUPORTED_LOG_ENGINE
+ALTER TABLE mysql.general_log ENGINE=Aria;
+ALTER TABLE mysql.general_log ENGINE=Aria transactional = 0;
+--error ER_UNSUPORTED_LOG_ENGINE
+ALTER TABLE mysql.slow_log ENGINE=Aria;
+ALTER TABLE mysql.slow_log ENGINE=Aria transactional = 0;
+
+drop table mysql.slow_log;
+drop table mysql.general_log;
+
+# check that table share cleanup is performed correctly (double drop)
+
+--error ER_BAD_TABLE_ERROR
+drop table mysql.general_log;
+--error ER_BAD_TABLE_ERROR
+drop table mysql.slow_log;
+
+# recreate tables and enable logs
+
+use mysql;
+
+CREATE TABLE `general_log` (
+ `event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
+ ON UPDATE CURRENT_TIMESTAMP,
+ `user_host` mediumtext NOT NULL,
+ `thread_id` BIGINT(21) UNSIGNED NOT NULL,
+ `server_id` int(10) unsigned NOT NULL,
+ `command_type` varchar(64) NOT NULL,
+ `argument` mediumtext NOT NULL
+) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
+
+CREATE TABLE `slow_log` (
+ `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
+ ON UPDATE CURRENT_TIMESTAMP,
+ `user_host` mediumtext NOT NULL,
+ `query_time` time(6) NOT NULL,
+ `lock_time` time(6) NOT NULL,
+ `rows_sent` bigint(20) unsigned NOT NULL,
+ `rows_examined` bigint(20) unsigned NOT NULL,
+ `db` varchar(512) NOT NULL,
+ `last_insert_id` int(11) NOT NULL,
+ `insert_id` int(11) NOT NULL,
+ `server_id` int(10) unsigned NOT NULL,
+ `sql_text` mediumtext NOT NULL,
+ `thread_id` BIGINT(21) UNSIGNED NOT NULL,
+ `rows_affected` bigint(20) unsigned NOT NULL
+) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
+
+set global general_log='ON';
+set global slow_query_log='ON';
+use test;
+
+#
+# Bug #20139 Infinite loop after "FLUSH" and "LOCK tabX, general_log"
+#
+
+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;
+
+#
+# Bug #17544 Cannot do atomic log rotate and
+# Bug #21785 Server crashes after rename of the log table
+#
+
+SET SESSION long_query_time = 1000;
+--disable_warnings
+drop table if exists mysql.renamed_general_log;
+drop table if exists mysql.renamed_slow_log;
+drop table if exists mysql.general_log_new;
+drop table if exists mysql.slow_log_new;
+--enable_warnings
+
+use mysql;
+# Should result in error
+--error ER_CANT_RENAME_LOG_TABLE
+RENAME TABLE general_log TO renamed_general_log;
+--error ER_CANT_RENAME_LOG_TABLE
+RENAME TABLE slow_log TO renamed_slow_log;
+
+#check rotate logs
+truncate table general_log;
+--replace_column 1 TIMESTAMP 2 USER_HOST 3 THREAD_ID
+select * from general_log;
+
+truncate table slow_log;
+--replace_column 1 TIMESTAMP 2 USER_HOST
+select * from slow_log;
+
+create table general_log_new like general_log;
+rename table general_log TO renamed_general_log, general_log_new TO general_log;
+
+create table slow_log_new like slow_log;
+rename table slow_log TO renamed_slow_log, slow_log_new TO slow_log;
+
+# check that rename checks more then first table in the list
+--error ER_CANT_RENAME_LOG_TABLE
+rename table general_log TO general_log_new, renamed_general_log TO general_log, slow_log to renamed_slow_log;
+
+# now check the content of tables
+--replace_column 1 TIMESTAMP 2 USER_HOST 3 THREAD_ID
+select * from general_log;
+--replace_column 1 TIMESTAMP 2 USER_HOST 3 THREAD_ID
+select * from renamed_general_log;
+
+# the content of the slow log is empty, but we will try a select anyway
+--replace_column 1 TIMESTAMP 2 USER_HOST
+select * from slow_log;
+--replace_column 1 TIMESTAMP 2 USER_HOST
+select * from renamed_slow_log;
+
+# check that we can do whatever we want with disabled log
+set global general_log='OFF';
+RENAME TABLE general_log TO general_log2;
+
+set global slow_query_log='OFF';
+RENAME TABLE slow_log TO slow_log2;
+
+# this should fail
+--error ER_NO_SUCH_TABLE
+set global general_log='ON';
+--error ER_NO_SUCH_TABLE
+set global slow_query_log='ON';
+
+RENAME TABLE general_log2 TO general_log;
+RENAME TABLE slow_log2 TO slow_log;
+SET SESSION long_query_time = @saved_long_query_time;
+
+# this should work
+set global general_log='ON';
+set global slow_query_log='ON';
+# now check flush logs
+flush logs;
+flush logs;
+drop table renamed_general_log, renamed_slow_log;
+use test;
+
+#
+# Bug#27858 (Failing to log to a log table doesn't log anything to error log)
+#
+# This test works as expected, it's a negative test.
+# The message "[ERROR] Failed to write to mysql.general_log"
+# is printed to master.err when writing to the table mysql.general_log
+# failed.
+# However, this message is picked up by mysql-test-run.pl,
+# and reported as a test failure, which is a false negative.
+# There is no current way to *selectively* filter out these expected error conditions
+# (see mysql-test/lib/mtr_report.pl, mtr_report_stats()).
+# Instead of filtering all occurences of "Failed to write to
+# mysql.general_log", which could hide bugs when the error is not expected,
+# this test case is commented instead.
+# TODO: improve filtering of expected errors in master.err in
+# mysql-test-run.pl (based on the test name ?), and uncomment this test.
+
+# --disable_warnings
+# drop table if exists mysql.bad_general_log;
+# drop table if exists mysql.bad_slow_log;
+# drop table if exists mysql.general_log_hide;
+# drop table if exists mysql.slow_log_hide;
+# --enable_warnings
+#
+# create table mysql.bad_general_log (a int) engine= CSV;
+# create table mysql.bad_slow_log (a int) engine= CSV;
+#
+# # Rename does not perform checks on the table structure,
+# # exploiting this to force a failure to log
+# rename table mysql.general_log to mysql.general_log_hide, mysql.bad_general_log TO mysql.general_log;
+# rename table mysql.slow_log to mysql.slow_log_hide, mysql.bad_slow_log TO mysql.slow_log;
+#
+# # The following message should be printed in master.log:
+# # [ERROR] Failed to write to mysql.general_log
+# # TODO: See how verifying this could be automated
+#
+# flush tables;
+# select "logging this should fail";
+#
+# # Restore the log tables
+#
+# rename table mysql.general_log to mysql.bad_general_log, mysql.general_log_hide TO mysql.general_log;
+# rename table mysql.slow_log to mysql.bad_slow_log, mysql.slow_log_hide TO mysql.slow_log;
+#
+# flush tables;
+#
+# drop table mysql.bad_general_log;
+# drop table mysql.bad_slow_log;
+
+#
+# Bug #21966 Strange warnings on repair of the log tables
+#
+
+use mysql;
+# check that no warning occurs on repair of the log tables
+repair table general_log;
+repair table slow_log;
+# check that no warning occurs on "create like" for the log tables
+create table general_log_new like general_log;
+create table slow_log_new like slow_log;
+show tables like "%log%";
+drop table slow_log_new, general_log_new;
+use test;
+
+#
+# Bug#69953 / MDEV-4851
+# Log tables should be modifable on LOG_OUTPUT != TABLE
+#
+#
+
+SET GLOBAL LOG_OUTPUT = 'FILE';
+SET GLOBAL slow_query_log = 1;
+SET GLOBAL general_log = 1;
+
+ALTER TABLE mysql.slow_log ADD COLUMN comment_text TEXT NOT NULL;
+ALTER TABLE mysql.general_log ADD COLUMN comment_text TEXT NOT NULL;
+
+SET GLOBAL LOG_OUTPUT = 'NONE';
+ALTER TABLE mysql.slow_log DROP COLUMN comment_text;
+ALTER TABLE mysql.general_log DROP COLUMN comment_text;
+
+
+#
+# Bug#27857 (Log tables supplies the wrong value for generating
+# AUTO_INCREMENT numbers)
+#
+
+SET GLOBAL LOG_OUTPUT = 'TABLE';
+
+## test the general log
+
+SET GLOBAL general_log = 0;
+FLUSH LOGS;
+
+TRUNCATE TABLE mysql.general_log;
+ALTER TABLE mysql.general_log ENGINE = MyISAM;
+ALTER TABLE mysql.general_log
+ ADD COLUMN seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
+
+SET GLOBAL general_log = 1;
+FLUSH LOGS;
+
+--replace_column 1 EVENT_TIME 2 USER_HOST 3 THREAD_ID 4 SERVER_ID
+SELECT * FROM mysql.general_log;
+--replace_column 1 EVENT_TIME 2 USER_HOST 3 THREAD_ID 4 SERVER_ID
+SELECT * FROM mysql.general_log;
+SELECT "My own query 1";
+SELECT "My own query 2";
+--replace_column 1 EVENT_TIME 2 USER_HOST 3 THREAD_ID 4 SERVER_ID
+SELECT * FROM mysql.general_log;
+
+SET GLOBAL general_log = 0;
+FLUSH LOGS;
+
+ALTER TABLE mysql.general_log DROP COLUMN seq;
+ALTER TABLE mysql.general_log ENGINE = CSV;
+
+## test the slow query log
+
+SET GLOBAL slow_query_log = 0;
+FLUSH LOGS;
+
+TRUNCATE TABLE mysql.slow_log;
+ALTER TABLE mysql.slow_log ENGINE = MyISAM;
+
+ALTER TABLE mysql.slow_log
+ ADD COLUMN seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
+
+SET SESSION long_query_time = 1;
+SET GLOBAL slow_query_log = 1;
+FLUSH LOGS;
+
+## FLUSH LOGS above might be slow, so the following is
+## logged as either seq 1-4 or seq 2-5
+SELECT "My own slow query", sleep(2);
+SELECT "My own slow query", sleep(2);
+SELECT "My own slow query", sleep(2);
+SELECT "My own slow query", sleep(2);
+
+## So we look for seq 2-4
+--replace_column 1 START_TIME 2 USER_HOST 3 QUERY_TIME 12 THREAD_ID
+SELECT * FROM mysql.slow_log WHERE seq >= 2 LIMIT 3;
+
+SET GLOBAL slow_query_log = 0;
+SET SESSION long_query_time =@saved_long_query_time;
+FLUSH LOGS;
+
+ALTER TABLE mysql.slow_log DROP COLUMN seq;
+ALTER TABLE mysql.slow_log ENGINE = CSV;
+
+SET GLOBAL general_log = @old_general_log;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+
+#
+# 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;
+ declare continue handler for sqlexception /* errors from truncate */
+ begin end;
+ 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;
+ declare continue handler for sqlexception /* errors from truncate */
+ begin end;
+ 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 ;//
+
+set @iterations=100;
+
+--echo "Serial test (proc25422_truncate_slow)"
+call proc25422_truncate_slow(@iterations);
+--echo "Serial test (proc25422_truncate_general)"
+call proc25422_truncate_general(@iterations);
+--echo "Serial test (proc25422_alter_slow)"
+call proc25422_alter_slow(@iterations);
+--echo "Serial test (proc25422_alter_general)"
+call proc25422_alter_general(@iterations);
+
+--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(@iterations);
+connection addconroot2;
+send call proc25422_truncate_slow(@iterations);
+
+connection addconroot3;
+send call proc25422_truncate_general(@iterations);
+connection addconroot4;
+send call proc25422_truncate_general(@iterations);
+
+connection addconroot5;
+send call proc25422_alter_slow(@iterations);
+connection addconroot6;
+send call proc25422_alter_slow(@iterations);
+
+connection addconroot7;
+send call proc25422_alter_general(@iterations);
+connection addconroot8;
+send call proc25422_alter_general(@iterations);
+
+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(6) default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+ `user_host` mediumtext ,
+ `query_time` time(6) ,
+ `lock_time` time(6) ,
+ `rows_sent` bigint(20) unsigned,
+ `rows_examined` bigint(20) unsigned,
+ `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,
+ `thread_id` bigint(21) unsigned default NULL,
+ `rows_affected` bigint(20) unsigned default NULL
+);
+
+CREATE TABLE `db_17876.general_log_data` (
+ `event_time` timestamp(6) 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(28);
+ DECLARE user_host MEDIUMTEXT;
+ DECLARE rows_set, rows_examined, last_insert_id, insert_id, server_id, rows_affected INT;
+ DECLARE thread_id BIGINT UNSIGNED;
+ 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, thread_id, rows_affected;
+ 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, thread_id,
+ rows_affected);
+ 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(28);
+ 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 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;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+
+#
+# Bug#21557 entries in the general query log truncated at 1000 characters.
+#
+
+select CONNECTION_ID() into @thread_id;
+--disable_ps_protocol
+truncate table mysql.general_log;
+--enable_ps_protocol
+set global general_log = on;
+--disable_result_log
+set @lparam = "000 001 002 003 004 005 006 007 008 009"
+ "010 011 012 013 014 015 016 017 018 019"
+ "020 021 022 023 024 025 026 027 028 029"
+ "030 031 032 033 034 035 036 037 038 039"
+ "040 041 042 043 044 045 046 047 048 049"
+ "050 051 052 053 054 055 056 057 058 059"
+ "060 061 062 063 064 065 066 067 068 069"
+ "070 071 072 073 074 075 076 077 078 079"
+ "080 081 082 083 084 085 086 087 088 089"
+ "090 091 092 093 094 095 096 097 098 099"
+ "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 127 128 129"
+ "130 131 132 133 134 135 136 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"
+ "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 224 225 226 227 228 229"
+ "230 231 232 233 234 235 236 237 238 239"
+ "240 241 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"
+ "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 296 297 298 299"
+ "300 301 302 303 304 305 306 307 308 309"
+ "310 311 312 313 314 315 316 317 318 319"
+ "320 321 322 323 324 325 326 327 328 329"
+ "330 331 332 333 334 335 336 337 338 339"
+ "340 341 342 343 344 345 346 347 348 349"
+ "350 351 352 353 354 355 356 357 358 359"
+ "360 361 362 363 364 365 366 367 368 369"
+ "370 371 372 373 374 375 376 377 378 379"
+ "380 381 382 383 384 385 386 387 388 389"
+ "390 391 392 393 394 395 396 397 398 399"
+ "400 401 402 403 404 405 406 407 408 409"
+ "410 411 412 413 414 415 416 417 418 419"
+ "420 421 422 423 424 425 426 427 428 429"
+ "430 431 432 433 434 435 436 437 438 439"
+ "440 441 442 443 444 445 446 447 448 449"
+ "450 451 452 453 454 455 456 457 458 459"
+ "460 461 462 463 464 465 466 467 468 469"
+ "470 471 472 473 474 475 476 477 478 479"
+ "480 481 482 483 484 485 486 487 488 489"
+ "490 491 492 493 494 495 496 497 498 499"
+ "500 501 502 503 504 505 506 507 508 509"
+ "510 511 512 513 514 515 516 517 518 519"
+ "520 521 522 523 524 525 526 527 528 529"
+ "530 531 532 533 534 535 536 537 538 539"
+ "540 541 542 543 544 545 546 547 548 549"
+ "550 551 552 553 554 555 556 557 558 559"
+ "560 561 562 563 564 565 566 567 568 569"
+ "570 571 572 573 574 575 576 577 578 579"
+ "580 581 582 583 584 585 586 587 588 589"
+ "590 591 592 593 594 595 596 597 598 599"
+ "600 601 602 603 604 605 606 607 608 609"
+ "610 611 612 613 614 615 616 617 618 619"
+ "620 621 622 623 624 625 626 627 628 629"
+ "630 631 632 633 634 635 636 637 638 639"
+ "640 641 642 643 644 645 646 647 648 649"
+ "650 651 652 653 654 655 656 657 658 659"
+ "660 661 662 663 664 665 666 667 668 669"
+ "670 671 672 673 674 675 676 677 678 679"
+ "680 681 682 683 684 685 686 687 688 689"
+ "690 691 692 693 694 695 696 697 698 699"
+ "700 701 702 703 704 705 706 707 708 709"
+ "710 711 712 713 714 715 716 717 718 719"
+ "720 721 722 723 724 725 726 727 728 729"
+ "730 731 732 733 734 735 736 737 738 739"
+ "740 741 742 743 744 745 746 747 748 749"
+ "750 751 752 753 754 755 756 757 758 759"
+ "760 761 762 763 764 765 766 767 768 769"
+ "770 771 772 773 774 775 776 777 778 779"
+ "780 781 782 783 784 785 786 787 788 789"
+ "790 791 792 793 794 795 796 797 798 799"
+ "800 801 802 803 804 805 806 807 808 809"
+ "810 811 812 813 814 815 816 817 818 819"
+ "820 821 822 823 824 825 826 827 828 829"
+ "830 831 832 833 834 835 836 837 838 839"
+ "840 841 842 843 844 845 846 847 848 849"
+ "850 851 852 853 854 855 856 857 858 859"
+ "860 861 862 863 864 865 866 867 868 869"
+ "870 871 872 873 874 875 876 877 878 879"
+ "880 881 882 883 884 885 886 887 888 889"
+ "890 891 892 893 894 895 896 897 898 899"
+ "900 901 902 903 904 905 906 907 908 909"
+ "910 911 912 913 914 915 916 917 918 919"
+ "920 921 922 923 924 925 926 927 928 929"
+ "930 931 932 933 934 935 936 937 938 939"
+ "940 941 942 943 944 945 946 947 948 949"
+ "950 951 952 953 954 955 956 957 958 959"
+ "960 961 962 963 964 965 966 967 968 969"
+ "970 971 972 973 974 975 976 977 978 979"
+ "980 981 982 983 984 985 986 987 988 989"
+ "990 991 992 993 994 995 996 997 998 999";
+--enable_result_log
+prepare long_query from "select ? as long_query";
+--disable_result_log
+execute long_query using @lparam;
+--enable_result_log
+set global general_log = off;
+select argument from mysql.general_log where thread_id = @thread_id
+AND (command_type = 'Query' OR command_type= 'Execute');
+deallocate prepare long_query;
+set global general_log = @old_general_log;
+
+#
+# Bug#34306: Can't make copy of log tables when server binary log is enabled
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS log_count;
+DROP TABLE IF EXISTS slow_log_copy;
+DROP TABLE IF EXISTS general_log_copy;
+--enable_warnings
+
+CREATE TABLE log_count (count BIGINT(21));
+
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+
+CREATE TABLE slow_log_copy SELECT * FROM mysql.slow_log;
+INSERT INTO slow_log_copy SELECT * FROM mysql.slow_log;
+INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.slow_log));
+DROP TABLE slow_log_copy;
+
+CREATE TABLE general_log_copy SELECT * FROM mysql.general_log;
+INSERT INTO general_log_copy SELECT * FROM mysql.general_log;
+INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.general_log));
+DROP TABLE general_log_copy;
+
+SET GLOBAL general_log = OFF;
+SET GLOBAL slow_query_log = OFF;
+
+CREATE TABLE slow_log_copy SELECT * FROM mysql.slow_log;
+INSERT INTO slow_log_copy SELECT * FROM mysql.slow_log;
+INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.slow_log));
+DROP TABLE slow_log_copy;
+
+CREATE TABLE general_log_copy SELECT * FROM mysql.general_log;
+INSERT INTO general_log_copy SELECT * FROM mysql.general_log;
+INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.general_log));
+DROP TABLE general_log_copy;
+
+SET GLOBAL general_log = @saved_general_log;
+SET GLOBAL slow_query_log = @saved_slow_query_log;
+
+DROP TABLE log_count;
+
+#
+# Bug #31700: thd->examined_row_count not incremented for 'const' type queries
+#
+
+SET SESSION long_query_time = 0;
+SET GLOBAL slow_query_log = ON;
+FLUSH LOGS;
+TRUNCATE TABLE mysql.slow_log;
+
+# Let there be three columns, unique, non-unique, and non-indexed:
+CREATE TABLE t1 (f1 SERIAL,f2 INT, f3 INT, PRIMARY KEY(f1), KEY(f2));
+INSERT INTO t1 VALUES (1,1,1);
+INSERT INTO t1 VALUES (2,2,2);
+INSERT INTO t1 VALUES (3,3,3);
+INSERT INTO t1 VALUES (4,4,4);
+
+--disable_ps2_protocol
+SELECT SQL_NO_CACHE 'Bug#31700 - SCAN',f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f3=4;
+SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f2=3;
+SELECT SQL_NO_CACHE 'Bug#31700 - PK', f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f1=2;
+
+--replace_column 1 TIMESTAMP
+SELECT start_time, rows_examined, rows_sent, sql_text FROM mysql.slow_log WHERE sql_text LIKE '%Bug#31700%' ORDER BY start_time;
+--enable_ps2_protocol
+
+DROP TABLE t1;
+
+TRUNCATE TABLE mysql.slow_log;
+
+#
+# Bug #47924 main.log_tables times out sporadically
+#
+
+use mysql;
+# Should result in error
+--disable_warnings
+drop table if exists renamed_general_log;
+drop table if exists renamed_slow_log;
+--enable_warnings
+--error ER_CANT_RENAME_LOG_TABLE
+RENAME TABLE general_log TO renamed_general_log;
+--error ER_CANT_RENAME_LOG_TABLE
+RENAME TABLE slow_log TO renamed_slow_log;
+
+use test;
+flush tables with read lock;
+unlock tables;
+
+SET @@session.long_query_time= @old_long_query_time;
+
+SET @@global.log_output= @old_log_output;
+SET @@global.slow_query_log= @old_slow_query_log;
+SET @@global.general_log= @old_general_log;