Pablo Bianchi schreef op di 03-06-2014 om 12:21 [-0300]:
Hi Robin!
So you agree with me that Appendix F ("Resetting the Koha Database") of current (v3.16) manual is wrong?
It should be updated. It works in MySQL < ~5.5, and it's due to MySQL changes that it no longer works.
Problems that we can avoid just with DELETE FROM biblio?
Yes, provided you don't turn off the constraints. Now, you might find an order of operations that allows truncate to work (I can't remember if it fails if a constraint /may/ be violated, or if it /will/ be violated.)
Despite the autoincrement issue, the DELETE FROM biblio way wasn't working for me. Now I'm not being able to reproduce the error, but I remember was very similar than with truncate, about foreign key constraint.
Right, what you need to do in this case is delete everything in the table that the error is referencing first. In some cases the constraints will propagate the delete, in other cases they won't. These differences are by design.
I wonder why with -d option of bulkmarcimport.pl I had similar problems than with TRUNCATE, throw to stderr something like:
#1701 - Cannot truncate a table referenced in a foreign key constraint (`koha_MyInstance`.`aqorders`, CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `koha_MyInstance`.`biblio` (`biblionumber`))
Yeah, so in this case, you'd want to delete/maybe truncate aqorders first, then try biblio again.
What we can expect just taking a look to the source code:
if ($delete) { if ($biblios){ print "deleting biblios\n"; $dbh->do("truncate biblio"); $dbh->do("truncate biblioitems"); $dbh->do("truncate items"); } else { print "deleting authorities\n"; $dbh->do("truncate auth_header"); } $dbh->do("truncate zebraqueue"); }
If this way is the right one, considering constraint checks, I can't see why we can't use it. Perhaps we can set up something differently in my.cnf...
This is old code, and it used to work, until MySQL changed its behaviour.
Also, as Tomas says, don't turn off constraints unless you really know the ramifications of what you're doing, otherwise you are very likely to end up with an inconsistent database. And that's bad. -- Robin Sheat Catalyst IT Ltd. ✆ +64 4 803 2204 GPG: 5FA7 4B49 1E4D CAA4 4C38 8505 77F5 B724 F871 3BDF