Bug 20229

Summary: Remove problematic SQL modes
Product: Koha Reporter: Jonathan Druart <jonathan.druart>
Component: Architecture, internals, and plumbingAssignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: fridolin.somers, mtj, nick
Version: unspecified   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 17258, 20521    
Attachments: Bug 20229: Explicitly list the SQL modes we support
Bug 20229: Explicitly list the SQL modes we support
Bug 20229: Explicitly list the SQL modes we support

Description Jonathan Druart 2018-02-16 15:28:14 UTC
In our installation procedure we ask the administrator to edit the MySQL|MariaDB configuration file to specify the SQL modes we support (see bug 17258 comment 6 and 7 for more information).

We are on the way to catch and fix all these issues and support these stricter modes (as they highlight problem in our codebase/DB structure) but in the meanwhile it may be good to remove this step and revert the changes when we are ready.
Comment 1 Jonathan Druart 2018-02-16 15:32:44 UTC
Created attachment 71819 [details] [review]
Bug 20229: Explicitly list the SQL modes we support

In our installation procedure we ask the administrator to edit the
MySQL|MariaDB configuration file to specify the SQL modes we support
(see bug 17258 comment 6 and 7 for more information).

We are on the way to catch and fix all these issues and support these
stricter modes (as they highlight problem in our codebase/DB structure)
but in the meanwhile it may be good to remove this step and revert the
changes when we are ready.

TODO:
- Remove that for dev installations (to let developers catch these bugs)
- Edit the wiki page to remove this step

Test plan:
0. Do not apply this patch
1. Edit your MySQL|MariaDB config and add:
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"
to the [mysqld] section (or edit it)
2. Restart your DBMS
3. Try to make the app explode (watch the logs)
(tips: you should get "'koha_kohadev.me.id' isn't in GROUP BY" when
editing an order)
4. Apply the patch, restart_all, restart your DBMS
5. Try to recreate the failure
=> You should no longer see the error in the logs
Comment 2 Nick Clemens 2018-02-16 17:03:05 UTC
Created attachment 71828 [details] [review]
Bug 20229: Explicitly list the SQL modes we support

In our installation procedure we ask the administrator to edit the
MySQL|MariaDB configuration file to specify the SQL modes we support
(see bug 17258 comment 6 and 7 for more information).

We are on the way to catch and fix all these issues and support these
stricter modes (as they highlight problem in our codebase/DB structure)
but in the meanwhile it may be good to remove this step and revert the
changes when we are ready.

TODO:
- Remove that for dev installations (to let developers catch these bugs)
- Edit the wiki page to remove this step

Test plan:
0. Do not apply this patch
1. Edit your MySQL|MariaDB config and add:
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"
to the [mysqld] section (or edit it)
2. Restart your DBMS
3. Try to make the app explode (watch the logs)
(tips: you should get "'koha_kohadev.me.id' isn't in GROUP BY" when
editing an order)
4. Apply the patch, restart_all, restart your DBMS
5. Try to recreate the failure
=> You should no longer see the error in the logs

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 3 Tomás Cohen Arazi 2018-02-16 17:18:12 UTC
Created attachment 71837 [details] [review]
Bug 20229: Explicitly list the SQL modes we support

In our installation procedure we ask the administrator to edit the
MySQL|MariaDB configuration file to specify the SQL modes we support
(see bug 17258 comment 6 and 7 for more information).

We are on the way to catch and fix all these issues and support these
stricter modes (as they highlight problem in our codebase/DB structure)
but in the meanwhile it may be good to remove this step and revert the
changes when we are ready.

TODO:
- Remove that for dev installations (to let developers catch these bugs)
- Edit the wiki page to remove this step

Test plan:
0. Do not apply this patch
1. Edit your MySQL|MariaDB config and add:
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"
to the [mysqld] section (or edit it)
2. Restart your DBMS
3. Try to make the app explode (watch the logs)
(tips: you should get "'koha_kohadev.me.id' isn't in GROUP BY" when
editing an order)
4. Apply the patch, restart_all, restart your DBMS
5. Try to recreate the failure
=> You should no longer see the error in the logs

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 4 Jonathan Druart 2018-02-16 21:18:12 UTC
Pushed to master for 18.05, thanks to everybody involved!
Comment 5 Nick Clemens 2018-04-06 17:44:48 UTC
Awesome work all, backported to stable for 17.11.05
Comment 6 Fridolin Somers 2018-04-10 06:57:44 UTC
Pushed to 17.05.x for v17.05.11
Comment 7 Jonathan Druart 2018-05-22 14:05:33 UTC
See https://wiki.koha-community.org/wiki/DBMS_configuration