Disabling foreign key checks on the command line

Question

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?

Ardennes

Answer 1

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.

Mulvey

This looks like the best way. It's surprising mysqldump doesn't have a --disable-foreign-keys option. – Sherrillsherrington

@Sherrillsherrington remember that foreign keys are a product of the InnoDB engine rather than MySQL itself – Mulvey

Thanks, that is working! So setting FK checks only works for one command, rather than persisting across multiple commands? – Ardennes

@Ardennes I think that it's per-session (run of the mysql command) – Mulvey

@ExplosionPills @Sherrillsherrington just ran into another issue, how would I disable FK checks on the actual data import? My table has an FK to itself that may reference a later ID. My import command is mysqlimport [user/pass] --local --fields-terminated-by="|" database "table.txt" – Ardennes

Don't use the --tab option, just do a normal mysqldump so the data is in the .sql file. Then this answer should work. – Sherrillsherrington

Anybody know how to do this in Windows? :/ – Bent

For windows you can use mysql -u root wotr -p < "echo SET FOREIGN_KEY_CHECKS=0" < dump.sql – Aver

Answer 2

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

Urinary

This is the best solution, better than the answer of "Explosion Pills" – Bimah

i tried like : mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" -u root -p DBname and i get You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" -u root -p DBName' at line 1 – Warmedover

mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" -u root -p DBNAME < importNAME.sql – Ardy

also you must be in the folder where your mysql script is located. For example I use xampp so it would be: C:\\xampp\mysql\bin – Ardy

this is the answer. – Applewhite

Answer 3

Login to mysql command line:

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

1 SET FOREIGN_KEY_CHECKS=0;

2.use <database_name>

3 SOURCE /pathToFile/backup.sql;

4 SET FOREIGN_KEY_CHECKS=1;

5 exit

Olsewski

I'm confused. Don't your instructions imply that you should be running this directly from the MySQL command line? – Glossitis

You may want to update your answer to clarify that then. Currently it says do not run directly from MySQL command line – Glossitis

don't forget to select database by using the following command before step2: 2) USE <database name> – Husha

this is my preferred method for uploading parent and child tables in no particular order. UPVOTED! ;) – Houstonhoustonia

This is not an option to disable from the command line, it is for the client. – Veratridine

Perfect ans! what I am looking for. Thanks – Transact

No luck, mysql showed the errors and continued with the import. Because the errors were somewhere half-way the import, I didn't notice them at first, thinking the import succeeded, but it was not :-( I ended up fixing the foreign key constraints in the SQL file. – Bankhead

Totally a different way to do mysql imports. wow. And Im using mysql for 15 years. ouch. – Mephitis

Answer 4

Just another one to do the same:

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

Benefactress

Thanks, this was perfect since I was zcating my other file. – Knotting

Answer 5

Another way with .gz files:

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

Purgation

Answer 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

Bromoform

Answer 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

Kauslick