Load data into MySQL with foreign key constraint issues

One of the MySQL databases I work with has a couple of hundred INNODB tables set up with foreign key constraints. I needed to copy the database using mysqldump to export data from one server and then load that into another but kept getting foreign key constraint errors. This post looks at how to load data from a file with MySQL and ignore foreign key constraints.

The issue

Foreign key constraints ensure database integrity by not allowing data to be inserted into a table if a matching key does not exist in another table, for fields set up in this way. However they can cause issues when trying to load a database backup into a database.

When attempting the load the backup (done by dumping into a tab delimited file - I've covered this previously in my using mysqldump to save data to CSV files post) I got foreign key constraint errors. I then attempted to re-arrange the order in which the tables were loaded but continued to get errors due to some tables having parent-child relationships in the same table. I would then have had to re-export the original data, ordering it to ensure no issues when loading it, and load all tables in a specific order.

This would all have been too hard and time consuming so it was simpler to import the data ignoring foreign key constraints; as long as all data was loaded then it would have integrity because the source database it. And any subseqeuent queries run on the database would be subject to the constraints.

The solution

The solution is to switch off foreign key constraint checking before loading the data and then switching it back on again after loading the data. To switch off foreign key constraint checking run the following SQL query:

SET FOREIGN_KEY_CHECKS = 0;

And to switch it back on again:

SET FOREIGN_KEY_CHECKS = 1;

The database dump I had done was each table into a tab delimited text file. I would normally load these in using the mysqlimport command line utility but in this case would not be able to run the SET FOREIGN_KEY_CHECKS query. However it is possible to load data from a text file into MySQL using the LOAD DATA INFILE command like so:

SET FOREIGN_KEY_CHECKS = 0;
LOAD DATA INFILE '/path/to/mytable.txt' INTO TABLE mytable;
SET FOREIGN_KEY_CHECKS = 1;

The LOAD DATA INFILE line would then need to be repeated for each file that needs to be loaded.

If the data had been dumped as a SQL query (which is the default when using mysqldump) it can be loaded using SOURCE by using the following query:

SET FOREIGN_KEY_CHECKS = 0;
SOURCE myfile.sql;
SET FOREIGN_KEY_CHECKS = 1;

These commands above were done using the the mysql command line, but it should also be possible to run them from within a programming language such as PHP and possibly even using an interface such as phpMyAdmin and the MySQL GUI tools.

Script to generate the SQL

I have followed this post up with a second one showing how to generate the SQL for multiple tables using a BASH shell script.

I recently showed how to load data into MySQL with foreign key constraint issues when loading a backup from tab delimited text files. This post provides a BASH command line script to generate the SQL to load all the files to save some time.

Example database

The example database in this post has three tables: products, categories and products2categories. There were dumped to text files like so:

mysqldump -u[username] -p -t -T/tmp/mysql-backup [databasename]

-u specifies the username and you would subsitute [username] for the actual username.

-p specifies that you wish to enter a password. mysqldump will prompt for the password when run.

-t specifies you do not want .sql files created which have the table creation information for each table individually. Instead we will do a second dump for table creation information only in a single file.

-T specifies the directory to write the files to. It must be writeable as the user the MySQL server runs as. In practise this means changing that directory to be writeable by all. In my example, the data is being written to /tmp/mysql-backup so do chmod 0777 /tmp/mysql-backup

Finally, change [databasename] to the name of the database to dump data for.

Now dump the database structure into a single file:

mysqldump -u[username] -p -d [databasename] > [databasename].sql

The only flag different frm the first example is -d which specifies we do not want the data to be dumped as well: just the database schema.

Files created by mysqldump

The following files are created for the example database using the two commands above:

[databasename].sql
categories.txt
products2categories.txt
products.txt

Create the tables

Before loading the data the tables need to be created first. Do this like so:

mysql -u[username] -p [otherdatabasename] < [databasename].sql

When using mysqldump to create the txt files, it will by default create a .sql file for each table. I prevented it from doing this with the -t flag and created a single sql file instead.

The reason for this is that it simplifies creating the tables because doing "mysql ... < *.sql" does not work, resulting in a "-bash: *.sql: ambiguous redirect" error. Rather than use xargs or some other workaround it's easier to simply dump the table creation all into a single file.

The script to generate the load SQL

We've finally got to the point of this post. The aim is to create a SQL file that looks like this:

SET FOREIGN_KEY_CHECKS = 0;
load data infile '/tmp/mysql-backup/categories.txt' INTO TABLE categories;
load data infile '/tmp/mysql-backup/products2categories.txt' INTO TABLE products2categories;
load data infile '/tmp/mysql-backup/products.txt' INTO TABLE products;

The BASH script to do this looks like so:

#!/bin/sh

echo "SET FOREIGN_KEY_CHECKS = 0;"

for f in `ls -1 *.txt`; do
        table=${f/.txt/}
        echo "load data infile '`pwd`/$f' INTO TABLE $table;"
done;

What it does it to get a list of all the .txt files in the directory and for each one create a "load data infile" line. The "table=${f/.txt/}" line creates a variable with the .txt part stripped from the filename.

Save the above script into the same directory that the data files were dumped, naming it e.g. sql.sh and then do this:

chmod 0700 sql.sh
./sql.sh > load.sql
mysql -u[username] -p [otherdatabasename] < load.sql

Line 1 changes the permissions so it can be run. Line 2 runs it and dumps the resulting lines into the file load.sql. Line 3 then runs the generated SQL file into the other database.

Because the first line of the script contains the set foreign check keys part then all foreign constraints are ignored while the data is loaded from the files. It needs to be run as a SQL script like this rather than using the mysql command line utility because it cannot be told to ignore foreign key checks (at least as far as I have been able to determine).

Related posts:

• Use mysqldump to get the schema only
• Selectively dumping data with mysqldump
• Restore data from a tab delimited file to MySQL
• Using mysqldump to save data to CSV files
• Backing up MySQL with mysqldump
• Execute MySQL statements from a text file
• Importing data with mysqlimport from multiple files