The Problem
In the Koha Wiki there is a separate wiki page for
this database problem. Koha moves entries between database tables when
deleting a patron or a book, for example. The same happens with checking out.
The entries in these tables have a unique ID, which is normally automatically
incremented. However, the problem is that MySQL only stores these counters in
memory and not on the hard disk. This only occurs if entries are deleted. An
example:
·
I create a new bibliographical
entry (e. g. a book). Among other things, it is stored in the table biblio. The
entry gets the ID 1, so the next value for the ID should be 2.
·
Now I delete this book
again. The entry is moved to the deletedbiblio table. There is no longer an entry
in the biblio table.
·
I restart the MySQL
server. This will reset the internal counter for the IDs.
·
Now I’ll create a new
book. It does not get ID 2, but 1, because there is no entry in the table
biblio.
·
If I now delete this
book, it will be deleted from the table, but not moved to the table
deletedbiblio, because there is already an entry with this ID. The consequence
is that in this case my entry is lost forever.
As already
mentioned, this does not only happen with bibliographical entries, but also
with checkouts, patrons etc.. For this reason Koha warns against the problem in
the current versions. But this has not yet been resolved.
·
Now I delete this book
again. The entry is moved to the deletedbiblio table. There is no longer an entry
in the biblio table.
·
I restart the MySQL
server. This will reset the internal counter for the IDs.
·
Now I’ll create a new
book. It does not get ID 2, but 1, because there is no entry in the table
biblio.
·
If I now delete this
book, it will be deleted from the table, but not moved to the table
deletedbiblio, because there is already an entry with this ID. The consequence
is that in this case my entry is lost forever.
As already
mentioned, this does not only happen with bibliographical entries, but also
with checkouts, patrons etc.. For this reason Koha warns against the problem in
the current versions. But this has not yet been resolved.
Fix the Koha “auto increment” problem
To
avoid data loss in the future, we’ll teach MySQL not to reset the IDs,
especially not to a value we’ve used before. To do this, we open the file
/etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf (we use a Ubuntu 16.04 LXD container with MariaDB) and add the
following line in the [mysqld] section:init-file=/var/lib/mysql/init-file_koha.sql
Then we create this
file with
$ sudo nano /var/lib/mysql/init-file_koha.sql
and copy the
following content to the file:
USE koha_DB_Name;
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;
Important: In the first line we have to replace koha_DB_Name with
the database name of our Koha installation. Usally this is
koha_name_of_the_koha_instance, for example, koha_library.
Finally we restart
MySQL or MariaDB:
$ sudo service mysql restart
Clean up incorrect data records
Now we have
prevented the problem from recurring, but our old records are still there. One
way is to delete the corresponding entries from deleted* tables. You may lose
some statistical information, but we can live without it in our small school
library. Otherwise, we have not seen any negative effects so far.
In order to delete
the “faulty” data records, we must first log on to our Koha server. Then we log
into the MySQL console with
$ sudo mysql -p koha_database_name
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with; or \g.
Your MariaDB connection id is 115
Server version: MariaDB Ubuntu 16.04
Copyright (c) 2000,2017, Oracle, MariaDB Corporation Ab and others.
Type' help;'' or' \h' for help. Type' \c' to clear the current input statement.
MariaDB[koha_database_name]>
At the top in the
screenshot you can see that our faulty records are in the tables biblio and
deletedbiblio. The IDs are duplicated and we delete the IDs from one of the two
tables, specifically from deletedbiblio. We first check which records are
affected:
MariaDB[koha_database_name]> SELECT * FROM deletedbiblio WHERE biblionumber IN (*Copy the IDs from the web interface here*);
If we really want
to delete the records we change the command slightly:
MariaDB[koha_database_name]> DELETE FROM deletedbiblio WHERE biblionumber IN (*Copy the IDs from the web interface here*);
If, for example,
there are also problematic data records with the checkouts, this would be the
command:
MariaDB[koha_database_name]> DELETE FROM old_issues WHERE issue_id IN (*Copy the IDs from the web interface here);
Now Koha no longer
displays incorrect data records:
