Greetings,

...

check out, check in, restart mysql server, check out, check in... old_issues bug!

What exactly is the bug?

Loss of history. Unless you do this after the server restart: use koha_library; insert into issues (borrowernumber) values (1); -- this will fill the gap, and should restore it. actually, whatever the mysql is to set the auto_increment to max(issue_id)+1 from old_issues would work too. GPML, Mark Tompsett

Is there some sort of pragma or option we could set on the database or table to prevent this autooptimization? On Sun, Sep 4, 2016, 18:20 David Cook <dcook@prosentient.com.au> wrote:

Glad to see someone else looking at this bug. We had this happen just the other day.

Mark, I think the reset of auto_increment also happens after OPTIMIZE TABLE, if I recall correctly. Very annoying...

Owen, you create a new entry in issues which gets an auto_id of 500. That bumps up the auto_increment to 501. However, if you move that entry into old_issues - with an id of 500 - then restart the MySQL server (it seems) or run OPTIMIZE TABLE issues, the auto_increment for issues is reset to 500. You try to check in that check out, and you get a software error, because there's already an entry with a primary key of 500 in the old_issues table.

Not only is there a loss of history, but I'm pretty sure that newer issue stays in the issues table, because it can't be moved, and that'll likely have all sorts of flow on effects.

Fun times!

I noticed this happening for reserves as well last year or the year before I think. We've tried to mitigate it locally, since I don't really see a way of fixing this issue with the current Koha methodology of using deletedtables...

David Cook Systems Librarian Prosentient Systems 72/330 Wattle St Ultimo, NSW 2007 Australia

Office: 02 9212 0899 Direct: 02 8005 0595

...

-----Original Message----- From: koha-devel-bounces@lists.koha-community.org [mailto:koha-devel- bounces@lists.koha-community.org] On Behalf Of Mark Tompsett Sent: Wednesday, 31 August 2016 11:42 PM To: Owen Leonard <oleonard@myacpl.org> Cc: Koha-devel <koha-devel@lists.koha-community.org> Subject: Re: [Koha-devel] Serious problem

Greetings,

...

...

check out, check in, restart mysql server, check out, check in... old_issues bug!

...

What exactly is the bug?

Loss of history. Unless you do this after the server restart: use koha_library; insert into issues (borrowernumber) values (1); -- this will fill the gap, and should restore it. actually, whatever the mysql is to set the auto_increment to max(issue_id)+1 from old_issues would work too.

GPML, Mark Tompsett

_______________________________________________ Koha-devel mailing list Koha-devel@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/

_______________________________________________ Koha-devel mailing list Koha-devel@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/

* Mark Tompsett (mtompset@hotmail.com) wrote:

Greetings,

I just hacked around it? https://github.com/mtompset/scripts/blob/master/innodb_hack.pl Yes, it’s hard coded and ugly, but it is semi-functional. And surely, there is a better way. Which is why I brought this issue up. Smile GPML, Mark Tompsett

How about we just don't insert that column into the oldissues table, make it auto incement like issues, then it just auto increments itself. Then we don't have to care, afaik nothing actually uses it, nothing is foreign keyed to it. Chris

From: Jesse Sent: Monday, September 05, 2016 4:15 PM To: David Cook ; Mark Tompsett ; Owen Leonard Cc: Koha-devel Subject: Re: [Koha-devel] Serious problem

Is there some sort of pragma or option we could set on the database or table to prevent this autooptimization?

On Sun, Sep 4, 2016, 18:20 David Cook <dcook@prosentient.com.au> wrote:

Glad to see someone else looking at this bug. We had this happen just the other day.

Mark, I think the reset of auto_increment also happens after OPTIMIZE TABLE, if I recall correctly. Very annoying...

Owen, you create a new entry in issues which gets an auto_id of 500. That bumps up the auto_increment to 501. However, if you move that entry into old_issues - with an id of 500 - then restart the MySQL server (it seems) or run OPTIMIZE TABLE issues, the auto_increment for issues is reset to 500. You try to check in that check out, and you get a software error, because there's already an entry with a primary key of 500 in the old_issues table.

Not only is there a loss of history, but I'm pretty sure that newer issue stays in the issues table, because it can't be moved, and that'll likely have all sorts of flow on effects.

Fun times!

I noticed this happening for reserves as well last year or the year before I think. We've tried to mitigate it locally, since I don't really see a way of fixing this issue with the current Koha methodology of using deletedtables...

David Cook Systems Librarian Prosentient Systems 72/330 Wattle St Ultimo, NSW 2007 Australia

Office: 02 9212 0899 Direct: 02 8005 0595

> -----Original Message----- > From: koha-devel-bounces@lists.koha-community.org [mailto:koha-devel- > bounces@lists.koha-community.org] On Behalf Of Mark Tompsett > Sent: Wednesday, 31 August 2016 11:42 PM > To: Owen Leonard <oleonard@myacpl.org> > Cc: Koha-devel <koha-devel@lists.koha-community.org> > Subject: Re: [Koha-devel] Serious problem > > Greetings, > > >> check out, check in, restart mysql server, check out, check in... > >> old_issues bug! > > > What exactly is the bug? > > Loss of history. > Unless you do this after the server restart: > use koha_library; > insert into issues (borrowernumber) values (1); > -- this will fill the gap, and should restore it. > actually, whatever the mysql is to set the auto_increment to > max(issue_id)+1 from old_issues would work too. > > GPML, > Mark Tompsett > > _______________________________________________ > Koha-devel mailing list > Koha-devel@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/

_______________________________________________ Koha-devel mailing list Koha-devel@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/

_______________________________________________ Koha-devel mailing list Koha-devel@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/

-- Chris Cormack Catalyst IT Ltd. +64 4 803 2238 PO Box 11-053, Manners St, Wellington 6142, New Zealand

* Mark Tompsett (mtompset@hotmail.com) wrote:

Greetings,

And what if you wish to undo a checkout? There are advantages to having it. -- Perhaps we should ponder why we are uniquely keying on the id in old_issues?

What? Undo an old check out? You mean like unreturn something? There is no code to do that. If you want to unreturn something, you just reissue it. It is just a history file, if it just auto_incremented the same way as issues does then the whole problem goes away. I see no more advantage to having no issues_id, than to have one that auto increments. You'd still have to have a custom query either way if someone did decide to write a bizarre piece of code that unreturned items. (Please no one do this, it's a horrible idea) Chris -- Chris Cormack Catalyst IT Ltd. +64 4 803 2238 PO Box 11-053, Manners St, Wellington 6142, New Zealand

* Chris Cormack (chrisc@catalyst.net.nz) wrote:

* Chris Cormack (chrisc@catalyst.net.nz) wrote:

...

* Mark Tompsett (mtompset@hotmail.com) wrote:

...

Greetings,

And what if you wish to undo a checkout? There are advantages to having it. -- Perhaps we should ponder why we are uniquely keying on the id in old_issues?

What? Undo an old check out? You mean like unreturn something? There is no code to do that. If you want to unreturn something, you just reissue it.

It is just a history file, if it just auto_incremented the same way as issues does then the whole problem goes away.

I see no more advantage to having no issues_id, than to have one that auto increments. You'd still have to have a custom query either way if someone did decide to write a bizarre piece of code that unreturned items. (Please no one do this, it's a horrible idea)

Chris

Of course, there is no reason that those tables (deleted* and old_*) need to be innodb. We do not foreign key to them. If they were MyISAM, then this problem would go away.

Answering myself again, this won't work, because the original table will still keep handing out the wrong IDs. What does MariaDB do? With their XtraDB tables (which are used by default still called InnoDB for compatibility) Chris

_______________________________________________ Koha-devel mailing list Koha-devel@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/

-- Chris Cormack Catalyst IT Ltd. +64 4 803 2238 PO Box 11-053, Manners St, Wellington 6142, New Zealand

-----Original Message----- From: koha-devel-bounces@lists.koha-community.org [mailto:koha-devel- bounces@lists.koha-community.org] On Behalf Of Chris Cormack Sent: Tuesday, 6 September 2016 6:56 AM To: Mark Tompsett <mtompset@hotmail.com> Cc: Koha-devel <koha-devel@lists.koha-community.org> Subject: Re: [Koha-devel] Serious problem

* Mark Tompsett (mtompset@hotmail.com) wrote:

...

Greetings,

And what if you wish to undo a checkout? There are advantages to having it. -- Perhaps we should ponder why we are uniquely keying on the id in old_issues?

What? Undo an old check out? You mean like unreturn something? There is no code to do that. If you want to unreturn something, you just reissue it.

It is just a history file, if it just auto_incremented the same way as issues does then the whole problem goes away.

I see no more advantage to having no issues_id, than to have one that auto increments. You'd still have to have a custom query either way if someone did decide to write a bizarre piece of code that unreturned items. (Please no one do

I suppose another option would be to still save the id from issues and reserves, but have them as a non-unique field, and have a separate unique id for the old_issues and old_reserves tables. It wouldn't be useful for "undoing" things, because you could still have duplicate IDs in the non-unique field, but I suppose it would preserve your history best. The trigger idea from my other email might be the easiest way to do it, although triggers aren't all that nice to manage. Chris, what about deletedbiblio and deletedbiblioitem tables? The history there might be more important, as I think we're now including those in the OAI-PMH responses? I reckon those tables would be vulnerable to this as well, although perhaps less likely due to issues and reserves being more high frequency in terms of inserts/deletions. David Cook Systems Librarian Prosentient Systems 72/330 Wattle St Ultimo, NSW 2007 Australia Office: 02 9212 0899 Direct: 02 8005 0595 this,

it's a horrible idea)

Chris

-- Chris Cormack Catalyst IT Ltd. +64 4 803 2238 PO Box 11-053, Manners St, Wellington 6142, New Zealand

Mmm, I think that I see what you're saying. In theory, for a library with high circulation, you would always have a newer checkout in the issues table, when you're doing a check-in, so it's unlikely that your IDs in the issues table would be lower than your IDs in your old_issues table. When I referred to frequency, I meant that there is probably a higher frequency of checking in items or fulfilling/cancelling reserves than deleting biblio records, although I suppose that's a subjective claim. In any case, that's a good point that the auto_increment is more likely to happen in a library with low circulation than high circulation. I've noticed this problem for years, but previously my communications about it have been ignored, so we've just mitigated it locally via scripts. Historically, we thought it only happened after running OPTIMZE TABLE, but thanks to Mark we see that it's also after MySQL server restarts. David Cook Systems Librarian Prosentient Systems 72/330 Wattle St Ultimo, NSW 2007 Australia Office: 02 9212 0899 Direct: 02 8005 0595

-----Original Message----- From: Chris Cormack [mailto:chrisc@catalyst.net.nz] Sent: Tuesday, 6 September 2016 11:40 AM To: David Cook <dcook@prosentient.com.au> Cc: 'Koha-devel' <koha-devel@lists.koha-community.org> Subject: Re: [Koha-devel] Serious problem

* David Cook (dcook@prosentient.com.au) wrote:

...

Chris, what about deletedbiblio and deletedbiblioitem tables? The history there might be more important, as I think we're now including those in the OAI-PMH responses? I reckon those tables would be vulnerable to this as well, although perhaps less likely due to issues and reserves being more high frequency in terms of inserts/deletions.

As Katrin rightly pointed out to me, frequency is not really the issue. This is only a problem when the most recently inserted item is deleted/return/issued. And then the server is restarted right then.

If you delete from anywhere except the tip of the table, it doesn't matter. So if you issue and return something (before anyone else issues something) and restart the server then you have the problem with issues. So in fact the higher the frequency the way less likely this will be to happen.

It will most likely only ever happen in a library that does almost no circulation.

Chris

-- Chris Cormack Catalyst IT Ltd. +64 4 803 2238 PO Box 11-053, Manners St, Wellington 6142, New Zealand

Jesse: I don’t think so. According to this ( <http://bugs.mysql.com/bug.php?id=18274> http://bugs.mysql.com/bug.php?id=18274), the OPTIMIZE TABLE issue is said to be fixed at least as of MySQL 5.5.29, but we still notice it on 5.5.46 of MariaDB. I suppose I don’t know how closely the versions track, but we’re still having the problem in any case. According to the MySQL 5.5 docs ( <http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html> http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html), it seems that the auto_increment is stored in memory and not on disk, so that’s why it resets after a restart. Some people suggest using triggers to workaround this ( <http://bugs.mysql.com/bug.php?id=727> http://bugs.mysql.com/bug.php?id=727), although I wonder if it actually works correctly with mysql_insert_id() which Koha uses a lot. Would have to test that. There’s something funny about triggers and mysql_insert_id()… although it’s not quite clear from what I’m reading: <http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id> http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_l... http://bugs.mysql.com/bug.php?id=727 <https://www.slicewise.net/datenbanken/mysql-innodb-duplicate-key-entries-nach-archivierung-vermeiden/> https://www.slicewise.net/datenbanken/mysql-innodb-duplicate-key-entries-nac... I think maybe that means that you can’t use LAST_INSERT_ID() within the trigger, but maybe you still get the correct ID after your insert… Some folk suggest emulating a sequence model, like the one used in PostgreSQL, but since it would hand-rolled, that doesn’t sound very ideal. Also don’t know how this would affect mysql_insert_id()… With a bit of testing, it looks like the before insert trigger might be the best bet… David Cook Systems Librarian Prosentient Systems 72/330 Wattle St Ultimo, NSW 2007 Australia Office: 02 9212 0899 Direct: 02 8005 0595 From: Jesse [mailto:pianohacker@gmail.com] Sent: Tuesday, 6 September 2016 6:16 AM To: David Cook <dcook@prosentient.com.au>; Mark Tompsett <mtompset@hotmail.com>; Owen Leonard <oleonard@myacpl.org> Cc: Koha-devel <koha-devel@lists.koha-community.org> Subject: Re: [Koha-devel] Serious problem Is there some sort of pragma or option we could set on the database or table to prevent this autooptimization? On Sun, Sep 4, 2016, 18:20 David Cook <dcook@prosentient.com.au <mailto:dcook@prosentient.com.au> > wrote: Glad to see someone else looking at this bug. We had this happen just the other day. Mark, I think the reset of auto_increment also happens after OPTIMIZE TABLE, if I recall correctly. Very annoying... Owen, you create a new entry in issues which gets an auto_id of 500. That bumps up the auto_increment to 501. However, if you move that entry into old_issues - with an id of 500 - then restart the MySQL server (it seems) or run OPTIMIZE TABLE issues, the auto_increment for issues is reset to 500. You try to check in that check out, and you get a software error, because there's already an entry with a primary key of 500 in the old_issues table. Not only is there a loss of history, but I'm pretty sure that newer issue stays in the issues table, because it can't be moved, and that'll likely have all sorts of flow on effects. Fun times! I noticed this happening for reserves as well last year or the year before I think. We've tried to mitigate it locally, since I don't really see a way of fixing this issue with the current Koha methodology of using deletedtables... David Cook Systems Librarian Prosentient Systems 72/330 Wattle St Ultimo, NSW 2007 Australia Office: 02 9212 0899 Direct: 02 8005 0595

-----Original Message----- From: koha-devel-bounces@lists.koha-community.org <mailto:koha-devel-bounces@lists.koha-community.org> [mailto:koha-devel- <mailto:koha-devel-> bounces@lists.koha-community.org <mailto:bounces@lists.koha-community.org> ] On Behalf Of Mark Tompsett Sent: Wednesday, 31 August 2016 11:42 PM To: Owen Leonard <oleonard@myacpl.org <mailto:oleonard@myacpl.org> > Cc: Koha-devel <koha-devel@lists.koha-community.org <mailto:koha-devel@lists.koha-community.org> > Subject: Re: [Koha-devel] Serious problem

Greetings,

...

...

check out, check in, restart mysql server, check out, check in... old_issues bug!

...

What exactly is the bug?

Loss of history. Unless you do this after the server restart: use koha_library; insert into issues (borrowernumber) values (1); -- this will fill the gap, and should restore it. actually, whatever the mysql is to set the auto_increment to max(issue_id)+1 from old_issues would work too.

GPML, Mark Tompsett

_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org <mailto:Koha-devel@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/ <http://community.org/> bugs : http://bugs.koha-community.org/

_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org <mailto:Koha-devel@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/