Bug 20229 - Remove problematic SQL modes
Summary: Remove problematic SQL modes
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low major (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks: 17258 20521
  Show dependency treegraph
 
Reported: 2018-02-16 15:28 UTC by Jonathan Druart
Modified: 2019-10-14 19:56 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 20229: Explicitly list the SQL modes we support (2.38 KB, patch)
2018-02-16 15:32 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 20229: Explicitly list the SQL modes we support (2.43 KB, patch)
2018-02-16 17:03 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 20229: Explicitly list the SQL modes we support (2.49 KB, patch)
2018-02-16 17:18 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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