[Koha] DBMS auto increment fix not working

[Michael Kuhn]

Hi Katrin

You wrote:

 > the fix did prevent the problem from occurring on older versions, but
 > it doesn't fix the data.
 >
 > Could it be the bad data was there before, maybe from using an older
 > DBMS version?
 >
 > The simplest fix is to delete the entries in the old_ and deleted_
 > tables with the doubled up IDs and then restart your DBMS.

Yes, I know the fix won't fix the data. So I usually do what you suggested.

But I had expected the fix to prevent the problem from occurring - only 
the "auto_increment fix" does NOT prevent the problem in MariaDB 
10.5.51. Instead from time to time new deletions (not all) produce the 
same error again.

That's why I wrote I'm not sure if the underlying problem is the same as 
the one that was fixed by the "auto_increment fix".

Best wishes: Michael
-- 
Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis
Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz
T 0041 (0)61 261 55 61 · E mik at adminkuhn.ch · W www.adminkuhn.ch



Am 25.08.22 um 11:03 schrieb Michael Kuhn:
 > Hi
 >
 > In our library we are using Debian 11 with MariaDB 10.5.15 and Koha
 > 21.05.14.
 >
 > When deleting bibliographic records in the staff client, some
 > deletions produce the message: "An error has occurred! Error 500 /
 > This message may have been caused by any of the following reasons:
 > etc." In such cases Koha menu "About Koha > System information" shows
 > the message aubout data problems, saying
 >
 >   Some of your tables have problems with their auto_increment values
 > which may lead to data loss.
 >
 >   You should not ignore this warning.
 >
 >   The problem is that InnoDB does not keep auto_increment across SQL
 > server restarts (it is only set in memory). So on server startup the
 > auto_increment values are set to max(table.id)+1.
 >
 >   To know how to avoid this problem see the related wiki page: DBMS
 > auto increment fix
 >
 >
 > According to
 > https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix the
 > problem shouldn't appear with MariaDB 10.5.15 but as a trial we have
 > implemented the solution described there and have restarted everything
 > - still the problem persists.
 >
 > In such cases file "plack-error.log" shows the following:
 >
 >   {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate entry
 > '6187-marcxml-MARC21' for key 'deletedbiblio_metadata_uniq_key' [for
 > Statement "
 >                  INSERT INTO deletedbiblio_metadata (biblionumber,
 > format, `schema`, metadata)
 >                  SELECT biblionumber, format, `schema`, metadata FROM
 > biblio_metadata WHERE biblionumber=?
 >              "] at /usr/share/koha/lib/C4/Biblio.pm line 2907
 >
 > I suspect this behavior indeed looks like the original auto_increment
 > problem described in bugs 18242, 18651, 18966, 19106 and 20271 but the
 > reason may in fact not be the same since the described problem
 > shouldn't appear at all in MariaDB 10.5.15.
 >
 > Does anyone have an idea what is happening and how we can solve it?
 >
 > Best wishes: Michael
 > --
 > Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg.
 > Fachausweis
 > Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz
 > T 0041 (0)61 261 55 61 · E mik at adminkuhn.ch · W www.adminkuhn.ch