Bug 12488

Summary: Make bulkmarcimport.pl -d use DELETE instead of TRUNCATE
Product: Koha Reporter: Pablo AB <pablo.bianchi>
Component: Command-line UtilitiesAssignee: Magnus Enger <magnus>
Status: CLOSED FIXED QA Contact: Marcel de Rooy <m.de.rooy>
Severity: normal    
Priority: P5 - low CC: arouss1980, bargioni, fridolin.somers, glasklas, katrin.fischer, lucas, m.de.rooy, magnus, martin.renvoize, matted-34813, mtompset, nick
Version: master   
Hardware: All   
OS: All   
See Also: http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12486
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Attachments: Bug 12488 - Make bulkmarcimport.pl -d use DELETE instead of TRUNCATE
Bug 12488: Make bulkmarcimport.pl -d use DELETE instead of TRUNCATE
Bug 12488: Make bulkmarcimport.pl -d use DELETE instead of TRUNCATE

Description Pablo AB 2014-06-26 13:25:45 UTC
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.
Comment 1 Katrin Fischer 2014-11-09 22:35:28 UTC
Still valid on current master.
Comment 2 Magnus Enger 2014-11-21 11:07:16 UTC
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?
Comment 3 Magnus Enger 2014-11-21 11:27:59 UTC
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.
Comment 4 wajasu 2014-11-21 17:42:21 UTC
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.
Comment 5 Magnus Enger 2015-03-25 10:07:39 UTC
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.
Comment 6 Mark Tompsett 2015-06-03 13:53:02 UTC
(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.
Comment 7 Magnus Enger 2015-10-27 10:12:31 UTC
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?
Comment 8 Mark Tompsett 2015-10-27 14:31:04 UTC
(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.
Comment 9 David Gustafsson 2016-12-27 12:07:30 UTC
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.
Comment 10 Jonathan Druart 2018-10-26 12:09:40 UTC
*** Bug 21655 has been marked as a duplicate of this bug. ***
Comment 11 Katrin Fischer 2019-02-10 10:42:03 UTC
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)
Comment 12 Magnus Enger 2019-02-11 10:23:28 UTC
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
Comment 13 Magnus Enger 2019-02-11 10:24:30 UTC
I added a patch so there is something concrete to test. :-)
Comment 14 Martin Renvoize 2019-02-11 10:40:01 UTC
It's eminently sane to make this change and it works for me.. Signing Off.
Comment 15 Martin Renvoize 2019-02-11 10:44:15 UTC
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
Comment 16 Marcel de Rooy 2019-02-15 09:16:49 UTC
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>
Comment 17 Marcel de Rooy 2019-02-15 09:21:08 UTC
@RM: We still keep:
$dbh->do("truncate auth_header");
$dbh->do("truncate zebraqueue");
Comment 18 Marcel de Rooy 2019-02-15 09:22:43 UTC
(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..
Comment 19 Nick Clemens 2019-02-15 18:45:52 UTC
Awesome work all!

Pushed to master for 19.05
Comment 20 Martin Renvoize 2019-02-25 13:30:05 UTC
Pushed to 18.11.x for 18.11.04
Comment 21 Lucas Gass 2019-03-05 15:29:17 UTC
backported to 18.05.x for 18.05.11