Koha How-To

Koha 17.05 Upgrade Issues with the biblioitems.marcxml table

Hello Partners!

We wanted to let you know about a potential issue with the upcoming Koha 17.05 update. A database change was made in Koha 17.05 that breaks reports using the biblioitems.marcxml field.

First of all, we want you to know that we’ve got you covered, and we are working behind the scenes to get these reports fixed for all partners who are affected by this issue.

In this post we’ll tell you more about what caused this issue, show you how you can fix the issue yourself if you would like to, and let you know what we’re doing to fix this for you.

Converting queries with biblioitems.marcxml to biblio_metadata.metadata.

In Koha 17.05, the Koha community decided to move the marcxml field out of the biblioitems table, into a table called biblio_metadata. (For more details, see Bug 17196: Move marcxml out of the biblioitems table.) This was done for good technical reasons, but unfortunately, it breaks a lot of reports that pull data from fields in the marc record that are not mapped to the Koha database. In order to make the transition somewhat easier, the community created Bug 17898: Add a way to automatically convert SQL reports. As you can see in the image below, this patch adds an Update SQL button to Home > Reports > Guided Reports Wizard > Saved Reports for each affected query.

Clicking the Update SQL button brings up a window which will allow the user to inspect the changes to the query, and accept the changes by clicking update if they’re acceptable.The Update SQL button will replace every occurrence of biblioitems with biblio_metadata and every marcxml with metadata. This will work perfectly as long as marcxml is the only field used from the biblioitems table.

Update SQL works with this query:

SELECT
    biblio.title,
    ExtractValue(biblioitems.marcxml,'//controlfield[@tag="001"]') AS "field_001"
FROM
    biblio
    LEFT JOIN biblioitems USING (biblionumber)

will be converted to

SELECT
    biblio.title,    
    ExtractValue(biblio_metadata.metadata,'//controlfield[@tag="001"]') AS "field_001"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)

Update SQL won’t work here:

Adding an extra field, like ISBN spoils the party.

SELECT
    biblio.title,
    biblioitems.isbn,
    ExtractValue(biblioitems.marcxml,'//controlfield[@tag="001"]') AS "field_001",
FROM
    biblio
    LEFT JOIN biblioitems USING (biblionumber)

 

… because the metadata table does not contain an ISBN column, this query will fail:

SELECT
    biblio.title,
    metadata.isbn,
    ExtractValue(biblio_metadata.metadata,'//controlfield[@tag="001"]') AS "field_001",
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)

So, these queries will need to be adjusted by hand.

Editing the query by hand

Any biblioitems columns other than marcxml or biblionumber must be called from the biblioitems table i.e. biblioitems.isbn stays biblioitems.isbn. Both the biblio_metadata and biblioitems need to be joined in order for the columns to be available. You should be fairly safe copying the biblioitems join, then changing biblioitems to biblio_metadata.

LEFT JOIN biblioitems USING (biblionumber)

Becomes…

    LEFT JOIN biblioitems USING (biblionumber)
    LEFT JOIN biblio_metadata USING (biblionumber)

The edited query looks like this:

SELECT
    biblio.title,
    biblioitems.isbn,
    ExtractValue(biblio_metadata.metadata,'//controlfield[@tag="001"]') AS "field_001",
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)
    LEFT JOIN biblioitems USING (biblionumber)


Our Plan for Upgrading Reports

If you are affected by this issue, on the day of the upgrade we will send you an email containing the following information:
  1. Which queries can be safely fixed by clicking the UPGRADE SQL button.
  2. Which queries we will fix by hand on the day of the upgrade — essentially, any queries that have run in the last 6 weeks.
  3. Which queries remain that need to be fixed by hand. We will fix these within a month of your upgrade.

If you need any reports fixed sooner than scheduled, just open a ticket and we will fix that report for you right away.

Please do not hesitate to contact us with any questions or concerns you have you about this process. You can always reach us at (888) 900-8944, chat with us through our website, contact us via IRC, or open a ticket.
Thanks for your patience as we work to resolve this for you as quickly as we can!