bulkmarcimport.pl -d ("Delete EVERYTHING related to biblio in koha-DB before import. Tables: biblio, biblioitems, items") use this 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"); } But, as Robin Sheat[1] point me this is a deprecated way. Now, with MySQL >~5.5 the way is just to: DELETE FROM biblio; And the constraints will propagate the delete to (ALMOST) all other tables. [1] http://lists.katipo.co.nz/public/koha/2014-June/039701.html Related bug: #12486.
Still valid on current master.
Hm, I guess we must expect Koha to run on MySQL both aboe and below 5.5. Will DELETE FROM work for both? Or should we use different things depending on the MySQL version?
Also, I got some nasty foreign key errors with the script as it is now, on MySQL version 5.5.40-0+wheezy1: deleting biblios DBD::mysql::db do failed: Cannot truncate a table referenced in a foreign key constraint (`koha_vulkan`.`biblioitems`, CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `koha_vulkan`.`biblio` (`biblionumber`)) at /usr/share/koha/bin/migration_tools/bulkmarcimport.pl line 137. DBD::mysql::db do failed: Cannot truncate a table referenced in a foreign key constraint (`koha_vulkan`.`items`, CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `koha_vulkan`.`biblioitems` (`biblioitemnumber`)) at /usr/share/koha/bin/migration_tools/bulkmarcimport.pl line 138. DBD::mysql::db do failed: Cannot truncate a table referenced in a foreign key constraint (`koha_vulkan`.`branchtransfers`, CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `koha_vulkan`.`items` (`itemnumber`)) at /usr/share/koha/bin/migration_tools/bulkmarcimport.pl line 139. Characteristic MARC flavour: NORMARC Changing "truncate" to DELETE FROM as suggested in the first comment made this problem go away.
FYI: If we move to DELETE FROM it will take longer as its a DML command and can be rolled back. TRUNCATE is a DDL command and is autocommitted, recreating the tables and indexes, with AUTO_INCREMENT starting over. So we must consider if we want to reset AUTO_INCREMENT on the table when using DELETE FROM as well. ( ALTER TABLE thetable AUTO_INCREMENT = 1 ). Also, an understanding of bulkimport.pl and the implications for folks with huge imports, and the resulting rollback support.
This does the trick for me: if ($delete) { if ($biblios){ print "deleting biblios\n"; $dbh->do("DELETE FROM biblio"); $dbh->do("ALTER TABLE biblio AUTO_INCREMENT = 1"); $dbh->do("DELETE FROM biblioitems"); $dbh->do("ALTER TABLE biblioitems AUTO_INCREMENT = 1"); $dbh->do("DELETE FROM items"); $dbh->do("ALTER TABLE items AUTO_INCREMENT = 1"); } else { print "deleting authorities\n"; $dbh->do("truncate auth_header"); } $dbh->do("truncate zebraqueue"); } On an uptodate Debian 7, running MySQL 5.5.41-0+wheezy1-log.
(In reply to wajasu from comment #4) > Also, an understanding of bulkimport.pl and the implications for folks with > huge imports, and the resulting rollback support. Slow processing is usually overcome by intermediate user feedback. It's a bulk import. People are going to assume it is slow to begin with. And the ability to rollback is very important in my opinion.
Could it be an idea to introduce a command line switch, to let the user choose between DELETE and TRUNCATE? With TRUNCATE (the current behaviour) as the default if e.g. --use_delete is not provided?
(In reply to Magnus Enger from comment #7) > Could it be an idea to introduce a command line switch, to let the user > choose between DELETE and TRUNCATE? With TRUNCATE (the current behaviour) as > the default if e.g. --use_delete is not provided? I'd think that --use_truncate would be better, and default with DELETE, because the original intent is almost always rollback, is it not? Adding a command line option would make this somewhere between a fix and enhancement (not going to make 3.22, is it?). Changing it to use DELETE would just be a fix (could make it into 3.22), in my opinion.
Keep getting "Cannot truncate a table referenced in a foreign key constraint" errors when using the -d option. So for me the only options have been to change the script to using DELETE FROM instead, or deleting manually through the MySQL cli.
*** Bug 21655 has been marked as a duplicate of this bug. ***
I think it would be worthwhile fixing this now, especially since MySQL >= 5.5 should be much more common now than in 2014 when this bug was initially filed? Can we agree an Magnus fix? (comment#5)
Created attachment 84961 [details] [review] Bug 12488 - Make bulkmarcimport.pl -d use DELETE instead of TRUNCATE On MySQL >= 5.5 bulkmarcimport.pl with the -d (delete) switch gives an error like "Cannot truncate a table referenced in a foreign key constraint". This patch proposes to replace the offending TRUNCATE with DELETE. Auto incerement counters are reset to 1 to preserve the functionality from TRUNCATE. To test: - Make sure you havae a test database with some records and items - Run bulkmarcimport.pl with the -d switch - Observe the error described above - Apply this patch - Run bulkmarcimport.pl with the -d switch again - Observe the lack of an error - Verify that the newly imported records and items have biblionumber and itemnumbers starting with 1
I added a patch so there is something concrete to test. :-)
It's eminently sane to make this change and it works for me.. Signing Off.
Created attachment 84963 [details] [review] Bug 12488: Make bulkmarcimport.pl -d use DELETE instead of TRUNCATE On MySQL >= 5.5 bulkmarcimport.pl with the -d (delete) switch gives an error like "Cannot truncate a table referenced in a foreign key constraint". This patch proposes to replace the offending TRUNCATE with DELETE. Auto incerement counters are reset to 1 to preserve the functionality from TRUNCATE. To test: - Make sure you havae a test database with some records and items - Run bulkmarcimport.pl with the -d switch - Observe the error described above - Apply this patch - Run bulkmarcimport.pl with the -d switch again - Observe the lack of an error - Verify that the newly imported records and items have biblionumber and itemnumbers starting with 1 Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> https://bugs.koha-community.org/show_bug.cgi?id=21488
Created attachment 85148 [details] [review] Bug 12488: Make bulkmarcimport.pl -d use DELETE instead of TRUNCATE On MySQL >= 5.5 bulkmarcimport.pl with the -d (delete) switch gives an error like "Cannot truncate a table referenced in a foreign key constraint". This patch proposes to replace the offending TRUNCATE with DELETE. Auto incerement counters are reset to 1 to preserve the functionality from TRUNCATE. To test: - Make sure you havae a test database with some records and items - Run bulkmarcimport.pl with the -d switch - Observe the error described above - Apply this patch - Run bulkmarcimport.pl with the -d switch again - Observe the lack of an error - Verify that the newly imported records and items have biblionumber and itemnumbers starting with 1 Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
@RM: We still keep: $dbh->do("truncate auth_header"); $dbh->do("truncate zebraqueue");
(In reply to Marcel de Rooy from comment #17) > @RM: We still keep: > $dbh->do("truncate auth_header"); > $dbh->do("truncate zebraqueue"); The lowercase here is bad here imo btw. Harder to locate..
Awesome work all! Pushed to master for 19.05
Pushed to 18.11.x for 18.11.04
backported to 18.05.x for 18.05.11