Ok, thanks. I'll be following the bug report Kind regards, Lennon Mazonde On 22/05/2017 19:39, Jonathan Druart 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 <lmmazonde@gmail.com <mailto:lmmazonde@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 <lmmazonde@gmail.com <mailto:lmmazonde@gmail.com> > <mailto:lmmazonde@gmail.com <mailto:lmmazonde@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 >> <lmmazonde@gmail.com <mailto:lmmazonde@gmail.com> <mailto:lmmazonde@gmail.com <mailto:lmmazonde@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> <http://154.120.230.62:27062>] >> AH01215: [Mon May 22 07:04:15 2017] >> returns.pl <http://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> <http://154.120.230.62:31881>] >> AH01215: [Mon May 22 07:08:57 2017] >> returns.pl <http://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> <http://154.120.230.62:43475>] >> AH01215: >> [Mon May 22 08:56:10 2017] returns.pl <http://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> <http://154.120.230.62:43475>] >> AH01215: [Mon May 22 08:56:17 2017] >> returns.pl <http://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> <http://154.120.230.62:43475>] >> AH01215: [Mon May 22 08:56:22 2017] >> returns.pl <http://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> <http://154.120.230.62:44454>] >> AH01215: >> [Mon May 22 08:59:21 2017] circulation.pl <http://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> <http://154.120.230.62:48991>] >> AH01215: >> [Mon May 22 09:08:18 2017] circulation.pl <http://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> <mailto: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 <mailto:Koha@lists.katipo.co.nz> https://lists.katipo.co.nz/mailman/listinfo/koha