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 and Ubuntu 16.04 with MySQL Server. I have not tried this solution on MariaDB.

Record reluctant to check-in.

See the message when try to checkin the book.
If we have access to the database of Koha, we can delete the particular entry. For that purpose, find the item number of the book. Right-click on the book name and open the record in a new window.
Click on edit button against the item to know the item number.
Check the address bar of the browser when the item information window open.
We need to access the database table "issues" where all 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 issue table from phpMyAdmin.
2. Click issues table to dive inside the table.
3. Search the particular itemnumber to delete the circulation entry reluctant to checkin.

Put the item number in the item number search box.
Delete the transaction entry.
DBMS auto increment fix
I borrowed the following steps from Koha Wiki. Open my.cnf file add a line;

sudo leafpad /etc/mysql/my.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.

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

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