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, 88 insertions, 5 deletions

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
 #