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
(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
I think the changing the constraint is the best solution here.
(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.
Created attachment 35456 [details] [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
(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?
(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!
yes, I believe I forgot to mention that updatedatabase.pl must be run if you already have a running koha instance
Isabela, would you care to sign off if you tested and it worked according to the description?
(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?
(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
*** Bug 13711 has been marked as a duplicate of this bug. ***
Created attachment 35932 [details] [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 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.
Same problem could happen on the import_records table, see bug 5366.
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
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>
Created attachment 36146 [details] [review] Bug 13523: DBIC updates Signed-off-by: Tomas Cohen Arazi <tomascohen@gmail.com>
Patch pushed to master. Thanks Benjamin!
Pushed to 3.18.x will be in 3.18.5
Pushed to 3.16.x, will be in 3.16.9
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?
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?
Pushed to 3.14, will be in 3.14.15.
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.