DBMS auto increment fix

From Koha Wiki
Jump to navigation Jump to search

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

The problem

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.

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 data loss problem should be fixed (by Bug 18966 and ancestors) but it was tedious. The fix resolves the problem of losing data during an operation, but upon encountering this issue the operation will still fail with a notice. To fix the problem so that everything works as expected, you must still complete the steps detailed below.

Database Versions Affected

MariaDB:

Versions prior to MariaDB 10.2.4 are affected. See the MariaDB 10.2.4

release notes: https://mariadb.com/kb/en/library/mariadb-1024-release-notes/

There was also a JIRA issue for this:

Persistent AUTO_INCREMENT for InnoDB (MDEV-6076). https://jira.mariadb.org/browse/MDEV-6076

MySQL:

Versions prior to MySQL 8.0 are affected. See the MySQL 8.0

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

Is your data corrupted?

To know if you are in a situation in which 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 returns something, you should deal with the corrupted data.

Dealing with corrupted data

The Koha community has not completed a way to deal with corrupted data yet, but a script is being worked on to fix this in the future.

See Bug 19016 and Bug 20271.

Until the script is ready for use to clean up the data, one possible fix is to delete the entries in the deleted* and old* tables matching the IDs listed on the about page.

How will Koha 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.

Solutions

Current Solution

THIS WILL NOT FIX ALREADY CORRUPTED DATA BUT WILL PREVENT MORE CORRUPTION IN THE FUTURE.

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

Single instance setup

If your instance is called kohadev, your probable database name is koha_kohadev (if it was created automatically). Follow this steps to apply the solution for the desired instance.

Note: don't be tempted to add an init-file= line for each instance as only the last one is picked.

  1. Edit /etc/mysql/my.cnf
  2. Locate the [mysqld] section and add:
    3. init-file=/var/lib/mysql/init-file_koha_fix.sql
  3. Create a file /var/lib/mysql/init-file_koha_fix.sql
  4. Edit it with the following contents, make sure to change the database name koha_kohadev in the first line to your database name.
  5. Repeat all statements for every database running on this DBMS server.
    6. 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 );
PREPARE st FROM @sql;
EXECUTE st;

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 );
PREPARE st FROM @sql;
EXECUTE st;

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 );
PREPARE st FROM @sql;
EXECUTE st;

SET @new_AI_biblio_metadata = ( SELECT GREATEST( IFNULL( ( SELECT MAX(id) FROM biblio_metadata ), 0 ), IFNULL( ( SELECT MAX(id) FROM deletedbiblio_metadata ), 0 ) ) + 1 );
SET @sql = CONCAT( 'ALTER TABLE biblio_metadata AUTO_INCREMENT = ', @new_AI_biblio_metadata );
PREPARE st FROM @sql;
EXECUTE st;

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 );
PREPARE st FROM @sql;
EXECUTE st;

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 );
PREPARE st FROM @sql;
EXECUTE st;

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 );
PREPARE st FROM @sql;
EXECUTE st;
  6. Restart MySQL
    7. sudo service mysql restart

Multiple instances

If you have multiple instances using the same DB engine, you need to copy and paste the same text as above on the init sql file, changing the USE db_name line accordingly for each instance.

In case you run the MySQL/MariaDB server on the same host as a Koha Debian package installation, you can also try the solution proposed here, which uses a systemd unit to apply the fix per database on system startup.

Troubleshooting

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.

Long-Term 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).

Retrieved from "https://wiki.koha-community.org/w/index.php?title=DBMS_auto_increment_fix&oldid=27149"