DBMS Auto Increment Errors

Fixing Koha Check-In Errors Triggered by DBMS Auto Increment Problems

Koha is a powerful open-source integrated library system used worldwide. However, many administrators occasionally face an issue where books refuse to check in. This problem is often due to inconsistencies in the database’s auto increment values. Here’s a temporary fix that works well on Koha installations running on Debian 8/9 or Ubuntu 16.04 with MySQL or MariaDB servers.

Let’s break it down step-by-step.

Symptoms of the Issue

• Books not checking in, even though they are physically returned.
• Error messages during check-in attempts.
• Duplicate or stuck entries visible in Koha > About > System Information.
• Affected tables like old_issues, deletedbiblio, deleteditems, and deletedborrowers.

Step 1: Access the Koha Database

Start by logging into the MySQL or MariaDB server:

sudo mysql -uroot -p

Then select your Koha database (usually named koha_library):

USE koha_library;

Step 2: Clean Faulty or Duplicate Records

Identify the problematic records from System Information and delete them using SQL:

DELETE FROM old_issues WHERE issue_id IN (1001,1002);
DELETE FROM deletedbiblio WHERE biblionumber IN (501,502);
DELETE FROM deleteditems WHERE biblionumber IN (501,502);
DELETE FROM deletedborrowers WHERE borrowernumber IN (601,602);

Make sure you replace the IDs with the exact values from your system.

Once done, exit the MySQL shell:

exit;

Step 3: Reset Auto Increment Values

Now, fix the auto increment behavior to avoid future issues.

Modify the MySQL/MariaDB Configuration:

Open the correct configuration file based on your system:

• MySQL (Ubuntu 16.04): /etc/mysql/my.cnf
• MySQL (Ubuntu 18.04): /etc/mysql/mysql.conf.d/mysqld.cnf
• MariaDB (Debian 9 or Ubuntu 16.04): /etc/mysql/mariadb.conf.d/50-server.cnf

Under [mysqld], add:

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

Create the Initialization Script

Now, create the referenced SQL file:

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

Paste the following (ensure the correct database name):

USE koha_library;
SET @val = (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 = ', @val);
PREPARE stmt FROM @sql; EXECUTE stmt;

-- Repeat similar blocks for biblio, biblioitems, items, issues, reserves

Save and close the file.

Step 4: Restart MySQL/MariaDB

Apply changes by restarting the service:

sudo service mysql restart

This step-by-step workaround helps resolve Koha check-in errors caused by DBMS auto increment mismatches. By cleaning up invalid entries and synchronizing your auto increment counters, you ensure smoother transactions. Always back up your database before making changes and monitor Koha for any updates regarding a permanent solution.