[Koha-devel] Please assist: auth values

[Paul A]

Thank you both, Joy and Robin S., for your replies.  I had started 
preparing the explanation below, when I found the solution - 'fl2_rare' 
must be some sort of a remnant from "pre-production" days, whereas what we 
deleted (and now restored) is 'fl2_rare ' -- note the space at the end.

Reading SSH screens and Firefox browsers did not make it obvious, but in 
hindsight I've learned a new lesson in querying MySQL; with and without the 
spaces gives identical results !!! :

mysql> SELECT location, COUNT(location) FROM items WHERE location = '2fl_rare';
+-----------+-----------------+
| location  | COUNT(location) |
+-----------+-----------------+
| 2fl_rare  |             920 |
+-----------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT location, COUNT(location) FROM items WHERE location = 
'2fl_rare ';
+-----------+-----------------+
| location  | COUNT(location) |
+-----------+-----------------+
| 2fl_rare  |             920 |
+-----------+-----------------+
1 row in set (0.00 sec)

Six hours scratching my head, but we're back in business.

The only outstanding query I might have is that a search for 
'mc-loc=2fl_rare' (with and without trailing space) finds 628 results -- 
but where is 'mc-loc' finding the 628 ???  And why only 628 of the 920 ???

As an aside, there seems to be some dev.mysql.com documentation on the 
treatment of trailing spaces in CHAR and VARCHAR types. I think (but could 
be wrong) that some Koha functions strip trailing spaces. Could this 
possibly be a point to consider with auth types?

Again with my sincere thanks -- Paul

Written earlier:
This reply covers points you both raised

At 05:33 PM 2/19/2014 -0600, Joy Nelson wrote:
>Golly Paul!  When you say that the volunteer deleted a loc value of
>rare book room, are you referring to the authorised value of rare book
>room?  If so, then you should be able to simply re-add a location of
>rare book room to the authorised value list and be ok.

Yes, exactly, but an sql query [SELECT * FROM authorised_values WHERE 
category='loc'] still finds the auth value:

id      category        authorized_value        lib     lib_opac
1834    loc     fl2_rare        2nd floor rare  2nd floor rare

so, if I try and "re-add" I get: "Could not add value "fl2_rare" for 
category "loc" - value already present." But in any case, the drop-down box 
for cataloguers editing "items" has definitely lost this value (but id=1833 
and 1835 are both still there. See 
<<http://navalmarinearchive.com/ims/koha_staff_edititem.jpg>http://navalmarinearchive.com/ims/koha_staff_edititem.jpg> 
-- nothing between "2nd floor" and "2nd floor reference".)

>When an authorised value is deleted, it does not delete the data in the items
>table (items.location)

This is what I expected:

mysql> SELECT location, COUNT(location) FROM items WHERE location = '2fl_rare';
+-----------+-----------------+
| location  | COUNT(location) |
+-----------+-----------------+
| 2fl_rare  |             920 |
+-----------+-----------------+
1 row in set (0.00 sec)

which seems about right -- but they're invisible in the OPAC. E.G. 
<http://opac.navalmarinearchive.com/cgi-bin/koha/opac-detail.pl?biblionumber=427>, 
but items.location is definitely correct, please see 
<http://navalmarinearchive.com/ims/koha_staff_biblio_427.txt> ( SELECT * 
FROM items WHERE biblionumber=427 --- it's too long to paste here.)

At worst, if I can restore the auth value to the drop down box, we could go 
through all 920 items and edit them by hand.








>I'm not sure what you mean when you say "so I can't "re-enter" the
>auth value which is no longer available for admin editing"
>This is not been my experience with the authorised_values list.
>
>Are you finding data on a query like this?
>  select barcode from items where location =<enter the code you used
>for rarebooks>
>or
>  select distinct(location) from items    -which will show you all the
>location values in use in the database.
>
>-joy
>
>On Wed, Feb 19, 2014 at 5:21 PM, Paul A <paul.a at navalmarinearchive.com> wrote:
> > Please can one of you assist?
> >
> > We are in the middle of shelving several hundred boxes of books, and seven
> > volunteer cataloguers have spent the day editing items to change the auth
> > value "loc" from "box_xyz" to the proper shelf/stack.
> >
> > At the end of the day, an enthusiastic volunteer instead of deleting an
> > empty box from the "loc" values, accidentally deleted our "rare book room"
> > (my fault, I gave him superlibrarian credentials instead of doing the job
> > myself.)
> >
> > A search still finds: "628 result(s) found for 'mc-loc=2fl_rare' in NMA
> > Catalog" but this is (I think) coming from somewhere other than
> > items.location.
> >
> > An sql query [SELECT * FROM authorised_values WHERE category='loc'] still
> > finds the auth value: ...
> > 1834 loc fl2_rare 2nd floor rare 2nd floor rare
> > ...
> > so I can't "re-enter" the auth value which is no longer available for admin
> > editing; so "editing" these 628 items is impossible. The OPAC is no longer
> > showing the location at all.
> >
> > The "catastrophe level" is that even if I revert to last nights backup, the
> > volunteers tell me they have no way to pull them all (about 3,000 
> books) out
> > and start over again.
> >
> > Can anyone, pretty please, suggest a course of action?
> >
> > Many thanks -- Paul
> >
> >
> >
> > _______________________________________________
> > Koha-devel mailing list
> > Koha-devel at lists.koha-community.org
> > http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
> > website : http://www.koha-community.org/
> > git : http://git.koha-community.org/
> > bugs : http://bugs.koha-community.org/
>
>
>
>--
>Joy Nelson
>Director of Migrations
>
>ByWater Solutions
>Support and Consulting for Open Source Software
>Office: Fort Worth, TX
>Phone/Fax (888)900-8944
>What is Koha?

---
Maritime heritage and history, preservation and conservation,
research and education through the written word and the arts.
<http://NavalMarineArchive.com> and <http://UltraMarine.ca>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20140220/7428ba3d/attachment.html>