Bug 37416

Reports to date (these may be isolated and not necessarily related to the holdings table change):

- Chat:
   . 404 error (probably something different): https://chat.koha-community.org/koha-community/pl/xp3n9xeeh3gqp855k7cwqzapqc
   . 500 error (item dates 0000-00-00 and how to fix): https://chat.koha-community.org/koha-community/pl/in85iz8y6pbi3r9j6jpdwm6hra

- Mailing list:
  . https://lists.katipo.co.nz/pipermail/koha/2024-July/060815.html

Some know causes of the 500 error for the record holdings table:

1. Types of item data issues that cause this:
   1.1 home or holding library information missing (both are needed)
   1.2 values for dropdown fields don't match what is in the authorized values
   1.3 item date fields that have 0000-00-00 values (such as date acquired)

Some things to work out:

1. How to identify the cause of the 500 error:
   1.1 Check the Koha logs on the server (which ones, where?)
   1.2 Use the web browser web developer tools to see if there are any errors in the console

2. SQL queries for reports to identify item data issues:
   2.1 Items not having a home library and current library (both are required)
   2.2 Item date fields that have 0000-00-00 - these need to be an actual date or NULL (MARC21 date fields are defined in 952, UNIMARC date fields are in 995).
   2.3 Invalid entries for any of the item fields that use authorized values - the values need to match the authorized values lists.

3. Check for other data issues:
   3.1 Check if bibliographic frameworks have any errors: Administration > Catalog > MARC Bibliographic framework test2.
   3.2 Command line scripts to help identify any issues:
       - misc/maintenance/audit_database.pl - checks to see if there are any database structure issues (added in 23.11)
       - misc/maintenance/search_for_data_inconsistencies.pl- checks for some data inconsistencies (but doesn't fix them), including:
         . items that have undefined home libraries (homebranch) and holding library (holdingbranch)
         . authorities with undefined authority types (authtypecodes)
         . issues with item types (item type codes must match with what is defined in Administration > Basic parameters > Item types; also includes issues when the 'item-level_itypes' system preference is set to 'specific item')
        . invalid MARCXML in bibliographic records (this now shows on record details pages)
        . patrons with invalid category types for their age (not relevant to item data)
        . bibliographic records without a title
        . patron guarantee/guarantor issues

SQL query to identify dates with 0000-00-00
===========================================

Date fields in the items table: dateaccessioned, replacementpricedate, datelastborrowed, datelastseen, damaged_on, itemlost_on, withdrawn_on, deleted_on, onloan

Not sure about: timestamp

Identify any date fields with 000-00-00 values:
-----------------------------------------------

select dateaccessioned, replacementpricedate, datelastborrowed, datelastseen, damaged_on, itemlost_on, withdrawn_on, deleted_on, onloan 
FROM items
WHERE 
dateaccessioned LIKE '0000-00-00%' OR 
replacementpricedate LIKE '0000-00-00%' OR 
datelastborrowed LIKE '0000-00-00%' OR 
datelastseen LIKE '0000-00-00%' OR 
damaged_on LIKE '0000-00-00%' OR 
itemlost_on LIKE '0000-00-00%' OR 
withdrawn_on LIKE '0000-00-00%' OR 
deleted_on LIKE '0000-00-00%' OR 
onloan LIKE '0000-00-00%'

SQL to fix date issues (example):
---------------------------------

UPDATE items
SET replacementpricedate = null 
WHERE replacementpricedate = '0000-00-00'


Source - Chats: 
- https://chat.koha-community.org/koha-community/pl/wjyuc67s3jgsf88kze8nzgiqne
- https://chat.koha-community.org/koha-community/pl/d3rnttkbz3b1ijcb9s8fgp58xo

A related bug: bug 37375 - Holdings table not loading if MARC framework is missing certain 952 subfields.

If 952 is missing subfields 8, a, b, c, or y, the holdings table is no longer loading but stuck at "Processing...".

This may help explain some of the issues people are having.

Weird.

After upgrading Koha to version 24.05.02.000, the 404 error on items/patrons/library ... seemed to have disappeared.

Then it appeared again!

Then disappeared again!

My Koha instance runs on my personal laptop, which of course I switch off and on from time to time. Could this intermittent behaviour have to do with a cache refresh, each time the various services associated with Koha are reactivated?

(In reply to giuseppe.angilella from comment #6)
> ...
> My Koha instance runs on my personal laptop, which of course I switch off
> and on from time to time. Could this intermittent behaviour have to do with
> a cache refresh, each time the various services associated with Koha are
> reactivated?

Sorry, I have no idea 8-(

Adding Bug 37005 (Holdings table will not load when noItemTypeImages is set to 'Don't show') as a related bug, although this was fixed in Koha 24.05.02.

Our dev site was upgraded to 24.05 a little over a month ago.  Since then we cannot get any Holdings to load.  We get the 500 error - but none of the 3 things listed as possible issues apply.  I'm also watching bugs 37375 and 37544 as they are similar discussions, but so far nothing seems to be the answer.  Any other ideas?

(In reply to Angela Berrett from comment #9)
> Our dev site was upgraded to 24.05 a little over a month ago.  Since then we
> cannot get any Holdings to load.  We get the 500 error - but none of the 3
> things listed as possible issues apply.  I'm also watching bugs 37375 and
> 37544 as they are similar discussions, but so far nothing seems to be the
> answer.  Any other ideas?

Check the logs for an error message when you try loading a detail page with a broken holdings table: tail -f /var/log/koha/instance_name/*.log

Also check the developer tools in the browser for a specific error. Both can help us identify the issue.

I don't know how to find those, can you walk me through it?

Hm, for the first you'd need access to the server. Are you self hosted or have you someone looking after it maybe?
For the second you can press F12 in your browser to open the developer tools and an error might show on loading the page.

We work with ByWater Solutions - they maintain our server I believe.  I am set as a Superlibrarian so I should have full permissions.  I've tried F12 and nothing happens. I've talked with them about this issue but was hoping I could get more info on my own as well.  Maybe it's best to just wait for them to look into it.

If ByWater Solutions do find the cause of your issues, it would be great if you could share what they find. This is a frustrating issue for those affected by it. (I'm reasonably convinced at the moment that it is related to either data or framework issues.)

To access your browser developer tools (either Google Chrome/Chromium or Firefox) (if you are using Edge or Safari, I'm not sure):
1. Firefox: 
   . Menu icon (the hamburger menu to the far right) > More tools > Web developer tools)
   . Select the Console tab, hard refresh a record page in the staff interface.
  . Result: There should be some output in the console tab
2. Similar for Google Chrome/Chromium:
   . Menu icon (three horizontal dots on the far right) > More tools > Developer tools

I've found Firefox provides more information on the error - Google Chrome is not so specific.

(In reply to Angela Berrett from comment #13)
> We work with ByWater Solutions - they maintain our server I believe.  I am
> set as a Superlibrarian so I should have full permissions.  I've tried F12
> and nothing happens. I've talked with them about this issue but was hoping I
> could get more info on my own as well.  Maybe it's best to just wait for
> them to look into it.

The error in this case was a 500 when loading the api (these won't show in the console). Checking the backend it was:
unhandled exception (Mojo::Exception)<<Can't call method "notforloan" on an undefined value at /usr/share/koha/lib/Koha/Item.pm line 1740.>>

The preference "item-level_itypes" was set to "bibliographic record", but the type wasn't set on the biblio level. Switching that allowed the table to load.

(In reply to Nick Clemens (kidclamp) from comment #15)
> The error in this case was a 500 when loading the api (these won't show in
> the console). Checking the backend it was:
> unhandled exception (Mojo::Exception)<<Can't call method "notforloan" on an
> undefined value at /usr/share/koha/lib/Koha/Item.pm line 1740.>>
> 
> The preference "item-level_itypes" was set to "bibliographic record", but
> the type wasn't set on the biblio level. Switching that allowed the table to
> load.

One step closer!  We no longer get the 500 error!  But it is still stuck on Processing and doesn't load.  I'm still very happy to see the errors gone. 
I will make sure our production system has the same setting.  Thanks!

(In reply to Angela Berrett from comment #16)
> (In reply to Nick Clemens (kidclamp) from comment #15)
> > The error in this case was a 500 when loading the api (these won't show in
> > the console). Checking the backend it was:
> > unhandled exception (Mojo::Exception)<<Can't call method "notforloan" on an
> > undefined value at /usr/share/koha/lib/Koha/Item.pm line 1740.>>
> > 
> > The preference "item-level_itypes" was set to "bibliographic record", but
> > the type wasn't set on the biblio level. Switching that allowed the table to
> > load.
> 
> One step closer!  We no longer get the 500 error!  But it is still stuck on
> Processing and doesn't load.  I'm still very happy to see the errors gone. 
> I will make sure our production system has the same setting.  Thanks!

Hi Angela, can you respond on our internal ticket with some examples? I can help debug and then share the resolutions here - Nick

(In reply to Nick Clemens (kidclamp) from comment #17)
> (In reply to Angela Berrett from comment #16)
> > (In reply to Nick Clemens (kidclamp) from comment #15)
> > > The error in this case was a 500 when loading the api (these won't show in
> > > the console). Checking the backend it was:
> > > unhandled exception (Mojo::Exception)<<Can't call method "notforloan" on an
> > > undefined value at /usr/share/koha/lib/Koha/Item.pm line 1740.>>
> > > 
> > > The preference "item-level_itypes" was set to "bibliographic record", but
> > > the type wasn't set on the biblio level. Switching that allowed the table to
> > > load.
> > 
> > One step closer!  We no longer get the 500 error!  But it is still stuck on
> > Processing and doesn't load.  I'm still very happy to see the errors gone. 
> > I will make sure our production system has the same setting.  Thanks!
> 
> Hi Angela, can you respond on our internal ticket with some examples? I can
> help debug and then share the resolutions here - Nick

We found a second issue was caused by an outdated plugin, updating it allowed the table to load correctly

Thanks Angela and Nick! (For reporting, finding the cause, and letting us know!)

It is great to have some more things that libraries can look for to try and resolve holding table loading issues and errors.

Check the 'item-level_itypes' system preference setting
=======================================================

If set to 'specific item', then all items must have a valid 952$y value (item add form for MARC21 installations: y - Koha item type).

If set to 'bibliographic', then all bibliographic records must have a valid 942$c value (for MARC21 installations, tab 9, 942$c - Koha item type).

(Or equivalent UNIMARC tags and subfields.)

The reason records and items may not have these values could include: 
. the frameworks were edited, and these tags and subfields were not made mandatory (or removed)
. previous data imports were missing these values (I think the import process it is a bit more rigorous now)
. previous cataloging (adding new records and items), where these fields were not mandatory

The misc/maintenance/search_for_data_inconsistencies.pl script can identify missing item types.

A report to help identify missing values (from the SQL Library - Items missing an item type or record missing an item type), although the search_for_data_inconsitencies script is probably better:

SELECT title, biblionumber, barcode, itemcallnumber, itemnumber
FROM items
JOIN biblio USING (biblionumber)
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE itype IS NULL
OR ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="c"]') = ''

Review any plugins that you have, or temporarily disable
========================================================

Plugins (Koha administration > Plugins > Manage plugins) may interfere with the display of items in the holdings table.

Some options include:
- Temporarily disabling suspect plugins to help identify if any are interfering
- Reviewing and updating plugins that interact with the holdings table

Another place to look for the cause of issues (from bug 37544, comments 36-39) - item location codes or collection codes are not linked to an authorized value category.

If the location code or collection code are not mapped to an authorized value category (in MARC21 these are 952$c for the location code, and 952$8 for the collection code), this will generate a JavaScript error in your browser web developer tools console. 
- 952$c: "...row._strings.location is undefined..."
- 952$8: "...row._strings.collection_code is undefined..."

To check, look at the tag subfields for your frameworks in the 'Authorized value' field for the "Other options (choose one") section:

1. Go to Koha administration > Catalog > choose a framework > Actions > MARC structure
2. Search for tag: 952
3. Actions > Edit subfields
4. Select the subfield ($c or $8 for MARC21)
5. In the "Other options (choose one)" section, check that the 'Authorized value' field is linked to an authorized value category:
   - LOC for the location code (952$c)
   - CCODE for the collection code (952$8)

For UNIMARC, look at the equivalent fields.

*** Bug 37811 has been marked as a duplicate of this bug. ***

   (In reply to David Nind from comment #20)
> Another place to look for the cause of issues (from bug 37544, comments
> 36-39) - item location codes or collection codes are not linked to an
> authorized value category.
> 
> If the location code or collection code are not mapped to an authorized
> value category (in MARC21 these are 952$c for the location code, and 952$8
> for the collection code), this will generate a JavaScript error in your
> browser web developer tools console. 
> - 952$c: "...row._strings.location is undefined..."
> - 952$8: "...row._strings.collection_code is undefined..."
> 
> To check, look at the tag subfields for your frameworks in the 'Authorized
> value' field for the "Other options (choose one") section:
> 
> 1. Go to Koha administration > Catalog > choose a framework > Actions > MARC
> structure
> 2. Search for tag: 952
> 3. Actions > Edit subfields
> 4. Select the subfield ($c or $8 for MARC21)
> 5. In the "Other options (choose one)" section, check that the 'Authorized
> value' field is linked to an authorized value category:
>    - LOC for the location code (952$c)
>    - CCODE for the collection code (952$8)
> 
> For UNIMARC, look at the equivalent fields.

This can also happen with field 952$y itemtype. Which is a bummer since we can't now use our value builder plugin to automatically generate itemtype for items. I think we need a way to priorities use of value builders in item creation even if authorized value also set to subfield.

It should be fixed with bug 37375. It includes the fix for item types.

(In reply to Jonathan Druart from comment #23)
> It should be fixed with bug 37375. It includes the fix for item types.

Thank you for pointing this out. This seems to solve our problem.