Thursday, June 7, 2007

PhyImport: Can't defer foreign keys in MySQL

The use of InnoDB with foreign keys now causes the following error in the parseTrees program:

DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails at ./ line 710.

This is not a problem in PG because foreign key checks are deferrable. Since foreigns keys are not deferrable in MySQL I am temporarily turning off FK checks in the PERL code:
to deal with this in MySQL.

This solves this problem, but now I am still getting problems with commit:
commit ineffective with AutoCommit enabled at ./ line 316.
Commmit ineffective while AutoCommit is on at ./ line 316.
DBD::mysql::db commit failed: Commmit ineffective while AutoCommit is on at ./ line 316.

I therefore added a check to see if AutoCommit was on before attempint $dbh->commit:

unless ($dbh->{AutoCommit}) {
I am now trying to see if this will fix the problem without introducing new errors.


Hilmar said...

I'm not sure why the script should ever run with autocommit enabled in one driver but not on the other.

Would there be harm in just adding AutoCommit => 0 to the MySQL connection parameters?

jestill said...

Assuming that I set the MySQL schema up correctly this should be fine. I just added AutoCommit=>0 to the ConnectToMySQL subfunction of I'll see if things work after I add $dbh->commit() to the code.