DBMS auto increment fix

From Koha Wiki

Jump to: navigation, search

This wiki page will guide you to fix potential issues related to how MySQL and MariaDB handle auto_increment (AI) values.

From https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization:

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

Contents

The problem

Koha moves deleted content from one table to another.

  • patrons (tables borrowers and deletedborrowers)
  • bibliographic records (tables biblio and deletedbiblio)
  • checkouts (table issues and old_issues)
  • holds (table reserves and old_reserves)

For these 4 kind of records, a restart of the MySQL server can cause lost of data in some case.

For instance:

  • If you create a biblio, the biblionumber will be set to 1.
  • Remove it, the row will be moved to the deletedbiblio table.
  • At this point the AI value for the biblio table is 2
  • However, if you restart mysqld, the AI of the biblio table will be reset to MAX(biblionumber), then 1
  • If you recreate a biblio and delete it, the data will be removed from the biblio table but not copied to the deletedbiblio table

=> Data lost!

This happens for the 4 tables.

The problem should be fixed (by bug 18242 and bug 18651) but it was tedious.

Solutions

This can be solved at code level but it is not that easy.

We should also merge the tables together and add a 'deleted' flag, but it is not trivial either (see discussions on bug 18003 and bug 18428 for ideas).

The solution

The quick and easy solution is to teach MySQL not to reset the ids to a value that already exist in the deleted table.

The following example assume that your database is named koha_kohadev

  • Edit /etc/mysql/my.cnf
  • Locate the [mysqld] section and add:
   init-file=/var/lib/mysql/init-file_kohadev.sql
  • Create a /var/lib/mysql/init-file_kohadev.sql
  • Edit it with the following contain:
SET @new_AI_borrowers = (
    SELECT GREATEST(
        IFNULL( ( SELECT MAX(borrowernumber) FROM koha_kohadev . borrowers ), 0 ),
        IFNULL( ( SELECT MAX(borrowernumber) FROM koha_kohadev . deletedborrowers ), 0 )
    ) + 1
);
SET @sql = CONCAT( 'ALTER TABLE koha_kohadev.borrowers AUTO_INCREMENT = ', @new_AI_borrowers );
PREPARE st FROM @sql;
EXECUTE st;
 
SET @new_AI_biblio = (
    SELECT GREATEST(
        IFNULL( ( SELECT MAX(biblionumber) FROM koha_kohadev . biblio ), 0 ),
        IFNULL( ( SELECT MAX(biblionumber) FROM koha_kohadev . deletedbiblio ), 0 )
    ) + 1
);
SET @sql = CONCAT( 'ALTER TABLE koha_kohadev.biblio AUTO_INCREMENT = ', @new_AI_biblio );
PREPARE st FROM @sql;
EXECUTE st;
 
SET @new_AI_issues = (
    SELECT GREATEST(
        IFNULL( ( SELECT MAX(issue_id) FROM koha_kohadev . issues ), 0 ),
        IFNULL( ( SELECT MAX(issue_id) FROM koha_kohadev . old_issues ), 0 )
    ) + 1
);
SET @sql = CONCAT( 'ALTER TABLE koha_kohadev.issues AUTO_INCREMENT = ', @new_AI_issues );
PREPARE st FROM @sql;
EXECUTE st;
 
SET @new_AI_reserves = (
    SELECT GREATEST(
        IFNULL( ( SELECT MAX(reserve_id) FROM koha_kohadev . reserves ), 0 ),
        IFNULL( ( SELECT MAX(reserve_id) FROM koha_kohadev . old_reserves ), 0 )
    ) + 1
);
SET @sql = CONCAT( 'ALTER TABLE koha_kohadev.reserves AUTO_INCREMENT = ', @new_AI_reserves );
PREPARE st FROM @sql;
EXECUTE st;
  • Restart MySQL
   % sudo service mysql restart


Notes

We have tested the script and verified that it fixes the problem with the PKs getting out of sync on restart of the MySQL server. We are not sure if this will also work for OTIMIZE or can be made to work.

  • We should add following tables as well:
    • items, deleteditems
    • biblio, deletedbiblio
    • biblioitems, deletedbiblioitems
  • (Multiple) DBs
    • have to make sure libraries change the script to the correct DB name
    • have to make sure all of the lines are repeated for every DB using the same MySQL server (could we automate this with packages?)

--Kfischer 10:44, 20 July 2017 (EDT)