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

Lennon Mazonde lmmazonde at gmail.com
Tue May 23 04:13:21 NZST 2017


Hi Nick,

Sorry for the slow responses. I'm not well-versed in MySQL so i'm 
figuring out a lot of things as i go.

> You can then alter those ids but pushing them up and bump the 
> auto_increment for the table above the MAX(issue_id) FROM old_issues.
>
   i checked my table data by using

    SHOW TABLE STATUS;

before <https://pastebin.com/raw/tAe68ABg>and after 
<https://pastebin.com/raw/huCVEa7S>i altered the auto_increment value. I 
increased the auto_increment to 15000 (my MAX(issue_id) for old_issues 
was 14858) using:

    mysql> ALTER TABLE issues AUTO_INCREMENT=15000;


I tried checking in an item, but the problem still remains. Do i

 1. alter the duplicate ids BY increasing the auto_increment for the
    issues table, or
 2. do i have alter the ids AND bump up the auto_increment for the
    issues table?

If it's the latter, how do i alter the ids?

Thanks for all your help,

Regards,

Lennon


>
>
> On Mon, May 22, 2017 at 6:00 AM Lennon Mazonde <lmmazonde at gmail.com 
> <mailto:lmmazonde at gmail.com>> wrote:
>
>     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 <http://154.120.230.62:27062>]
>     AH01215: [Mon May 22 07:04:15 2017]
>     returns.pl <http://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 <http://154.120.230.62:31881>]
>     AH01215: [Mon May 22 07:08:57 2017]
>     returns.pl <http://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 <http://154.120.230.62:43475>] AH01215:
>              [Mon May 22 08:56:10 2017] returns.pl
>     <http://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 <http://154.120.230.62:43475>]
>     AH01215: [Mon May 22 08:56:17 2017]
>     returns.pl <http://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 <http://154.120.230.62:43475>]
>     AH01215: [Mon May 22 08:56:22 2017]
>     returns.pl <http://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 <http://154.120.230.62:44454>] AH01215:
>              [Mon May 22 08:59:21 2017] circulation.pl
>     <http://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 <http://154.120.230.62:48991>] AH01215:
>              [Mon May 22 09:08:18 2017] circulation.pl
>     <http://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
>
>     _______________________________________________
>     Koha mailing list http://koha-community.org
>     Koha at lists.katipo.co.nz <mailto:Koha at lists.katipo.co.nz>
>     https://lists.katipo.co.nz/mailman/listinfo/koha
>

-- 
Kind regards,

Lennon Mazonde



More information about the Koha mailing list