[Koha] DBMS auto increment fix not working

[Michael Kuhn]

Hi Marcel

Yesterday you wrote:

 > The fact that it will no longer happen under MariaDB 10.5.15 does not
 > guarantee that you still suffer the consequences of older occurrences.
 > The wiki promises a script but it never made it probably.
 >
 > But the easiest 'solution' is deleting the records in the deleted_*
 > tables that have a corresponding id in the normal tables.

We have done this before and I did this again today. At the moment there 
are no biblionumbers existing in table "deletedbiblio" that also exist 
in table "biblio". Menu "About Koha > System information" does not show 
errors anymore.

 > After that verify that your autoincrement pointers on the normal
 > tables are higher than the maximum id values in the deleted tables. If
 > they would not be, you may bump into the issue again one day.

I have checked the currentauto_increment values for all tables, the 
values in the normal tables are always higher than the ones in the 
respective deleted tables:

  147200 - biblio
  146933 - deletedbiblio

  147200 - biblio_metadata
  68588 - deletedbiblio_metadata

  147200 - biblioitems
  NULL - deletedbiblioitems

  73229 - items
  NULL - deleteditems

So if I understand right with this setup when using MariaDB 10.5.15 the 
problem should no more happen.

Unfortunately I'm quite sure it will happen again anyway (as before). I 
will then write to the list again.

PS: This is the 3rd trial to get this e-mail through.

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




> ________________________________
> Van: Koha <koha-bounces at lists.katipo.co.nz> namens Michael Kuhn <mik at adminkuhn.ch>
> Verzonden: donderdag 25 augustus 2022 11:03
> Aan: Koha <koha at lists.katipo.co.nz>
> Onderwerp: [Koha] DBMS auto increment fix not working
> 
> 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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.koha-community.org%2Fwiki%2FDBMS_auto_increment_fix&data=05%7C01%7Cm.de.rooy%40rijksmuseum.nl%7C5c1a779522df4601c58808da8678d0b4%7C635b05eb66c748e1a94fb4b05a1b058b%7C0%7C0%7C637970150719835805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=5BkXPF4yrrhvuLP1nFx66eQITkFCQPhoGPDtsdCDWso%3D&reserved=0 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 https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.adminkuhn.ch%2F&data=05%7C01%7Cm.de.rooy%40rijksmuseum.nl%7C5c1a779522df4601c58808da8678d0b4%7C635b05eb66c748e1a94fb4b05a1b058b%7C0%7C0%7C637970150719835805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=fS0wgTtECweT98NfdwYV%2FV82IYhpm82iqLYuWP8war4%3D&reserved=0
> _______________________________________________
> 
> Koha mailing list  https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fkoha-community.org%2F&data=05%7C01%7Cm.de.rooy%40rijksmuseum.nl%7C5c1a779522df4601c58808da8678d0b4%7C635b05eb66c748e1a94fb4b05a1b058b%7C0%7C0%7C637970150719835805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=8d%2BiOpLG4oo49VF%2BwC9Y9hA7ZDJ6nUxcb2v7K1qdaxs%3D&reserved=0
> Koha at lists.katipo.co.nz
> Unsubscribe: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.katipo.co.nz%2Fmailman%2Flistinfo%2Fkoha&data=05%7C01%7Cm.de.rooy%40rijksmuseum.nl%7C5c1a779522df4601c58808da8678d0b4%7C635b05eb66c748e1a94fb4b05a1b058b%7C0%7C0%7C637970150719835805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=jNLi6yPA%2FG5CLqcpf1nVBI4tnedT4a7zHdM4wWMpcXI%3D&reserved=0
> _______________________________________________
> 
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha