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.

Record reluctant to check-in.

See the message when try to checkin the book.
We have access to the database of Koha and delete the particular entries. Then only we can apply DBMS auto increment fix.

Check Koha > About > System Information
Find the entries duplicated.

We need to access the database table "old_issues" where all old circulation entries store. Need to install phpMyAdmin tool to access the database table. Open a terminal and apply the following command to install the phpMyAdmin.

sudo apt-get install phpmyadmin

If any occur while PHPMyAdmin installation, please check the documentation.

Open a new window in your browser and enter the following address to start phpMyAdmin, http://127.0.1.1/phpmyadmin


1. Search and find the old_issues table in koha_library database.
2. Click old_issues table to dive inside the table.
3. Search the particular ID Number to delete the circulation entry reluctant to check-in.


Search the ID.
Select and delete the duplicated entry.
Delete the duplicated entries one by one. After delete all entries, check again Koha > About Koha > System Information.

DBMS auto increment fix as per Koha Wiki
I borrowed the following steps from Koha Wiki.

For MySQL users,

sudo leafpad /etc/mysql/my.cnf

For MariaDB users,

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

Place following line under [mysqld]. Hope that your Koha DB name is koha_library.

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

Save and close the file.

Create a new file,

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

Copy and paste 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

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

2 comments:

  1. Thank you for the help.

    Can you send a notice when the problems has fixed?

    ReplyDelete
    Replies
    1. Please check the blog post. I have updated it.

      Delete