Hi Lennon, I would suspect the issue is not hitting the limit on issue_id, but having duplicate values in the tables.
When a server is restarted MySQL will reset the auto_increment counter to the highest value in the current table. This means issue_is in issues can end up overlapping with issue_id in old_issues. Try: SELECT issue_id FROM issues JOIN old_issues USING (issue_id) That should show any overlapping values. 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. A similar problem can occur in the reserves/old_reserves tables -Nick On Mon, May 22, 2017 at 6:00 AM Lennon Mazonde <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] AH01215: [Mon May 22 07:04:15 2017] > 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] AH01215: [Mon May 22 07:08:57 2017] > 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] AH01215: > [Mon May 22 08:56:10 2017] 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] AH01215: [Mon May 22 08:56:17 2017] > 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] AH01215: [Mon May 22 08:56:22 2017] > 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] AH01215: > [Mon May 22 08:59:21 2017] 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] AH01215: > [Mon May 22 09:08:18 2017] 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 > https://lists.katipo.co.nz/mailman/listinfo/koha > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
