Deep Dive into MySQL ONLY_FULL_GROUP_BY Error: From SQLSTATE[42000] to Yii2 Project Fix

Problem Background and Error Analysis

After upgrading Ubuntu from 15.10 to 16.04, many developers encounter a specific MySQL error in Yii2 projects: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'iicityYii.opportunity_conditions.money' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. The core issue lies in the ONLY_FULL_GROUP_BY mode enabled in MySQL's sql_mode setting, which requires that non-aggregated columns in the SELECT list must appear in the GROUP BY clause or be functionally dependent on GROUP BY columns.

Structural Issues in the SQL Query

The original query exhibits significant structural problems:

SELECT SUM(oc.money), op.id, oc.money, op.mantaghe, op.`time`, op.`id`, `op`.`logo`, `pd`.`user_id`, `op`.`name`, `pd`.`co_name`, `op`.`address`, `op`.`project_type_id`, `op`.`state_id`
FROM `opportunity` op 
INNER JOIN `profile_details` pd ON op.user_id=pd.user_id  
INNER JOIN `opportunity_conditions` oc ON op.id=oc.opportunity_id
GROUP BY `op`.`id`
ORDER BY `op`.`id` DESC

Here, the SELECT list includes multiple non-aggregated columns (e.g., oc.money, op.mantaghe), but the GROUP BY clause only specifies op.id. Under ONLY_FULL_GROUP_BY mode, this inconsistency causes an error because MySQL cannot determine how to select values for non-aggregated columns per group.

Solution Based on the Best Answer

According to the highest-rated answer, the most fundamental solution is to rewrite the SQL query to comply with ONLY_FULL_GROUP_BY requirements. This requires careful analysis of the data model and query intent:

1. Identify Functional Dependencies: Determine which columns are logically dependent on GROUP BY columns. For example, if opportunity.id is a primary key, other columns of the same opportunity (e.g., op.name, op.address) may have functional dependencies.
2. Extend the GROUP BY Clause: Add all non-aggregated columns (or their functionally dependent columns) from the SELECT list to GROUP BY. A corrected query example is:

SELECT SUM(oc.money), 
       op.id, 
       oc.money, 
       op.mantaghe, 
       op.`time`, 
       op.`logo`, 
       pd.`user_id`, 
       op.`name`, 
       pd.`co_name`, 
       op.`address`, 
       op.`project_type_id`, 
       op.`state_id` 
FROM `opportunity` op 
INNER JOIN `profile_details` pd ON op.user_id = pd.user_id 
INNER JOIN `opportunity_conditions` oc ON op.id = oc.opportunity_id 
GROUP BY op.`id`, pd.`user_id`, oc.`money`, op.`mantaghe`, op.`time`, op.`logo`, op.`name`, pd.`co_name`, op.`address`, op.`project_type_id`, op.`state_id` 
ORDER BY op.`id` DESC

In this version, the GROUP BY clause includes all non-aggregated columns, ensuring query compliance. However, this approach may lead to overly fine-grained grouping, affecting query performance, so optimization based on actual data distribution is necessary.

Supplementary Solution: Adjusting MySQL Configuration

Beyond query modification, ONLY_FULL_GROUP_BY can be temporarily or permanently disabled by adjusting MySQL's sql_mode. This offers flexibility but may obscure potential data consistency issues.

1. Temporary Disabling: Execute SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')) in a MySQL session, but this does not persist after service restart.
2. Permanent Configuration: Edit the MySQL configuration file (e.g., /etc/mysql/my.cnf), set sql_mode under the [mysqld] section, such as sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION", then restart the MySQL service.

Note that disabling ONLY_FULL_GROUP_BY may cause non-deterministic query results, as MySQL might arbitrarily select values for non-aggregated columns, which should be used cautiously in production environments.

Framework-Level Adjustments

For projects using frameworks like Laravel, strict mode can be adjusted in database configurations. For example, in Laravel's config/database.php, change 'strict' => true to 'strict' => false, which can indirectly affect ONLY_FULL_GROUP_BY behavior. However, this method may impact other strictness checks and is recommended as a temporary measure.

Best Practices and Summary

When handling ONLY_FULL_GROUP_BY errors, prioritize rewriting queries to ensure data logic clarity and consistency. This requires developers to deeply understand data models and SQL standards:

• Use aggregate functions (e.g., SUM, AVG) for columns requiring summarization.
• Ensure non-aggregated columns are either listed in GROUP BY or functionally dependent on GROUP BY columns via primary-foreign key relationships.
• Enable ONLY_FULL_GROUP_BY in development environments to catch potential issues early, while balancing configuration based on performance needs in production.

By combining query optimization and configuration management, compatibility issues from upgrades can be effectively resolved, enhancing code quality and maintainability.