de-obfuscate sys_vars.delay_key_write_func test

Test fails, because of a bug: global delay_key_write is
cached in the THD and the cached value becomes out-of-date
when the global value changes.

This is fixed in MDEV-11335

2 files changed, 26 insertions, 59 deletions

diff --git a/mysql-test/suite/sys_vars/r/delay_key_write_func.result b/mysql-test/suite/sys_vars/r/delay_key_write_func.result
index d55cca7ab8e..5cc4b2eaaad 100644
--- a/mysql-test/suite/sys_vars/r/delay_key_write_func.result
+++ b/mysql-test/suite/sys_vars/r/delay_key_write_func.result
@@ -1,23 +1,20 @@
 '#--------------------FN_DYNVARS_023_01-------------------------#'
 SET @start_value= @@global.delay_key_write;
-SET @@global.delay_key_write = ON;
-SELECT @@global.delay_key_write;
-@@global.delay_key_write
-ON
-'connect (user1,localhost,root,,,,)'
-'connection user1'
-SELECT @@global.delay_key_write AS res_is_ON;
-res_is_ON
-ON
-SET @@global.delay_key_write = ALL;
-'connect (user1,localhost,root,,,,)'
-'connection user1'
-SELECT @@global.delay_key_write AS res_is_ALL;
-res_is_ALL
-ALL
 '#--------------------FN_DYNVARS_023_02-------------------------#'
+CREATE PROCEDURE sp_addRecords (IN var1 INT,IN var2 INT)
+BEGIN
+WHILE (var1 < var2) DO
+INSERT INTO t1 VALUES(var1,REPEAT('MYSQL',10),100000.0/var1);
+SET var1=var1+1;
+END WHILE;
+END//
 '---check when delay_key_write is OFF---'
 SET @@global.delay_key_write = OFF;
+CREATE TABLE t1(
+a INT PRIMARY KEY,
+b VARCHAR(512),
+c DOUBLE
+) DELAY_KEY_WRITE = 1;
 FLUSH STATUS;
 CALL sp_addRecords(1,10);
 SHOW STATUS LIKE 'Key_reads';
@@ -32,8 +29,14 @@ Key_write_requests	9
 SELECT COUNT(*) FROM t1;
 COUNT(*)
 9
+DROP TABLE t1;
 '----check when delay_key_write is ON---'
 SET @@global.delay_key_write = ON;
+CREATE TABLE t1(
+a INT PRIMARY KEY,
+b VARCHAR(512),
+c DOUBLE
+) DELAY_KEY_WRITE = 1;
 FLUSH STATUS;
 CALL sp_addRecords(1,10);
 SHOW STATUS LIKE 'Key_reads';
@@ -48,8 +51,14 @@ Key_write_requests	9
 SELECT COUNT(*) FROM t1;
 COUNT(*)
 9
+DROP TABLE t1;
 '----check when delay_key_write is ALL---'
 SET @@global.delay_key_write = ALL;
+CREATE TABLE t1(
+a INT PRIMARY KEY,
+b VARCHAR(512),
+c DOUBLE
+) DELAY_KEY_WRITE = 0;
 FLUSH STATUS;
 CALL sp_addRecords(1,10);
 SHOW STATUS LIKE 'Key_reads';
diff --git a/mysql-test/suite/sys_vars/t/delay_key_write_func.test b/mysql-test/suite/sys_vars/t/delay_key_write_func.test
index a44b37653d2..e823e51954c 100644
--- a/mysql-test/suite/sys_vars/t/delay_key_write_func.test
+++ b/mysql-test/suite/sys_vars/t/delay_key_write_func.test
@@ -20,36 +20,14 @@
 ###############################################################################
 
 --echo '#--------------------FN_DYNVARS_023_01-------------------------#'
-#######################################################################
-# Check if setting delay_key_write is changed in every new connection #
-#######################################################################
-
 SET @start_value= @@global.delay_key_write;
 
-SET @@global.delay_key_write = ON;
-SELECT @@global.delay_key_write;
-
---echo 'connect (user1,localhost,root,,,,)'
-connect (user1,localhost,root,,,,);
---echo 'connection user1'
-connection user1;
-SELECT @@global.delay_key_write AS res_is_ON;
-SET @@global.delay_key_write = ALL;
-disconnect user1;
-
---echo 'connect (user1,localhost,root,,,,)'
-connect (user1,localhost,root,,,,);
---echo 'connection user1'
-connection user1;
-SELECT @@global.delay_key_write AS res_is_ALL;
-
 --echo '#--------------------FN_DYNVARS_023_02-------------------------#'
 ######################################################
 # Begin the functionality Testing of delay_key_write #
 ######################################################
 
 # create procedure to add rows
---disable_query_log
 DELIMITER //;
 CREATE PROCEDURE sp_addRecords (IN var1 INT,IN var2 INT)
 BEGIN
@@ -59,28 +37,19 @@ BEGIN
    END WHILE;
 END//
 DELIMITER ;//
---enable_query_log
 
 #==============================================================================
 --echo '---check when delay_key_write is OFF---'
 #==============================================================================
 
-
 SET @@global.delay_key_write = OFF;
 
-
---disable_query_log
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
 # create a table with delay_key_write enabled
 CREATE TABLE t1(
 a INT PRIMARY KEY,
 b VARCHAR(512),
 c DOUBLE
 ) DELAY_KEY_WRITE = 1;
---enable_query_log
-
 
 FLUSH STATUS;
 
@@ -90,6 +59,7 @@ SHOW STATUS LIKE 'Key_reads';
 SHOW STATUS LIKE 'Key_writes';
 SHOW STATUS LIKE 'Key_write_requests';
 SELECT COUNT(*) FROM t1;
+DROP TABLE t1;
 
 #==============================================================================
 --echo '----check when delay_key_write is ON---'
@@ -97,17 +67,12 @@ SELECT COUNT(*) FROM t1;
 
 SET @@global.delay_key_write = ON;
 
---disable_query_log
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
 # create a table with delay_key_write enabled
 CREATE TABLE t1(
 a INT PRIMARY KEY,
 b VARCHAR(512),
 c DOUBLE
 ) DELAY_KEY_WRITE = 1;
---enable_query_log
 
 FLUSH STATUS;
 CALL sp_addRecords(1,10);
@@ -116,23 +81,19 @@ SHOW STATUS LIKE 'Key_reads';
 SHOW STATUS LIKE 'Key_writes';
 SHOW STATUS LIKE 'Key_write_requests';
 SELECT COUNT(*) FROM t1;
+DROP TABLE t1;
 
 #==============================================================================
 --echo '----check when delay_key_write is ALL---'
 #==============================================================================
 SET @@global.delay_key_write = ALL;
 
---disable_query_log
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
 # create a table with delay_key_write disabled
 CREATE TABLE t1(
 a INT PRIMARY KEY,
 b VARCHAR(512),
 c DOUBLE
 ) DELAY_KEY_WRITE = 0;
---enable_query_log
 
 FLUSH STATUS;
 CALL sp_addRecords(1,10);
@@ -144,12 +105,9 @@ SELECT COUNT(*) FROM t1;
 
 DROP PROCEDURE sp_addRecords;
 DROP TABLE t1;
-disconnect user1;
 
-connection default;
 SET @@global.delay_key_write= @start_value;
 
 ####################################################
 # End of functionality testing for delay_key_write #
 ####################################################
-