More problems with MySQL 5.7
Hi, testing things on MySQL 5.7 - Koha 3.22, found a new problem: memberentry.pl: DBD::mysql::st execute failed: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'kohadev.categories.categorycode' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ... This comes trying to add a patron after fresh install, 3.22+marc21+all sample data Can we change sql_mode for Koha? Regards Bernardo -- Bernardo Gonzalez Kriegel bgkriegel@gmail.com
Maybe it is worth checking if it can be configured client-side. As we do for UTF-8 El jue., 26 may. 2016 a las 9:45, Bernardo Gonzalez Kriegel (< bgkriegel@gmail.com>) escribió:
Hi, testing things on MySQL 5.7 - Koha 3.22, found a new problem:
memberentry.pl: DBD::mysql::st execute failed: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'kohadev.categories.categorycode' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ...
This comes trying to add a patron after fresh install, 3.22+marc21+all sample data
Can we change sql_mode for Koha?
Regards Bernardo
-- Bernardo Gonzalez Kriegel bgkriegel@gmail.com _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F
Hello, we found this issue some days ago, and this is what we do: sub _new_schema { require Koha::Schema; my $context = C4::Context->new(); my $db_driver = $context->{db_driver}; my $db_name = $context->config("database"); my $db_host = $context->config("hostname"); my $db_port = $context->config("port") || ''; my $db_user = $context->config("user"); my $db_passwd = $context->config("pass"); my ( %encoding_attr, $encoding_query, $tz_query, $sql_mode ); 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 = "set sql_mode = ''"; } elsif ( $db_driver eq 'Pg' ) { $encoding_query = "set client_encoding = 'UTF8';"; $tz_query = qq(SET TIME ZONE = "$tz") if $tz; } my $schema = Koha::Schema->connect( { dsn => "dbi:$db_driver:database=$db_name;host=$db_host;port=$db_port", user => $db_user, password => $db_passwd, %encoding_attr, RaiseError => $ENV{DEBUG} ? 1 : 0, PrintError => 1, unsafe => 1, quote_names => 1, on_connect_do => [ $encoding_query || (), $tz_query || (), $sql_mode || (), ] } ); my $dbh = $schema->storage->dbh; eval { $dbh->{RaiseError} = 1; $dbh->do(q| SELECT * FROM systempreferences WHERE 1 = 0 | ); $dbh->{RaiseError} = $ENV{DEBUG} ? 1 : 0; }; $dbh->{RaiseError} = 0 if $@; return $schema;} Regards, 2016-05-26 17:59 GMT+02:00 Tomas Cohen Arazi <tomascohen@gmail.com>:
Maybe it is worth checking if it can be configured client-side. As we do for UTF-8
El jue., 26 may. 2016 a las 9:45, Bernardo Gonzalez Kriegel (< bgkriegel@gmail.com>) escribió:
Hi, testing things on MySQL 5.7 - Koha 3.22, found a new problem:
memberentry.pl: DBD::mysql::st execute failed: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'kohadev.categories.categorycode' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ...
This comes trying to add a patron after fresh install, 3.22+marc21+all sample data
Can we change sql_mode for Koha?
Regards Bernardo
-- Bernardo Gonzalez Kriegel bgkriegel@gmail.com _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- -- * Juan Francisco Romay Sieira* *Chief Technology Officer* juan.sieira@xercode.es C/ Hedras, 4 2ºA | 15895 | Milladoiro - A Coruña - Spain www.xercode.es | info@xercode.es Tel. (+34) 881 975 576 La información contenida en este mensaje de correo electrónico es confidencial y puede revestir el carácter de reservada. Está destinada exclusivamente a su destinatario. El acceso o uso de este mensaje, por parte de cualquier otra persona que no esté autorizada, puede ser ilegal. Si no es Ud. la persona destinataria, le rogamos que proceda a eliminar su contenido y comunicar dicha anomalía a su remitente. En cumplimiento de lo establecido por la LOPD 15/1999 le comunicamos que los datos personales contenidos en esta comunicación han sido recogidos de los contactos mantenidos por Ud. con personal de Xercode Media Software S.L. o de fuentes accesibles al público, y han sido incorporados a un fichero CLIENTES cuya finalidad es la de mantener con usted relaciones comerciales y/o profesionales. Asimismo, podrá ejercitar sus derechos de acceso, rectificación, cancelación y oposición enviando un escrito a C/ Hedras, 4 2ºA , 15895, Milladoiro - Ames, A Coruña – Spain, junto con prueba válida en derecho, como fotocopia del D.N.I. e indicando en el asunto "PROTECCIÓN DE DATOS".
Hello, Thank you very much for sharing experiences. Best, Kamal ---------- Forwarded message ---------- From: Partha Mukhopadhyay <psm_vu@india.com> Date: Mon, May 30, 2016 at 10:50 PM Subject: Re: [Koha-devel] More problems with MySQL 5.7 To: koha-devel <koha-devel@lists.koha-community.org> Dear All I also got the same problems in installation and post-installation of Koha 3.22.06 on Ubuntu 16.04 (MySQL 5.7.12). I followed the following steps to get rid of the situation in our departmental ICT lab. Installation 1. Off default sql_mode; 2. Web installer still fails in step 3; 3. Changed Kohastructue.sql (Tables letter and permissions) 4. Imported kohastructure.sql manually to Koha_instancename db (after vacating it) 5. Altered Koha_instancename db to make it Unicode compatible as we are using multilingual records (ALTER DATABASE `koha_instancaname` CHARACTER SET utf8 COLLATE utf8_unicode_ci;) 6. Again started web installer and this time it worked 7. Finished installation and logged on to koha for post installation works 6. Retured to default sql_mode Post installtion work 1. Tried all basic operations in post installation phase - all worked except add patron (always says no patron category thoug it is present there) 2. Again set off ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES in sql_mode 3. Recheked and found everything is working Questions 1. Is it safe and secured to keep ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES off in MySQL 5.7.12 (or latter versions)? 2. Is it wise to use Koha 3.22.06 or 16.05 vesion (having same problems) on ubuntu 16.04 (with MySQL 5.7.12) in production mode? The entire process in step-step manner has alreday been documented and uploaed in github. Those who are facing same problems and wish to have a solution may check it from here: https://github.com/psmukhopadhyay/OLS https://github.com/psmukhopadhyay/OLS/blob/master/Koha%203.22.x%20on%20Ubunt... But a caution: I'm no DBMS expert (a teacher in LIS by profession to be exact) and the entire process requires authentication and clearence by Koha experts working DBMS domain. Regards ---------------------------------------------------- Dr. Parthasarathi Mukhopadhyay Associate Professor Department of Library and Information Science University of Kalyani (WB, India) ------------------------------------------ At 30 May 2016 09:32:09 +0000 (+00:00) from Juan Francisco Romay Sieira < juan.sieira@xercode.es>: Hello, we found this issue some days ago, and this is what we do: sub _new_schema { require Koha::Schema; my $context = C4::Context->new(); my $db_driver = $context->{db_driver}; my $db_name = $context->config("database"); my $db_host = $context->config("hostname"); my $db_port = $context->config("port") || ''; my $db_user = $context->config("user"); my $db_passwd = $context->config("pass"); my ( %encoding_attr, $encoding_query, $tz_query, $sql_mode ); 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 = "set sql_mode = ''"; } elsif ( $db_driver eq 'Pg' ) { $encoding_query = "set client_encoding = 'UTF8';"; $tz_query = qq(SET TIME ZONE = "$tz") if $tz; } my $schema = Koha::Schema->connect( { dsn => "dbi:$db_driver:database=$db_name;host=$db_host;port=$db_port", user => $db_user, password => $db_passwd, %encoding_attr, RaiseError => $ENV{DEBUG} ? 1 : 0, PrintError => 1, unsafe => 1, quote_names => 1, on_connect_do => [ $encoding_query || (), $tz_query || (), $sql_mode || (), ] } ); my $dbh = $schema->storage->dbh; eval { $dbh->{RaiseError} = 1; $dbh->do(q| SELECT * FROM systempreferences WHERE 1 = 0 | ); $dbh->{RaiseError} = $ENV{DEBUG} ? 1 : 0; }; $dbh->{RaiseError} = 0 if $@; return $schema;} Regards, 2016-05-26 17:59 GMT+02:00 Tomas Cohen Arazi <tomascohen@gmail.com>:
Maybe it is worth checking if it can be configured client-side. As we do for UTF-8
El jue., 26 may. 2016 a las 9:45, Bernardo Gonzalez Kriegel (< bgkriegel@gmail.com>) escribió:
Hi, testing things on MySQL 5.7 - Koha 3.22, found a new problem:
memberentry.pl: DBD::mysql::st execute failed: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'kohadev.categories.categorycode' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ...
This comes trying to add a patron after fresh install, 3.22+marc21+all sample data
Can we change sql_mode for Koha?
Regards Bernardo
-- Bernardo Gonzalez Kriegel bgkriegel@gmail.com _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- -- *Juan Francisco Romay Sieira* *Chief Technology Officer* juan.sieira@xercode.es C/ Hedras, 4 2ºA | 15895 | Milladoiro - A Coruña - Spain www.xercode.es | info@xercode.es Tel. (+34) 881 975 576 La información contenida en este mensaje de correo electrónico es confidencial y puede revestir el carácter de reservada. Está destinada exclusivamente a su destinatario. El acceso o uso de este mensaje, por parte de cualquier otra persona que no esté autorizada, puede ser ilegal. Si no es Ud. la persona destinataria, le rogamos que proceda a eliminar su contenido y comunicar dicha anomalía a su remitente. En cumplimiento de lo establecido por la LOPD 15/1999 le comunicamos que los datos personales contenidos en esta comunicación han sido recogidos de los contactos mantenidos por Ud. con personal de Xercode Media Software S.L. o de fuentes accesibles al público, y han sido incorporados a un fichero CLIENTES cuya finalidad es la de mantener con usted relaciones comerciales y/o profesionales. Asimismo, podrá ejercitar sus derechos de acceso, rectificación, cancelación y oposición enviando un escrito a C/ Hedras, 4 2ºA , 15895, Milladoiro - Ames, A Coruña – Spain, junto con prueba válida en derecho, como fotocopia del D.N.I. e indicando en el asunto "PROTECCIÓN DE DATOS". -- _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/ -- Kamal Hossain Sr. Assistant Librarian University of Liberal Arts Bangladesh (ULAB) House 56, Road 4/A, Dhaka 1209 Bangladesh Tel: 9661255, 9661301 (Ext. 203) Fax: 9660610 Website:www.ulab.edu.bd
participants (5)
-
Bernardo Gonzalez Kriegel -
Juan Francisco Romay Sieira -
Kamal Hossain -
Partha Mukhopadhyay -
Tomas Cohen Arazi