A Practical Guide to Auto Increment Fixes

Resolve Koha Check-In Failures Caused by DBMS Auto Increment Bugs

Koha is a powerful, open-source integrated library management system. But like any software, it can encounter technical glitches particularly related to database auto increment settings in MySQL or MariaDB. A common issue arises when certain records refuse to check-in, leaving them stuck in circulation. The root cause often lies in duplicated entries and broken auto increment sequences in tables like old_issues, deletedbiblio, or deletedborrowers.

Here’s a quick and effective workaround you can apply while developers continue to work on a permanent fix.

Identifying the Problem

Signs you’re dealing with an auto increment issue in Koha:

• Items don’t check in as expected.
• The circulation module shows persistent errors.
• Koha > About > System Information displays abnormal entries in key tables.
• Duplicate or stuck records exist in deleted-related tables.

Step-by-Step Solution to Clean Faulty Records

1. Access the Koha Database

Open a terminal and log in to MySQL or MariaDB:

sudo mysql -uroot -p

Choose your Koha database:

USE koha_library;

(Replace koha_library with your actual database name.)

2. Delete Faulty Entries from Affected Tables

Use the following SQL commands to clean problematic records:

DELETE FROM old_issues WHERE issue_id IN (901,902,903);
DELETE FROM deletedbiblio WHERE biblionumber IN (1101,1102);
DELETE FROM deleteditems WHERE biblionumber IN (1201,1202);
DELETE FROM deletedborrowers WHERE borrowernumber IN (1301,1302);

Then exit:

exit;

3. Configure Auto Increment Reset File

Edit the appropriate config file:

• Ubuntu 16.04: /etc/mysql/my.cnf
• Ubuntu 18.04+: /etc/mysql/mysql.conf.d/mysqld.cnf
• Debian/MariaDB: /etc/mysql/mariadb.conf.d/50-server.cnf

Add this line under [mysqld]:

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

4. Create Init File to Reset Auto Increment

Create the SQL script file:

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

Paste the following lines:

USE koha_library;

SET @bn = (SELECT GREATEST(IFNULL(MAX(borrowernumber),0), IFNULL((SELECT MAX(borrowernumber) FROM deletedborrowers),0)) + 1);
SET @sql = CONCAT('ALTER TABLE borrowers AUTO_INCREMENT=', @bn);
PREPARE stmt FROM @sql; EXECUTE stmt;

-- Repeat for other tables: biblio, biblioitems, items, issues, reserves

Save and close the file.

5. Restart the Database Service

Apply the changes:

sudo service mysql restart

Koha’s auto increment problems can disrupt check-ins and circulation, but this temporary fix helps restore functionality. Regular maintenance and database monitoring are essential to ensure long-term system health. Until a full patch is released, this method offers a reliable workaround.