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.


The problem

Koha moves deleted content from one table to another.

  • patrons (tables borrowers and deletedborrowers)
  • bibliographic records (tables biblio, deletedbiblio, biblioitems, deletedbiblioitems)
  • items (tables items and deleteditems)
  • checkouts (table issues and old_issues)
  • holds (table reserves and old_reserves)

For these 5 kinds of records, a restart of the MySQL server can cause loss 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 is lost!

This happens for the 5 tables.

The problem should be fixed (by Bug 18966 and ancestors) but it was tedious.

Are your data corrupted?

To know if you are in a situation your data may be lost you should run the following SQL queries:

SELECT b.borrowernumber FROM borrowers b JOIN deletedborrowers db ON b.borrowernumber=db.borrowernumber;
SELECT b.biblionumber   FROM biblio b    JOIN deletedbiblio db    ON b.biblionumber=db.biblionumber;
SELECT i.itemnumber     FROM items i     JOIN deleteditems di     ON i.itemnumber=di.itemnumber;
SELECT i.issue_id       FROM issues i    JOIN old_issues oi       ON i.issue_id=oi.issue_id;
SELECT r.reserve_id     FROM reserves r  JOIN old_reserves o      ON r.reserve_id=o.reserve_id;

If one of these queries return something, you should take care of these data.

Deal with corrupted data

The Koha community did not provide a way to deal with corrupted data yet, but a script is going to be provided soon.

See Bug 19016.

How Koha will behave?

The way your Koha installation will behave strongly depends on the version of Koha you are using.

We tried to fix the problem for the checkin (issues/old_issues) in the Koha codebase to fix the problem for everybody without the need to edit configuration files, but the more we progressed the more the situation was catastrophic. If you want to understand why it has been so complicated you can read the description of Bug 18966.

  • Historical behaviour
    • Before 16.11.06, 16.05.11 (included), and all previous versions

The failing checkins will be lost (removed from the issues table but not copied to the old_issues table). The checkin will be considered as done (the patron will be charged, etc.)

  • With Bug 18242
    • 17.05.00
    • 16.11.07, 16.11.08, 16.11.09
    • 16.05.12, 16.05.13, 16.05.14

The checkins are not lost but the patron will be charged anyway. Moreover other checkins may fail as well, not only the ones that contain "corrupted ids".

The checkins are not lost and the patron will not be charged. But other checkins may fail (problematic for batch checkins).

  • With Bug 18966
    • After 17.05.02, 16.11.10, 16.05.15 (included)

Decision to keep issue_id in sync and remove the different previous try and keep the code as simple as possible. The problematic checkins will be rejected and an error will be displayed to the librarian. The message is The item has not been checked in due to a configuration issue in your system. You must ask an administrator to take a look at the about page and search for the "data problems" section

  • Data corruption and SIP2

Bug 18996 has been added to deal with the data corruption problem when checking in an item via the Koha SIP server. Before this bug and after bug 18966, the SIP response pretends that the checkin was handled correctly but in reality the item was not checked in! With this bug, SIP will show that the checkin failed. At this moment we still need some people to test the proposed fix on this report.

Note that the solution proposed on this page will fix the problem for all Koha versions.


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 id's to a value that already exists in the deleted table.

The following example assumes that your database is named koha_kohadev

  • Edit /etc/mysql/my.cnf
  • Locate the [mysqld] section and add:
  • Create a file /var/lib/mysql/init-file_kohadev.sql
  • Edit it with the following contents, make sure to change the database name 'koha_kohadev' in the first line to your database name.
  • Repeat all statements for every database running on this DBMS server.
USE koha_kohadev;
SET @new_AI_borrowers = ( SELECT GREATEST( IFNULL( ( SELECT MAX(borrowernumber) FROM borrowers ), 0 ), IFNULL( ( SELECT MAX(borrowernumber) FROM deletedborrowers ), 0 ) ) + 1 );
SET @sql = CONCAT( 'ALTER TABLE borrowers AUTO_INCREMENT = ', @new_AI_borrowers );
SET @new_AI_biblio = ( SELECT GREATEST( IFNULL( ( SELECT MAX(biblionumber) FROM biblio ), 0 ), IFNULL( ( SELECT MAX(biblionumber) FROM deletedbiblio ), 0 ) ) + 1 );
SET @sql = CONCAT( 'ALTER TABLE biblio AUTO_INCREMENT = ', @new_AI_biblio );
SET @new_AI_biblioitems = ( SELECT GREATEST( IFNULL( ( SELECT MAX(biblioitemnumber) FROM biblioitems ), 0 ), IFNULL( ( SELECT MAX(biblioitemnumber) FROM deletedbiblioitems ), 0 ) ) + 1 );
SET @sql = CONCAT( 'ALTER TABLE biblioitems AUTO_INCREMENT = ', @new_AI_biblioitems );
SET @new_AI_items = ( SELECT GREATEST( IFNULL( ( SELECT MAX(itemnumber) FROM items ), 0 ), IFNULL( ( SELECT MAX(itemnumber) FROM deleteditems ), 0 ) ) + 1 );
SET @sql = CONCAT( 'ALTER TABLE items AUTO_INCREMENT = ', @new_AI_items );
SET @new_AI_issues = ( SELECT GREATEST( IFNULL( ( SELECT MAX(issue_id) FROM issues ), 0 ), IFNULL( ( SELECT MAX(issue_id) FROM old_issues ), 0 ) ) + 1 );
SET @sql = CONCAT( 'ALTER TABLE issues AUTO_INCREMENT = ', @new_AI_issues );
SET @new_AI_reserves = ( SELECT GREATEST( IFNULL( ( SELECT MAX(reserve_id) FROM reserves ), 0 ), IFNULL( ( SELECT MAX(reserve_id) FROM old_reserves ), 0 ) ) + 1 );
SET @sql = CONCAT( 'ALTER TABLE reserves AUTO_INCREMENT = ', @new_AI_reserves );
  • Restart MySQL
  % sudo service mysql restart


You can check if the script is run without errors by restarting MySQL and checking the error log at the same time:

 % sudo tail -f /var/log/mysql/error.log

MySQL errors

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near  at line 1

From the MySQL manual: Read SQL statements from this file at startup. Each statement must be on a single line and should not include comments.

Make sure that you have no line breaks in your SQL statements and no comments using -- or /* */. This problem might only appear on MySQL, not MariaDB.

Note: Statement on several lines works with Ver 15.1 Distrib 10.0.30-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 but not with Ver 14.14 Distrib 5.5.55, for debian-linux-gnu (x86_64) using readline 6.3.

Notes from testing

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 OPTIMIZE or can be made to work.

  • DONE We should add following tables as well:
    • items, deleteditems
    • biblio, deletedbiblio
    • biblioitems, deletedbiblioitems
  • DONE (added instructions above) (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)