[Bug 12488] New: bulkmarcimport.pl -d option should use DELETE instead of TRUNCATE
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12488 Bug ID: 12488 Summary: bulkmarcimport.pl -d option should use DELETE instead of TRUNCATE Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: trivial Priority: P5 - low Component: Command-line Utilities Assignee: gmcharlt@gmail.com Reporter: pablo.bianchi@gmail.com QA Contact: testopia@bugs.koha-community.org 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. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12488 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |katrin.fischer@bsz-bw.de --- Comment #1 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Still valid on current master. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12488 Magnus Enger <magnus@enger.priv.no> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |magnus@enger.priv.no --- Comment #2 from Magnus Enger <magnus@enger.priv.no> --- 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? -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12488 --- Comment #3 from Magnus Enger <magnus@enger.priv.no> --- 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. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12488 wajasu <matted-34813@mypacks.net> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |matted-34813@mypacks.net --- Comment #4 from wajasu <matted-34813@mypacks.net> --- 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. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12488 --- Comment #5 from Magnus Enger <magnus@enger.priv.no> --- 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. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12488 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mtompset@hotmail.com --- Comment #6 from M. Tompsett <mtompset@hotmail.com> --- (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. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12488 --- Comment #7 from Magnus Enger <magnus@libriotech.no> --- 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? -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12488 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |http://bugs.koha-community. | |org/bugzilla3/show_bug.cgi? | |id=12486 -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12488 --- Comment #8 from M. Tompsett <mtompset@hotmail.com> --- (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. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org