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