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 <lmmazo...@gmail.com
<mailto: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>
> <mailto: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>
<mailto: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> <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
_______________________________________________
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha
