AUTO_INCREMENT fix - prevent data lost

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

AUTO_INCREMENT fix - prevent data lost

Jonathan Druart
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
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

Rodrigo Santellan
I only have one question about it, it only affects MySQL 5.7 and MariaDB 10.02?

Koha should not say that it works with certain version of the MySQL or MariaDB? I been struggling a lot with issues on the DB because I'm on new version.

Regards,
Rodrigo


On Wed, Jul 12, 2017 at 3:11 PM, Jonathan Druart <[hidden email]> wrote:
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
[hidden email]
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/


_______________________________________________
Koha-devel mailing list
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

Jonathan Druart
It affects all versions of Koha with any versions of MySQL or MariaDB.
Last versions of Koha have been affected with bad bugs because we tried to fix the problem at code level.
We introduced other related issues, see bug 18242 and 18651.
If you have 18242 without 18651 you may encounter problems when checking in.
That is why it is preferable to not try to fix the other tables ('borrowers', 'reserves' and 'biblio') at code level using the same trick we did for 'issues'.

On Wed, 12 Jul 2017 at 15:44 Rodrigo Santellan <[hidden email]> wrote:
I only have one question about it, it only affects MySQL 5.7 and MariaDB 10.02?

Koha should not say that it works with certain version of the MySQL or MariaDB? I been struggling a lot with issues on the DB because I'm on new version.

Regards,
Rodrigo


On Wed, Jul 12, 2017 at 3:11 PM, Jonathan Druart <[hidden email]> wrote:
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
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

David Cook
In reply to this post by Jonathan Druart

I’ve looked at the wiki page, but I haven’t looked at Bugzilla or the source code. Why on Earth would we get lost data? Shouldn’t we be copying the code to the “deleted*” tables and THEN deleting it from the main table?

 

In pre-5.7 versions, you could produce this same behaviour by running OPTIMIZE on a table; it would reset the auto increment counter for the table, and it would cause errors because you couldn’t delete records because there was already a record with that key in the deleted* table.

 

Anyway, I don’t have time to dwell at the moment, but just adding that to the mix.

 

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

 

Office: 02 9212 0899

Direct: 02 8005 0595

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Jonathan Druart
Sent: Thursday, 13 July 2017 4:12 AM
To: [hidden email]
Subject: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

 

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
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

Katrin Fischer-2

Hi David,

the problem is not the sequence. The problem is doubled up ids between the tables. If you try to move an entry with an id that already exists in the deleted* or old* table, the data will get lost. But doubled up ids are also likely to cause even more trouble.

Katrin


On 13.07.2017 05:07, David Cook wrote:

I’ve looked at the wiki page, but I haven’t looked at Bugzilla or the source code. Why on Earth would we get lost data? Shouldn’t we be copying the code to the “deleted*” tables and THEN deleting it from the main table?

 

In pre-5.7 versions, you could produce this same behaviour by running OPTIMIZE on a table; it would reset the auto increment counter for the table, and it would cause errors because you couldn’t delete records because there was already a record with that key in the deleted* table.

 

Anyway, I don’t have time to dwell at the moment, but just adding that to the mix.

 

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

 

Office: 02 9212 0899

Direct: 02 8005 0595

 

From: [hidden email] [[hidden email]] On Behalf Of Jonathan Druart
Sent: Thursday, 13 July 2017 4:12 AM
To: [hidden email]
Subject: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

 

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
[hidden email]
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/


_______________________________________________
Koha-devel mailing list
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

David Cook

Hi Katrin,

 

I think there must be some miscommunication here. I’ve experienced the doubled up IDs with reserves, and no data has been lost. It creates a fatal error, since the key already exists in the deleted/old table, and the whole process stops. If a person deleted the data out of the deleted/old table in order to perform the delete, then I could see data being lost, but that’s it. If there is data being lost (perhaps with a different tables than reserves), then something must be horribly wrong with the code to allow it to delete before checking that it can be copied/moved first. That’s all I’m saying.

 

In either case, it’s not good.

 

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

 

Office: 02 9212 0899

Direct: 02 8005 0595

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Katrin
Sent: Friday, 14 July 2017 7:00 PM
To: [hidden email]
Subject: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

 

Hi David,

the problem is not the sequence. The problem is doubled up ids between the tables. If you try to move an entry with an id that already exists in the deleted* or old* table, the data will get lost. But doubled up ids are also likely to cause even more trouble.

Katrin

 

On 13.07.2017 05:07, David Cook wrote:

I’ve looked at the wiki page, but I haven’t looked at Bugzilla or the source code. Why on Earth would we get lost data? Shouldn’t we be copying the code to the “deleted*” tables and THEN deleting it from the main table?

 

In pre-5.7 versions, you could produce this same behaviour by running OPTIMIZE on a table; it would reset the auto increment counter for the table, and it would cause errors because you couldn’t delete records because there was already a record with that key in the deleted* table.

 

Anyway, I don’t have time to dwell at the moment, but just adding that to the mix.

 

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

 

Office: 02 9212 0899

Direct: 02 8005 0595

 

From: [hidden email] [[hidden email]] On Behalf Of Jonathan Druart
Sent: Thursday, 13 July 2017 4:12 AM
To: [hidden email]
Subject: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

 

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
[hidden email]
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/

 


_______________________________________________
Koha-devel mailing list
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

Jonathan Druart
In reply to this post by Jonathan Druart
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 <[hidden email]> wrote:
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
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

David Cook

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

Direct: 02 8005 0595

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Jonathan Druart
Sent: Thursday, 20 July 2017 1:39 AM
To: [hidden email]
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 <[hidden email]> wrote:

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
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

Jonathan Druart
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 <[hidden email]> 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: <a href="tel:02%2092%2012%2008%2099" value="+33292120899" target="_blank">02 9212 0899

Direct: <a href="tel:02%2080%2005%2005%2095" value="+33280050595" target="_blank">02 8005 0595

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Jonathan Druart
Sent: Thursday, 20 July 2017 1:39 AM


To: [hidden email]
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 <[hidden email]> wrote:

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
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

Michael Hafen-4
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 <[hidden email]> 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 <[hidden email]> 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: <a href="tel:02%2092%2012%2008%2099" value="+33292120899" target="_blank">02 9212 0899

Direct: <a href="tel:02%2080%2005%2005%2095" value="+33280050595" target="_blank">02 8005 0595

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Jonathan Druart
Sent: Thursday, 20 July 2017 1:39 AM


To: [hidden email]
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 <[hidden email]> wrote:

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
[hidden email]
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


_______________________________________________
Koha-devel mailing list
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

Jonathan Druart
In reply to this post by Jonathan Druart
And see bug 18966 for a third (last!?) round. I tried to resume in the description of the bug what has been done and what I propose to move forward.
BE INVOLVED!

On Wed, 12 Jul 2017 at 15:11 Jonathan Druart <[hidden email]> wrote:
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
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

David Cook
In reply to this post by Jonathan Druart

Thanks for that excerpt, Jonathan. We run our systems with DEBUG on, which must be why we’re not losing data!

 

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

 

Office: 02 9212 0899

Direct: 02 8005 0595

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Jonathan Druart
Sent: Thursday, 20 July 2017 10:10 AM
To: [hidden email]
Subject: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

 

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 <[hidden email]> 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: <a href="tel:02%2092%2012%2008%2099" target="_blank">02 9212 0899

Direct: <a href="tel:02%2080%2005%2005%2095" target="_blank">02 8005 0595

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Jonathan Druart
Sent: Thursday, 20 July 2017 1:39 AM


To: [hidden email]
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 <[hidden email]> wrote:

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
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

David Cook
In reply to this post by Michael Hafen-4

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: [hidden email] [mailto:[hidden email]] On Behalf Of Michael Hafen
Sent: Friday, 21 July 2017 1:47 AM
To: Jonathan Druart <[hidden email]>
Cc: koha-devel <[hidden email]>
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 <[hidden email]> 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 <[hidden email]> 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: <a href="tel:02%2092%2012%2008%2099" target="_blank">02 9212 0899

Direct: <a href="tel:02%2080%2005%2005%2095" target="_blank">02 8005 0595

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Jonathan Druart
Sent: Thursday, 20 July 2017 1:39 AM


To: [hidden email]
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 <[hidden email]> wrote:

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
[hidden email]
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


_______________________________________________
Koha-devel mailing list
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

David Cook
In reply to this post by Jonathan Druart

Jonathan, have you thought anymore about merging the deleted* tables back in with the active tables? This would have the benefit of fixing problems with things like “Checkout history” where issues containing deleted borrowers or deleted items have these details nullified and thus disappear from the system.

 

I suppose we’d have to make sure the deleted flag is indexed, and it would mean changing many SQL reports not to include deleted records… so that might be easier said than done.

 

Alternatively, maybe we need more robust logical (ie code) ways of managing the relationships between active and inactive (ie deleted) data? I mean… what’s the point of keeping a history of data if we can’t use it?

 

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

 

Office: 02 9212 0899

Direct: 02 8005 0595

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Jonathan Druart
Sent: Friday, 21 July 2017 3:23 AM
To: [hidden email]
Subject: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

 

And see bug 18966 for a third (last!?) round. I tried to resume in the description of the bug what has been done and what I propose to move forward.
BE INVOLVED!

 

On Wed, 12 Jul 2017 at 15:11 Jonathan Druart <[hidden email]> wrote:

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
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

Katrin Fischer-2

Merging the tables can only be a long term solution, but we need a solution for the stable branches now. If we agree to merge, not only reports will be affected for some of the tables, it will be a lot of work.

Katrin


On 21.07.2017 05:57, David Cook wrote:

Jonathan, have you thought anymore about merging the deleted* tables back in with the active tables? This would have the benefit of fixing problems with things like “Checkout history” where issues containing deleted borrowers or deleted items have these details nullified and thus disappear from the system.

 

I suppose we’d have to make sure the deleted flag is indexed, and it would mean changing many SQL reports not to include deleted records… so that might be easier said than done.

 

Alternatively, maybe we need more robust logical (ie code) ways of managing the relationships between active and inactive (ie deleted) data? I mean… what’s the point of keeping a history of data if we can’t use it?

 

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

 

Office: 02 9212 0899

Direct: 02 8005 0595

 

From: [hidden email] [[hidden email]] On Behalf Of Jonathan Druart
Sent: Friday, 21 July 2017 3:23 AM
To: [hidden email]
Subject: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

 

And see bug 18966 for a third (last!?) round. I tried to resume in the description of the bug what has been done and what I propose to move forward.
BE INVOLVED!

 

On Wed, 12 Jul 2017 at 15:11 Jonathan Druart <[hidden email]> wrote:

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
[hidden email]
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/


_______________________________________________
Koha-devel mailing list
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

Marcel de Rooy
In reply to this post by David Cook

Not sure if 18966 is the way to go, "halting the system" until we fixed the autoincrement.

The solution of merging the tables, as David refers to, is the ideal one, but it seems too big to implement right now, as we need to fix stable branches right now.
The renumbering fix depended on locking. The locks were removed quickly, but did we search further on that road? Why wouldn't it be possible to lock a table somehow?
If we completely revert the renumbering fix, I think we should test at each insert if the new issue id is greater than the max in old_issues. If not, fix it in that process. This would prevent new clashes. And when we move to old_issues, take some temporary data loss for granted (if the id's clash, choose for the newest one or the one with a fine or so).

Marcel


Van: [hidden email] <[hidden email]> namens David Cook <[hidden email]>
Verzonden: vrijdag 21 juli 2017 05:57
Aan: 'Jonathan Druart'; [hidden email]
Onderwerp: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost
 

Jonathan, have you thought anymore about merging the deleted* tables back in with the active tables? This would have the benefit of fixing problems with things like “Checkout history” where issues containing deleted borrowers or deleted items have these details nullified and thus disappear from the system.

 

I suppose we’d have to make sure the deleted flag is indexed, and it would mean changing many SQL reports not to include deleted records… so that might be easier said than done.

 

Alternatively, maybe we need more robust logical (ie code) ways of managing the relationships between active and inactive (ie deleted) data? I mean… what’s the point of keeping a history of data if we can’t use it?

 

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

 

Office: 02 9212 0899

Direct: 02 8005 0595

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Jonathan Druart
Sent: Friday, 21 July 2017 3:23 AM
To: [hidden email]
Subject: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

 

And see bug 18966 for a third (last!?) round. I tried to resume in the description of the bug what has been done and what I propose to move forward.
BE INVOLVED!

 

On Wed, 12 Jul 2017 at 15:11 Jonathan Druart <[hidden email]> wrote:

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
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

FW: AUTO_INCREMENT fix - prevent data lost

Marcel de Rooy

Added bug 18970 for the test each new issue id - approach.




Van: Marcel de Rooy
Verzonden: vrijdag 21 juli 2017 07:58
Aan: [hidden email]
Onderwerp: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost
 

Not sure if 18966 is the way to go, "halting the system" until we fixed the autoincrement.

The solution of merging the tables, as David refers to, is the ideal one, but it seems too big to implement right now, as we need to fix stable branches right now.
The renumbering fix depended on locking. The locks were removed quickly, but did we search further on that road? Why wouldn't it be possible to lock a table somehow?
If we completely revert the renumbering fix, I think we should test at each insert if the new issue id is greater than the max in old_issues. If not, fix it in that process. This would prevent new clashes. And when we move to old_issues, take some temporary data loss for granted (if the id's clash, choose for the newest one or the one with a fine or so).

Marcel


Van: [hidden email] <[hidden email]> namens David Cook <[hidden email]>
Verzonden: vrijdag 21 juli 2017 05:57
Aan: 'Jonathan Druart'; [hidden email]
Onderwerp: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost
 

Jonathan, have you thought anymore about merging the deleted* tables back in with the active tables? This would have the benefit of fixing problems with things like “Checkout history” where issues containing deleted borrowers or deleted items have these details nullified and thus disappear from the system.

 

I suppose we’d have to make sure the deleted flag is indexed, and it would mean changing many SQL reports not to include deleted records… so that might be easier said than done.

 

Alternatively, maybe we need more robust logical (ie code) ways of managing the relationships between active and inactive (ie deleted) data? I mean… what’s the point of keeping a history of data if we can’t use it?

 

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

 

Office: 02 9212 0899

Direct: 02 8005 0595

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Jonathan Druart
Sent: Friday, 21 July 2017 3:23 AM
To: [hidden email]
Subject: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

 

And see bug 18966 for a third (last!?) round. I tried to resume in the description of the bug what has been done and what I propose to move forward.
BE INVOLVED!

 

On Wed, 12 Jul 2017 at 15:11 Jonathan Druart <[hidden email]> wrote:

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
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

Tomas Cohen Arazi
In reply to this post by Katrin Fischer-2
For reports a view can work.

El vie., 21 jul. 2017 a las 2:49, Katrin (<[hidden email]>) escribió:

Merging the tables can only be a long term solution, but we need a solution for the stable branches now. If we agree to merge, not only reports will be affected for some of the tables, it will be a lot of work.

Katrin


On 21.07.2017 05:57, David Cook wrote:

Jonathan, have you thought anymore about merging the deleted* tables back in with the active tables? This would have the benefit of fixing problems with things like “Checkout history” where issues containing deleted borrowers or deleted items have these details nullified and thus disappear from the system.

 

I suppose we’d have to make sure the deleted flag is indexed, and it would mean changing many SQL reports not to include deleted records… so that might be easier said than done.

 

Alternatively, maybe we need more robust logical (ie code) ways of managing the relationships between active and inactive (ie deleted) data? I mean… what’s the point of keeping a history of data if we can’t use it?

 

David Cook

Systems Librarian

Prosentient Systems

72/330 Wattle St

Ultimo, NSW 2007

Australia

 

Office: 02 9212 0899

Direct: 02 8005 0595

 

From: [hidden email] [[hidden email]] On Behalf Of Jonathan Druart
Sent: Friday, 21 July 2017 3:23 AM
To: [hidden email]
Subject: Re: [Koha-devel] AUTO_INCREMENT fix - prevent data lost

 

And see bug 18966 for a third (last!?) round. I tried to resume in the description of the bug what has been done and what I propose to move forward.
BE INVOLVED!

 

On Wed, 12 Jul 2017 at 15:11 Jonathan Druart <[hidden email]> wrote:

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
[hidden email]
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/

_______________________________________________
Koha-devel mailing list
[hidden email]
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/
--
Tomás Cohen Arazi
Theke Solutions (https://theke.io)
✆ +54 9351 3513384
GPG: B2F3C15F

_______________________________________________
Koha-devel mailing list
[hidden email]
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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AUTO_INCREMENT fix - prevent data lost

Ulrich Kleiber
In reply to this post by Jonathan Druart
Hi Jonathan,

I have tested the solution from
https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix.
A restart of the MySQL Server with a large Koha Database (several
hundred thousand biblio) or a large number of Koha Sites can take a long
time. This is mainly because the ALTER TABLE command rebuilds the entire
table.
Usually you can save a lot of time and resources when you update
AUTO_INCREMENT only if necessary. Here is an attempt to solve this using
the example of biblioitems/deletedbiblioitems.

USE koha_kohadev;

SET @ai = ( IFNULL( ( SELECT AUTO_INCREMENT FROM
information_schema.tables WHERE TABLE_SCHEMA = 'koha_kohadev' AND
TABLE_NAME = 'biblioitems' ), 0 ) );

SET @shadow_ai = ( IFNULL( ( SELECT MAX(biblioitemnumber) FROM
deletedbiblioitems ), 0 ) + 1 );

SET @sql = ( SELECT IF(@shadow_ai > @ai, CONCAT( 'ALTER TABLE
biblioitems AUTO_INCREMENT = ', @shadow_ai ), 'SELECT "skip"' ) );

PREPARE st FROM @sql;

EXECUTE st;

Cheers,
Uli

Ulrich Kleiber
Bibliotheksservice-Zentrum Baden-Württemberg (BSZ)
78457 Konstanz / Germany
Phone: +49 7531 88 4179
E-Mail: [hidden email]
http://www.bsz-bw.de

> On Wed, 12 Jul 2017 at 15:11 Jonathan Druart
> <[hidden email]
> <mailto:[hidden email]>> 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
> [hidden email]
> 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/
>

_______________________________________________
Koha-devel mailing list
[hidden email]
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/

smime.p7s (7K) Download Attachment
Loading...