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
