[Koha] Items Not Being Cleared From Patron Account After Check-In

[Lennon Mazonde]

Hi Everyone,

I'm running Koha 16.11.07, installed via packages, on Ubuntu 14.04. 
Recently, one of my clients started having circulation problems with 
their Koha system. Items that have been checked in are still appearing 
in the patron's account. This has been happening randomly to different 
patrons at different times using different items. There is no consistent 
traceable pattern, and there are no errors showing client-side.

When i checked the Koha intranet logs, the following errors came up:

         [Mon May 22 07:04:15.958812 2017] [cgi:error] [pid 6424] 
[client 154.120.230.62:27062] AH01215: [Mon May 22 07:04:15 2017] 
returns.pl: DBD::mysql::db do failed: Duplicate entry '14846' for key 
'PRIMARY' [for Statement "UPDATE issues SET issue_id = ? WHERE issue_id 
= ?"] at /usr/share/koha/lib/C4/Circulation.pm line 2179., referer: 
http://aby.com/cgi-bin/koha/circ/returns.pl
         [Mon May 22 07:08:57.501919 2017] [cgi:error] [pid 6879] 
[client 154.120.230.62:31881] AH01215: [Mon May 22 07:08:57 2017] 
returns.pl: DBD::mysql::db do failed: Duplicate entry '14847' for key 
'PRIMARY' [for Statement "UPDATE issues SET issue_id = ? WHERE issue_id 
= ?"] at /usr/share/koha/lib/C4/Circulation.pm line 2179., referer: 
http://aby.com/cgi-bin/koha/circ/circulation.pl
         [Mon May 22 08:56:10.808209 2017] [cgi:error] [pid 20939] 
[client 154.120.230.62:43475] AH01215:
         [Mon May 22 08:56:10 2017] returns.pl: DBD::mysql::db do 
failed: Duplicate entry '14848' for key 'PRIMARY' [for Statement "UPDATE 
issues SET issue_id = ? WHERE issue_id = ?"] at 
/usr/share/koha/lib/C4/Circulation.pm line 2179., referer: 
http://aby.com/cgi-bin/koha/circ/returns.pl
         [Mon May 22 08:56:17.116111 2017] [cgi:error] [pid 20939] 
[client 154.120.230.62:43475] AH01215: [Mon May 22 08:56:17 2017] 
returns.pl: DBD::mysql::db do failed: Duplicate entry '14849' for key 
'PRIMARY' [for Statement "UPDATE issues SET issue_id = ? WHERE issue_id 
= ?"] at /usr/share/koha/lib/C4/Circulation.pm line 2179., referer: 
http://aby.com/cgi-bin/koha/circ/returns.pl
         [Mon May 22 08:56:22.893948 2017] [cgi:error] [pid 20939] 
[client 154.120.230.62:43475] AH01215: [Mon May 22 08:56:22 2017] 
returns.pl: DBD::mysql::db do failed: Duplicate entry '14850' for key 
'PRIMARY' [for Statement "UPDATE issues SET issue_id = ? WHERE issue_id 
= ?"] at /usr/share/koha/lib/C4/Circulation.pm line 2179., referer: 
http://aby.com/cgi-bin/koha/circ/returns.pl
         [Mon May 22 08:59:21.098733 2017] [cgi:error] [pid 21279] 
[client 154.120.230.62:44454] AH01215:
         [Mon May 22 08:59:21 2017] circulation.pl: DBD::mysql::db do 
failed: Duplicate entry '14851' for key 'PRIMARY' [for Statement "UPDATE 
issues SET issue_id = ? WHERE issue_id = ?"] at 
/usr/share/koha/lib/C4/Circulation.pm line 2179., referer: 
http://aby.com/cgi-bin/koha/circ/circulation.pl
         [Mon May 22 09:08:18.138124 2017] [cgi:error] [pid 22448] 
[client 154.120.230.62:48991] AH01215:
         [Mon May 22 09:08:18 2017] circulation.pl: 
DBIx::Class::Storage::DBI::select_single(): Query returned more than one 
row. SQL that returns multiple rows is DEPRECATED for ->find and 
->single at /usr/share/koha/lib/Koha/Objects.pm line 83, referer: 
http://aby.com/cgi-bin/koha/circ/circulation.pl

 From the little that i understand, the duplicate entry is occurring 
because that table has reached the upper limit of the auto-increment 
field (see the first comment in this thread). Therefore, when  a new 
record is inserted into the table, instead of generating a unique value, 
it is producing a duplicate key entry.

What i think this then does on the client side is that when an item is 
checked in, instead of a new unique value being created for that 
particular transaction in the issue/old_issue fields, a duplicate value 
is generated and assigned to that check-in. The system then thinks that 
the item has already been checked in, since the supposedly unique value 
has been generated. The item will then continue showing in the patron's 
account, even though it's been checked in.

Am i correct in my assessment of what is happening?

I opened the module that was generating that error

         sudo nano -Bu +2179 /usr/share/koha/lib/C4/Circulation.pm

The lines of code in the section around line 2179 deal with two tables 
in particular, issues and old_issues;

         # FIXME Improve the return value and handle it from callers
         $schema->txn_do(sub {
         $dbh->do( $query, undef, @bind );
         my $id_already_exists = $dbh->selectrow_array(
         q|SELECT COUNT(*) FROM old_issues WHERE issue_id = ?|,
         undef, $issue_id
         );
         if ( $id_already_exists ) {
         my $new_issue_id = $dbh->selectrow_array(q|SELECT 
MAX(issue_id)+1 FROM old_issues|);
         $dbh->do(
         q|UPDATE issues SET issue_id = ? WHERE issue_id = ?|,
         undef, $new_issue_id, $issue_id
         );
         $issue_id = $new_issue_id;
         }
         $dbh->do(q|INSERT INTO old_issues SELECT * FROM issues WHERE 
issue_id = ?|, undef, $issue_id);

I assumed the problems with the duplicate keys are emanating from these 
two tables. I tried increasing the index field of the column "issue_id" 
in both the old_issues and issues tables from INT to BIGINT (in the 
hopes of increasing the maximum limit of unique keys being generated) by 
doing the following in MySQL:

         mysql> use koha;
         mysql> describe old_issues;
         mysql> ALTER TABLE old_issues MODIFY issue_id BIGINT UNSIGNED 
NOT NULL AUTO_INCREMENT;
         mysql> describe issues;
         mysql> ALTER TABLE issues MODIFY issue_id BIGINT UNSIGNED NOT 
NULL AUTO_INCREMENT;

However, the problem is still occurring. Can anyone help with an 
explanation of what is happening and why, and how i can fix it? For now, 
i'm just going to reinstall my client's database, but i don't think 
that's going to be a permanent fix since the problem is likely to recur.

Regards,

Lennon Mazonde