Seventh set of fixes for bug 35988.

5 files changed, 675 insertions, 0 deletions

diff --git a/mysql-test/r/query_cache_wlock_invalidate_func.result b/mysql-test/r/query_cache_wlock_invalidate_func.result
new file mode 100644
index 00000000000..9278b6605ab
--- /dev/null
+++ b/mysql-test/r/query_cache_wlock_invalidate_func.result
@@ -0,0 +1,164 @@
+** Setup **
+
+** Connecting con0 using root **
+** Connecting con1 using root **
+** Connection con0 **
+SET @start_global_value = @@GLOBAL.query_cache_wlock_invalidate;
+SET @start_session_value = @@SESSION.query_cache_wlock_invalidate;
+SET @old_cache_size = @@GLOBAL.query_cache_size;
+SET @old_cache_type = @@GLOBAL.query_cache_type;
+CREATE TABLE t1(id int, value varchar(10));
+INSERT INTO t1 VALUES(1, 'val1');
+INSERT INTO t1 VALUES(2, 'val2');
+INSERT INTO t1 VALUES(3, 'val3');
+SET GLOBAL query_cache_size = 131072;
+FLUSHING CACHE
+SET GLOBAL query_cache_size = 0;
+SET GLOBAL query_cache_size = 131072;
+SET GLOBAL query_cache_type = ON;
+Testing for value ON
+** Connection con0 **
+SET SESSION query_cache_wlock_invalidate = ON;
+** Connection con1 **
+SET SESSION query_cache_wlock_invalidate = ON;
+'#----------------------------FN_DYNVARS_136_01--------------#'
+Testing for cache invalidation
+SELECT * FROM t1;
+id	value
+1	val1
+2	val2
+3	val3
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+Variable_name	Value
+Qcache_queries_in_cache	1
+1 Expected
+LOCK TABLE t1 WRITE;
+UNLOCK TABLES;
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+Variable_name	Value
+Qcache_queries_in_cache	0
+0 Expected
+'#----------------------------FN_DYNVARS_136_02-----------------------#'
+SELECT * FROM t1;
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+** Connection con1 **
+SELECT * FROM t1;
+id	value
+1	val1
+2	val2
+3	val3
+** Connection con0 **
+LOCK TABLE t1 WRITE;
+** Connection con1 **
+** Asynchronous Execution **
+SET @startTime = NOW();
+SELECT * FROM t1;
+SET @endTime = NOW();
+SET @TimeDifference = TIME_TO_SEC(TIMEDIFF(@endTime, @startTime)) >= 2;
+SELECT @TimeDifference;|
+** Connection con0 **
+Sleeping 2 Seconds before unlock
+UNLOCK TABLES;
+** Connection con1 **
+** Asynchronous Result **
+id	value
+1	val1
+2	val2
+3	val3
+@TimeDifference
+1
+Testing for value OFF
+** Connection con0 **
+SET SESSION query_cache_wlock_invalidate = OFF;
+** Connection con1 **
+SET SESSION query_cache_wlock_invalidate = OFF;
+'#----------------------------FN_DYNVARS_136_03------------------#'
+Testing for cache invalidation
+SELECT * FROM t1;
+id	value
+1	val1
+2	val2
+3	val3
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+Variable_name	Value
+Qcache_queries_in_cache	1
+1 Expected
+LOCK TABLE t1 WRITE;
+UNLOCK TABLES;
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+Variable_name	Value
+Qcache_queries_in_cache	1
+1 Expected
+'#----------------------------FN_DYNVARS_136_04---------------------#'
+SELECT * FROM t1;
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+** Connection con1 **
+SELECT * FROM t1;
+id	value
+1	val1
+2	val2
+3	val3
+** Connection con0 **
+LOCK TABLE t1 WRITE;
+** Connection con1 **
+** Asynchronous Execution **
+SET @startTime = NOW();
+SELECT * FROM t1;
+SET @endTime = NOW();
+SET @TimeDifference = TIME_TO_SEC(TIMEDIFF(@endTime, @startTime));
+SELECT @TimeDifference;|
+** Connection con0 **
+Sleeping 2 Seconds before unlock
+UNLOCK TABLES;
+** Connection con1 **
+** Asynchronous Result **
+id	value
+1	val1
+2	val2
+3	val3
+@TimeDifference
+2
+'Bug#35390 the time difference should not be 2 seconds in this case,'
+'it should be less than a second;'
+'#----------------------------FN_DYNVARS_136_05------------------------#'
+SET GLOBAL query_cache_wlock_invalidate = OFF;
+** Connecting con_int1 using root **
+** Connection con_int1 **
+SELECT @@SESSION.query_cache_wlock_invalidate;
+@@SESSION.query_cache_wlock_invalidate
+0
+Expected Value : 0 / OFF;
+SET SESSION query_cache_wlock_invalidate = ON;
+** Connecting con_int2 using root **
+** Connection con_int2 **
+SELECT @@SESSION.query_cache_wlock_invalidate;
+@@SESSION.query_cache_wlock_invalidate
+0
+Expected Value : 0 / OFF;
+SET SESSION query_cache_wlock_invalidate = OFF;
+** Connection con_int1 **
+SELECT @@SESSION.query_cache_wlock_invalidate;
+@@SESSION.query_cache_wlock_invalidate
+1
+Expected Value : 1 / ON;
+** Connection con_int2 **
+SELECT @@SESSION.query_cache_wlock_invalidate;
+@@SESSION.query_cache_wlock_invalidate
+0
+Expected Value : 0 / OFF;
+SELECT @@GLOBAL.query_cache_wlock_invalidate;
+@@GLOBAL.query_cache_wlock_invalidate
+0
+Expected Value : 0 / OFF;
+** Connection default **
+Disconnecting Connections con_int1, con_int2
+
+CLEANUP
+** Connection con0 **
+SET @@GLOBAL.query_cache_wlock_invalidate = @start_global_value;
+SET @@SESSION.query_cache_wlock_invalidate = @start_session_value ;
+SET @@GLOBAL.query_cache_size = @old_cache_size;
+SET @@GLOBAL.query_cache_type = @old_cache_type;
+** Connection default **
+Disconnecting Connections con0, con1
+DROP TABLE t1;
diff --git a/mysql-test/r/timestamp_func.result b/mysql-test/r/timestamp_func.result
new file mode 100644
index 00000000000..c41db55cae6
--- /dev/null
+++ b/mysql-test/r/timestamp_func.result
@@ -0,0 +1,33 @@
+** Setup **
+
+** Connecting con0 using root **
+** Connecting con1 using root **
+'#-----------------------------FN_DYNVARS_179_01------------------#'
+** Connection con0 **
+SET @ts_old = @@SESSION.timestamp;
+waiting 1 sec
+SET @ts_new = @@SESSION.timestamp;
+SELECT @ts_new - @ts_old AS 'Timestamp Difference';
+Timestamp Difference
+1
+1 Expected
+** Connection con1 **
+SET @ts_old = @@SESSION.timestamp;
+waiting 4 sec
+SET @ts_new = @@SESSION.timestamp;
+SELECT @ts_new - @ts_old AS 'Timestamp Difference';
+Timestamp Difference
+4
+4 Expected
+'#-----------------------------FN_DYNVARS_179_02---------------------#'
+SET @ts_old = @@SESSION.timestamp;
+Changing time zone
+SET time_zone = 'MET';
+SET @ts_new = @@SESSION.timestamp;
+SELECT @ts_new - @ts_old >= 1 AS 'Timestamp Difference';
+Timestamp Difference
+1
+>=1 Expected
+** Cleanup **
+** Connection default **
+Disconnecting Connections con0, con1
diff --git a/mysql-test/t/innodb_table_locks_func.test b/mysql-test/t/innodb_table_locks_func.test
new file mode 100644
index 00000000000..d233a2a5240
--- /dev/null
+++ b/mysql-test/t/innodb_table_locks_func.test
@@ -0,0 +1,88 @@
+################# mysql-test\t\innodb_table_locks_func.test  ##################
+#                                                                             #
+# Variable Name: innodb_table_locks                                           #
+# Scope: GLOBAL | SESSION                                                     #
+# Access Type: Dynamic                                                        #
+# Data Type: boolean                                                          #
+# Default Value: 1                                                            #
+# Range: 0,1                                                                  #
+#                                                                             #
+#                                                                             #
+# Creation Date: 2008-03-08                                                   #
+# Author:  Rizwan                                                             #
+#                                                                             #
+# Description: Test Cases of Dynamic System Variable innodb_table_locks       #
+#              that checks the behavior of this variable                      #
+#                                                                             #
+# Reference: http://dev.mysql.com/doc/refman/5.1/en/                          #
+#  server-system-variables.html                                               #
+#                                                                             #
+###############################################################################
+
+--source include/not_embedded.inc
+--source include/have_innodb.inc
+
+--echo '#--------------------FN_DYNVARS_048_01-------------------------#'
+####################################################################
+# Check if setting innodb_table_locks is changed in new connection # 
+####################################################################
+
+SET @@global.innodb_table_locks = OFF;
+--echo 'connect (con1,localhost,root,,,,)'
+connect (con1,localhost,root,,,,);
+--echo 'connection con1'
+connection con1;
+SELECT @@global.innodb_table_locks;
+SELECT @@session.innodb_table_locks;
+disconnect con1;
+
+
+--echo '#--------------------FN_DYNVARS_048_02-------------------------#'
+###########################################################
+# Begin the functionality Testing of innodb_table_locks   #
+###########################################################
+
+#==============================================================================
+--echo '----check when innodb_table_locks = ON and autocommit = OFF---'
+#==============================================================================
+--echo 'connect (con2,localhost,root,,,,)'
+connect (con2,localhost,root,,,,);
+
+--echo 'connection default'
+connection default;
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+CREATE TABLE t1 (a INT) ENGINE=INNODB;
+
+SET @@autocommit = OFF;
+SET @@innodb_table_locks = ON;
+
+BEGIN;
+INSERT INTO t1 VALUES(1);
+SELECT * FROM t1 FOR UPDATE;
+
+--echo 'CONNECTION con2'
+CONNECTION con2;
+SET @@innodb_table_locks = ON;
+SET @@autocommit = OFF;
+send LOCK TABLES t1 WRITE;
+
+--echo 'CONNECTION default'
+CONNECTION default;
+COMMIT;
+
+--echo 'CONNECTION con2'
+CONNECTION con2;
+UNLOCK tables;
+
+DROP TABLE t1;
+disconnect con2;
+
+
+##########################################################
+# End of functionality Testing for innodb_table_locks    #
+##########################################################
+
+
diff --git a/mysql-test/t/query_cache_wlock_invalidate_func.test b/mysql-test/t/query_cache_wlock_invalidate_func.test
new file mode 100644
index 00000000000..70b56f77da7
--- /dev/null
+++ b/mysql-test/t/query_cache_wlock_invalidate_func.test
@@ -0,0 +1,311 @@
+############# mysql-test\t\query_cache_wlock_invalidate_func.test ####################
+#                                                                                    #
+# Variable Name: query_cache_wlock_invalidate                                        #
+# Scope: GLOBAL & SESSION                                                            #
+# Access Type: Dynamic                                                               #
+# Data Type: boolean                                                                 #
+# Default Value: FALSE                                                               #
+# Values: TRUE / 1, FALSE / 0                                                        #
+#                                                                                    #
+#                                                                                    #
+# Creation Date: 2008-02-21                                                          #
+# Author:  Sharique Abdullah                                                         #
+#                                                                                    #
+# Description: Test Cases of Dynamic System Variable "query_cache_wlock_invalidate"  #
+#              that checks behavior of this variable in the following ways           #
+#              * Default Value                                                       #
+#              * Valid & Invalid values                                              #
+#              * Scope & Access method                                               #
+#              * Cache behaviors                                                     #
+#                                                                                    #
+# Reference: http://dev.mysql.com/doc/refman/5.1/en/                                 #
+#            server-system-variables.html#option_mysqld_query_cache_wlock_invalidate #
+#                                                                                    #
+######################################################################################
+
+--echo ** Setup **
+--echo
+#
+# Setup
+#
+
+# disabled due to differences in the result
+--disable_ps_protocol
+#
+# Save initial value
+#
+--echo ** Connecting con0 using root **
+connect (con0,localhost,root,,);
+--echo ** Connecting con1 using root **
+connect (con1, localhost, root,,);
+--echo ** Connection con0 **
+connection con0;
+
+SET @start_global_value = @@GLOBAL.query_cache_wlock_invalidate;
+SET @start_session_value = @@SESSION.query_cache_wlock_invalidate;
+
+SET @old_cache_size = @@GLOBAL.query_cache_size;
+SET @old_cache_type = @@GLOBAL.query_cache_type;
+
+#
+# Creating test table
+#
+
+CREATE TABLE t1(id int, value varchar(10));
+
+INSERT INTO t1 VALUES(1, 'val1');
+INSERT INTO t1 VALUES(2, 'val2');
+INSERT INTO t1 VALUES(3, 'val3');
+
+#
+# Clearing the query cache and setting up cache size
+#
+
+SET GLOBAL query_cache_size = 131072;
+
+--echo FLUSHING CACHE
+SET GLOBAL query_cache_size = 0;
+SET GLOBAL query_cache_size = 131072;
+
+SET GLOBAL query_cache_type = ON;
+
+#
+# Testing for value ON
+#
+--echo Testing for value ON
+
+--echo ** Connection con0 **
+connection con0;
+
+SET SESSION query_cache_wlock_invalidate = ON;
+
+--echo ** Connection con1 **
+connection con1;
+
+SET SESSION query_cache_wlock_invalidate = ON;
+
+--echo '#----------------------------FN_DYNVARS_136_01--------------#'
+#
+# Testing for cache invalidation
+#
+
+--echo Testing for cache invalidation
+
+SELECT * FROM t1;
+
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+--echo 1 Expected
+
+LOCK TABLE t1 WRITE;
+
+UNLOCK TABLES;
+
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+--echo 0 Expected
+
+--echo '#----------------------------FN_DYNVARS_136_02-----------------------#'
+#
+# Testing for blockage of access
+#
+
+disable_result_log;
+
+SELECT * FROM t1;
+
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+
+enable_result_log;
+
+--echo ** Connection con1 **
+connection con1;
+
+SELECT * FROM t1;
+
+--echo ** Connection con0 **
+connection con0;
+
+LOCK TABLE t1 WRITE;
+
+--echo ** Connection con1 **
+connection con1;
+
+--echo ** Asynchronous Execution **
+delimiter |;
+
+send
+SET @startTime = NOW();
+SELECT * FROM t1;
+SET @endTime = NOW();
+SET @TimeDifference = TIME_TO_SEC(TIMEDIFF(@endTime, @startTime)) >= 2;
+SELECT @TimeDifference;|
+
+delimiter ;|
+
+--echo ** Connection con0 **
+connection con0;
+
+--echo Sleeping 2 Seconds before unlock
+--sleep 2
+UNLOCK TABLES;
+
+--echo ** Connection con1 **
+connection con1;
+--echo ** Asynchronous Result **
+reap;
+
+#
+# Testing for value OFF
+#
+--echo Testing for value OFF
+
+--echo ** Connection con0 **
+connection con0;
+
+SET SESSION query_cache_wlock_invalidate = OFF;
+
+--echo ** Connection con1 **
+connection con1;
+
+SET SESSION query_cache_wlock_invalidate = OFF;
+
+--echo '#----------------------------FN_DYNVARS_136_03------------------#'
+#
+# Testing for cache invalidation
+#
+
+--echo Testing for cache invalidation
+
+SELECT * FROM t1;
+
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+--echo 1 Expected
+
+LOCK TABLE t1 WRITE;
+
+UNLOCK TABLES;
+
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+--echo 1 Expected
+
+--echo '#----------------------------FN_DYNVARS_136_04---------------------#'
+#
+# Testing for blockage of access
+#
+
+disable_result_log;
+
+SELECT * FROM t1;
+
+SHOW STATUS LIKE 'Qcache_queries_in_cache';
+
+enable_result_log;
+
+--echo ** Connection con1 **
+connection con1;
+
+SELECT * FROM t1;
+
+--echo ** Connection con0 **
+connection con0;
+
+LOCK TABLE t1 WRITE;
+
+--echo ** Connection con1 **
+connection con1;
+
+--echo ** Asynchronous Execution **
+delimiter |;
+
+send
+SET @startTime = NOW();
+SELECT * FROM t1;
+SET @endTime = NOW();
+SET @TimeDifference = TIME_TO_SEC(TIMEDIFF(@endTime, @startTime));
+SELECT @TimeDifference;|
+
+delimiter ;|
+
+--echo ** Connection con0 **
+connection con0;
+
+--echo Sleeping 2 Seconds before unlock
+--sleep 2
+UNLOCK TABLES;
+
+--echo ** Connection con1 **
+connection con1;
+--echo ** Asynchronous Result **
+reap;
+
+--echo 'Bug#35390 the time difference should not be 2 seconds in this case,'
+--echo 'it should be less than a second;'
+
+--echo '#----------------------------FN_DYNVARS_136_05------------------------#'
+#
+# Session data integrity check & GLOBAL Value check
+#
+
+SET GLOBAL query_cache_wlock_invalidate = OFF;
+
+--echo ** Connecting con_int1 using root **
+connect (con_int1,localhost,root,,);
+--echo ** Connection con_int1 **
+connection con_int1;
+
+SELECT @@SESSION.query_cache_wlock_invalidate;
+--echo Expected Value : 0 / OFF;
+SET SESSION query_cache_wlock_invalidate = ON;
+
+--echo ** Connecting con_int2 using root **
+connect (con_int2,localhost,root,,);
+--echo ** Connection con_int2 **
+connection con_int2;
+
+SELECT @@SESSION.query_cache_wlock_invalidate;
+--echo Expected Value : 0 / OFF;
+
+SET SESSION query_cache_wlock_invalidate = OFF;
+
+--echo ** Connection con_int1 **
+connection con_int1;
+SELECT @@SESSION.query_cache_wlock_invalidate;
+--echo Expected Value : 1 / ON;
+
+--echo ** Connection con_int2 **
+connection con_int2;
+SELECT @@SESSION.query_cache_wlock_invalidate;
+--echo Expected Value : 0 / OFF;
+
+SELECT @@GLOBAL.query_cache_wlock_invalidate;
+--echo Expected Value : 0 / OFF;
+
+--echo ** Connection default **
+connection default;
+--echo Disconnecting Connections con_int1, con_int2
+disconnect con_int1;
+disconnect con_int2;
+
+#
+# Cleanup
+#
+--echo
+--echo CLEANUP
+
+--echo ** Connection con0 **
+connection con0;
+
+SET @@GLOBAL.query_cache_wlock_invalidate = @start_global_value;
+SET @@SESSION.query_cache_wlock_invalidate = @start_session_value ;
+SET @@GLOBAL.query_cache_size = @old_cache_size;
+SET @@GLOBAL.query_cache_type = @old_cache_type;
+
+--echo ** Connection default **
+connection default;
+--echo Disconnecting Connections con0, con1
+disconnect con0;
+disconnect con1;
+
+DROP TABLE t1;
+
+--enable_ps_protocol
+
diff --git a/mysql-test/t/timestamp_func.test b/mysql-test/t/timestamp_func.test
new file mode 100644
index 00000000000..84c66441e6f
--- /dev/null
+++ b/mysql-test/t/timestamp_func.test
@@ -0,0 +1,79 @@
+############# mysql-test\t\timestamp_func.test #############################
+#                                                                          #
+# Variable Name: timestamp                                                 #
+# Scope: GLOBAL                                                            #
+# Access Type: Dynamic                                                     #
+# Data Type: INTEGER                                                       #
+#                                                                          #
+#                                                                          #
+# Creation Date: 2008-02-25                                                #
+# Author:  Sharique Abdullah                                               #
+#                                                                          #
+# Description: Test Cases of Dynamic System Variable "timestamp"           #
+#              that checks behavior of this variable in the following ways #
+#              * Functionality based on different values                   #
+#                                                                          #
+# Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html        #
+#                                                                          #
+############################################################################
+
+--echo ** Setup **
+--echo
+#
+# Setup
+#
+--echo ** Connecting con0 using root **
+connect (con0,localhost,root,,);
+--echo ** Connecting con1 using root **
+connect (con1, localhost, root,,);
+
+--echo '#-----------------------------FN_DYNVARS_179_01------------------#'
+#
+# Checking for connection 1
+#
+
+--echo ** Connection con0 **
+connection con0;
+SET @ts_old = @@SESSION.timestamp;
+--echo waiting 1 sec
+--sleep 1
+SET @ts_new = @@SESSION.timestamp;
+SELECT @ts_new - @ts_old AS 'Timestamp Difference';
+--echo 1 Expected
+
+
+#
+# Checking for connection 2
+#
+--echo ** Connection con1 **
+connection con1;
+SET @ts_old = @@SESSION.timestamp;
+--echo waiting 4 sec
+--sleep 4
+SET @ts_new = @@SESSION.timestamp;
+SELECT @ts_new - @ts_old AS 'Timestamp Difference';
+--echo 4 Expected
+
+--echo '#-----------------------------FN_DYNVARS_179_02---------------------#'
+#
+# Testing timezone change effect
+#
+
+SET @ts_old = @@SESSION.timestamp;
+--sleep 1
+--echo Changing time zone
+SET time_zone = 'MET';
+SET @ts_new = @@SESSION.timestamp;
+SELECT @ts_new - @ts_old >= 1 AS 'Timestamp Difference';
+--echo >=1 Expected
+
+#
+# Cleanup
+#
+--echo ** Cleanup **
+
+--echo ** Connection default **
+connection default;
+--echo Disconnecting Connections con0, con1
+disconnect con0;
+disconnect con1;