A MySQL/MariaDB query executed in phpMyAdmin/PHP script fails when the ONLY_FULL_GROUP_BY SQL mode is configured

Dmitry Bystrov

3 months ago

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: 1055

Expression #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.

For Linux

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.cnf configuration 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_BY from the sql-mode variable.
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

For Windows Server

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.ini file 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_BY from the sql-mode variable. If sql-mode option is missing, add it without ONLY_FULL_GROUP_BY flag in [mysqld] section:

sql_mode=NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Save the…

0 Shares