To test: Add new field 952 $i to framework and map to items.stocknumber. Add a new item, make stocknumber 1. Add another item, make stocknumber 1. The second stocknumber will not be saved, because the items.stocknumber is unique. The logs show an error message: [Sun Mar 13 22:12:37 2011] [error] [client ::1] [Sun Mar 13 22:12:37 2011] additem.pl: DBD::mysql::st execute failed: Duplicate entry '1' for key 'itemstocknumberidx' at /home/katrin/kohaclone/C4/Items.pm line 1896., referer: http://localhost:8080/cgi-bin/koha/cataloguing/additem.pl We need an error message for the user like we have barcode, which is also unique.
I'm not sure that stocknumber must be unique. I think it would be better to have it not unique accepted (functionnaly, at least in France, it's common to have libraries having multiple stocknumber, even if it's intellectually strange, I agree. For example, when a library buys 4 of the same book at the same time, the 4 have the same stocknumber)
I think we agree that it is a bug at the moment that can be solved in 2 different ways: 1) items.stocknumber stays unique and we give a proper error message. 2) items.stucknumber is changed to not be unique, no error message required. I think the situation in Germany might be similar to France here. It can happen that a stocknumber is assigned for more than 1 item. I think I saw it happen for serials items. And if you have old data it can happen that a stocknumber was duplicated by accident. See also discussion about stocknumber's uniqueness on bug 5839
Is someone strictly opposed to changing items.stocknumber to be not unique? I would like to work on that this weekend.
Created attachment 3506 [details] [review] Patch
Created attachment 3615 [details] [review] Signed patch Includes typo fix.
Patch pushed, please test
This patch needs a follow-up for 3.2.x and master. Problem: The applied patch and update will work only for databases created after the stocknumber field was added to the items table. For databases created before stocknumber was introduced to the database the update will fail with an error message and the unique attribute will not be removed. The problem is, that the indexname in kohastructure.pl is different to the name in updatadatabase.pl: kohastructur.sql: 1033 KEY `itemstocknumberidx` (`stocknumber`), updatadatabase.pl: 3251 ALTER TABLE items ADD UNIQUE INDEX itemsstocknumberidx (stocknumber); I am not sure how to solve this so that no error message will be shown while updating the database. And not sure I can work on this in time for the 3.4 release. If someone else has time and is able to fix this - please do.
Follow up patch sent
Created attachment 4092 [details] [review] Follow up patch
(In reply to comment #9) > Created attachment 4092 [details] [review] > Follow up patch Patch uses select on information_schema. If a server runs multiple Koha instances [and versions], this could not be reliable. Statement show indexes from items where key_name=... runs on current db. Sending signed patch including this change.
Created attachment 4103 [details] [review] Signed followup patch
Hi Marcel, thx for your work on that! I talked to Chris about it in the morning and we debated if checking for the uniqueness of the index might be away to make it easier not relying on version numbers. We also need a patch for 3.2.x and 3.4, because the bug is currently in all maintained versions. People are at different stages when updating, some might be in between and have run into a mysql error message and are already past the update with index still being unique. I think what we need to do is add another database update at the end. Making sure that in every case the index will be fixed. As I said a check for uniqueness using the information_schema tablae and the NOT_UNIQUE field might be easier here.
(In reply to comment #12) > I talked to Chris about it in the morning and we debated if checking for the > uniqueness of the index might be away to make it easier not relying on version > numbers. > We also need a patch for 3.2.x and 3.4, because the bug is currently in all > maintained versions. People are at different stages when updating, some might > be in between and have run into a mysql error message and are already past the > update with index still being unique. > I think what we need to do is add another database update at the end. Making > sure that in every case the index will be fixed. As I said a check for > uniqueness using the information_schema tablae and the NOT_UNIQUE field might > be easier here. I would not use information_schema if you do not include name of database (Table schema). Do you send another revision?
Hi Marcel, I will try to work on it at the weekend, but will be happy if someone else steps in. I tried "show indexes from items" and see the field non_unique - so I think we can combine both ideas to improve this.
Sending two additinal patches for 32x and 34x. Idea is: just delete both indexes if needed and recreate the non-unique one.
After some discussion on IRC with Chris and Katrin, now submitting three new revised patches: one for master, one for 32x and one for 34x. Incorporating all changes in one. The patch tests if it is needed to replace the index. Setting status back to needs signoff. Katrin or Chris: could you sign it please?
Created attachment 4121 [details] [review] Followup patch for 3.2.x
Created attachment 4122 [details] [review] Followup patch for 3.4.x
Created attachment 4123 [details] [review] Followup patch for master
Created attachment 4141 [details] [review] Patch for MASTER, tested and signed off
Created attachment 4143 [details] [review] Patch for 3.4.x, tested and signed off
Created attachment 4144 [details] [review] Patch for 3.2.x, tested and signed off
Patch for master pushed, please test
Patches have been pushed to master, 3.4.x. and 3.2.x. Closing this resolved.