Auto increment problem in Koha. How to fix the Koha auto increment problem? When you check-in a book if you get error message "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". Then the issue is with Koha auto increment.

 How to fix the Koha auto increment problem? When you check-in a book if you get error message "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". Then the issue is with Koha auto increment.

Check the detailed information regarding the issue in Koha, go to About Koha -> System Information. You will see the issue as in the screenshot below:

Fix or solution to the issue:

First clean up the incorrect data records. To do this first log on to the koha server and then log into the mysql:
          sudo mysql -u root -p (Note: koha_library = Database Name)

         use koha_library; 

Now delete all the data that is corrupted from respective database tables with the delete command. The IDs of the corrupted items can be copied from the System Information :

MariaDB[koha_library]> DELETE FROM deletedbiblio WHERE biblionumber IN (1, 2, 3, 4, 5, 6, 8, 9, 13, 14, 15, 16, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 48, 49, 50, 51, 57, 58, 62, 63, 64, 65, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 97, 98, 99, 100, 106, 109, 111, 112, 113, 114, 115, 116, 117, 118, 119, 122, 125, 127, 128, 129, 130, 131, 132, 133, 134, 135, 138, 141, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 4835, 4836);

MariaDB[koha_library]>DELETE FROM deleteditems WHERE itemno IN (15202, 15203, 15204, 15205, 15206, 15984, 15985, 15986, 15987, 15988, 15989, 15990, 15991, 15992, 15993, 15994, 15995, 15996, 15997, 15998, 15999, 16000);

MariaDB[koha_library]>DELETE FROM old_issues WHERE issue_id IN (44, 3, 24, 59, 1, 65, 69, 67, 83, 61, 60, 76, 57, 37, 34, 22, 35, 90, 80, 64, 38, 28, 20, 23, 87, 82, 79, 75, 39, 84, 18, 17, 15, 9, 86, 53, 56, 73, 40, 33, 58, 46, 68, 51, 62, 71, 91, 74, 52, 19, 41, 78, 13, 12, 54, 66, 11, 30, 29, 27, 63, 42, 45, 25, 26, 72, 5, 7, 31, 32, 36, 43, 21, 55, 49, 6, 8, 10, 92, 89, 48, 47, 14, 50, 4, 16, 2, 70, 81, 88, 85, 77);

 

MariaDB[koha_library]> DELETE FROM old_reserves WHERE reserve_id IN (60, 180);

MariaDB[koha_library]>DELETE FROM deletedborrowers WHERE borrowernumber IN (2958);

 

Now Koha will not display the issue.

We have removed all the corrupted data, now how do we prevent from data getting corrupted in future. Here is the solution:
I am assuming we are using Ububtu 16.04 with MariaDB as our database.

First open the file: vim /etc/mysql/mariadb.conf.d/50-server.cnf

Add the following line in the [mysqld] section:

init-file=/var/lib/mysql/init-file_library.sql  (I assumed that koha instance name is library)

Then we create a file  as follows:

sudo vim /var/lib/mysql/init-file_library.sql

 Then copy the following content to the file created above:

USE koha_library;
 
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;

Then we restart the MariaDB:

sudo service mysql restart