<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-AU link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><a name="_MailEndCompose"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>Jesse: I don’t think so.<o:p></o:p></span></a></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>According to this (</span><a href="http://bugs.mysql.com/bug.php?id=18274"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>http://bugs.mysql.com/bug.php?id=18274</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>), 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. <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>According to the MySQL 5.5 docs (</span><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>), it seems that the auto_increment is stored in memory and not on disk, so that’s why it resets after a restart. <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>Some people suggest using triggers to workaround this (</span><a href="http://bugs.mysql.com/bug.php?id=727"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>http://bugs.mysql.com/bug.php?id=727</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>), 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:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><a href="http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>http://bugs.mysql.com/bug.php?id=727<o:p></o:p></span></p><p class=MsoNormal><a href="https://www.slicewise.net/datenbanken/mysql-innodb-duplicate-key-entries-nach-archivierung-vermeiden/"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>https://www.slicewise.net/datenbanken/mysql-innodb-duplicate-key-entries-nach-archivierung-vermeiden/</span></a><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>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…<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>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()… <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>With a bit of testing, it looks like the before insert trigger might be the best bet… <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>David Cook<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Systems Librarian<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Prosentient Systems<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>72/330 Wattle St<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Ultimo, NSW 2007<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Australia<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Office: 02 9212 0899<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Direct: 02 8005 0595<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri",sans-serif'> Jesse [mailto:pianohacker@gmail.com] <br><b>Sent:</b> Tuesday, 6 September 2016 6:16 AM<br><b>To:</b> David Cook <dcook@prosentient.com.au>; Mark Tompsett <mtompset@hotmail.com>; Owen Leonard <oleonard@myacpl.org><br><b>Cc:</b> Koha-devel <koha-devel@lists.koha-community.org><br><b>Subject:</b> Re: [Koha-devel] Serious problem<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p>Is there some sort of pragma or option we could set on the database or table to prevent this autooptimization? <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>On Sun, Sep 4, 2016, 18:20 David Cook <<a href="mailto:dcook@prosentient.com.au">dcook@prosentient.com.au</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><p class=MsoNormal>Glad to see someone else looking at this bug. We had this happen just the<br>other day.<br><br>Mark, I think the reset of auto_increment also happens after OPTIMIZE TABLE,<br>if I recall correctly. Very annoying...<br><br>Owen, you create a new entry in issues which gets an auto_id of 500. That<br>bumps up the auto_increment to 501. However, if you move that entry into<br>old_issues - with an id of 500 - then restart the MySQL server (it seems) or<br>run OPTIMIZE TABLE issues, the auto_increment for issues is reset to 500.<br>You try to check in that check out, and you get a software error, because<br>there's already an entry with a primary key of 500 in the old_issues table.<br><br>Not only is there a loss of history, but I'm pretty sure that newer issue<br>stays in the issues table, because it can't be moved, and that'll likely<br>have all sorts of flow on effects.<br><br>Fun times!<br><br>I noticed this happening for reserves as well last year or the year before I<br>think. We've tried to mitigate it locally, since I don't really see a way of<br>fixing this issue with the current Koha methodology of using<br>deletedtables...<br><br>David Cook<br>Systems Librarian<br>Prosentient Systems<br>72/330 Wattle St<br>Ultimo, NSW 2007<br>Australia<br><br>Office: 02 9212 0899<br>Direct: 02 8005 0595<br><br><br>> -----Original Message-----<br>> From: <a href="mailto:koha-devel-bounces@lists.koha-community.org" target="_blank">koha-devel-bounces@lists.koha-community.org</a> [mailto:<a href="mailto:koha-devel-" target="_blank">koha-devel-</a><br>> <a href="mailto:bounces@lists.koha-community.org" target="_blank">bounces@lists.koha-community.org</a>] On Behalf Of Mark Tompsett<br>> Sent: Wednesday, 31 August 2016 11:42 PM<br>> To: Owen Leonard <<a href="mailto:oleonard@myacpl.org" target="_blank">oleonard@myacpl.org</a>><br>> Cc: Koha-devel <<a href="mailto:koha-devel@lists.koha-community.org" target="_blank">koha-devel@lists.koha-community.org</a>><br>> Subject: Re: [Koha-devel] Serious problem<br>><br>> Greetings,<br>><br>> >> check out, check in, restart mysql server, check out, check in...<br>> >> old_issues bug!<br>><br>> > What exactly is the bug?<br>><br>> Loss of history.<br>> Unless you do this after the server restart:<br>> use koha_library;<br>> insert into issues (borrowernumber) values (1);<br>> -- this will fill the gap, and should restore it.<br>> actually, whatever the mysql is to set the auto_increment to<br>> max(issue_id)+1 from old_issues would work too.<br>><br>> GPML,<br>> Mark Tompsett<br>><br>> _______________________________________________<br>> Koha-devel mailing list<br>> <a href="mailto:Koha-devel@lists.koha-community.org" target="_blank">Koha-devel@lists.koha-community.org</a><br>> <a href="http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel" target="_blank">http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel</a><br>> website : <a href="http://www.koha-community.org/" target="_blank">http://www.koha-community.org/</a> git : <a href="http://git.koha-" target="_blank">http://git.koha-</a><br>> <a href="http://community.org/" target="_blank">community.org/</a> bugs : <a href="http://bugs.koha-community.org/" target="_blank">http://bugs.koha-community.org/</a><br><br><br>_______________________________________________<br>Koha-devel mailing list<br><a href="mailto:Koha-devel@lists.koha-community.org" target="_blank">Koha-devel@lists.koha-community.org</a><br><a href="http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel" target="_blank">http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel</a><br>website : <a href="http://www.koha-community.org/" target="_blank">http://www.koha-community.org/</a><br>git : <a href="http://git.koha-community.org/" target="_blank">http://git.koha-community.org/</a><br>bugs : <a href="http://bugs.koha-community.org/" target="_blank">http://bugs.koha-community.org/</a><o:p></o:p></p></blockquote></div></div></div></body></html>