The error aka “zor ka jhatkaa”

Last week a client partner raised a rather interesting support ticket: when they are checking in overdue items with fines, the fines seem to be vanishing. No, they were not waiving anything, the fines were simply going away and no record of there being ever a fine or its waiver were showing up in the Accounting tab of the patron.

The investigation

Turns out this was indeed happening. The instance was Koha 21.05.07 running on Debian 10.11 using MariaDB 10.3 running without plack. The evidence the fines been reset to zero and removed was available in the “Log viewer” under the Tools menu.

The solution

Doing a diff on the database backup of the table circulation_rules between the database backup from 2 days before and the one from that day, revealed that the day before, the client had re-jigged their circulation rules during which they had inadvertently changed the Fine interval aka chargeperiod setting from “1” to blank.

As the user manual says :

Enter the ‘Fine charging interval’ in the unit you set (ex. charge fines every 1 day, or every 2 hours). The finesCalendar system preference controls whether the days the library is closed will be taken into account or not.

In this specific instance, not only was the finesMode system preference set to “Calculate and charge” with fines being charged by the daily cron job, it also had CalculateFinesOnReturn system preference set to “Do”. Thus, while the cron job was creating and then updating fines once a day, the CalculateFinesOnReturn triggered at the time of check-in was resetting the fines to “0” as the chargeperiod was not set. This was why the fines were disappearing.

The simple fix was to ensure that for all the circulation rules the chargeperiod was set to “1”. And immediately the problem went away.

KISS, an acronym for “keep it simple, stupid” or “keep it stupid simple”, is a design principle noted by the U.S. Navy in 1960. The KISS principle states that most systems work best if they are kept simple rather than made complicated; therefore, simplicity should be a key goal in design, and unnecessary complexity should be avoided. The phrase has been associated with aircraft engineer Kelly Johnson. Variations on the phrase include: “Keep it simple, silly”, “keep it short and simple”, “keep it simple and straightforward”, “keep it small and simple”, or “keep it stupid simple”. [1]

Earlier today we received a request to add a new SQL report to generate a list of all students with their email ids from the librarian at our client partner Sister Nivedita University. The request prima facie was simple. However, when we looked at the patron categories for students under Administration, the first reaction was “Uh oh!”.

Let me explain. There were 8 patron categories for students (of all types) and all these student patron categories had different mnemonic codes that were as follows:

Still, just to fulfill the service request, all we needed to do was to create an SQL query like

SELECT
   cardnumber, firstname, surname, email
FROM borrowers 
WHERE categorycode IN 
('DIP', 'GNM-3', 'RS', 'SPG-2', 'SPG-3', 'SUG-3', 'SUG-4', 'SUG-5')

The ‘problem’

There was no way to know from the mnemonic values that all these codes designated a student patron category, as there was no uniform standardization of the values used for student categories. To put the problem in a perspective that library professionals will understand easily – e.g. if we saw a DDC class number that started with 004, we’ll immediately know that it belongs to “computer science“, but what if the computer science documents were marked out with numbers that did not denote computer science under DDC???

Further, if in the future, the library added another student patron category, the SQL report would need to be updated in order to give the correct output that will include members belonging to this newly added category.

The ‘solution’

Luckily the solution was both simple and straightforward. We had to standardize and re-code the student patron categories and update the affected patrons. To do that we prefixed every student patron category with “STD_“. And thus, the student patron categories became:

This was followed by updating the categorycode in the borrowers table to reflect the new changes. As a result the SQL query became much simplified:

SELECT
   cardnumber, firstname, surname, email
FROM borrowers 
WHERE categorycode LIKE 'STD_%'

If the library added a new student patron category in the future, all they would need to do is prefix the code with “STD_” and this query would continue to work without any change with this simple and straightforward update.

Legalese

This solution required us to directly access the production database with MySQL’s foreign key check turned off with SET FOREIGN_KEY_CHECKS=0;. If you get inspired to follow this, please ensure you have a complete database backup before you attempt this. And if by chance, you manage to mess it up or damage your database while attempting to do this, we are NOT responsible in any manner.

References [1] https://en.wikipedia.org/wiki/KISS_principle