Bug 33183

Summary: Error inserting matchpoint_components when creating record matching rules with MariaDB 10.6
Product: Koha Reporter: Kyle M Hall <kyle>
Component: Architecture, internals, and plumbingAssignee: Kyle M Hall <kyle>
Status: RESOLVED FIXED QA Contact: Martin Renvoize <martin.renvoize>
Severity: critical    
Priority: P5 - low CC: arthur.suzuki, dcook, lucas, martin.renvoize, wainuiwitikapark
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
23.05.00,22.11.04,22.05.12,21.11.20
Attachments: Bug 33183: Error inserting matchpoint_components when creating record matching rules with MariaDB 10.6
Bug 33183: Error inserting matchpoint_components when creating record matching rules with MariaDB 10.6
Bug 33183: Error inserting matchpoint_components when creating record matching rules with MariaDB 10.6

Description Kyle M Hall 2023-03-09 16:28:37 UTC
The word "offset" is now a keyword. At the very least we need to escape the fieldname with backticks. It would be nice to switch to Koha::Objects for this module eventually.

C4::Matcher::_store_matchpoint(): DBI Exception: 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 'offset, length) VALUES ('27', '1', '999'...' at line 2 at /usr/share/koha/lib/C4/Matcher.pm line 314
Comment 1 Kyle M Hall 2023-03-09 16:47:42 UTC
Created attachment 148017 [details] [review]
Bug 33183: Error inserting matchpoint_components when creating record matching rules with MariaDB 10.6

The word "offset" is now a keyword. At the very least we need to escape the fieldname with backticks. It would be nice to switch to Koha::Objects for this module eventually.

C4::Matcher::_store_matchpoint(): DBI Exception: 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 'offset, length) VALUES ('27', '1', '999'...' at line 2 at /usr/share/koha/lib/C4/Matcher.pm line 314

Test Plan:
1) Set up Koha with MariaDB 10.6
2) Attempt to create a matching rule
3) Note the error screen
4) Apply this patch
5) Restart all the things!
6) Attempt to create another matching rule
7) No errors this time
Comment 2 Katrin Fischer 2023-03-09 18:47:12 UTC
Created attachment 148025 [details] [review]
Bug 33183: Error inserting matchpoint_components when creating record matching rules with MariaDB 10.6

The word "offset" is now a keyword. At the very least we need to escape the fieldname with backticks. It would be nice to switch to Koha::Objects for this module eventually.

C4::Matcher::_store_matchpoint(): DBI Exception: 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 'offset, length) VALUES ('27', '1', '999'...' at line 2 at /usr/share/koha/lib/C4/Matcher.pm line 314

Test Plan:
1) Set up Koha with MariaDB 10.6
2) Attempt to create a matching rule
3) Note the error screen
4) Apply this patch
5) Restart all the things!
6) Attempt to create another matching rule
7) No errors this time

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 3 Katrin Fischer 2023-03-09 18:47:48 UTC
I couldn't test with this specific DBMS version, but I did a regression test and the code looks good.
Comment 4 Katrin Fischer 2023-03-09 18:49:07 UTC
Tested with:
mariadb  Ver 15.1 Distrib 10.5.18-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

I also verified we have backticks in kohastructure.sql.
Comment 5 Martin Renvoize 2023-03-15 15:54:15 UTC
Created attachment 148222 [details] [review]
Bug 33183: Error inserting matchpoint_components when creating record matching rules with MariaDB 10.6

The word "offset" is now a keyword. At the very least we need to escape the fieldname with backticks.
It would be nice to switch to Koha::Objects for this module eventually.

C4::Matcher::_store_matchpoint(): DBI Exception: 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 'offset, length) VALUES ('27', '1', '999'...' at line 2 at /usr/share/koha/lib/C4/Matcher.pm
line 314

Test Plan:
1) Set up Koha with MariaDB 10.6
2) Attempt to create a matching rule
3) Note the error screen
4) Apply this patch
5) Restart all the things!
6) Attempt to create another matching rule
7) No errors this time

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 6 Tomás Cohen Arazi 2023-03-16 19:31:47 UTC
Pushed to master for 23.05.

Nice work everyone, thanks!
Comment 7 Matt Blenkinsop 2023-03-17 17:03:03 UTC
Nice work everyone!

Pushed to stable for 22.11.x
Comment 8 Lucas Gass 2023-04-14 18:33:03 UTC
Backported to 22.05.x for upcoming 22.05.12
Comment 9 Arthur Suzuki 2023-04-20 09:18:38 UTC
applied to 21.11.x for 21.11.20
Comment 10 wainuiwitikapark 2023-04-27 05:59:48 UTC
Not backported to 21.05.x