Bug 18428

Summary: move of holds to old_reserves is not handled correctly.
Product: Koha Reporter: Barton Chittenden <barton>
Component: DatabaseAssignee: Galen Charlton <gmcharlt>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: jonathan.druart, jplevyak, magnus
Version: master   
Hardware: All   
OS: All   
URL: https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18242
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18931
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:

Description Barton Chittenden 2017-04-13 13:42:21 UTC
I'm not exactly sure how we got ourseleves into this situation, but one of our libraries is getting the following error when trying to check in an item that is on hold:

DBD::mysql::st execute failed: Duplicate entry '39' for key 'PRIMARY' [for Statement "INSERT INTO `old_reserves` ( `biblionumber`, `borrowernumber`, `branchcode`, `cancellationdate`, `expirationdate`, `found`, `itemnumber`, `itemtype`, `lowestPriority`, `notificationdate`, `priority`, `reminderdate`, `reserve_id`, `reservedate`, `reservenotes`, `suspend`, `suspend_until`, `timestamp`, `waitingdate`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" with ParamValues: 0='11198', 1='693', 2='CFOT', 3=undef, 4=undef, 5='F', 6=undef, 7=undef, 8='0', 9=undef, 10=0, 11=undef, 12='39', 13='2017-04-13', 14='', 15='0', 16=undef, 17='2017-04-13 06:19:29', 18=undef] at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1832.
DBIx::Class::Storage::DBI::_dbh_execute(): Duplicate entry '39' for key 'PRIMARY' at /usr/share/koha/lib/Koha/Object.pm line 120

This is analogous to bug 18242 for old_issues.

I wonder if we need to do the same thing for the deleted* tables.
Comment 1 Barton Chittenden 2017-06-19 20:54:49 UTC
The underlying problem is that innodb doesn't keep auto_increment across SQL server restarts -- it simply sets auto_increment to max(reserves.reserve_id)+1 on startup.

Because the action of moving rows from reserves to old_reserves makes old_reserves.reserve_id higher than reserves.reserve_id, rebooting the server when it's in that state will create duplicate reserve_ids between reserves and old_reserves.

since we treat deleted* tables the same way, we should also fix

select distinct table_name from information_schema.columns where table_name like 'deleted%';  
+--------------------+
| table_name         |
+--------------------+
| deletedbiblio      |
| deletedbiblioitems |
| deletedborrowers   |
| deleteditems       |
+--------------------+
Comment 2 Jonathan Druart 2017-06-19 20:59:08 UTC
To me the only clean and safe way to do that is to merge the tables. I plan to work on that.
Comment 3 Barton Chittenden 2017-06-20 00:07:58 UTC
(In reply to Jonathan Druart from comment #2)
> To me the only clean and safe way to do that is to merge the tables. I plan
> to work on that.

I don't disagree that merging the tables is probably the right thing to do, but I think that we *can* safely fix the problem by adjusting the auto_increment at startup, as mentioned in IRC:

21:22	barton	rangi: I think the right place to fix this is in the mysql init file.
21:22		... we fix the problem on server startup, then we don't have to worry about it.
21:24	rangi	thats not so hard for debian
21:24		there is a /etc/mysql/conf.d/
21:24	barton	by 'fix the problem' I mean set the auto_increment for issues and reserves.
21:25	rangi	and my.cnf does includedir /etc/mysql/conf.d/
21:25		so you can put a koha.cnf in there
21:25		and it will be used
21:25		and not overwritten by mysql upgrades
21:25	barton	excellent. We should probably do the same thing for the deleted* tables.

... all told, I think that the tables that need to be merged or twiddled at startup is issues/old_issues, reserves/old_reserves, biblios/oldbiblios, biblioitems/oldbiblioitems, items/olditems, borrowers/deletedborrowers -- that's a lot fix at once. If we figure out how to fix one table in koha.cnf, we can do all six, then work on the plumbing.

Just my two cents.
Comment 4 Magnus Enger 2017-06-21 12:01:46 UTC
(In reply to Jonathan Druart from comment #2)
> To me the only clean and safe way to do that is to merge the tables. I plan
> to work on that.

Sounds good to me! :-) (I just had a customer run into this.)
Comment 5 Jonathan Druart 2019-12-11 10:53:59 UTC
This is now fixed, see wiki page for more info: https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix

Tables still not merged however.