How to fix the Koha auto increment problem

The Problem

  • 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.

Fix the Koha “auto increment” problem

init-file=/var/lib/mysql/init-file_koha.sql
$ sudo nano /var/lib/mysql/init-file_koha.sql
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;
$ sudo service mysql restart

Clean up incorrect data records

$ 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]>
MariaDB[koha_database_name]> SELECT * FROM deletedbiblio WHERE biblionumber IN (*Copy the IDs from the web interface here*);
MariaDB[koha_database_name]> DELETE FROM deletedbiblio WHERE biblionumber IN (*Copy the IDs from the web interface here*);
MariaDB[koha_database_name]> DELETE FROM old_issues WHERE issue_id IN (*Copy the IDs from the web interface here);

Conclusion

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store