[Koha] Items Not Being Cleared From Patron Account After Check-In
Lennon Mazonde
lmmazonde at gmail.com
Tue May 23 02:15:37 NZST 2017
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
>
More information about the Koha
mailing list