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.
Is anyone working on this one?
Created attachment 38644 [details] [review] 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.
Created attachment 38720 [details] [review] [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.
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>
Patch pushed to master. Thanks Jonathan!
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
(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
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.
I have restored the constraint on bug 14978, but I think the checkout will now make Koha explodes (the DB will raise on error).