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 .
"temp_query.sql","a+");
fwrite($file,$sql);
fclose($file);
exec("mysql -u " . DB_USERNAME .
" --password=='" . DB_PASSWORD .
"' " . DB_NAME . " < "
. TEMP_CSV_LOCATION . "temp_query.sql");
}
Comments:
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.