Below is the list of changes that have just been committed into a local
6.0 repository of cbell. When cbell does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-11-21 08:44:19-05:00, cbell@mysql_cab_desk. +3 -0
WL#4150 : Online Backup : Disable Foreign Key Constraint on Restore
This patch adds the ability to turn off foreign key constraints during
restore. It also adds a test for foreign key constraint disable on restore.
mysql-test/r/backup_fkey.result@stripped, 2007-11-21 08:44:14-05:00, cbell@mysql_cab_desk. +150 -0
WL#4150 : Online Backup : Disable Foreign Key Constraint on Restore
New result file.
mysql-test/r/backup_fkey.result@stripped, 2007-11-21 08:44:14-05:00, cbell@mysql_cab_desk. +0 -0
mysql-test/t/backup_fkey.test@stripped, 2007-11-21 08:44:15-05:00, cbell@mysql_cab_desk. +138 -0
WL#4150 : Online Backup : Disable Foreign Key Constraint on Restore
New test file to test foreign key constraint disable on restore.
mysql-test/t/backup_fkey.test@stripped, 2007-11-21 08:44:15-05:00, cbell@mysql_cab_desk. +0 -0
sql/backup/sql_backup.cc@stripped, 2007-11-21 08:44:14-05:00, cbell@mysql_cab_desk. +41 -0
WL#4150 : Online Backup : Disable Foreign Key Constraint on Restore
This patch adds code to toggle the foreign key constraints option.
diff -Nrup a/mysql-test/r/backup_fkey.result b/mysql-test/r/backup_fkey.result
--- /dev/null Wed Dec 31 16:00:00 196900
+++ b/mysql-test/r/backup_fkey.result 2007-11-21 08:44:14 -05:00
@@ -0,0 +1,150 @@
+DROP DATABASE IF EXISTS backup_fkey;
+CREATE DATABASE backup_fkey;
+Test 1:
+Create tables and add data.
+CREATE TABLE backup_fkey.parent (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
+CREATE TABLE backup_fkey.child (id INT, parent_id INT, INDEX par_ind (parent_id),
+FOREIGN KEY (parent_id) REFERENCES parent(id)
+ON DELETE CASCADE) ENGINE=INNODB;
+Turn on foreign key constraints.
+SET foreign_key_checks = 1;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+Variable_name Value
+foreign_key_checks ON
+INSERT INTO backup_fkey.parent VALUES (1);
+INSERT INTO backup_fkey.parent VALUES (2);
+INSERT INTO backup_fkey.parent VALUES (3);
+INSERT INTO backup_fkey.parent VALUES (4);
+INSERT INTO backup_fkey.parent VALUES (5);
+INSERT INTO backup_fkey.child VALUES (1, 1);
+INSERT INTO backup_fkey.child VALUES (2, 5);
+INSERT INTO backup_fkey.child VALUES (2, 4);
+INSERT INTO backup_fkey.child VALUES (3, 2);
+INSERT INTO backup_fkey.child VALUES (4, 6);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`backup_fkey`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE)
+Backup the database originally.
+BACKUP DATABASE backup_fkey TO 'backup_fkey_orig.bak';
+Backup Summary
+ header = 32 bytes
+ meta-data = 376 bytes
+ data = 106 bytes
+ --------------
+ total 514 bytes
+Turn off foreign key constraints.
+SET foreign_key_checks = 0;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+Variable_name Value
+foreign_key_checks OFF
+Drop the parent table.
+DROP TABLE backup_fkey.parent;
+Backup the database without the parent table.
+BACKUP DATABASE backup_fkey TO 'backup_fkey.bak';
+Backup Summary
+ header = 24 bytes
+ meta-data = 264 bytes
+ data = 56 bytes
+ --------------
+ total 344 bytes
+Turn on foreign key constraints.
+SET foreign_key_checks = 1;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+Variable_name Value
+foreign_key_checks ON
+Now restore the database.
+RESTORE FROM 'backup_fkey.bak';
+Restore Summary
+ header = 24 bytes
+ meta-data = 264 bytes
+ data = 56 bytes
+ --------------
+ total 344 bytes
+RESTORE FROM 'backup_fkey_orig.bak';
+Restore Summary
+ header = 32 bytes
+ meta-data = 376 bytes
+ data = 106 bytes
+ --------------
+ total 514 bytes
+Show data
+SELECT * FROM backup_fkey.parent;
+id
+1
+2
+3
+4
+5
+SELECT * FROM backup_fkey.child;
+id parent_id
+1 1
+2 5
+2 4
+3 2
+DROP TABLE backup_fkey.child, backup_fkey.parent;
+Test 2:
+Create table and add data.
+CREATE TABLE backup_fkey.t1 (a char(40));
+INSERT INTO backup_fkey.t1 VALUES ("01 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("02 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("03 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("04 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("05 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("06 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("07 Test #2 - foreign key constraints");
+Backup the data
+BACKUP DATABASE backup_fkey TO 'backup_fkey.bak';
+Backup Summary
+ header = 21 bytes
+ meta-data = 92 bytes
+ data = 301 bytes
+ --------------
+ total 414 bytes
+Turn on foreign key constraints.
+SET foreign_key_checks = 1;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+Variable_name Value
+foreign_key_checks ON
+Restoring data
+RESTORE FROM 'backup_fkey.bak';
+Restore Summary
+ header = 21 bytes
+ meta-data = 92 bytes
+ data = 301 bytes
+ --------------
+ total 414 bytes
+Verify foreign_key_checks = ON
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+Variable_name Value
+foreign_key_checks ON
+Turn off foreign key constraints.
+SET foreign_key_checks = 0;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+Variable_name Value
+foreign_key_checks OFF
+Restoring data
+RESTORE FROM 'backup_fkey.bak';
+Restore Summary
+ header = 21 bytes
+ meta-data = 92 bytes
+ data = 301 bytes
+ --------------
+ total 414 bytes
+Verify foreign_key_checks = OFF
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+Variable_name Value
+foreign_key_checks OFF
+SELECT * FROM backup_fkey.t1;
+a
+01 Test #2 - foreign key constraints
+02 Test #2 - foreign key constraints
+03 Test #2 - foreign key constraints
+04 Test #2 - foreign key constraints
+05 Test #2 - foreign key constraints
+06 Test #2 - foreign key constraints
+07 Test #2 - foreign key constraints
+Turn on foreign key constraints.
+SET foreign_key_checks = 1;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+Variable_name Value
+foreign_key_checks ON
+Cleanup
+DROP DATABASE backup_fkey;
diff -Nrup a/mysql-test/t/backup_fkey.test b/mysql-test/t/backup_fkey.test
--- /dev/null Wed Dec 31 16:00:00 196900
+++ b/mysql-test/t/backup_fkey.test 2007-11-21 08:44:15 -05:00
@@ -0,0 +1,138 @@
+#
+# This test includes tests for disabling foreign key constraints during backup.
+#
+# There are two scenarios that should be tested:
+#
+# 1) Test to ensure a restore can successfully restore tables that have
+# foreign key constraint.
+# 2) Test to ensure that the status of the foreign key constraints system
+# variable "foreign_key_checks" is not affected by the restore.
+#
+
+--source include/have_innodb.inc
+
+--disable_warnings
+DROP DATABASE IF EXISTS backup_fkey;
+--enable_warnings
+
+CREATE DATABASE backup_fkey;
+
+#
+# Test 1 - Test restore of database with foreign key constraints.
+#
+--echo Test 1:
+--echo Create tables and add data.
+CREATE TABLE backup_fkey.parent (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
+CREATE TABLE backup_fkey.child (id INT, parent_id INT, INDEX par_ind (parent_id),
+ FOREIGN KEY (parent_id) REFERENCES parent(id)
+ ON DELETE CASCADE) ENGINE=INNODB;
+
+# Create some data.
+--echo Turn on foreign key constraints.
+SET foreign_key_checks = 1;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+
+INSERT INTO backup_fkey.parent VALUES (1);
+INSERT INTO backup_fkey.parent VALUES (2);
+INSERT INTO backup_fkey.parent VALUES (3);
+INSERT INTO backup_fkey.parent VALUES (4);
+INSERT INTO backup_fkey.parent VALUES (5);
+
+INSERT INTO backup_fkey.child VALUES (1, 1);
+INSERT INTO backup_fkey.child VALUES (2, 5);
+INSERT INTO backup_fkey.child VALUES (2, 4);
+INSERT INTO backup_fkey.child VALUES (3, 2);
+
+# Show that foreign key constraints are enforced.
+--error 1452
+INSERT INTO backup_fkey.child VALUES (4, 6);
+
+# Backup the database with fkey constraints preserved.
+--echo Backup the database originally.
+BACKUP DATABASE backup_fkey TO 'backup_fkey_orig.bak';
+
+# Turn off foreign key constraints to create a backup that has
+# fkey violations (for testing of course).
+--echo Turn off foreign key constraints.
+SET foreign_key_checks = 0;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+
+# Drop the parent table
+--echo Drop the parent table.
+DROP TABLE backup_fkey.parent;
+
+# Backup only the child table.
+--echo Backup the database without the parent table.
+BACKUP DATABASE backup_fkey TO 'backup_fkey.bak';
+
+--echo Turn on foreign key constraints.
+SET foreign_key_checks = 1;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+
+# Restore the database and ensure there are no errors.
+--echo Now restore the database.
+RESTORE FROM 'backup_fkey.bak';
+
+# Now restore the original files to show there are no problems
+# restoring valid fkey relationships.
+RESTORE FROM 'backup_fkey_orig.bak';
+
+--echo Show data
+SELECT * FROM backup_fkey.parent;
+SELECT * FROM backup_fkey.child;
+
+DROP TABLE backup_fkey.child, backup_fkey.parent;
+
+#
+# Test 2 - Test restore of database to ensure foreign key constraints
+# variable is not changed.
+#
+--echo Test 2:
+--echo Create table and add data.
+CREATE TABLE backup_fkey.t1 (a char(40));
+
+INSERT INTO backup_fkey.t1 VALUES ("01 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("02 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("03 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("04 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("05 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("06 Test #2 - foreign key constraints");
+INSERT INTO backup_fkey.t1 VALUES ("07 Test #2 - foreign key constraints");
+
+--echo Backup the data
+BACKUP DATABASE backup_fkey TO 'backup_fkey.bak';
+
+# Turn ON foreign key constraints and do restore then check result.
+--echo Turn on foreign key constraints.
+SET foreign_key_checks = 1;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+
+--echo Restoring data
+RESTORE FROM 'backup_fkey.bak';
+
+--echo Verify foreign_key_checks = ON
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+
+--echo Turn off foreign key constraints.
+SET foreign_key_checks = 0;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+
+--echo Restoring data
+RESTORE FROM 'backup_fkey.bak';
+
+--echo Verify foreign_key_checks = OFF
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+
+SELECT * FROM backup_fkey.t1;
+
+--echo Turn on foreign key constraints.
+SET foreign_key_checks = 1;
+SHOW VARIABLES LIKE 'foreign_key_checks%';
+
+--echo Cleanup
+
+DROP DATABASE backup_fkey;
+
+remove_file $MYSQLTEST_VARDIR/master-data/backup_fkey.bak;
+remove_file $MYSQLTEST_VARDIR/master-data/backup_fkey_orig.bak;
+
diff -Nrup a/sql/backup/sql_backup.cc b/sql/backup/sql_backup.cc
--- a/sql/backup/sql_backup.cc 2007-11-09 16:19:39 -05:00
+++ b/sql/backup/sql_backup.cc 2007-11-21 08:44:14 -05:00
@@ -1060,6 +1060,29 @@ int restore_table_data(THD*, Restore_inf
} // backup namespace
+/**
+ Toggle foreign key constraints on and off.
+
+ @param THD thd Current thread structure.
+ @param my_bool turn_on TRUE = turn on, FALSE = turn off.
+
+ @returns TRUE if foreign key contraints are turned on already
+ @returns FALSE if foreign key contraints are turned off
+ */
+my_bool fkey_constr(THD *thd, my_bool turn_on)
+{
+ my_bool fk_status= FALSE;
+
+ DBUG_ENTER("mysql_restore");
+ if (turn_on)
+ thd->options&= ~OPTION_NO_FOREIGN_KEY_CHECKS;
+ else
+ {
+ fk_status= (thd->options & OPTION_NO_FOREIGN_KEY_CHECKS)? FALSE : TRUE;
+ thd->options|= OPTION_NO_FOREIGN_KEY_CHECKS;
+ }
+ DBUG_RETURN(fk_status);
+}
/**
Restore items saved in backup archive.
@@ -1069,19 +1092,37 @@ int restore_table_data(THD*, Restore_inf
*/
int mysql_restore(THD *thd, backup::Restore_info &info, backup::IStream &s)
{
+ my_bool using_fkey_constr= FALSE;
+
DBUG_ENTER("mysql_restore");
size_t start_bytes= s.bytes;
DBUG_PRINT("restore",("Restoring meta-data"));
+ /*
+ Turn off foreign key constraints (if turned on)
+ */
+ using_fkey_constr= fkey_constr(thd, FALSE);
+
if (backup::restore_meta_data(thd, info, s))
+ {
+ fkey_constr(thd, using_fkey_constr);
DBUG_RETURN(backup::ERROR);
+ }
DBUG_PRINT("restore",("Restoring table data"));
if (backup::restore_table_data(thd,info,s))
+ {
+ fkey_constr(thd, using_fkey_constr);
DBUG_RETURN(backup::ERROR);
+ }
+
+ /*
+ Turn on foreign key constraints (if previously turned on)
+ */
+ fkey_constr(thd, using_fkey_constr);
DBUG_PRINT("restore",("Done."));
| Thread |
|---|
| • bk commit into 6.0 tree (cbell:1.2667) WL#4150 | cbell | 21 Nov |
