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> 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> > 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
