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  
[all the imports]
echo "SET FOREIGN_KEY_CHECKS=1" | mysql

Is there some other way to disable FK checks on the command line?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

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.

Method 3

mysql -u <username> -p -h <host_name or ip> Then run

1 SET FOREIGN_KEY_CHECKS=0;

2.use database <database_name>

3 SOURCE /pathToFile/backup.sql;

4 SET FOREIGN_KEY_CHECKS=1;

Method 4

Just another one to do the same:

{ echo "SET FOREIGN_KEY_CHECKS=0;" ; cat imports.sql ; } | mysql

Method 5

Another way with .gz files:

gunzip < backup.sql.gz | mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" -u <username> -p

Method 6

Based off the comments and answers, I ended up using this for a zipped database import with both InnoDB and MyISAM:

{ echo "SET FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0;" ; zcat dump.gz ; } | mysql

Method 7

Simply, you can call any command from cmd, this way:

mysql -e "SET SESSION FOREIGN_KEY_CHECKS=1;"

Of course, you need to specify the username, password and host using -u, -p and -h

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating