Bug 13523

Summary: AddBiblio fails on mysql with STRICT_TRANS_TABLES
Product: Koha Reporter: Benjamin Rokseth <benjamin.rokseth>
Component: CatalogingAssignee: Benjamin Rokseth <benjamin.rokseth>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: bob-ewart, chris, fridolin.somers, isabelaterra, jonathan.druart, koha, m.de.rooy, mtj, swarren, tomascohen
Version: unspecified   
Hardware: All   
OS: All   
See Also: http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5366
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:
Attachments: Bug 13523 - AddBiblio fails on mysql with STRICT_TRANS_TABLES
Bug 13523 - AddBiblio fails on mysql with STRICT_TRANS_TABLES
[PASSED QA] Bug 13523 - AddBiblio fails on mysql with STRICT_TRANS_TABLES
Bug 13523: DBIC updates

Description Benjamin Rokseth 2015-01-06 14:15:07 UTC 
Adding a biblio either via /svc endpoint or via Cataloguing interface fails on mysql versions > 5.3.21 because of a change in the default settings in GLOBAL.sql_mode to add STRICT_TRANS_TABLES.

when adding a biblio in Cataloguing or via /svc API: 
---
Software error:

Can't call method "fields" on an undefined value at /usr/share/koha/intranet/cgi-bin/cataloguing/additem.pl line 683.
---

Doing this manually in SQL results in:
---
ERROR 1364 (HY000): Field 'marcxml' doesn't have a default value
---

This is probably a change that will live (Mysql becomes stricter in v5.7) and is purposefully added to better handle syntax checks in the SQL.

bugs #10732, #5366 and #10215 all relate to the same but do not propose a fix.

This particular bug proposes to fix the particular issue with adding biblios, either by
 
1) changing the database schema to NOT set field `marcxml` to NOT NULL
   (field `marc` in the same table is NULLABLE)
2) changing the AddBiblio code in Biblio.pm so it doesn't require multiple 
   INSERT statements
Comment 1 koha 2015-01-06 20:22:03 UTC 
(In reply to Benjamin Rokseth from comment #0)
> on mysql versions > 5.3.21 

This should be 'on mysql versions > 5.6.21'. (not 5.3.21)

5.6.22 is the current stable version of mysql - released 2014-12-01: 

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-22.html
Comment 2 Chris Cormack 2015-01-06 20:30:00 UTC 
I think the changing the constraint is the best solution here.
Comment 3 Benjamin Rokseth 2015-01-07 11:19:30 UTC 
(In reply to Chris Cormack from comment #2)
> I think the changing the constraint is the best solution here.

If by this you mean removing the NOT NULL constraint on the marcxml field (as is the case with the marc field) we agree on the most reasonable solution.

Hope this does not affect any other part of Koha.

Will include a patch soon.
Comment 4 Benjamin Rokseth 2015-01-22 11:36:34 UTC Comment hidden (obsolete)
Comment 5 Isabela Terra 2015-01-28 11:37:48 UTC 
(In reply to Benjamin Rokseth from comment #4)
> Created attachment 35456 [details] [review] [review]
> Bug 13523 - AddBiblio fails on mysql with STRICT_TRANS_TABLES
> 
> This patch removes the NOT NULL restriction on biblioitems.marcxml and
> deletedbiblioitems.marcxml due to STRICT_TRANS_TABLES default in
> Mysql 5.6.22 and onwards.
> 
> Test:
> 
> 1. check mysql global settings:
>    mysql > SELECT @@GLOBAL.sql_mode;
>    verify that STRICT_TRANS_TABLES is not set
> 2. set STRICT_TRANS_TABLES (or upgrade mysql to > 5.6.21)
>    mysql > SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES';
> 3. try to add biblio in Cataloguing module
>    verify that it fails with a "software error"
> 4. apply patch
> 5. repeat 3 and verify that there is no "software error" any more

Hello,
I applied these changes but the error continues.
Is there anything else for me to do?
Comment 6 Isabela Terra 2015-01-29 13:34:13 UTC 
(In reply to Isabela Terra from comment #5)
> (In reply to Benjamin Rokseth from comment #4)
> > Created attachment 35456 [details] [review] [review] [review]
> > Bug 13523 - AddBiblio fails on mysql with STRICT_TRANS_TABLES
> > 
> > This patch removes the NOT NULL restriction on biblioitems.marcxml and
> > deletedbiblioitems.marcxml due to STRICT_TRANS_TABLES default in
> > Mysql 5.6.22 and onwards.
> > 
> > Test:
> > 
> > 1. check mysql global settings:
> >    mysql > SELECT @@GLOBAL.sql_mode;
> >    verify that STRICT_TRANS_TABLES is not set
> > 2. set STRICT_TRANS_TABLES (or upgrade mysql to > 5.6.21)
> >    mysql > SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES';
> > 3. try to add biblio in Cataloguing module
> >    verify that it fails with a "software error"
> > 4. apply patch
> > 5. repeat 3 and verify that there is no "software error" any more
> 
> Hello,
> I applied these changes but the error continues.
> Is there anything else for me to do?

Resolved! Thanks!
Comment 7 Benjamin Rokseth 2015-01-29 14:32:47 UTC 
yes, I believe I forgot to mention that updatedatabase.pl must be run if you already have a running koha instance
Comment 8 Benjamin Rokseth 2015-02-05 08:53:27 UTC 
Isabela, would you care to sign off if you tested and it worked according to the description?
Comment 9 Isabela Terra 2015-02-05 10:20:09 UTC 
(In reply to Benjamin Rokseth from comment #8)
> Isabela, would you care to sign off if you tested and it worked according to
> the description?

Yes, i can do this!
How?
Comment 10 Benjamin Rokseth 2015-02-05 12:01:38 UTC 
(In reply to Isabela Terra from comment #9)
> (In reply to Benjamin Rokseth from comment #8)
> > Isabela, would you care to sign off if you tested and it worked according to
> > the description?
> 
> Yes, i can do this!
> How?

You can do it from within bugzilla.
Describe in short what you did to confirm it worked.
Then change status to Signed Off
Comment 11 Jonathan Druart 2015-02-16 12:00:55 UTC 
*** Bug 13711 has been marked as a duplicate of this bug. ***
Comment 12 Jonathan Druart 2015-02-16 12:14:19 UTC Comment hidden (obsolete)
Comment 13 Jonathan Druart 2015-02-16 12:49:50 UTC 
Same problem could happen on the import_records table, see bug 5366.
Comment 14 Bob Ewart 2015-02-16 18:17:53 UTC 
auth_header, import_items and import_records all have marcxml set as NOT NULL, marcxml_old in import_records is also.

New from Z39.50/SRU fails ever time on marcxml_old does not have a default value.

Removing the restriction make it work.

I'm using Mariadb 10.0.13 on openSUSE 13.2
Comment 15 Kyle M Hall (khall) 2015-02-20 14:22:21 UTC 
Created attachment 36087 [details] [review]
[PASSED QA] Bug 13523 - AddBiblio fails on mysql with STRICT_TRANS_TABLES

This patch removes the NOT NULL restriction on biblioitems.marcxml and
deletedbiblioitems.marcxml due to STRICT_TRANS_TABLES default in
Mysql 5.6.22 and onwards.

Test:

1. check mysql global settings:
   mysql > SELECT @@GLOBAL.sql_mode;
   verify that STRICT_TRANS_TABLES is not set
2. set STRICT_TRANS_TABLES (or upgrade mysql to > 5.6.21)
   mysql > SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES';
3. try to add biblio in Cataloguing module
   verify that it fails with a "software error"
4. apply patch
5. repeat 3 and verify that there is no "software error" any more

Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
I confirm with the problem with mariadb and STRICT_TRANS_TABLES enabled.
On adding a biblio (C4::Biblio::AddBiblio), biblioitems is populate in
_koha_add_biblioitem but marxcml is not generated yet. It's in
ModBiblioMarc, few lines later.
The NOT NULL condition is not valid here.

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 16 Tomás Cohen Arazi (tcohen) 2015-02-24 17:21:01 UTC 
Created attachment 36146 [details] [review]
Bug 13523: DBIC updates

Signed-off-by: Tomas Cohen Arazi <tomascohen@gmail.com>
Comment 17 Tomás Cohen Arazi (tcohen) 2015-02-24 18:14:54 UTC 
Patch pushed to master.

Thanks Benjamin!
Comment 18 Chris Cormack 2015-03-10 19:13:06 UTC 
Pushed to 3.18.x will be in 3.18.5
Comment 19 Mason James 2015-03-26 01:53:28 UTC 
Pushed to 3.16.x, will be in 3.16.9
Comment 20 Stephen Warren 2015-03-31 15:15:52 UTC 
I have 3.18.05.1 installed from your dpkg repo, so I think I have this fix:

> # dpkg -l koha-common
> ii  koha-common  3.18.05.1  all  integrated (physical) library management system

I'm still seeing this error when editing items (as reported by my user; I can get more details exactly which edit page she's using if needed).

I checked my database and as far as I can tell, the DB schema patch has been applied:

> mysql> show columns from biblioitems;
> | Field                 | Type         | Null | Key | Default           | Extra                       |
> ...
> | marcxml               | longtext     | YES  |     | NULL              |                             |

Was the "edit" use-case not covered by the patch, or is there something else I should look at to track down the issue?
Comment 21 Benjamin Rokseth 2015-04-08 12:34:55 UTC 
Stephen, I was not able to reproduce your issue. Looking at your schema the marcxml row looks ok. Are you able to get a detailed description of the issue?
Comment 22 Fridolin Somers 2015-04-22 07:15:30 UTC 
Pushed to 3.14, will be in 3.14.15.
Comment 23 Stephen Warren 2015-05-12 04:29:42 UTC 
The issue I mentioned in comment 20 here was caused by user error. For the record, when I modified my koha-conf.xml to add some lines to fix bug 13396, I didn't realize that the __xxx__ values needed to be replaced with their actual values. It seems there are some fields that contain __xxx__ after koha-create and some that don't... After fixing that and rebuilding the search index, everything seems to be working again.