Jonathan, I've updated bug 18651 with my (possibly incomplete) interpretation of some research that Nick has done on the root causes of this error.
On Mon, May 22, 2017 at 1:39 PM, Jonathan Druart < jonathan.dru...@bugs.koha-community.org> 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 <lmmazo...@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 <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 > > >> > > > > > > > > > _______________________________________________ > > 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 > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
