[Bug 14069] New: Follow-up 13790: Fix database update to drop PK on issues/old_issues first
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Bug ID: 14069 Summary: Follow-up 13790: Fix database update to drop PK on issues/old_issues first Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: blocker Priority: P5 - low Component: Database Assignee: gmcharlt@gmail.com Reporter: katrin.fischer@bsz-bw.de QA Contact: testopia@bugs.koha-community.org In some older databases there is a PK on the itemnumber in issues. The database update for 13790 needs to be fixed to also run without problems on those databases. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |13790 -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on|13790 | -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |13790 -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@biblibre.co | |m, tomascohen@gmail.com --- Comment #1 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Is anyone working on this one? -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |kyle.m.hall@gmail.com -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dpavlin@rot13.org -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 --- Comment #2 from Jonathan Druart <jonathan.druart@biblibre.com> --- Created attachment 38644 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38644&action=edit Bug 14069: Drop existing primary key on items if exists On old databases, the issues table has a primary key on itemnumber. The DBrev 3.19.00.028 (Bug 13790 - Add unique id issue_id to issues and oldissues tables) has to remove it before adding the new primary key Test plan: 1/ Make sure you have an old DB, or add primary key (issues.itemnumber)manually on a DB created before 3.19.00.028. 2/ Execute the updatedatabase.pl script 3/ You should not get any error and the primary key on itemnumber should have been removed and the new one created as expected. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|gmcharlt@gmail.com |jonathan.druart@biblibre.co | |m -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #38644|0 |1 is obsolete| | --- Comment #3 from Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> --- Created attachment 38720 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38720&action=edit [SIGNED-OFF] Bug 14069: Drop existing primary key on items if exists On old databases, the issues table has a primary key on itemnumber. The DBrev 3.19.00.028 (Bug 13790 - Add unique id issue_id to issues and oldissues tables) has to remove it before adding the new primary key Test plan: 1/ Make sure you have an old DB, or add primary key (issues.itemnumber)manually on a DB created before 3.19.00.028. 2/ Execute the updatedatabase.pl script 3/ You should not get any error and the primary key on itemnumber should have been removed and the new one created as expected. Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> No koha-qa errors Tested using a 3.4 db (3.0404000) Interestingly, it gives a similar error on the same table but on another (older $DBversion = "3.05.00.009";) update 1) Pre-patch, 3.4 DB, updatedatabase from master, errors (older) DBD::mysql::db do failed: Multiple primary key defined at installer/data/mysql/updatedatabase.pl line 4435. (13790) DBD::mysql::db do failed: Multiple primary key defined at installer/data/mysql/updatedatabase.pl line 10166. DBD::mysql::db do failed: Unknown column 'issue_id' in 'order clause' at installer/data/mysql/updatedatabase.pl line 10170. DBD::mysql::st execute failed: Unknown column 'me.issue_id' in 'field list' at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1593. DBIx::Class::ResultSetColumn::max(): Unknown column 'me.issue_id' in 'field list' at installer/data/mysql/updatedatabase.pl line 10174 2) Post-patch, same conditions (only older) DBD::mysql::db do failed: Multiple primary key defined at installer/data/mysql/updatedatabase.pl line 4435. There are other warnings, but nor related with keys. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |bgkriegel@gmail.com Patch complexity|--- |Trivial patch Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #38720|0 |1 is obsolete| | --- Comment #4 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 38736 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38736&action=edit [PASSED QA] Bug 14069: Drop existing primary key on items if exists On old databases, the issues table has a primary key on itemnumber. The DBrev 3.19.00.028 (Bug 13790 - Add unique id issue_id to issues and oldissues tables) has to remove it before adding the new primary key Test plan: 1/ Make sure you have an old DB, or add primary key (issues.itemnumber)manually on a DB created before 3.19.00.028. 2/ Execute the updatedatabase.pl script 3/ You should not get any error and the primary key on itemnumber should have been removed and the new one created as expected. Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> No koha-qa errors Tested using a 3.4 db (3.0404000) Interestingly, it gives a similar error on the same table but on another (older $DBversion = "3.05.00.009";) update 1) Pre-patch, 3.4 DB, updatedatabase from master, errors (older) DBD::mysql::db do failed: Multiple primary key defined at installer/data/mysql/updatedatabase.pl line 4435. (13790) DBD::mysql::db do failed: Multiple primary key defined at installer/data/mysql/updatedatabase.pl line 10166. DBD::mysql::db do failed: Unknown column 'issue_id' in 'order clause' at installer/data/mysql/updatedatabase.pl line 10170. DBD::mysql::st execute failed: Unknown column 'me.issue_id' in 'field list' at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1593. DBIx::Class::ResultSetColumn::max(): Unknown column 'me.issue_id' in 'field list' at installer/data/mysql/updatedatabase.pl line 10174 2) Post-patch, same conditions (only older) DBD::mysql::db do failed: Multiple primary key defined at installer/data/mysql/updatedatabase.pl line 4435. There are other warnings, but nor related with keys. Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to Master --- Comment #5 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Patch pushed to master. Thanks Jonathan! -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Colin Campbell <colin.campbell@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |colin.campbell@ptfs-europe. | |com --- Comment #6 from Colin Campbell <colin.campbell@ptfs-europe.com> --- Wonder if there needs to be a unique constraint on itemnumber in issues. Have noticed after losing the old PK constraint that duplicate issues can now occur, in various places ( fines? holds?) I think there are assumptions that only 1 isssue will exist for an itemnumber -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 --- Comment #7 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Colin Campbell from comment #6)
Wonder if there needs to be a unique constraint on itemnumber in issues. Have noticed after losing the old PK constraint that duplicate issues can now occur, in various places ( fines? holds?) I think there are assumptions that only 1 isssue will exist for an itemnumber
I think a unique for the issues table would make sense. Did you experience double issues? It sounds scary, but I am still hoping we are not doing that, as it looked like not all installations had the PK to begin with. We removed the line from kohastructure quite a while back by accident with: http://git.koha-community.org/gitweb/?p=koha.git;a=commit;h=91b5a09c6cef398d... -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Liz Rea <liz@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |liz@catalyst.net.nz --- Comment #8 from Liz Rea <liz@catalyst.net.nz> --- I have seen double ups in issues on 3.20, probably because of this. Example: +----------+----------------+------------+---------------------+---------------------+---------------------+ | issue_id | borrowernumber | itemnumber | date_due | timestamp | issuedate | +----------+----------------+------------+---------------------+---------------------+---------------------+ | 1840368 | 547 | 123292 | 2015-10-29 23:59:00 | 2015-10-08 10:09:20 | 2015-10-08 10:09:19 | | 1840369 | 547 | 123292 | 2015-10-29 23:59:00 | 2015-10-08 10:09:21 | 2015-10-08 10:09:20 | +----------+----------------+------------+---------------------+---------------------+---------------------+ Because the ajax table is slow to load, sometimes extra careful staff members scan things twice because they thought they didn't get it. Seems a bit odd that it wouldn't prompt for renew instead of generating another issue, but there it is. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |14978 Referenced Bugs: http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14978 [Bug 14978] issues.itemnumber should be a unique key -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14069 --- Comment #9 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- I have restored the constraint on bug 14978, but I think the checkout will now make Koha explodes (the DB will raise on error). -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org