We use Koha for our library, a very powerful and great open source software. On the project’s mailing list I had already read about the “Koha auto increment” problem and hoped it didn’t affect us. But a few weeks ago, one of our staff members from the school library came to me and gave me a book that he could not check in. Every time you tried to check in the book you got the following error message:

Under More → About Koha in the tab “System Information” I could see that some books were affected by this problem.

But what is the problem? How did this happen?

The Problem

In the Koha Wiki there is a separate wiki page for this database problem. Koha moves entries between database tables when deleting a patron or a book, for example. The same happens with checking out. The entries in these tables have a unique ID, which is normally automatically incremented. However, the problem is that MySQL only stores these counters in memory and not on the hard disk. This only occurs if entries are deleted. An example:

• I create a new bibliographical entry (e. g. a book). Among other things, it is stored in the table biblio. The entry gets the ID 1, so the next value for the ID should be 2.
• Now I delete this book again. The entry is moved to the deletedbiblio table. There is no longer an entry in the biblio table.
• I restart the MySQL server. This will reset the internal counter for the IDs.
• Now I’ll create a new book. It does not get ID 2, but 1, because there is no entry in the table biblio.
• If I now delete this book, it will be deleted from the table, but not moved to the table deletedbiblio, because there is already an entry with this ID. The consequence is that in this case my entry is lost forever.

As already mentioned, this does not only happen with bibliographical entries, but also with checkouts, patrons etc.. For this reason Koha warns against the problem in the current versions. But this has not yet been resolved.

Fix the Koha “auto increment” problem

To avoid data loss in the future, we’ll teach MySQL not to reset the IDs, especially not to a value we’ve used before. To do this, we open the file /etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf (we use a Ubuntu 16.04 LXD container with MariaDB) and add the following line in the [mysqld] section:

init-file=/var/lib/mysql/init-file_koha.sql

Then we create this file with

$ sudo nano /var/lib/mysql/init-file_koha.sql

and copy the following content to the file:

USE koha_DB_Name;
 
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_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;

Important: In the first line we have to replace koha_DB_Name with the database name of our Koha installation. Usally this is koha_name_of_the_koha_instance, for example, koha_library.

Finally we restart MySQL or MariaDB:

$ sudo service mysql restart

Clean up incorrect data records

Now we have prevented the problem from recurring, but our old records are still there. One way is to delete the corresponding entries from deleted* tables. You may lose some statistical information, but we can live without it in our small school library. Otherwise, we have not seen any negative effects so far.

In order to delete the “faulty” data records, we must first log on to our Koha server. Then we log into the MySQL console with

$ sudo mysql -p koha_database_name
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with; or \g.
Your MariaDB connection id is 115
Server version: MariaDB Ubuntu 16.04

Copyright (c) 2000,2017, Oracle, MariaDB Corporation Ab and others.

Type' help;'' or' \h' for help. Type' \c' to clear the current input statement.

MariaDB[koha_database_name]>

At the top in the screenshot you can see that our faulty records are in the tables biblio and deletedbiblio. The IDs are duplicated and we delete the IDs from one of the two tables, specifically from deletedbiblio. We first check which records are affected:

MariaDB[koha_database_name]> SELECT * FROM deletedbiblio WHERE biblionumber IN (*Copy the IDs from the web interface here*);

If we really want to delete the records we change the command slightly:

MariaDB[koha_database_name]> DELETE FROM deletedbiblio WHERE biblionumber IN (*Copy the IDs from the web interface here*);

If, for example, there are also problematic data records with the checkouts, this would be the command:

MariaDB[koha_database_name]> DELETE FROM old_issues WHERE issue_id IN (*Copy the IDs from the web interface here);

Now Koha no longer displays incorrect data records:

Conclusion

If you have this problem in your Koha installation, it is very advisable to solve this problem. However, there are different approaches. The way described above is a quick fix, but may not be suitable for every installation. Koha is still working on a script to fix the problem automatically. Despite this database problem, we have not regretted the switch to Koha. It is and remains a great software, which certainly needs its familiarization time, but the profit, the flexibility and the freedom gained with it are seemingly boundless.

Are you using Koha? What experiences have you had so far?