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.

3 files changed, 133 insertions, 62 deletions

diff --git a/mysql-test/include/log_slow_debug_common.inc b/mysql-test/include/log_slow_debug_common.inc
new file mode 100644
index 00000000000..f8c9075e1e2
--- /dev/null
+++ b/mysql-test/include/log_slow_debug_common.inc
@@ -0,0 +1,20 @@
+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;
+
+CREATE SEQUENCE s4;
+ALTER SEQUENCE s4 MAXVALUE 100;
+PREPARE stmt FROM 'ALTER SEQUENCE s4 MAXVALUE=101';
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+DROP SEQUENCE s4;
diff --git a/mysql-test/main/log_slow_debug.result b/mysql-test/main/log_slow_debug.result
index 6409aed8535..2da10949315 100644
--- a/mysql-test/main/log_slow_debug.result
+++ b/mysql-test/main/log_slow_debug.result
@@ -25,7 +25,7 @@ $$
 #
 # Expect all admin statements in the slow log (ON,DEFAULT)
 #
-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);
@@ -41,7 +41,13 @@ 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();
+CREATE SEQUENCE s4;
+ALTER SEQUENCE s4 MAXVALUE 100;
+PREPARE stmt FROM 'ALTER SEQUENCE s4 MAXVALUE=101';
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+DROP SEQUENCE s4;
+CALL show_slow_log();
 sql_text
 [slow] TRUNCATE TABLE mysql.slow_log
 [slow] CREATE TABLE t1 (a INT)
@@ -57,10 +63,16 @@ sql_text
 [slow] ALTER TABLE t4 MODIFY a INT DEFAULT 1
 [slow] DEALLOCATE PREPARE stmt
 [slow] DROP TABLE t4
+[slow] CREATE SEQUENCE s4
+[slow] ALTER SEQUENCE s4 MAXVALUE 100
+[slow] PREPARE stmt FROM 'ALTER SEQUENCE s4 MAXVALUE=101'
+[slow] ALTER SEQUENCE s4 MAXVALUE=101
+[slow] DEALLOCATE PREPARE stmt
+[slow] DROP SEQUENCE s4
 #
 # Expect all admin statements in the slow log (ON,admin)
 #
-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);
@@ -76,6 +88,12 @@ PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1';
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 DROP TABLE t4;
+CREATE SEQUENCE s4;
+ALTER SEQUENCE s4 MAXVALUE 100;
+PREPARE stmt FROM 'ALTER SEQUENCE s4 MAXVALUE=101';
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+DROP SEQUENCE s4;
 CALL show_slow_log();
 sql_text
 [slow] CREATE INDEX t1a ON t1 (a)
@@ -83,10 +101,12 @@ sql_text
 [slow] ALTER TABLE t2 RENAME t2
 [slow] RENAME TABLE t2 TO t3
 [slow] ALTER TABLE t4 MODIFY a INT DEFAULT 1
+[slow] ALTER SEQUENCE s4 MAXVALUE 100
+[slow] ALTER SEQUENCE s4 MAXVALUE=101
 #
 # Expect none of admin DDL statements in the slow log (ON,filesort)
 #
-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);
@@ -102,12 +122,63 @@ PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1';
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 DROP TABLE t4;
+CREATE SEQUENCE s4;
+ALTER SEQUENCE s4 MAXVALUE 100;
+PREPARE stmt FROM 'ALTER SEQUENCE s4 MAXVALUE=101';
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+DROP SEQUENCE s4;
 CALL show_slow_log();
 sql_text
 #
 # Expect none of admin statements in the slow log (OFF,DEFAULT)
 #
+SET @@SESSION.log_slow_admin_statements=OFF;
+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;
+CREATE SEQUENCE s4;
+ALTER SEQUENCE s4 MAXVALUE 100;
+PREPARE stmt FROM 'ALTER SEQUENCE s4 MAXVALUE=101';
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+DROP SEQUENCE s4;
+CALL show_slow_log();
+sql_text
+[slow] TRUNCATE TABLE mysql.slow_log
+[slow] CREATE TABLE t1 (a INT)
+[slow] DROP TABLE t1
+[slow] CREATE TABLE t2 (a INT)
+[slow] DROP TABLE t3
+[slow] CREATE TABLE t4 (a INT)
+[slow] PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1'
+[slow] DEALLOCATE PREPARE stmt
+[slow] DROP TABLE t4
+[slow] CREATE SEQUENCE s4
+[slow] PREPARE stmt FROM 'ALTER SEQUENCE s4 MAXVALUE=101'
+[slow] DEALLOCATE PREPARE stmt
+[slow] DROP SEQUENCE s4
+#
+# Expect all admin statements in the slow log (GLOBAL OFF,LOCAL ON,DEFAULT)
+# In the original implementation, this combination disabled slow log for admin commands.
+# However, instead of this exception in GLOBAL vs LOCAL variable behaviour,
+# we should make max_system_variables.log_slow_admin_statements=0
+# prevent enabling globally suppressed logging by setting the session variable to ON.
+#
 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);
@@ -123,7 +194,13 @@ 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();
+CREATE SEQUENCE s4;
+ALTER SEQUENCE s4 MAXVALUE 100;
+PREPARE stmt FROM 'ALTER SEQUENCE s4 MAXVALUE=101';
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+DROP SEQUENCE s4;
+CALL show_slow_log();
 sql_text
 [slow] TRUNCATE TABLE mysql.slow_log
 [slow] CREATE TABLE t1 (a INT)
@@ -139,6 +216,12 @@ sql_text
 [slow] ALTER TABLE t4 MODIFY a INT DEFAULT 1
 [slow] DEALLOCATE PREPARE stmt
 [slow] DROP TABLE t4
+[slow] CREATE SEQUENCE s4
+[slow] ALTER SEQUENCE s4 MAXVALUE 100
+[slow] PREPARE stmt FROM 'ALTER SEQUENCE s4 MAXVALUE=101'
+[slow] ALTER SEQUENCE s4 MAXVALUE=101
+[slow] DEALLOCATE PREPARE stmt
+[slow] DROP SEQUENCE s4
 #
 # Clean up
 #
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 #