Bug 23330

Summary:	deleteditemsstocknumberidx should not be a unique index
Product:	Koha	Reporter:	David Cook <dcook>
Component:	Database	Assignee:	Bugs List <koha-bugs>
Status:	CLOSED WONTFIX	QA Contact:	Testopia <testopia>
Severity:	normal
Priority:	P5 - low	CC:	m.de.rooy
Version:	Main
Hardware:	All
OS:	All
See Also:	https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30481
Change sponsored?:	---	Patch complexity:	---
Documentation contact:		Documentation submission:
Text to go in the release notes:		Version(s) released in:
Circulation function:

Description David Cook 2019-07-17 08:21:53 UTC

At the moment, deleteditemsstocknumberidx is a unique index, which means that an item will fail to delete if its "Inventory number" already exists in the deleteditems table.

Its counterpart itemsstocknumberidx was renamed to itemstocknumberidx in version 3.03.00.042 (dating from April/May 2011) and made non-unique.

I've worked on Koha since early 2012 and only today have I had someone stung by this problem. They've been storing "Copy number" in "Inventory number" for some reason, and it's caught them out.

Comment 1 Katrin Fischer 2019-08-18 21:05:10 UTC

It looks like we never dropped the unique index on the deleteditems table... but fixed it only for new installations:

kohastructure.sql
deleteditems
KEY `delitemstocknumberidx` (`stocknumber`),


3.01.00.095 Added *items.stocknumber with a unique index
3.03.00.042 Removed the unique index from items.stocknumber

But no mention of the same change for deleteditems.

Comment 2 David Cook 2020-02-18 01:42:57 UTC

We could probably close this one off as it only affects pre-3.04 versions of Koha.

I think there are other cases where similar things have happened, so I'm sure upgraded databases vs new databases are already out of sync in other ways...

Comment 3 Katrin Fischer 2022-02-14 22:50:50 UTC

(In reply to David Cook from comment #2)
> We could probably close this one off as it only affects pre-3.04 versions of
> Koha.

Comment 4 Marcel de Rooy 2022-04-07 09:50:33 UTC

(In reply to David Cook from comment #2)
> We could probably close this one off as it only affects pre-3.04 versions of
> Koha.
> 
> I think there are other cases where similar things have happened, so I'm
> sure upgraded databases vs new databases are already out of sync in other
> ways...

Yes, I have opened a few of these reports the last few days. Added some code on at least one of them. These patches are not very popular.
But I would recommend to leave them open until they are fixed.
These kind of sync problems could at some point cause issues..