Tags
active-record, database, devops, ei-capitan, mac, mysql, osx, rails, ruby, server, software-development
MySql version 5 contains breaking changes among which default SQL mode is one of them.
The new default SQL mode includes these modes:
- ONLY_FULL_GROUP_BY,
- STRICT_TRANS_TABLES
- NO_ZERO_IN_DATE
- NO_ZERO_DATE
- ERROR_FOR_DIVISION_BY_ZERO
- NO_AUTO_CREATE_USER
- NO_ENGINE_SUBSTITUTION.
Brief on new changed modes
- ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5
- NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7
- ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8
You can find different SQl modes here
The ONLY_FULL_GROUP_BY mode means :-
Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
Example
To give you an example of what that means here is a simple SQL query that will fail with the above SQL mode.
SELECT first_name, IF(last_name IS NULL, 'DUMMY', last_name) as last_name from users GROUP BY first_name
SELECT first_name, IF(last_name IS NULL, ‘DUMMY’, last_name) as last_name from users GROUP BY first_name
This query will give you the following error: –
Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘tmp_tbl.value’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT value, table_id, count(*) as tables_count
FROM (
SELECT value, table_id
FROM noker_tables
WHERE noker_id = 8
ORDER BY id DESC
) as tmp_tbl
GROUP BY table_id
Completed 500 Internal Server Error in 11.5ms
Solution
If you are like me and have used Homebrew to install/upgrade Mysql, then you can do the following steps to fix the issue.
Copy the default my-default.cnf to /etc/my.cnf
sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf sudo vi /etc/my.cnf +31
Now set sql_mode it to this(you can find this near line 31) :-
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
After writing my.cnf, restart your MySQL server.
**But if you are trying something in MySQL console use this commands
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'; set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Now your queries will start working again. Happy debugging