Symptoms
-
Execution of a MySQL/MariaDB query in phpMyAdmin terminates with:
#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'exampl_db.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
- A database SELECT query with umlaut symbols (ä, ë, ï, ö, ü, ÿ) does not work inside a PHP script with mysqli function.
-
A website shows the following error:
A Database Error Occurred
Error Number: 1055Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'exampl_db.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
-
The mass password reset script fails with:
[FATAL_ERROR] [MYSQL ERROR] Unable to execute query. Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'psa.domains.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Cause
Incompatibility of the ONLY_FULL_GROUP_BY SQL mode with the executed query.
Resolution
Disable the ONLY_FULL_GROUP_BY SQL mode in MySQL/MariaDB configuration.
-
Connect to the Plesk server via SSH.
Note: If direct SSH access to the server is not possible, contact server administrator for further assistance.
-
Open the
my.cnfconfiguration file in a text editor. In this example, we use the vi editor:-
on CentOS/RHEL-based distributions
# vi /etc/my.cnf
-
on Debian/Ubuntu-based distributions
# vi /etc/mysql/my.cnf
-
-
Remove
ONLY_FULL_GROUP_BYfrom thesql-modevariable. -
Save the changes and close the file.
-
Restart the MySQL/MariaDB service to apply the changes (The command depends on operating system and MySQL/MariaDB version):
# service mariadb restart
# service mysql restart
# service mysqld restart
Note: If the issue occurs on MySQL > 5.7.5 the value "ONLY_FULL_GROUP_BY" is enabled by default. It is not presented in /etc/my.cnf file, so it is required specify sql_mode variable without the option ONLY_FULL_GROUP_BY.
For example, edit in /etc/my.cnf file and add the following line:
[mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
-
Connect to the server via RDP.
Note: If direct RDP access to the server is not possible, contact server administrator for further assistance.
-
Open the
%plesk_dir%DatabasesMySQLmy.inifile in a text editor. In this example, we use Windows Notepad:Start a command prompt and run the command:
C:> notepad %plesk_dir%DatabasesMySQLmy.ini
-
Remove
ONLY_FULL_GROUP_BYfrom thesql-modevariable. Ifsql-modeoption is missing, add it withoutONLY_FULL_GROUP_BYflag in [mysqld] section:sql_mode=NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
-
Save the…
