[Koha] Duplicate entry in old_reserves

[Jonathan Druart]

This is being discussed on bug 18003, please continue the discussion on the
bug.

On Tue, 31 Jan 2017 at 20:44 Tomas Cohen Arazi <tomascohen at gmail.com> wrote:

> We need to add ids to old_<tablenames> and have the original id be kept for
> historical purposes. As it is not reliable to use auto_increments on InnoDB
> the way we use them.
>
> "Switch to Postgres" is not a valid answer, please :-D
>
> El mar., 31 ene. 2017 a las 16:42, Tim McMahon (<tmcmahon at wlpl.org>)
> escribió:
>
> On 01/31/2017 12:37 PM, Jonathan Druart wrote:
> > On Tue, 31 Jan 2017 at 18:59 Tim McMahon <tmcmahon at wlpl.org> wrote:
> >
> >> Thanks for the quick reply.  We've been on Koha since 2006 and the item
> >> in question was fairly new, so that wouldn't be it.  I agree, the
> >> auto-incremement on the reserves key should keep this problem from
> >> happening.
> >>
> >
> > Unfortunately, not at all.
> > From
> >
>
> https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
> > """
> > If you specify an AUTO_INCREMENT column for an InnoDB table, the table
> > handle in the InnoDB data dictionary contains a special counter called
> the
> > auto-increment counter that is used in assigning new values for the
> column.
> > This counter is stored only in main memory, not on disk.
> > """
> >
> > That means you can get twice the same number if you restart mysql.
> > Bug or feature?...
> >
> >
> >
> I don't remember restarting MySQL recently, but that doesn't mean for
> sure that I didn't.  I'm willing to say that it could happen again and
> just hope it doesn't.  The conflicting reserve_id was from reserves
> placed days apart with a few reserves placed between, so that doesn't
> look like it would be the cause.
>
> Thanks!
>
> >> On 01/31/2017 11:39 AM, Hugo Agud wrote:
> >>> Hi
> >>>
> >>> We have seen this when we have migrated data from another ils, the
> issue
> >>> has an auto-incremental id, but old_issues copy the id from the issues,
> I
> >>> guess that perhaps it is the same with reserves and old_reserves.
> >>>
> >>> then the question is, have you migrated koha from another ILS and you
> >> have
> >>> migrated historical data on reserves? If yes.. that it should be the
> >> answer
> >>>
> >>> 2017-01-31 18:16 GMT+01:00 Tim McMahon <tmcmahon at wlpl.org>:
> >>>
> >>>> I got an internal server error while checking out a reserved item to a
> >>>> patron.
> >>>>
> >>>> The plack-error.log showed this error:
> >>>>
> >>>> DBD::mysql::st execute failed: Duplicate entry '4515' 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='4095', 1='4310', 2='WLPL', 3=undef, 4=undef, 5='F', 6='4177',
> >> 7=undef,
> >>>> 8='0', 9=undef, 10=0, 11=undef, 12='4515', 13='2017-01-26', 14='',
> >> 15='0',
> >>>> 16=undef, 17='2017-01-26 14:05:25', 18='2017-01-26'] at
> >>>> /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1832.
> >>>> DBIx::Class::Storage::DBI::_dbh_execute(): Duplicate entry '4515' for
> >> key
> >>>> 'PRIMARY' at /usr/share/koha/lib/Koha/Object.pm line 120
> >>>>
> >>>> I checked in the old_reserves table and found that there was already
> an
> >>>> entry with a reserve_id of 4515.
> >>>>
> >>>> Does anyone know how this would happen?
> >>>>
> >>>> I'm running Koha 16.11.20.000 on Debian 8 and just updated from
> packages
> >>>> last Wednesday if that's any help.
> >>>>
> >>>> --
> >>>> Tim McMahon
> >>>> Technical Services
> >>>> West Liberty Public Library
> >>>> _______________________________________________
> >>>> Koha mailing list  http://koha-community.org
> >>>> Koha at lists.katipo.co.nz
> >>>> https://lists.katipo.co.nz/mailman/listinfo/koha
> >>>>
> >>>
> >>>
> >>>
> >>
> >> --
> >> Tim McMahon
> >> Technical Services
> >> West Liberty Public Library
> >> _______________________________________________
> >> Koha mailing list  http://koha-community.org
> >> Koha at lists.katipo.co.nz
> >> https://lists.katipo.co.nz/mailman/listinfo/koha
> >>
> > _______________________________________________
> > Koha mailing list  http://koha-community.org
> > Koha at lists.katipo.co.nz
> > https://lists.katipo.co.nz/mailman/listinfo/koha
> >
>
> --
> Tim McMahon
> Technical Services
> West Liberty Public Library
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
> --
> Tomás Cohen Arazi
> Theke Solutions (https://theke.io <http://theke.io/>)
> ✆ +54 9351 3513384 <+54%209%20351%20351-3384> <+54%209%20351%20351-3384>
> GPG: B2F3C15F
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>