Bug 3309 - Deleting authorized values when data exists
Summary: Deleting authorized values when data exists
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Galen Charlton
QA Contact:
URL:
Keywords:
Depends on: 9272
Blocks:
  Show dependency treegraph
 
Reported: 2009-06-08 05:19 UTC by Chris Cormack
Modified: 2019-05-05 14:50 UTC (History)
5 users (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Chris Cormack 2010-05-21 01:08:58 UTC


---- Reported by maria.laude@liblime.com 2009-06-08 17:19:29 ----

Encountered this problem when exporting Statistical Reports.  An authorized value had been deleted from system even though items existed with that value.  

User should not be able to delete authorized value if still existent in item table.



--- Bug imported by chris@bigballofwax.co.nz 2010-05-21 01:08 UTC  ---

This bug was previously known as _bug_ 3309 at http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=3309

Actual time not defined. Setting to 0.0
The original reporter of this bug does not have
   an account here. Reassigning to the person who moved
   it here: chris@bigballofwax.co.nz.
   Previous reporter was maria.laude@liblime.com.
CC member maria.laude@liblime.com does not have an account here

Comment 1 Mirko Tietgen 2012-12-16 13:42:58 UTC
This seems to still be valid. To reproduce I
- added a value to NOT_LOAN: '-5' with value 'fish'
- set an item to use that value
- deleted the authorizes value.

Two things happen:
- DBD::mysql::st execute failed: Unknown column 'id' in 'where clause' at /home/mirko/koha/admin/authorised_values.pl line 228.
- value is deleted, field shows '-5'

So there is no routine to check if the value is used and there is something wrong with the deletion. The line in referenced in the error message is

$sth = $dbh->prepare("DELETE FROM authorised_values_branches WHERE id = ?");

authorised_values_branches does not have a column called 'id', it is called 'av_id'
Comment 2 Owen Leonard 2014-07-11 16:19:44 UTC
(In reply to Mirko Tietgen from comment #1)
> This seems to still be valid. To reproduce I
> - added a value to NOT_LOAN: '-5' with value 'fish'
> - set an item to use that value
> - deleted the authorizes value.

I find that this is still the case -- I'm not blocked from deleting an authorized value which is in use by an item. However I see no related errors in the log.
Comment 3 Jonathan Druart 2015-03-24 09:49:33 UTC
(In reply to Mirko Tietgen from comment #1)
> Two things happen:
> - DBD::mysql::st execute failed: Unknown column 'id' in 'where clause' at
> /home/mirko/koha/admin/authorised_values.pl line 228.
> - value is deleted, field shows '-5'
> 
> So there is no routine to check if the value is used and there is something
> wrong with the deletion. The line in referenced in the error message is
> 
> $sth = $dbh->prepare("DELETE FROM authorised_values_branches WHERE id = ?");
> 
> authorised_values_branches does not have a column called 'id', it is called
> 'av_id'

This is fixed by bug 9272, not related to the main problem.

(In reply to Owen Leonard from comment #2)
> (In reply to Mirko Tietgen from comment #1)
> > This seems to still be valid. To reproduce I
> > - added a value to NOT_LOAN: '-5' with value 'fish'
> > - set an item to use that value
> > - deleted the authorizes value.
> 
> I find that this is still the case -- I'm not blocked from deleting an
> authorized value which is in use by an item. However I see no related errors
> in the log.

Not sure how we could fix this problem.
We will need to check all items, it will be time consuming.
But we could imagine a "check" function to know if the AV is used (category linked to a framework and value in the DB field).
Comment 4 Katrin Fischer 2019-05-04 20:41:41 UTC
Authorised values are used in so many places and can be user defined too - it seems impossible to introduce a reliable check here.
Comment 5 Jonathan Druart 2019-05-05 14:50:55 UTC
(In reply to Katrin Fischer from comment #4)
> Authorised values are used in so many places and can be user defined too -
> it seems impossible to introduce a reliable check here.
;..
For AVs linked to biblio frameworks I added a test to the "search for inconsistencies" script, see bug 21466.