[SOLVED] Disabling foreign key checks on the command line

Disabling foreign key checks on the command line

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?

Solution

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.