[Koha] DBMS auto increment fix not working

[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