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_last-insert-id 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-nach-archivierung-vermeiden/ 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:[email protected]] Sent: Tuesday, 6 September 2016 6:16 AM To: David Cook <[email protected]>; Mark Tompsett <[email protected]>; Owen Leonard <[email protected]> Cc: Koha-devel <[email protected]> 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 <[email protected] <mailto:[email protected]> > 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: [email protected] > <mailto:[email protected]> [mailto:koha-devel- > <mailto:koha-devel-> > [email protected] <mailto:[email protected]> ] > On Behalf Of Mark Tompsett > Sent: Wednesday, 31 August 2016 11:42 PM > To: Owen Leonard <[email protected] <mailto:[email protected]> > > Cc: Koha-devel <[email protected] > <mailto:[email protected]> > > 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 > [email protected] > <mailto:[email protected]> > 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 [email protected] <mailto:[email protected]> 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 [email protected] 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/
