[Koha] DBMS auto increment fix not working

[Katrin Fischer]

Hi Michael,

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.

Hope this helps,

Katrin

On 25.08.22 11:03, Michael Kuhn wrote:
> 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
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha