Bug 14069 - Follow-up 13790: Fix database update to drop PK on issues/old_issues first
Summary: Follow-up 13790: Fix database update to drop PK on issues/old_issues first
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P5 - low blocker (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
Depends on: 13790
Blocks: 14978
  Show dependency treegraph
 
Reported: 2015-04-28 09:53 UTC by Katrin Fischer
Modified: 2019-06-27 09:24 UTC (History)
7 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 14069: Drop existing primary key on items if exists (1.41 KB, patch)
2015-04-29 08:20 UTC, Jonathan Druart
Details | Diff | Splinter Review
[SIGNED-OFF] Bug 14069: Drop existing primary key on items if exists (2.53 KB, patch)
2015-04-30 13:32 UTC, Bernardo Gonzalez Kriegel
Details | Diff | Splinter Review
[PASSED QA] Bug 14069: Drop existing primary key on items if exists (2.59 KB, patch)
2015-05-01 12:59 UTC, Kyle M Hall
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Katrin Fischer 2015-04-28 09:53:44 UTC
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.
Comment 1 Tomás Cohen Arazi 2015-04-28 17:19:01 UTC
Is anyone working on this one?
Comment 2 Jonathan Druart 2015-04-29 08:20:08 UTC Comment hidden (obsolete)
Comment 3 Bernardo Gonzalez Kriegel 2015-04-30 13:32:46 UTC Comment hidden (obsolete)
Comment 4 Kyle M Hall 2015-05-01 12:59:48 UTC
Created attachment 38736 [details] [review]
[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>
Comment 5 Tomás Cohen Arazi 2015-05-04 14:48:01 UTC
Patch pushed to master.

Thanks Jonathan!
Comment 6 Colin Campbell 2015-05-12 09:39:48 UTC
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
Comment 7 Katrin Fischer 2015-05-12 09:58:09 UTC
(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=91b5a09c6cef398d9ad39ca1ced7d6569a87456b
Comment 8 Liz Rea 2015-10-07 23:34:21 UTC
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.
Comment 9 Jonathan Druart 2015-10-08 08:20:57 UTC
I have restored the constraint on bug 14978, but I think the checkout will now make Koha explodes (the DB will raise on error).