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.
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 | +--------------------+
To me the only clean and safe way to do that is to merge the tables. I plan to work on that.
(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.
(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.)
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.