Bug 6428 - refered column 'items.coded_location_qualifier' does not exist
Summary: refered column 'items.coded_location_qualifier' does not exist
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P2 blocker (vote)
Assignee: Nuño López Ansótegui
QA Contact: Mason James
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-05-30 07:36 UTC by Mason James
Modified: 2023-12-06 13:37 UTC (History)
8 users (show)

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


Attachments
patch (85.52 KB, patch)
2011-05-30 23:36 UTC, Mason James
Details | Diff | Splinter Review
[SIGNED-OFF] bug 6428: remove mapping to non-existent field in frameworks (84.18 KB, patch)
2011-06-15 08:28 UTC, Katrin Fischer
Details | Diff | Splinter Review
patch for existing Kohas (1.25 KB, patch)
2011-07-20 15:34 UTC, Mason James
Details | Diff | Splinter Review
Signed follow up patch (db rev) (1.52 KB, patch)
2011-08-04 08:56 UTC, Marcel de Rooy
Details | Diff | Splinter Review
pix (107.25 KB, image/png)
2011-10-08 11:15 UTC, Mason James
Details
patch (86.50 KB, patch)
2011-10-08 12:04 UTC, Mason James
Details | Diff | Splinter Review
proposed patch (3.22 KB, patch)
2012-10-23 11:58 UTC, Nuño López Ansótegui
Details | Diff | Splinter Review
fixed patch (3.15 KB, patch)
2012-10-23 12:13 UTC, Nuño López Ansótegui
Details | Diff | Splinter Review
[SIGNED-OFF] Bug 6428 - refered column 'items.coded_location_qualifier' does not exist (3.27 KB, patch)
2012-11-02 13:13 UTC, Kyle M Hall
Details | Diff | Splinter Review
affix to patch (1.36 KB, patch)
2012-11-12 08:53 UTC, Nuño López Ansótegui
Details | Diff | Splinter Review
affix to patch (1.36 KB, patch)
2012-11-12 09:05 UTC, Nuño López Ansótegui
Details | Diff | Splinter Review
affix to patch (2.07 KB, patch)
2012-11-12 10:53 UTC, Nuño López Ansótegui
Details | Diff | Splinter Review
[SIGNED-OFF] Bug 6428 [Follow-up] refered column 'items.coded_location_qualifier' does not exist - affix (2.16 KB, patch)
2012-11-12 16:54 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 6428 [Follow-up] refered column 'items.coded_location_qualifier' does not exist (2.50 KB, patch)
2012-11-13 10:19 UTC, Nuño López Ansótegui
Details | Diff | Splinter Review
Bug 6428 [Follow-up] refered column 'items.coded_location_qualifier' does not exist (2.54 KB, patch)
2012-11-13 10:37 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Mason James 2011-05-30 07:36:03 UTC

    
Comment 1 Mason James 2011-05-30 07:40:43 UTC
missing column is referered to in 23 framework files

koha.git:$ grep -irl 'coded_location_qualifier' ./* |wc -l
23

http://git.koha-community.org/cgi-bin/gitweb.cgi?p=koha.git;a=blob;f=installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql;h=383a9d92735530224271ec739ac30ad60e96504e;hb=HEAD#l121


and causes additem.pl to fail sometimes


[Mon May 30 15:11:49 2011] additem.pl: DBD::mysql::st execute failed: Unknown column 'coded_location_qualifier' in 'field list' at /home/koha/koha.git/C4/Items.pm line 2088.
[Mon May 30 15:11:49 2011] additem.pl: ERROR in _koha_modify_item UPDATE items SET restricted=?,items.cn_sort=?,wthdrawn=?,coded_location_qualifier=?,notforloan=?,replacementpricedate=?,itemnumber=?,itemnotes=?,ccode=?,location=?,itemcallnumber=?,stack=?,itemlost=?,barcode=?,uri=?,items.cn_source=?,materials=?,datelastseen=?,price=?,issues=?,homebranch=?,replacementprice=?,more_subfields_xml=?,holdingbranch=?,booksellerid=?,damaged=?,stocknumber=?,datelastborrowed=?,dateaccessioned=?,enumchron=?,copynumber=?,permanent_location=?,itype=?,paidfor=? WHERE itemnumber=?Unknown column 'coded_location_qualifier' in 'field list' at /home/koha/koha.git/C4/Items.pm line 2091.
Comment 2 Mason James 2011-05-30 08:09:57 UTC
i'm not sure what the fix is here...

the missing column is not refered to *explicilty* in any code either.

so, we either remove the rows from the framework 'marc_subfield_structure' table
or, add the missing column to the items table
Comment 3 Mason James 2011-05-30 23:16:17 UTC
hmm, an old email thread on the missing 'items.items.coded_location_qualifier' column hints that its something to ignore

http://koha.1045719.n5.nabble.com/What-is-the-coded-location-qualifier-in-952-f-td3061124.html

-------------------------------------------------
Re: What is the coded location qualifier in 952 $f 

Practical answer: a field to ignore entirely ;) 

Long answer: It's probably derived from the MARC21 definition of the 
852 $f, also called "coded location qualifier", which is meant to 
encode a situation where most of the items in a run of a serial are 
held in a location (e.g., the magazine stacks) with a few held in a 
second location (the new magazines area).  For the gory details, 
please see: 

http://www.loc.gov/marc/holdings/hd852.html

Koha doesn't do anything with that subfield, and certainly makes no 
attempt to parse the coding. 

Regards, 

Galen
-------------------------------------------------
Comment 4 Mason James 2011-05-30 23:33:53 UTC
(In reply to comment #2)
> i'm not sure what the fix is here...
> 
> the missing column is not refered to *explicilty* in any code either.
> 
> so, we either remove the rows from the framework 'marc_subfield_structure'
> table
> or, add the missing column to the items table


i have a better idea for this fix ;)

lets leave the 952$f defined in the frameworks (as it is in the LOC specs)
but remove the buggy reference to  the 'items.coded_location_qualifier'  column

patch attached....
Comment 5 Mason James 2011-05-30 23:36:15 UTC Comment hidden (obsolete)
Comment 6 Katrin Fischer 2011-06-15 08:28:29 UTC Comment hidden (obsolete)
Comment 7 Katrin Fischer 2011-06-15 08:31:46 UTC
This patch will only work for new installations. 
Do we need a follow-up with a database update for existing installations?
Comment 8 Mason James 2011-07-20 15:30:08 UTC
(In reply to comment #7)
> This patch will only work for new installations. 
> Do we need a follow-up with a database update for existing installations?

indeed, i'll add a patch to fix existing systems too...
Comment 9 Mason James 2011-07-20 15:34:44 UTC Comment hidden (obsolete)
Comment 10 Marcel de Rooy 2011-08-04 08:56:12 UTC Comment hidden (obsolete)
Comment 11 Paul Poulain 2011-10-06 12:09:54 UTC
Mason, could you sent an updated patch please, this one doesn't apply anymore.
Comment 12 Mason James 2011-10-08 11:15:07 UTC Comment hidden (obsolete)
Comment 13 Mason James 2011-10-08 12:02:58 UTC
(In reply to comment #11)
> Mason, could you sent an updated patch please, this one doesn't apply anymore.

yes, no problem

my new patch combines both of my old patches (4835, 4474), and is applied to
commit cfe8b6bd9a4f1921cb728d784083300344bcf2b1
Comment 14 Mason James 2011-10-08 12:04:46 UTC Comment hidden (obsolete)
Comment 15 Paul Poulain 2011-10-13 13:04:49 UTC
Mason, you've updated the status to "pushed for QA". Shouldn't it be "needs signoff" -or "signed-off"(needs QA) ?
Comment 16 Mason James 2011-10-13 17:25:39 UTC
(In reply to comment #15)
> Mason, you've updated the status to "pushed for QA". Shouldn't it be "needs
> signoff" -or "signed-off"(needs QA) ?

oops!  good spotting Paul :)

yep, i've corrected the status to 'signed-off'
Comment 17 Paul Poulain 2011-10-14 09:50:33 UTC
QA comments:

There are a few references to the coded_location_qualifier field:
installer/data/Pg/en/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 1, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'BKS', '', '', NULL),
installer/data/Pg/en/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 1, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'CF', '', '', NULL),
installer/data/Pg/en/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 1, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'SR', '', '', NULL),
installer/data/Pg/en/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 1, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'VR', '', '', NULL),
installer/data/Pg/en/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 1, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'AR', '', '', NULL),
installer/data/Pg/en/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 1, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'KT', '', '', NULL),
installer/data/Pg/en/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 1, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'IR', '', '', NULL),
installer/data/Pg/en/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 1, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'SER', '', '', NULL),
installer/data/Pg/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 1, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, '', '', '', NULL),
installer/data/mysql/es-ES/marcflavour/marc21/optional/marc21_fastadd_framework.sql:		('952','f','Coded location qualifier','Coded location qualifier',0,0,'items.coded_location_qualifier',10,'','','',NULL,0,'FA','',NULL,NULL),
installer/data/mysql/es-ES/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 0, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'BKS', '', '', NULL),
installer/data/mysql/es-ES/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 0, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'CF', '', '', NULL),
installer/data/mysql/es-ES/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 0, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'SR', '', '', NULL),
installer/data/mysql/es-ES/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 0, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'VR', '', '', NULL),
installer/data/mysql/es-ES/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 0, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'AR', '', '', NULL),
installer/data/mysql/es-ES/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 0, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'KT', '', '', NULL),
installer/data/mysql/es-ES/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 0, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'IR', '', '', NULL),
installer/data/mysql/es-ES/marcflavour/marc21/optional/marc21_simple_bib_frameworks.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 0, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, 'SER', '', '', NULL),
installer/data/mysql/es-ES/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql:		('952', 'f', 'Coded location qualifier', 'Coded location qualifier', 0, 0, 'items.coded_location_qualifier', 10, '', '', '', NULL, 0, '', '', '', NULL),

Could you remove them as well ?

The updatedatabase SQL says :
installer/data/mysql/updatedatabase.pl:    $dbh->do("UPDATE marc_subfield_structure SET kohafield = NULL WHERE tagfield = 952 AND tagsubfield = 'f' AND kohafield = 'items.coded_location_qualifier'");

That won't fix the bug with someone having moved this reference in another field. I think this can happend only in theory and wouldn't have marked failed QA just for this, but as you'll have to add some forgotten references, you can also update the SQL (or object/argue if you think i'm wrong)
Comment 18 Nuño López Ansótegui 2012-10-22 10:42:12 UTC
This bug is yet active, the solution would so easy as add the column 'items.coded_location_qualifier'.
Where is the problem? I don´t understand
Comment 19 Paul Poulain 2012-10-22 11:32:58 UTC
(In reply to comment #18)
> This bug is yet active, the solution would so easy as add the column
> 'items.coded_location_qualifier'.
> Where is the problem? I don´t understand

Nuno,

The problem is that I made some comments about mistakes in the patch, and No-one (hey, mason, are you here ?) took care of my comments.
Comment 20 Nuño López Ansótegui 2012-10-23 11:58:03 UTC Comment hidden (obsolete)
Comment 21 Nuño López Ansótegui 2012-10-23 12:13:35 UTC Comment hidden (obsolete)
Comment 22 Kyle M Hall 2012-11-02 13:13:49 UTC Comment hidden (obsolete)
Comment 23 Mason James 2012-11-09 02:02:37 UTC
(In reply to comment #22)
> Created attachment 13222 [details] [review]
> [SIGNED-OFF] Bug 6428 - refered column 'items.coded_location_qualifier' does
> not exist
> 
> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>

tested OK, passing QA... thanks for the patch Nuño! :)

$ koha-qa.pl -c 1 

testing 1 commit(s) (applied to 9ace63b 'Bug 9001: Remove Zebraqueue_daemon
')

 7bcc447 Bug 6428 - refered column 'items.coded_location_qualifier' does not exist
 - C4/Items.pm
 - installer/data/mysql/kohastructure.sql
 - installer/data/mysql/updatedatabase.pl

 * C4/Items.pm                                                              OK
 * installer/data/mysql/updatedatabase.pl                                   OK
Comment 24 Jared Camins-Esakov 2012-11-10 01:38:44 UTC
This patch has been pushed to master.
Comment 25 Katrin Fischer 2012-11-10 21:10:05 UTC
I think there is a bigger problem with this patch - only the table items is altered, but deleteditems is not. Removing 'pushed to master' - I think we need another patch here.
Comment 26 Chris Cormack 2012-11-11 02:34:12 UTC
I agree this needs to be reverted or fixed before the release,  with mismatched tables deleted items are not copied. This results in data loss,  updating to blocker.
Comment 27 Nuño López Ansótegui 2012-11-12 08:53:15 UTC Comment hidden (obsolete)
Comment 28 Nuño López Ansótegui 2012-11-12 09:05:32 UTC Comment hidden (obsolete)
Comment 29 Katrin Fischer 2012-11-12 10:08:59 UTC
Thank you for your follow-up! Can you please also it do updatedatabase as well?

As it's already pushed I am not sure if we can just change the original one or better do a new database update statement, maybe we can get some other opinion on this?
Comment 30 Nuño López Ansótegui 2012-11-12 10:53:57 UTC Comment hidden (obsolete)
Comment 31 Jared Camins-Esakov 2012-11-12 13:20:00 UTC
It must be a new database revision.
Comment 32 Paul Poulain 2012-11-12 13:48:01 UTC
The follow-up need signoff (I haven't tested it myself yet)

waiting for the follow-up to be pushed to master before pushing the main patch to 3.10.x branch
Comment 33 Owen Leonard 2012-11-12 16:54:01 UTC Comment hidden (obsolete)
Comment 34 Jonathan Druart 2012-11-13 09:50:53 UTC
QA Comments:
Same as previous patch.
Normally the new column has to be positionned in the table after another field in order to have the same structure as in kohastructure.pl
The first patch has been pushed like this so I supposed this one could be pushed like this too.
Marked as Passed QA.
Comment 35 Nuño López Ansótegui 2012-11-13 10:19:58 UTC Comment hidden (obsolete)
Comment 36 Jonathan Druart 2012-11-13 10:37:31 UTC
Created attachment 13420 [details] [review]
Bug 6428 [Follow-up] refered column 'items.coded_location_qualifier' does not exist

Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
Comment 37 Jared Camins-Esakov 2012-11-13 14:48:10 UTC
This patch has been pushed to master.
Comment 38 Paul Poulain 2012-11-16 17:24:48 UTC
Patch pushed to branch 3.10.x
Comment 39 Chris Cormack 2013-04-22 08:48:04 UTC
Released in 3.10.0
Comment 40 Marcel de Rooy 2023-12-06 13:37:20 UTC
I just stumble over this report anno 2023.

I noticed in my data (empty) occurrences of $f in more_subfields_xml.
And surprisingly, there is no mapping in my data from $f to items.coded_location_qualifier.

So this leaves a theoretical issue:
AFAICS the patches here did not check if there was a Koha to MARC mapping when adding the item field but assumed that it was there? Hence possible data in the xml field was lost when no mapping existed.
But yes, if you do not use the field, no problem :)