We use Koha for our library, a very powerful and great open source software. On the project’s mailing list I had already read about the “Koha auto increment” problem and hoped it didn’t affect us. But a few weeks ago, one of our staff members from the school library came to me and gave me a book that he could not check in. Every time you tried to check in the book you got the following error message:

Check In Error

Under More → About Koha in the tab “System Information” I could see that some books were affected by this problem.

Koha auto increment problem

But what is the problem? How did this happen?

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.

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:

Then we create this file with

and copy the following content to the file:

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:

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

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:

If we really want to delete the records we change the command slightly:

If, for example, there are also problematic data records with the checkouts, this would be the command:

Now Koha no longer displays incorrect data records:

About Koha

Conclusion

If you have this problem in your Koha installation, it is very advisable to solve this problem. However, there are different approaches. The way described above is a quick fix, but may not be suitable for every installation. Koha is still working on a script to fix the problem automatically. Despite this database problem, we have not regretted the switch to Koha. It is and remains a great software, which certainly needs its familiarization time, but the profit, the flexibility and the freedom gained with it are seemingly boundless.

Are you using Koha? What experiences have you had so far?

Categories: HowTo

Stephan

Stephan

I'm a teacher and IT system administrator in an international school. I love open source software and I used it over a decade in my private and work life. My passion is to solve problems with open source software!

8 Comments

Jemal · February 13, 2018 at 6:55 pm

Thank you!

    Stephan

    Stephan · February 13, 2018 at 6:57 pm

    You’re welcome!

Sinnan · April 5, 2018 at 11:31 am

Sir ,

Thanks for your solution, but its not effecting in koha after adding in mysql, also the auto increment issue is not solved.

    Stephan

    Stephan · April 5, 2018 at 11:39 am

    @Sinnan: Actually that’s the recommended way. You can check the Koha Wiki on that problem: https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix

      Sinnan · April 19, 2018 at 12:49 pm

      Sir I had already gone through the above Link but the problem is not yet solved.

        Stephan

        Stephan · April 19, 2018 at 8:07 pm

        Maybe its best to ask your questions on the Koha mailinglist.

          Sinnan · April 24, 2018 at 11:16 am

          Ok Sir, Thanks for your help…

          And did you know how to create patron card creator in koha 17.11.I had already created layouts and card templates with the configuration in koha 16.05.But when I created in koha 17.11 ,the result is not appearing in a manner.

ULISES CASTREJON · May 8, 2018 at 4:06 am

Thank you sir for your great help. I was trying to fix this problem with the koha wiki without success. Later I found your solution in google and that did the trick. Voilá no problem anymore. Thanks a lot!!!!

Comments are closed.