Menu
I have a backup script for my MySQL database, using mysqldump with the --tab option so it produces a .sql file for the structure and a .txt file (pipe-separated) for the content.
Some tables have foreign keys, so when I import it I'm getting the error:
ERROR 1217 (23000) at line 8: Cannot delete or update a parent row: a foreign key constraint fails
I know about using SET FOREIGN_KEY_CHECKS=0 (and SET FOREIGN_KEY_CHECKS=1 afterward). If I add those to each .sql file then the import works. But then obviously on the next mysqldump those get overwritten.
I also tried running it as a separate command, like below but the error comes back:
echo "SET FOREIGN_KEY_CHECKS=0" | mysql [user/pass/database] [all the imports] echo "SET FOREIGN_KEY_CHECKS=1" | mysql [user/pass/database] Is there some other way to disable FK checks on the command line?
355
You can disable foreign key check in MySQL by setting the system variable foreign_key_checks to 0. However, please note, after you enable foreign key checks, MySQL will not re-validate your existing data that you added after disabling foreign key check. It will only check any new additions/updates to your database.
FOREIGN_KEY_CHECKS option specifies whether or not to check foreign key constraints for InnoDB tables.
From the Export tab, select the "Custom" export method. In the "Format-specific options:" area, look for and check "Disable foreign key checks". It does the same thing Misa Lazovic suggested to do, but since you're using phpMyAdmin to do the export this is the graphical way of adding those lines.
If you don’t disable foreign key checks, you have to load data into the parent tables first and then the child tables in sequence, which can be tedious. Another scenario that you want to disable the foreign key check is when you want to drop a table.
To disable foreign key checks, you set the foreign_key_checks variable to zero as follows: To re-enable foreign key constraint check, you set the value of the foreign_key_checks to 1: Notice that setting foreign_key_checks to 1 does not trigger any validation of the existing table data. In other words, MySQL will not verify the consistency ...
Is there some other way to disable FK checks on the command line? You can also use --init-command parameter of mysql command. I.e.: mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" ... also you must be in the folder where your mysql script is located.
Unless you disable the foreign key checks, you cannot drop a table referenced by a foreign key constraint. To disable foreign key checks, you set the foreign_key_checks variable to zero as follows: SET foreign_key_checks = 0; Code language: SQL (Structured Query Language) (sql)
You can also use --init-command parameter of mysql command.
I.e.: mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" ...
MySQL 5.5 Documentation - mysql options
115
You can do this by concatenating the string to the file inline. I'm sure there's an easier way to concatenate strings and files, but it works.
cat <(echo "SET FOREIGN_KEY_CHECKS=0;") imports.sql | mysql I don't think you need to set it back to 1 since it's just one session.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
