Delete circulation entries from databse tables (DBMS auto increment)

Certain records reluctant to Checkin due to DBMS auto increment problem. In such cases, the transaction remains in the tables. Developers still working on the solution. Here is a temporary solution. I tried this steps on Koha installed on Debian 8,9 and Ubuntu 16.04 with MySQL and MariaDB Server.

Symptoms of DB increment problem

Record reluctant to check-in.

See the message when trying to check-in the book.

Check Koha > About > System Information where you can find affected tables and entries in it.

Find the entries duplicated.

Clean the wrong entries from the affected tables

The problem often affected on deletedbiblio, deleteditems, deletedborrowers and old_issues tables in Koha database. We have to access the database of Koha and delete that specific entry. Then we apply DBMS auto increment fix. First identify the tables with wrong entries. Apply SQL queries to delete the wrong entries;

Log into MySQL/MariaDB

sudo mysql -uroot -p

use koha_library;

Apply following SQL query to delete the wrong entries from old_issues table;

DELETE FROM old_issues WHERE issue_id IN (Copy and paste issue IDs inside the brackets);

e.g. DELETE FROM old_issues WHERE issue_id IN (910,909,908,911);

Delete wrong entries from deletedbiblio

DELETE FROM deletedbiblio WHERE biblionumber IN (Copy and paste biblio numbers inside the brackets);

Delete wrong entries from deleteditems

DELETE FROM deleteditems WHERE biblionumber IN (Copy and paste biblionumbers inside the brackets);

Delete wrong entries from deletedborrowers

DELETE FROM deletedborrowers WHERE borrowernumber IN (Copy and paste borrower numbers inside the brackets);

Exit from MySQL after the deletion of wrong entries from all tables. Apply the following command;

exit

Check again Koha > About Koha > System Information after deleting all wrong entries from affected tables.

DBMS auto increment fix as per Koha Wiki

For MySQL with Ubuntu 16.04 LTS,

sudo leafpad /etc/mysql/my.cnf

For MySQL with Ubuntu 18.04 LTS,
 
sudo leafpad /etc/mysql/mysql.conf.d/mysqld.cnf

For MariaDB with Ubuntu 16.04 and Debian 9,

sudo leafpad /etc/mysql/mariadb.conf.d/50-server.cnf 

Place following line under [mysqld]. Hope that your Koha DB name is koha_library, otherwise change the DB name in the line;

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

Save and close the file.

Create a new file, remember to add the DB name at the end of the line.

sudo leafpad /var/lib/mysql/init-file_koha_library.sql

Copy and paste the following lines into the file and save, close. Replace the Koha DB name in the first line.

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;

Restart MySQL

sudo service mysql restart

References
https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix
https://medium.com/@OpenSchoolZ/how-to-fix-the-koha-auto-increment-problem-5a2dbee91031