From ac2a798f5badbc7d4fe2be14b658353a0a7b7770 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 16 Feb 2018 12:23:26 -0300 Subject: [PATCH] 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 Signed-off-by: Tomas Cohen Arazi --- Koha/Database.pm | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/Koha/Database.pm b/Koha/Database.pm index 7daf5f0cac..52962b2612 100644 --- a/Koha/Database.pm +++ b/Koha/Database.pm @@ -69,12 +69,13 @@ sub _new_schema { - my ( %encoding_attr, $encoding_query, $tz_query ); + my ( %encoding_attr, $encoding_query, $tz_query, $sql_mode_query ); my $tz = $ENV{TZ}; if ( $db_driver eq 'mysql' ) { %encoding_attr = ( mysql_enable_utf8 => 1 ); $encoding_query = "set NAMES 'utf8'"; $tz_query = qq(SET time_zone = "$tz") if $tz; + $sql_mode_query = q{SET sql_mode = 'IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'}; } elsif ( $db_driver eq 'Pg' ) { $encoding_query = "set client_encoding = 'UTF8';"; @@ -93,6 +94,7 @@ sub _new_schema { on_connect_do => [ $encoding_query || (), $tz_query || (), + $sql_mode_query || (), ] } ); -- 2.14.1