MySQL 5.7 - BC Break - only_full_group_by mode

**Speddy** · Jul 30th 2016

Bekomme folgende Fehlermeldung angezeigt wenn ich ein Komplett nagelneues Imscp installiert habe und unter denn Kunden account ein Datenbankuser account erstellen möchte.

Bitte sagt mir was ihr braucht.
Bekomme folgende email zu geschickt für dieses Problemm

Code

Dear administrator,
An exception has been thrown in file
/var/www/imscp/gui/library/Functions/Shared.php at line 2206:
==========================================================================
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of
SELECT list is not in GROUP BY clause and contains nonaggregated column
'imscp.sql_user.sqlu_id' which is not functionally dependent on columns in
GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Query was:
SELECT sqlu_id, sqlu_name, sqlu_host FROM sql_user INNER JOIN
sql_database USING(sqld_id) WHERE domain_id = :domain_id AND
sqld_id != :sqld_id AND CONCAT(sqlu_name, sqlu_host) NOT IN(
SELECT CONCAT(sqlu_name, sqlu_host) FROM sql_user WHERE sqld_id = :sqld_id
) GROUP BY sqlu_name, sqlu_host
==========================================================================
Backtrace:
__________
File: /var/www/imscp/gui/public/client/sql_user_add.php at line 83
Function: exec_query()
File: /var/www/imscp/gui/public/client/sql_user_add.php at line 386
Function: client_generateSqlUserList()
Contextual information:
_______________________
Request Uri: /client/sql_user_add.php?id=1
Http Referer:
Remote Addr: 46.59
Server Addr: 94.23
Note: You will not receive further emails for this exception in the next 24
hours.
Please do not reply to this email.

Display More

System immer aktuel
Debian 8.5
Mysql server 5.7
Imscp 1.3.0

Ein unerwarteter Fehler ist aufgetreten
Ein unerwarteter Fehler ist aufgetreten. Bitte kontaktieren Sie Ihren Administrator.

Get the following error message when I have a complete brand new I MSCP installed and for customer account an SQL user want to create account.

An unexpected error occurred

An unexpected error occurred. Please contact your administrator.

**Nuxwin** · Jul 30th 2016

@Speddy

Are you sure that you use i-MSCP 1.3.0? In i-MSCP version 1.3.0, there is a configuration parameter added to avoid such a backward compatibility problem with MySQL 5.7.

Please show us the result of the following comand:

Code

# cat /etc/mysql/conf.d/imscp.cnf

Note: Don't forget to hide the password that is in that file.

In that file, you should have a line such as: https://github.com/i-MSCP/imsc…d/mysql/installer.pm#L237
If that not the case, this means either that you don't use i-MSCP 1.3.0 or that something went wrong during installation.

To solve the problem, add that parameter if missing and restart the mysql service:

Code

# service mysql restart

**Speddy** · Jul 30th 2016

Code

root@ns367176:~# cat /etc/mysql/conf.d/imscp.cnf
[mysqldump]
host = localhost
port = 3306
user = "imscp_user"
password =
[mysqld]
event_scheduler = DISABLED
default_password_lifetime = 0
innodb_use_native_aio = 1
performance_schema = 0
[mysql_upgrade]
host = localhost
port = 3306
user = "imscp_user"
password =
socket = /var/run/mysqld/mysqld.sock

Display More

**Nuxwin** · Jul 30th 2016

@Speddy

Please, give us the result of the following command:

Code

cat /etc/imscp/imscp.conf |grep Version

and please, read my previous post again to know how to solve your problem (I've edited it).

**Speddy** · Jul 30th 2016

Code

root@ns367176:~# cat /etc/imscp/imscp.conf |grep Version
Version = Git 1.3.x

**Nuxwin** · Jul 30th 2016

@Speedy

Ok, I'll re-add the fix for backward compatibility in the 1.3.x branch. It seem that we must audit all our SQL queries before allowing the only_full_group_by SQL mode. I given you the solution already to fix the problem

Thank you for your report.

Thread closed.

**Nuxwin** · Jul 31st 2016

Fix re-added in the 1.3.x branch. See https://github.com/i-MSCP/imsc…0ba137587de486728dd2a37d9

Share

Daily visitors