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

Lennon Mazonde lmmazonde at gmail.com
Tue May 23 05:55:01 NZST 2017


Ok, thanks. I'll be following the bug report

Kind regards,

Lennon Mazonde

On 22/05/2017 19:39, Jonathan Druart wrote:
> I did not follow everything in your manipulations, but there is 
> definitely a bug.
> I have opened bug 18651 to try and fix it.
>
>
> On Mon, 22 May 2017 at 13:49 Lennon Mazonde <lmmazonde at gmail.com 
> <mailto:lmmazonde at gmail.com>> wrote:
>
>     Hi Nick,
>
>     Just out of interest's sake, i changed the increment value in
>     /usr/share/koha/lib/C4/Circulation.pm on line 2178 from +1 to a random
>     number +1111.
>
>     This resulted in a successful check-in and the next old_issue_id that
>     was generated increased by +1111.
>
>     I reverted the increment value in Circulation.pm to +1 and subsequent
>     check-ins are (for now) processing successfully. I'm sure i shouldn't
>     temper with those files, but it's given my client another bone to chew
>     while I try and sort out the problem.
>
>     Regards,
>
>     Lennon
>
>     On 22/05/2017 16:34, Nick Clemens wrote:
>     > Hi Lennon,
>     >
>     > The auto_increment will need to be increased and those
>     duplicated IDs
>     > as well.
>     >
>     > Trying to clarify the error below
>     >
>     > The error seems to stem from a situation where there are greater
>     > issues.issue_id's than old_issues.issue_id
>     >
>     > For a scenario:
>     > Old issues has: 1,2,3,4,5,9,10
>     > Issues has: 6,7,8 (auto_increment 11)
>     > SERVER RESTARTED
>     > Old issues unchanged
>     > Issues has: 6,7,8 (auto_increment 9)
>     >
>     > Actions:
>     > Issues 9, 10, 11, 12 created
>     > checkin issue 9 - found in old_issues, try increasing to 11 - but
>     > issues 11 exists - checkin fails
>     >
>     > Talking with Jonathan and Kyle, we face another problem in that
>     > accountlines are now linked to issue_id - so altering one breaks the
>     > connection. This scenario is rare at least but must be handled.
>     >
>     >
>     > On Mon, May 22, 2017 at 10:15 AM Lennon Mazonde
>     <lmmazonde at gmail.com <mailto:lmmazonde at gmail.com>
>     > <mailto:lmmazonde at gmail.com <mailto:lmmazonde at gmail.com>>> wrote:
>     >
>     >     Hi Nick,
>     >
>     >     Thanks for responding.
>     >
>     >>     Try: SELECT issue_id FROM issues JOIN old_issues USING
>     (issue_id)
>     >
>     >     Running the above produced about 91 rows of overlapping values:
>     >
>     >>     mysql> SELECT issue_id FROM issues JOIN old_issues USING
>     (issue_id);
>     >>     +----------+
>     >>     | issue_id |
>     >>     +----------+
>     >>     |    14374 |
>     >>     |    14424 |
>     >>     |    14372 |
>     >>     |    14369 |
>     >>     |    14617 |
>     >>     |    14391 |
>     >>     |    14383 |
>     >>     |    14381 |
>     >     and so on...
>     >
>     >
>     >>     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'll increase the auto increment and let you know if that solves
>     >     the issue.
>     >
>     >
>     >>     A similar problem can occur in the reserves/old_reserves tables
>     >>
>     >     I  checked the reserves/old_reserves tables, there no duplicate
>     >     reserve_ids there.
>     >
>     >     Regards,
>     >
>     >     Lennon
>     >
>     >>
>     >>
>     >>     On Mon, May 22, 2017 at 6:00 AM Lennon Mazonde
>     >>     <lmmazonde at gmail.com <mailto:lmmazonde at gmail.com>
>     <mailto: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> <http://154.120.230.62:27062>]
>     >>         AH01215: [Mon May 22 07:04:15 2017]
>     >> returns.pl <http://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> <http://154.120.230.62:31881>]
>     >>         AH01215: [Mon May 22 07:08:57 2017]
>     >> returns.pl <http://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> <http://154.120.230.62:43475>]
>     >>         AH01215:
>     >>                  [Mon May 22 08:56:10 2017] returns.pl
>     <http://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> <http://154.120.230.62:43475>]
>     >>         AH01215: [Mon May 22 08:56:17 2017]
>     >> returns.pl <http://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> <http://154.120.230.62:43475>]
>     >>         AH01215: [Mon May 22 08:56:22 2017]
>     >> returns.pl <http://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> <http://154.120.230.62:44454>]
>     >>         AH01215:
>     >>                  [Mon May 22 08:59:21 2017] circulation.pl
>     <http://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> <http://154.120.230.62:48991>]
>     >>         AH01215:
>     >>                  [Mon May 22 09:08:18 2017] circulation.pl
>     <http://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>
>     <mailto:Koha at lists.katipo.co.nz <mailto:Koha at lists.katipo.co.nz>>
>     >> https://lists.katipo.co.nz/mailman/listinfo/koha
>     >>
>     >
>
>
>     _______________________________________________
>     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
>



More information about the Koha mailing list