MySQL Error 1055: Analysis and Solutions for GROUP BY Issues under ONLY_FULL_GROUP_BY Mode

Problem Background and Error Analysis

In daily MySQL database operations, developers frequently encounter Error Code 1055: "Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by". This error typically appears starting from MySQL version 5.7.5, as the ONLY_FULL_GROUP_BY mode is enabled by default from this version onward.

The core issue lies in the SQL query structure not conforming to ANSI SQL standards. When using the GROUP BY clause for grouped queries, all non-aggregated columns in the SELECT list must either appear in the GROUP BY clause or be functionally dependent on the GROUP BY columns. Violating this rule triggers Error 1055.

Error Reproduction and Root Causes

Consider the following typical problematic query example:

select libelle,credit_initial,disponible_v,sum(montant) as montant 
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee 
and annee = year(current_timestamp) GROUP BY libelle order by libelle asc

In this query, the columns credit_initial and disponible_v appear in the SELECT list but are not included in the GROUP BY clause, nor are they results of aggregate functions. This query structure might have been permitted in versions prior to MySQL 5.7.5 but is rejected in newer versions with ONLY_FULL_GROUP_BY mode enabled.

Solution 1: Modifying MySQL Configuration File

The most comprehensive solution involves adjusting the sql_mode setting by modifying the MySQL configuration file. This approach permanently resolves the issue and is suitable for production environments.

Specific operational steps:

1. Open the MySQL configuration file using a text editor: sudo vim /etc/mysql/conf.d/mysql.cnf
2. Add the following configuration at the end of the file:

   [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

3. Save the file and exit the editor
4. Restart the MySQL service to apply the configuration: sudo service mysql restart

The advantage of this method is configuration persistence—the settings remain effective after server restarts. The new sql_mode setting removes ONLY_FULL_GROUP_BY while retaining other important strict mode options, achieving a balance between compatibility and data integrity.

Solution 2: Temporary SQL Command Modification

For scenarios requiring quick testing or temporary problem resolution, you can directly modify the sql_mode setting at the global or session level using SQL commands.

Global level modification (affects all connections):

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Session level modification (affects only the current connection):

SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

To execute these commands, appropriate privileges are required:

mysql -u root -p
# Or when sudo privileges are needed:
sudo mysql -u root -p

The advantage of this approach is its simplicity and speed, making it suitable for development and testing environments. The disadvantage is that settings are lost after server restart, making it unsuitable as a long-term solution for production environments.

Solution 3: Optimizing SQL Query Statements

From a code quality perspective, the most ideal solution is to modify the query statements to comply with SQL standards. This not only resolves the current issue but also improves code maintainability and cross-database compatibility.

Optimized version of the original problematic query:

select libelle,credit_initial,disponible_v,sum(montant) as montant 
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee 
and annee = year(current_timestamp) GROUP BY libelle,credit_initial,disponible_v order by libelle asc

In this optimized version, all non-aggregated columns appearing in the SELECT list (libelle, credit_initial, disponible_v) are included in the GROUP BY clause. This writing style ensures deterministic query results and conforms to SQL standard specifications.

In-Depth Understanding of sql_mode Configuration

To view the current sql_mode setting, use the following SQL command:

SHOW VARIABLES LIKE 'sql_mode';

Typical output might display:

+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------+

When STRICT_TRANS_TABLES appears in the value list, it indicates that strict mode is enabled. Strict mode includes multiple sub-modes, with ONLY_FULL_GROUP_BY being the specific cause of Error 1055.

Version Compatibility Considerations

MySQL 5.7.5 represents a significant watershed version. Before this version:

• ONLY_FULL_GROUP_BY mode was disabled by default
• MySQL's detection of functional dependencies was less strict
• Ambiguous GROUP BY queries might return non-deterministic results

Starting from MySQL 5.7.5:

• ONLY_FULL_GROUP_BY mode is enabled by default
• Enhanced functional dependency detection
• Enforced stricter compliance with SQL standards

This change reflects MySQL's development trend toward standard SQL alignment. While it may cause compatibility issues in the short term, it benefits code quality and database interoperability in the long run.

Best Practice Recommendations

Based on thorough problem analysis, we recommend:

1. Development Environment: Employ temporary sql_mode modification methods for quick testing and debugging
2. Testing Environment: Maintain ONLY_FULL_GROUP_BY enabled to early detect potential SQL issues
3. Production Environment: If choosing to disable strict mode, implement persistent configuration through configuration files
4. Code Quality: Prioritize optimizing SQL query statements to comply with standard specifications

By understanding MySQL version differences and the working principles of sql_mode, developers can better manage database compatibility issues and write more robust, maintainable SQL code.