[Koha] Wipe Koha database (just auth and biblio records)

Robin Sheat robin at catalyst.net.nz
Wed Jun 4 11:08:40 NZST 2014

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
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

More information about the Koha mailing list