[Koha] Items Not Being Cleared From Patron Account After Check-In
Jonathan Druart
jonathan.druart at bugs.koha-community.org
Tue May 23 05:39:24 NZST 2017
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> 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>> 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>> 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
> >>
> >
>
>
> _______________________________________________
> Koha mailing list http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
More information about the Koha
mailing list