Summary: | Authorities MARC Structure not inserted with SQL strict modes | ||
---|---|---|---|
Product: | Koha | Reporter: | Nabila <lovenabila> |
Component: | MARC Authority data support | Assignee: | Jonathan Druart <jonathan.druart> |
Status: | CLOSED FIXED | QA Contact: | Testopia <testopia> |
Severity: | normal | ||
Priority: | P3 | CC: | ashwinloyal, cnighswonger, fridolin.somers, jonathan.druart, 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
|
|
Circulation function: | |||
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
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 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 (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. (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. Did you set strict_sql_modes in your koha-conf.xml file? What is the exact version of Koha and MySQL/MariaDB are you using? (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 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? (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? (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? UPDATE: i changed the sql_mode as required in my.cnf files but the problem persists. 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 ' 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. (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. I am sorry if I did something wrong while submitted last comment. The patch works. (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 (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. (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? 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. Patch tested with a sandbox, by nabila <lovenabila@gmail.com> 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> 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> Nice work! Pushed to master for 19.11.00 Pushed to 19.05.x for 19.05.01 backported to 18.11.x for 18.11.07 *** Bug 6993 has been marked as a duplicate of this bug. *** *** Bug 23239 has been marked as a duplicate of this bug. *** |