Bug 23289

Summary: Label Template - Creation not working (MariaDB >= 10.2.4)
Product: Koha Reporter: Andre <user>
Component: Label/patron card printingAssignee: Jonathan Druart <jonathan.druart>
Status: Pushed to stable --- QA Contact: Marcel de Rooy <m.de.rooy>
Severity: major    
Priority: P5 - low CC: amitddng135, chris, fridolin.somers, gmcharlt, jonathan.druart, katrin.fischer, m.de.rooy, martin.renvoize, user, wizzyrea
Version: master   
Hardware: PC   
OS: Linux   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23960
Change sponsored?: --- Patch complexity: Small patch
Who signed the patch off: Text to go in the release notes:
Version(s) released in:
19.11.00,19.05.04
Attachments: Bug 23289: Quote new 'rows' MariaDB reserved word
Bug 23289: Quote new 'rows' MariaDB reserved word
Bug 23289: Quote new 'rows' MariaDB reserved word

Description Andre 2019-07-09 16:17:14 UTC
Hi, 

Create a "Label Template" and save.
(any configuration ....)

There is no error message.
But it shows a message indicating that "there is no template".

The error log shows the following:
label-edit-template.pl: Database returned the following error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows, page_height, label_width, label_height) VALUES ('0','Labels','0','0','0','' at line 1 at /usr/share/koha/lib/C4/Creators/Template.pm line 201.: /usr/share/koha/intranet/cgi-bin/labels/label-edit-template.pl

Koha version: 19.0501000
MariaDB: 10.3.15

Could you verify it, please.
Thanks in advance
Andre
Comment 1 Katrin Fischer 2019-07-13 10:32:04 UTC
Hi Andre,

it's working for me on master. Did you change/add anything in the form?
Comment 2 Andre 2019-07-13 13:00:44 UTC
Hi Katrin,

I changed nothing. It is just a new installation on Debian 10.
Comment 3 Katrin Fischer 2019-07-14 11:09:12 UTC
Is it possible you are running MariaDB in strict mode? It should be off by default, but depending on how you installed or changed it could be a reason (https://wiki.koha-community.org/wiki/SQL_modes)

Running a bit out of ideas, it might be worth bringing this up on the koha-devel mailing list as more people will read it there.
Comment 4 Andre 2019-07-14 16:57:17 UTC
You are right. MariaDB seems to be running in strict mode, but this is as default since 10.2.4
https://mariadb.com/kb/en/library/sql-mode/#strict-mode

After a standard Debian 10 installation (no changes on default) and following these sites:
https://wiki.koha-community.org/wiki/Preparing_your_system
https://wiki.koha-community.org/wiki/Koha_on_Debian
I installed MariaDB as descriped under "Install the Database"

The error is only for the "lable template", the printer profile and layouts are working.
Comment 5 Chris Cormack 2019-07-25 21:05:52 UTC
Dropping the severity a bit, still an issue though.
Comment 6 Galen Charlton 2019-08-26 21:17:08 UTC
I'm not sure that the SQL mode is the problem per se; rather, as of 10.2.4, "rows" is now a reserved word in MariaDB and cannot be used as a identifier (e.g., a column name) without quoting it.

Using backticks to quote the word in the insert statement would make it work, but of course that violates the SQL6 guideline (though given commit fd30cac85, perhaps that guideline isn't being enforced?). It doesn't look like ANSI_QUOTES or ANSI are being set either. Renaming label_templates.rows column is another option, of course.
Comment 7 Jonathan Druart 2019-09-01 19:29:25 UTC
Created attachment 92520 [details] [review]
Bug 23289: Quote new 'rows' MariaDB reserved word

Since MariaDB 10.2.4 rows is a reserved word:
https://mariadb.com/kb/en/library/mariadb-1024-release-notes/

"""
New reserved word: ROWS. This can no longer be used as an identifier
without being quoted.
"""

Test plan:
With MariaDB >= 10.2.4, create a new label template.
Also, prove t/db_dependent/Labels/t_Template.t

Without this patch you will get the following SQL error: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows, creator, template_code, page_height,
Comment 8 Katrin Fischer 2019-09-01 21:31:08 UTC
Created attachment 92528 [details] [review]
Bug 23289: Quote new 'rows' MariaDB reserved word

Since MariaDB 10.2.4 rows is a reserved word:
https://mariadb.com/kb/en/library/mariadb-1024-release-notes/

"""
New reserved word: ROWS. This can no longer be used as an identifier
without being quoted.
"""

Test plan:
With MariaDB >= 10.2.4, create a new label template.
Also, prove t/db_dependent/Labels/t_Template.t

Without this patch you will get the following SQL error: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows, creator, template_code, page_height,

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 9 Marcel de Rooy 2019-09-06 05:49:44 UTC
Created attachment 92613 [details] [review]
Bug 23289: Quote new 'rows' MariaDB reserved word

Since MariaDB 10.2.4 rows is a reserved word:
https://mariadb.com/kb/en/library/mariadb-1024-release-notes/

"""
New reserved word: ROWS. This can no longer be used as an identifier
without being quoted.
"""

Test plan:
With MariaDB >= 10.2.4, create a new label template.
Also, prove t/db_dependent/Labels/t_Template.t

Without this patch you will get the following SQL error: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows, creator, template_code, page_height,

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 10 Martin Renvoize 2019-09-06 12:12:29 UTC
Nice work!

Pushed to master for 19.11.00
Comment 11 Fridolin SOMERS 2019-09-09 08:25:23 UTC
Pushed to 19.05.x for 19.05.04
Comment 12 Jonathan Druart 2019-11-08 11:59:28 UTC
*** Bug 23960 has been marked as a duplicate of this bug. ***