[Bug 18428] New: Duplicate key error in old_reserves table.

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 18428] New: Duplicate key error in old_reserves table.

bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18428

            Bug ID: 18428
           Summary: Duplicate key error in old_reserves table.
 Change sponsored?: ---
           Product: Koha
           Version: master
          Hardware: All
                OS: All
            Status: NEW
          Severity: normal
          Priority: P5 - low
         Component: Database
          Assignee: [hidden email]
          Reporter: [hidden email]
        QA Contact: [hidden email]

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.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 18428] Duplicate key error in old_reserves table.

bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18428

Barton Chittenden <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|normal                      |major

--- Comment #1 from Barton Chittenden <[hidden email]> ---
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       |
+--------------------+

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 18428] Duplicate key error in old_reserves table.

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18428

Barton Chittenden <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           See Also|                            |https://bugs.koha-community
                   |                            |.org/bugzilla3/show_bug.cgi
                   |                            |?id=18242

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 18428] Duplicate key error in old_reserves table.

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18428

Jonathan Druart <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]-c
                   |                            |ommunity.org

--- Comment #2 from Jonathan Druart <[hidden email]> ---
To me the only clean and safe way to do that is to merge the tables. I plan to
work on that.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 18428] move of holds to old_reserves is not handled correctly.

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18428

Barton Chittenden <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|Duplicate key error in      |move of holds to
                   |old_reserves table.         |old_reserves is not handled
                   |                            |correctly.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 18428] move of holds to old_reserves is not handled correctly.

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18428

--- Comment #3 from Barton Chittenden <[hidden email]> ---
(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.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 18428] move of holds to old_reserves is not handled correctly.

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18428

Magnus Enger <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #4 from Magnus Enger <[hidden email]> ---
(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.)

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 18428] move of holds to old_reserves is not handled correctly.

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18428

Jonathan Druart <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           See Also|                            |https://bugs.koha-community
                   |                            |.org/bugzilla3/show_bug.cgi
                   |                            |?id=18931

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 18428] move of holds to old_reserves is not handled correctly.

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18428

Jonathan Druart <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                URL|                            |https://wiki.koha-community
                   |                            |.org/wiki/DBMS_auto_increme
                   |                            |nt_fix

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Loading...