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 <lmmazo...@gmail.com
<mailto:lmmazo...@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
<lmmazo...@gmail.com <mailto:lmmazo...@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@lists.katipo.co.nz <mailto:Koha@lists.katipo.co.nz>
https://lists.katipo.co.nz/mailman/listinfo/koha
