SEQUENCE tests for MDEV-18892 Regression in slow log and admin statements

Adding tests to cover how SEQUENCE related statements work in combination
with the slow log configuration commands.

1 files changed, 25 insertions, 57 deletions

diff --git a/mysql-test/main/log_slow_debug.test b/mysql-test/main/log_slow_debug.test
index 7945328fee3..5bacda31d93 100644
--- a/mysql-test/main/log_slow_debug.test
+++ b/mysql-test/main/log_slow_debug.test
@@ -34,45 +34,21 @@ DELIMITER ;$$
 --echo # Expect all admin statements in the slow log (ON,DEFAULT)
 --echo #
 
-SET @@GLOBAL.log_slow_admin_statements=ON;
+SET @@SESSION.log_slow_admin_statements=ON;
 SET log_slow_filter=DEFAULT;
 TRUNCATE TABLE mysql.slow_log;
-CREATE TABLE t1 (a INT);
-CREATE INDEX t1a ON t1 (a);
-DROP INDEX t1a ON t1;
-DROP TABLE t1;
-CREATE TABLE t2 (a INT);
-ALTER TABLE t2 RENAME t2;
-RENAME TABLE t2 TO t3;
-DROP TABLE t3;
-CREATE TABLE t4 (a INT);
-PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1';
-EXECUTE stmt;
-DEALLOCATE PREPARE stmt;
-DROP TABLE t4;
-CALL show_slow_log_exclude_ps();
+--source include/log_slow_debug_common.inc
+CALL show_slow_log();
 
 
 --echo #
 --echo # Expect all admin statements in the slow log (ON,admin)
 --echo #
 
-SET @@GLOBAL.log_slow_admin_statements=ON;
+SET @@SESSION.log_slow_admin_statements=ON;
 SET log_slow_filter=admin;
 TRUNCATE TABLE mysql.slow_log;
-CREATE TABLE t1 (a INT);
-CREATE INDEX t1a ON t1 (a);
-DROP INDEX t1a ON t1;
-DROP TABLE t1;
-CREATE TABLE t2 (a INT);
-ALTER TABLE t2 RENAME t2;
-RENAME TABLE t2 TO t3;
-DROP TABLE t3;
-CREATE TABLE t4 (a INT);
-PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1';
-EXECUTE stmt;
-DEALLOCATE PREPARE stmt;
-DROP TABLE t4;
+--source include/log_slow_debug_common.inc
 CALL show_slow_log();
 
 
@@ -80,22 +56,10 @@ CALL show_slow_log();
 --echo # Expect none of admin DDL statements in the slow log (ON,filesort)
 --echo #
 
-SET @@GLOBAL.log_slow_admin_statements=ON;
+SET @@SESSION.log_slow_admin_statements=ON;
 SET log_slow_filter=filesort;
 TRUNCATE TABLE mysql.slow_log;
-CREATE TABLE t1 (a INT);
-CREATE INDEX t1a ON t1 (a);
-DROP INDEX t1a ON t1;
-DROP TABLE t1;
-CREATE TABLE t2 (a INT);
-ALTER TABLE t2 RENAME t2;
-RENAME TABLE t2 TO t3;
-DROP TABLE t3;
-CREATE TABLE t4 (a INT);
-PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1';
-EXECUTE stmt;
-DEALLOCATE PREPARE stmt;
-DROP TABLE t4;
+--source include/log_slow_debug_common.inc
 CALL show_slow_log();
 
 
@@ -103,23 +67,27 @@ CALL show_slow_log();
 --echo # Expect none of admin statements in the slow log (OFF,DEFAULT)
 --echo #
 
+SET @@SESSION.log_slow_admin_statements=OFF;
+SET log_slow_filter=DEFAULT;
+TRUNCATE TABLE mysql.slow_log;
+--source include/log_slow_debug_common.inc
+CALL show_slow_log();
+
+
+--echo #
+--echo # Expect all admin statements in the slow log (GLOBAL OFF,LOCAL ON,DEFAULT)
+--echo # In the original implementation, this combination disabled slow log for admin commands.
+--echo # However, instead of this exception in GLOBAL vs LOCAL variable behaviour,
+--echo # we should make max_system_variables.log_slow_admin_statements=0
+--echo # prevent enabling globally suppressed logging by setting the session variable to ON.
+--echo #
+
 SET @@GLOBAL.log_slow_admin_statements=OFF;
+SET @@SESSION.log_slow_admin_statements=ON;
 SET log_slow_filter=DEFAULT;
 TRUNCATE TABLE mysql.slow_log;
-CREATE TABLE t1 (a INT);
-CREATE INDEX t1a ON t1 (a);
-DROP INDEX t1a ON t1;
-DROP TABLE t1;
-CREATE TABLE t2 (a INT);
-ALTER TABLE t2 RENAME t2;
-RENAME TABLE t2 TO t3;
-DROP TABLE t3;
-CREATE TABLE t4 (a INT);
-PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1';
-EXECUTE stmt;
-DEALLOCATE PREPARE stmt;
-DROP TABLE t4;
-CALL show_slow_log_exclude_ps();
+--source include/log_slow_debug_common.inc
+CALL show_slow_log();
 
 
 --echo #