Bug 22919

Summary: Authorities MARC Structure not inserted with SQL strict modes
Product: Koha Reporter: Nabila <lovenabila>
Component: MARC Authority data supportAssignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: normal    
Priority: P3 CC: ashwinloyal, cnighswonger, fridolin.somers, jonathan.druart, katrin.fischer, lovenabila, lucas, nick, sandboxes
Version: Main   
Hardware: PC   
OS: Linux   
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
19.11.00,19.05.01
Bug Depends on:    
Bug Blocks: 17258    
Attachments: Screen shown when Adding new Authority
Bug 22919: Fix insert of authorities_normal_marc21.sql with strict SQL modes
Bug 22919: Fix insert of authorities_normal_marc21.sql with strict SQL modes
Bug 22919: Fix insert of authorities_normal_marc21.sql with strict SQL modes

Description Nabila 2019-05-16 05:35:33 UTC
Created attachment 89813 [details]
Screen shown when Adding new Authority

I am logged in as SuperLibrarian with all rights. When I Select  Home - Authorities - New Authority - MARC Structure : It does not load the MARC form at all and the screen is blank but will show Save, Z39.50 Search, Cancel button.

I tried the following:
1) Tried selecting different MARC Template (Chronological Term, Default, Personal Name, etc.)

2) If I click Save button (even the form is not showing) then it will save the record as blank (not showing any field at this point also; only blank screen saying Authority #1 - Koha will also allow me to Download Record as MARCXML - When I open MARCXML it will show me the default tags
----------------
MARCXML File

<record xsi:schemaLocation="http://www.loc.gov/MARC21/slim http://www.loc.gov/standards/marcxml/schema/MARC21slim.xsd"><leader>00190    a2200097   4500</leader><controlfield tag="001">1</controlfield><controlfield tag="003">OSt</controlfield><controlfield tag="005">20190516105224.0</controlfield><controlfield tag="008">190516|| aca||aabn           | a|a     d</controlfield><datafield tag="040" ind1=" " ind2=" "><subfield code="a">OSt</subfield><subfield code="c">OSt</subfield></datafield><datafield tag="942" ind1=" " ind2=" "><subfield code="a">PERSO_NAME</subfield></datafield></record>
----------------------
3) From created report If I run report listing all authorities, then it will show me records but without any main_heading.

4) I can edit the MARC Structure from Koha Administration - Authority Type
5) I tried by creating new Authority Template
6) I have multiple instances created and it works fine in all of it.
Comment 1 Nick Clemens 2019-05-22 14:28:45 UTC
I don't recreate this in 18.11 - can you check for any errors in the logs or the JS console? It may be a framework issue or something else
Comment 2 Nabila 2019-05-28 04:40:43 UTC
I created new instance with different port to run and see if there is any error while installing Web Setup.

The following error was found while doing web setup under mandatory defaults:

authorities_normal_marc21.sql

[Tue May 28 09:52:16 2019] install.pl: DBD::mysql::st execute failed: Incorrect integer value: '' for column 'linkid' at row 148 at /usr/share/perl5/DBIx/RunSQL.pm line 273
Comment 3 Katrin Fischer 2019-05-28 05:04:55 UTC
(In reply to Nabila from comment #2)
> I created new instance with different port to run and see if there is any
> error while installing Web Setup.
> 
> The following error was found while doing web setup under mandatory defaults:
> 
> authorities_normal_marc21.sql
> 
> [Tue May 28 09:52:16 2019] install.pl: DBD::mysql::st execute failed:
> Incorrect integer value: '' for column 'linkid' at row 148 at
> /usr/share/perl5/DBIx/RunSQL.pm line 273

Hi Nabila, thank you, this is very helpful. Did you pick English or another language? It might be related to your DBMS having a strict configuration: https://wiki.koha-community.org/wiki/DBMS_configuration

Updating severity - if this blocks installation, it's at least normal.
Comment 4 Nabila 2019-05-28 06:36:43 UTC
(In reply to Katrin Fischer from comment #3)
> (In reply to Nabila from comment #2)
> > I created new instance with different port to run and see if there is any
> > error while installing Web Setup.
> > 
> > The following error was found while doing web setup under mandatory defaults:
> > 
> > authorities_normal_marc21.sql
> > 
> > [Tue May 28 09:52:16 2019] install.pl: DBD::mysql::st execute failed:
> > Incorrect integer value: '' for column 'linkid' at row 148 at
> > /usr/share/perl5/DBIx/RunSQL.pm line 273
> 
> Hi Nabila, thank you, this is very helpful. Did you pick English or another
> language? It might be related to your DBMS having a strict configuration:
> https://wiki.koha-community.org/wiki/DBMS_configuration
> 
> Updating severity - if this blocks installation, it's at least normal.

Selected Language is English.

Weird thing is, that I have till now 4 separate port running 4 separate Koha setup. 1st one that I created is working properly for Authorities. Problem started with the rest.
Comment 5 Jonathan Druart 2019-05-28 23:16:00 UTC
Did you set strict_sql_modes in your koha-conf.xml file?
Comment 6 Jonathan Druart 2019-05-28 23:17:18 UTC
What is the exact version of Koha and MySQL/MariaDB are you using?
Comment 7 Nabila 2019-05-30 03:33:34 UTC
(In reply to Jonathan Druart from comment #6)
> What is the exact version of Koha and MySQL/MariaDB are you using?

Koha = 	18.11.05.000
Perl = 	5.022001
MySQL = 5.7.26

I added the STRICT_TRANS_TABLES mode in /etc/mysql/my.cnf file and /etc/mysql/mysql.conf.d/mysqld.conf
Comment 8 Nabila 2019-05-30 04:08:24 UTC
UPDATE: after setting strict_trans_table(In reply to Jonathan Druart from comment #5)
> Did you set strict_sql_modes in your koha-conf.xml file?

How exactly should I do it? in <config> section?
Comment 9 Nabila 2019-05-30 04:09:42 UTC
(In reply to Jonathan Druart from comment #5)
> Did you set strict_sql_modes in your koha-conf.xml file?

How should I do it? in <config> section?
Comment 10 Nabila 2019-05-30 04:17:36 UTC
(In reply to Jonathan Druart from comment #5)
> Did you set strict_sql_modes in your koha-conf.xml file?

How should I do it? in <config> section?
Comment 11 Nabila 2019-05-30 04:19:11 UTC
UPDATE: i changed the sql_mode as required in my.cnf files but the problem persists.
Comment 12 Jonathan Druart 2019-05-30 14:39:41 UTC
Created attachment 90203 [details] [review]
Bug 22919: Fix insert of authorities_normal_marc21.sql with strict SQL modes

If strict SQL modes are set, the insert of authorities_normal_marc21.sql
will fail.

Test plan:
DELETE FROM auth_subfield_structure;
DELETE FROM auth_tag_structure;
DELETE FROM auth_types;
SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
source /home/vagrant/kohaclone/installer/data/mysql/en/marcflavour/marc21/mandatory/authorities_normal_marc21.sql

You should not see any errors.
Without this patch you see:
ERROR 1366 (22007) at line 280 in file:
'/home/vagrant/kohaclone/installer/data/mysql/en/marcflavour/marc21/mandatory/authorities_normal_marc21.sql':
Incorrect integer value: ' for column 'linkid' at row 148
'
Comment 13 Jonathan Druart 2019-05-30 14:42:36 UTC
Nabila, you should *not* have the SQL strict modes turned on in production, so you should not have the strict_sql_modes config entry in your koha-conf.xml

Koha code is not ready yet for that, and we are forcing the non-strict modes when we established the connnection to the DB.
I do not know how you face this issue if you did not explicitly set it.

However it is a bug we must fix, here is a patch.
Comment 14 Nabila 2019-05-31 06:13:24 UTC
(In reply to Jonathan Druart from comment #13)
> Nabila, you should *not* have the SQL strict modes turned on in production,
> so you should not have the strict_sql_modes config entry in your
> koha-conf.xml
> 
> Koha code is not ready yet for that, and we are forcing the non-strict modes
> when we established the connnection to the DB.
> I do not know how you face this issue if you did not explicitly set it.
> 
> However it is a bug we must fix, here is a patch.

Thank you @Jonathan. It worked!

I did think that it was a problem in sql file but I did not know that sql files are not in our system and also I was not able to find the authorities_normal_marc21.sql file online to find the ROW 148.
Comment 15 Nabila 2019-05-31 06:19:15 UTC
I am sorry if I did something wrong while submitted last comment.
Comment 16 Nabila 2019-06-01 03:46:58 UTC
The patch works.
Comment 17 Jonathan Druart 2019-06-01 20:30:37 UTC
(In reply to Nabila from comment #16)
> The patch works.

You should not need this patch to have Koha working correctly. You will certainly face other problems.

By any chances, could you sign the patch off?
https://wiki.koha-community.org/wiki/Sign_off_on_patches
Comment 18 Nabila 2019-06-04 05:56:44 UTC
(In reply to Jonathan Druart from comment #17)
> (In reply to Nabila from comment #16)
> > The patch works.
> 
> You should not need this patch to have Koha working correctly. You will
> certainly face other problems.
> 
> By any chances, could you sign the patch off?
> https://wiki.koha-community.org/wiki/Sign_off_on_patches

If this patch is not supposed to run - then how do I sign it off? (Sorry, first time).

I had 4 instances of Koha created on my system. The first one did not throw this error, only rest of the 3 did. If everytime MySQL and Koha setup is same. This error should not have been encountered in the first place. Yet, when error started to come for authority, the change mentioned in authorities_normal_marc21.sql file worked.
Comment 19 Nabila 2019-06-04 06:15:44 UTC
(In reply to Jonathan Druart from comment #17)
> (In reply to Nabila from comment #16)
> > The patch works.
> 
> You should not need this patch to have Koha working correctly. You will
> certainly face other problems.
> 
> By any chances, could you sign the patch off?
> https://wiki.koha-community.org/wiki/Sign_off_on_patches

If I have to test the patch in Sandbox then I will have to run the Web Installer. Which Username/password should I use?
Comment 20 Jonathan Druart 2019-06-04 23:16:11 UTC
What are the differences between the 4 Koha you have installed?

To sign it off you could just add your signed-off-by line at the bottom of the git commit, if you have a Koha git repo.
Otherwise I will add it for you.
Comment 21 Biblibre Sandboxes 2019-06-06 05:35:11 UTC
Patch tested with a sandbox, by nabila <lovenabila@gmail.com>
Comment 22 Biblibre Sandboxes 2019-06-06 05:35:35 UTC
Created attachment 90346 [details] [review]
Bug 22919: Fix insert of authorities_normal_marc21.sql with strict SQL modes

If strict SQL modes are set, the insert of authorities_normal_marc21.sql
will fail.

Test plan:
DELETE FROM auth_subfield_structure;
DELETE FROM auth_tag_structure;
DELETE FROM auth_types;
SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
source /home/vagrant/kohaclone/installer/data/mysql/en/marcflavour/marc21/mandatory/authorities_normal_marc21.sql

You should not see any errors.
Without this patch you see:
ERROR 1366 (22007) at line 280 in file:
'/home/vagrant/kohaclone/installer/data/mysql/en/marcflavour/marc21/mandatory/authorities_normal_marc21.sql':
Incorrect integer value: ' for column 'linkid' at row 148
'

Signed-off-by: nabila <lovenabila@gmail.com>
Comment 23 Nick Clemens 2019-06-06 11:21:22 UTC
Created attachment 90351 [details] [review]
Bug 22919: Fix insert of authorities_normal_marc21.sql with strict SQL modes

If strict SQL modes are set, the insert of authorities_normal_marc21.sql
will fail.

Test plan:
DELETE FROM auth_subfield_structure;
DELETE FROM auth_tag_structure;
DELETE FROM auth_types;
SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
source /home/vagrant/kohaclone/installer/data/mysql/en/marcflavour/marc21/mandatory/authorities_normal_marc21.sql

You should not see any errors.
Without this patch you see:
ERROR 1366 (22007) at line 280 in file:
'/home/vagrant/kohaclone/installer/data/mysql/en/marcflavour/marc21/mandatory/authorities_normal_marc21.sql':
Incorrect integer value: ' for column 'linkid' at row 148
'

Signed-off-by: nabila <lovenabila@gmail.com>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 24 Martin Renvoize 2019-06-06 14:27:53 UTC
Nice work!

Pushed to master for 19.11.00
Comment 25 Fridolin Somers 2019-06-11 13:51:23 UTC
Pushed to 19.05.x for 19.05.01
Comment 26 Lucas Gass 2019-06-12 22:19:12 UTC
backported to 18.11.x for 18.11.07
Comment 27 Jonathan Druart 2019-06-19 15:15:07 UTC
*** Bug 6993 has been marked as a duplicate of this bug. ***
Comment 28 Jonathan Druart 2019-11-20 15:11:45 UTC
*** Bug 23239 has been marked as a duplicate of this bug. ***