MySQL load data infile and foreign key constraints

I’ve been up against a(nother) MySQL 4/PHP 5 ‘not doing all I want it
to’ type problem. This time, it involved trying to do a bulk update on
potentially tens of thousands of lines. Now, I don’t want to do an
INSERT for each line and I’ve already written some classes to handle
outputting data objects into csv files which handles all of the primary
keys. There’s an option with LOAD DATA INFILE to REPLACE values where
primary key conflicts would occur so I thought if I used that with
existing rows then I should be okay.

What I have found, though, is that LOAD DATA INFILE with REPLACE appears
to try to delete the row and then re-insert it. This breaks other
foreign constraints and so the database engine won’t let it. Fair
enough, but it would have been nice if the manual mentioned it.

So: next step is to disable the foreign keys temporarily with SET
FOREIGN_KEY_CHECKS=0. From the command line this works fine, but it
seems that within PHP the foreign key checks are re-enabled
automatically after each command is run, which means the net result of
running SET FOREIGN_KEY_CHECKS=0 is absolutely nothing. So when I try
and to the LOAD DATA INFILE the constraints are still there and it
doesn’t work.

If we trusted MySQL 5 fully yet (and wanted to go through the pain of
installing it) I could probably write a stored procedure, with whatever
headaches that would bring, but as it is I’m stuck with MySQL 4 for the
moment. The solution I’ve come up with is to write the SQL query to a
text file and then use an exec() command from within PHP to execute the
commands in the file. I don’t like it, and it doesn’t feel very
portable, but it seems to work. In fact, it seems so handy that I’ve
created a static method so I can do it with any batch of SQL queries.

The handy class follows (with constants in place of the database
names/connection values/temporary folder):

public static function multipleDbQueries($sql)
 $file = fopen(TEMP_CSV_LOCATION .
 exec("mysql -u " . DB_USERNAME .
  " --password=='" . DB_PASSWORD .
  "' " . DB_NAME . " < "
  . TEMP_CSV_LOCATION . "temp_query.sql");

0 thoughts on “MySQL load data infile and foreign key constraints”

  1. Tim in T.O. – Your solution is helpful; however, I put it in my code and then realized there’s a big disconnect between using a DSN as best-practice in my application and then somehow providing username/password for this. Argh! Mysql doesn’t know anything about DSNs. So I’ll have to hard-code in a new password for the DB, all so I can do a runtime REPLACE which, as you observed, can’t handle a foreign key reference.

Leave a Reply

Your email address will not be published. Required fields are marked *