Hi Robin! So you agree with me that Appendix F ("Resetting the Koha Database") of current (v3.16) manual is wrong? 2014-06-02 20:21 GMT-03:00 Robin Sheat <robin@catalyst.net.nz>:

Pablo Bianchi schreef op ma 02-06-2014 om 14:33 [-0300]:

...

Am I right? This could be risky, having side effects?

This could have nasty side-effects.

By turning off the ​​ constraint checks, you're not going to have any messages telling you reasons why you shouldn't do what you're doing. For example, circulation history, serial subscriptions, reserves, and anything else that links with biblios and/or items aren't going to be cleared. This means that you'll have entries in them pointing to things that don't exist.

Problems that we can avoid just with​ *DELETE FROM biblio*?

Some better solutions might be to: * Do 'DELETE FROM table;' and then reset the auto numbering back to 1, something like 'ALTER TABLE table AUTO INCREMENT=1;' This'll ensure that things that should be deleted via constraints will be, or it won't let you do it. Also ensure that things that aren't constraint-linked are deleted (I think old_issues is one of these.)

​​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* *.* 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`))*

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... ​Regards, Pablo

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

Pablo, you might as well import using the web tool, which handles deletion too. On Tue, Jun 24, 2014 at 4:38 PM, Pablo Bianchi <pablo.bianchi@gmail.com> wrote:

Hi Robin,

I'm aware about turning foreign keys constrains off is risky, what I was trying to know is how risky could be, but OK, I realize that depends of each database, and always a bad idea.

So at least we can say manual (and also bulkmarkimport.pl -d option) should be updated (MySQL >~5.5)* adding a note about DELETE FROM biblio way not always will work right away. This bring us to:

...

In some cases the constraints will propagate the delete, in other cases they won't. These differences are by design. There are good design reasons for no letting "delete biblio" table propagate in all/any cases? Propagation in any case will make really simple to update manual and bulkmarkimport.

*I'll then fill a bug report about this two issues (manual and bulkmarkimport -d option) update.

​Regards, Pablo ​ _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha

-- Tomás Cohen Arazi Prosecretaría de Informática Universidad Nacional de Córdoba ✆ +54 351 5353750 ext 13168 GPG: B76C 6E7C 2D80 551A C765 E225 0A27 2EA1 B2F3 C15F