[Koha] Database discrepancy flagged after Koha upgrade

[City & Guilds of London Art School Admin]

Hello,

Platform: Ubuntu 20.04.6 LTS
DB: 10.3.38-MariaDB
Koha Version: 23.11

Since the most recent upgrade (using the package manager) from 23.05.x to
23.11.00, the following is returned in *About Koha -> System Information*

======

Data problems

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
Problems found
Bibliographic records

The following IDs exist in both tables biblio and deletedbiblioitems:

2 , 3 , 4

======

Note that the fix here:
https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix has been
implemented since we started with Koha, and that the five tests for data
corruption documented there return empty sets. The mysql error.log reports
no issues with parsing the bespoke init file etc.

Investigating further I see that indeed:

> select biblionumber, timestamp from deletedbiblioitems where biblionumber
<= '8';
+--------------+---------------------+
| biblionumber | timestamp           |
+--------------+---------------------+
|            1 | 2023-01-13 11:00:38 |
|            2 | 2021-06-09 14:06:34 |
|            3 | 2021-06-09 14:06:52 |
|            4 | 2021-06-09 14:06:57 |
|            5 | 2021-06-09 14:07:00 |
|            6 | 2021-06-09 14:07:06 |
|            7 | 2021-06-09 14:07:10 |
|            8 | 2021-06-09 14:07:13 |
+--------------+---------------------+
8 rows in set (0.000 sec)

Whereas:

> select biblionumber, timestamp from biblio where biblionumber <= '8';
+--------------+---------------------+
| biblionumber | timestamp           |
+--------------+---------------------+
|            2 | 2021-11-04 16:32:46 |
|            3 | 2022-01-17 16:50:10 |
|            4 | 2022-08-02 16:00:02 |
+--------------+---------------------+
3 rows in set (0.001 sec)

Also:

> select biblionumber, timestamp from deletedbiblio where biblionumber <=
'8';
+--------------+---------------------+
| biblionumber | timestamp           |
+--------------+---------------------+
|            1 | 2023-01-13 11:00:39 |
|            5 | 2021-06-09 14:07:02 |
|            6 | 2021-06-09 14:07:07 |
|            7 | 2021-06-09 14:07:12 |
|            8 | 2021-06-09 14:07:14 |
+--------------+---------------------+
5 rows in set (0.000 sec)

Correct me if I'm wrong, but my understanding is that Koha row counts
should *always* be identical for the pair of tables: *biblio* and
*biblioitems*, and also be identical for the pair *deletedbiblio* and
*deletedbiblioitems*, since the latter is copied from the former. However,
we have:

> select count(*) from biblio;
+----------+
| count(*) |
+----------+
|    11508 |
+----------+
1 row in set (0.006 sec)

> select count(*) from biblioitems;
+----------+
| count(*) |
+----------+
|    11508 |
+----------+
1 row in set (0.033 sec)

> select count(*) from deletedbiblioitems;
+----------+
| count(*) |
+----------+
|    15777 |
+----------+
1 row in set (0.072 sec)

> select count(*) from deletedbiblio;
+----------+
| count(*) |
+----------+
|    15774 |
+----------+
1 row in set (0.011 sec)

Also, I was able to ascertain from backups that this discrepancy went back
some way - at least till mid October '23 (which as as far back as our
backups go), however, Koha did not report on it till after the most recent
upgrade from 23.05 to 23.11.00.

While this is easy enough to "solve" by removing records 2 - 4 from
*deletedbiblioitems*, I have the following questions:


*1.* If I run a query modeled on the existing queries for data corruption
in the linked wiki page, unsurprisingly I get:

> SELECT b.biblionumber   FROM biblio b    JOIN deletedbiblioitems db    ON
b.biblionumber=db.biblionumber;
+--------------+
| biblionumber |
+--------------+
|            2 |
|            3 |
|            4 |
+--------------+
3 rows in set (0.000 sec)

So I'm wondering if the documented fix needs updating since it was last
reviewed (11th December 2020). My guess is that an additional corruption
test comparing *deletedbiblio* and *deletedbiblioitems*, and something like
the below may be needed, but I would prefer someone a *lot* more
knowledgeable about SQL (and Koha) to give their input, since my SQL skills
are pretty basic:

SET @new_AI_deletedbiblio = ( SELECT GREATEST( IFNULL( ( SELECT
MAX(biblioitemnumber) FROM deletedbiblio ), 0 ), IFNULL( ( SELECT
MAX(biblioitemnumber) FROM deletedbiblioitems ), 0 ) ) + 1 );
SET @sql = CONCAT( 'ALTER TABLE deletedbiblio AUTO_INCREMENT = ',
@new_AI_deletedbiblio );
PREPARE st FROM @sql;
EXECUTE st;

2. Although I can only check actual backups of the db back to mid October,
it seems logical that this error has always existed since we got beyond 4
deleted records (presumably June 2021). So I'm wondering what causes Koha
to flag it up now?

3. Is there any chance this could be caused by writes occurring during a
mysqldump operation not run as a single transaction?

4. According to the wiki page the issue with persistence only affects <
MariaDB 10.2.4. As we are running a later version presumably the statements
have nothing to correct and I need to look elsewhere for a cause? Or is
this just a case of the Wiki page needing an update?

Thanks and all the best,

Chris