[Koha-devel] AUTO_INCREMENT fix - prevent data lost

Fri Jul 21 02:12:47 CEST 2017

Not in my experience, but we are running in DEBUG mode, so you’ll typically have a max of 1 collision with a table pairing which is very easy to remedy. But that’s academic. As Jonathan has pointed out, if you don’t have DEBUG on, you’ll lose data, and that’s not good! 

Jonathan, I started reading through your links yesterday, but I got sidetracked by other priorities. I’ll look at providing more feedback today. In short, I’m not 100% confident in the MySQL init solution. While it may solve the restart issue, I don’t know that it solves the OPTIMIZE issue, so I rather something more robust and less hacky. But I’ll review all the links and provide more in-depth feedback. (I don’t think Koha by default uses OPTIMIZE, so I think it would be fair for the Koha community to ignore that issue, but it would still be unfortunate for us since we do use OPTIMIZE and that resets the auto_increment counter.)

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

Office: 02 9212 0899

Direct: 02 8005 0595

From: koha-devel-bounces at lists.koha-community.org [mailto:koha-devel-bounces at lists.koha-community.org] On Behalf Of Michael Hafen
Sent: Friday, 21 July 2017 1:47 AM
To: Jonathan Druart <jonathan.druart at bugs.koha-community.org>
Cc: koha-devel <koha-devel at lists.koha-community.org>
Subject: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

On top of that by the time you get to that error you already have a primary key conflict, between biblios and deletedbiblios as an example, because the conflict is what causes the error.  There would be a lot of table that would have to be touched to resolve the key conflict in order to maintain data integrity.

On Wed, Jul 19, 2017 at 6:10 PM, Jonathan Druart <jonathan.druart at bugs.koha-community.org <mailto:jonathan.druart at bugs.koha-community.org> > wrote:

>From Koha/Database.pm
 89             RaiseError => $ENV{DEBUG} ? 1 : 0,

=> We do not raise SQL errors.

The insert into old_issues fails, but the delete from issues is done anyway.

On Wed, 19 Jul 2017 at 20:43 David Cook <dcook at prosentient.com.au <mailto:dcook at prosentient.com.au> > wrote:

Hi Jonathan,

I appreciate what you’re trying to do here, so I’m going to take a few minutes now to check through all of these links. 

I’ve been dealing with this issue on and off over the years due to our local use of OPTIMIZE: https://bugs.mysql.com/bug.php?id=18274.  I hadn’t realized that the auto_increment reset happens on server restarts as well until you mentioned it on the list. In any case, I’m glad to see your interest in this, because this really is a frustrating issue!

I still don’t understand why you’d have data lost, because you should get a fatal software error before anything is actually lost, and that should give you the opportunity to manually repair the increment counter and problematic IDs, but… I haven’t yet read all the links, so I’ll keep reading. Regardless of this fine point, I still think it is something worth fixing, so thanks again for your work on this one!

I’ll let you know what I think/find after reviewing the links…

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

Office: 02 9212 0899 <tel:02%2092%2012%2008%2099> 

Direct: 02 8005 0595 <tel:02%2080%2005%2005%2095> 

From: koha-devel-bounces at lists.koha-community.org <mailto:koha-devel-bounces at lists.koha-community.org>  [mailto:koha-devel-bounces at lists.koha-community.org <mailto:koha-devel-bounces at lists.koha-community.org> ] On Behalf Of Jonathan Druart
Sent: Thursday, 20 July 2017 1:39 AM

To: koha-devel at lists.koha-community.org <mailto:koha-devel at lists.koha-community.org> 
Subject: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

I did not get confirmation, so I am not sure you all understand how important is this fix.

We are going to fix properly and globally a long standing issue, and prevent data lost.

I *really* want to see some of you to test this trick and confirm me that you agree on the solution and we can advertise it on the general Koha ML.

On Wed, 12 Jul 2017 at 15:11 Jonathan Druart <jonathan.druart at bugs.koha-community.org <mailto:jonathan.druart at bugs.koha-community.org> > wrote:

Hi devs,

Please review and test https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix

I have added a ref from the Koha on Debian wiki page https://wiki.koha-community.org/wiki/Koha_on_Debian#Further_Configuration

It sounds like I should add it somewhere more visible, but do not find where, any ideas?

See also bug 18931 where I would like to add a warning on the about page if data are corrupted.

Please review quickly, that way we can share the tips on the general mailing list.

Cheers,

Jonathan

_______________________________________________
Koha-devel mailing list
Koha-devel at lists.koha-community.org <mailto:Koha-devel at lists.koha-community.org> 
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/

-- 

Michael Hafen

Washington County School District Technology Department

Systems Analyst

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20170721/ac8158df/attachment.html>