WL#4681: Took the system variable tests out of the main test suite, put them into "sys_vars", updated some reult files and tests.

1 files changed, 139 insertions, 0 deletions

diff --git a/mysql-test/suite/sys_vars/t/foreign_key_checks_func.test b/mysql-test/suite/sys_vars/t/foreign_key_checks_func.test
new file mode 100644
index 00000000000..4d2c63bbce6
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/foreign_key_checks_func.test
@@ -0,0 +1,139 @@
+############## mysql-test\t\foreign_key_checks_func.test  #####################
+#                                                                             #
+# Variable Name: foreign_key_checks                                           #
+# Scope: SESSION                                                              # 
+# Access Type: Dynamic                                                        #
+# Data Type: boolean                                                          #
+# Default Value: NA                                                           #
+# Range: NA                                                                   #
+#                                                                             #
+#                                                                             #
+# Creation Date: 2008-03-08                                                   #
+# Author:  Rizwan                                                             #
+#                                                                             #
+# Description: Test Cases of Dynamic System Variable foreign_key_checks       #
+#              that checks the behavior of this variable                      #
+#                                                                             #
+# Reference: http://dev.mysql.com/doc/refman/5.1/en/                          #
+#  server-system-variables.html                                               #
+#                                                                             #
+###############################################################################
+
+--source include/have_innodb.inc
+
+--echo '#--------------------FN_DYNVARS_032_01-------------------------#'
+####################################################################
+# Check if setting foreign_key_checks is changed in new connection # 
+####################################################################
+
+SET @@session.foreign_key_checks = 0;
+# con1 will be default connection from now on
+--echo 'connect (con1,localhost,root,,,,)'
+connect (con1,localhost,root,,,,);
+--echo 'connection con1'
+connection con1;
+SELECT @@session.foreign_key_checks;
+SET @@session.foreign_key_checks = 1;
+--echo 'connect (con2,localhost,root,,,,)'
+connect (con2,localhost,root,,,,);
+--echo 'connection con2'
+connection con2;
+SELECT @@session.foreign_key_checks;
+disconnect con2;
+
+--echo '#--------------------FN_DYNVARS_032_02-------------------------#'
+#################################################################
+# Begin the functionality Testing of foreign_key_checks         #
+#################################################################
+
+--echo 'connection con1'
+connection con1;
+
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2;
+--enable_warnings
+
+CREATE TABLE t1(a INT PRIMARY KEY)ENGINE = INNODB;
+CREATE TABLE t2(a INT PRIMARY KEY,b INT)ENGINE = INNODB;
+
+ALTER TABLE t2
+ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a);
+
+#===========================================================
+--echo '---Check when foreign_key_checks is enabled---'
+#===========================================================
+
+
+SET @@session.foreign_key_checks = 1;
+
+INSERT INTO t1 values (1),(2),(3);
+
+INSERT INTO t2 values (10,1);
+--Error ER_NO_REFERENCED_ROW_2
+INSERT INTO t2 values (20,22);
+
+#===========================================================
+--echo '---Check when foreign_key_checks is disabled---'
+#===========================================================
+
+--Error ER_ROW_IS_REFERENCED_2
+TRUNCATE t1;
+
+SET @@session.foreign_key_checks = 0;
+
+TRUNCATE t1;
+TRUNCATE t2;
+
+INSERT INTO t1 values (1),(2),(3);
+
+INSERT INTO t2 values (10,1);
+INSERT INTO t2 values (20,4);
+
+--echo 'try enabling foreign_key_checks again';
+SET @@session.foreign_key_checks = 1;
+
+UPDATE t2 SET b=4 where a=20;
+--echo 'Bug#35358: Updating an incorrect foreign key(inserted by disabling '
+--echo 'foreign_key_checks)to the same value does not raise error after '
+--echo 'enabling foreign_key_checks'
+
+#==============================================================================
+--echo 'Check when foreign_key_checks is enabled and FK constraint is re-created'
+#==============================================================================
+
+SET @@session.foreign_key_checks = 0;
+TRUNCATE t2;
+TRUNCATE t1;
+
+INSERT INTO t1 values (1),(2),(3);
+INSERT INTO t2 values (10,1),(20,4);
+
+ALTER TABLE t2 DROP FOREIGN KEY fk;
+
+SET @@session.foreign_key_checks = 1;
+
+# Test disabled as error description is different. The resulting description has
+# difference in code #sql-xxx_2 where xxx is different for each run.
+#--Error ER_NO_REFERENCED_ROW_2
+#ALTER TABLE t2
+#ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a);
+
+# delete all rows with incorrect reference
+DELETE FROM t2 WHERE b not in (SELECT a from t1);
+
+ALTER TABLE t2
+ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a);
+
+INSERT INTO t2 values (20,2);
+
+SELECT * from t2;
+
+--disable_warnings
+DROP TABLE IF EXISTS t2;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+##########################################################
+# End of functionality Testing for foreign_key_checks    #
+##########################################################
+