Resolving MySQL Error 1055 in CodeIgniter Grouped Queries
When working with legacy CodeIgniter applications, you often encounter queries that worked perfectly fine in older versions of MySQL but suddenly fail after an upgrade to version 8.0 or later. The error log entry Expression #1 of SELECT list is not in GROUP BY clause (Error Number: 1055) is a classic symptom of this transition. It signals that the database engine is no longer tolerating implicit aggregation, enforcing strict SQL modes like only_full_group_by.
In the specific scenario provided, the CodeIgniter model method attempts to join the departement and villes tables while grouping by nom_dept. However, the query uses $this->db->select('*'), which requests every column from both tables. Because MySQL 8.0+ requires that any non-aggregated column in a SELECT list must either be part of the GROUP BY clause or be functionally dependent on it, the query fails immediately upon execution.
The core issue lies in how CodeIgniter's Active Record handles the select('*') method combined with a group_by. When you group by a single column like nom_dept, MySQL expects that every other column returned (such as id_dept, num_dept, or city names) is either aggregated using functions like MAX() or MIN(), or explicitly included in the grouping. Since * expands to all columns without aggregation, the database throws the incompatibility error.
To fix this, you have two primary approaches depending on your specific data needs. The first approach involves modifying the query to explicitly list only the columns you require and ensuring they adhere to the grouping rules. Instead of selecting everything, select the department name and perhaps one representative city or an aggregate value:
public function group_all_ville() {
$this->db->select('departement.nom_dept, MAX(villes.nom_ville) as ville_example');
$this->db->from('departement');
$this->db->join('villes', 'villes.num_dept = departement.num_dept');
$this->db->group_by('departement.nom_dept');
$query = $this->db->get();
return $query->result();
}
By replacing the wildcard * with specific columns, you resolve the dependency issue. If you need to retrieve all cities associated with a department, you cannot simply group by the department name while selecting individual city rows; doing so would result in duplicate rows for each department if multiple cities exist within that department. In such cases, you might need to reconsider whether grouping at all is necessary for your view logic, or use a subquery to fetch distinct departments before joining the full list of cities.
The second approach involves adjusting the server configuration to revert to legacy behavior, though this is generally discouraged in production environments running MySQL 8.0+. You can disable the ONLY_FULL_GROUP_BY SQL mode by adding the following line to your my.cnf file or via a SQL command:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
While this restores compatibility with older query syntax, it reduces data integrity checks and may hide logical errors where you intended to aggregate but forgot to do so. If you are maintaining an existing project and cannot refactor the database schema immediately, adjusting the SQL mode is a viable short-term mitigation strategy, provided you understand the risks involved in relying on non-aggregated columns without explicit grouping.
Ultimately, the robust solution is to refine the query logic within CodeIgniter to match modern SQL standards. This ensures your application remains stable across different MySQL versions and avoids unexpected failures during database upgrades.