I accidentally did SET sql_mode = '' while trying to disable ONLY_FULL_GROUP_BY which resets all modes currently enabled. How can I revert to the default settings? I'm running MySQL 5.7.20 on a Homestead Vagrant box.
Thanks
Add a comment
|
3 Answers
The query
SET sql_mode = '';
only works on the current connection.
So you can disconnect and reconnect your client and your default sql_mode should be restored.
To disable 'ONLY_FULL_GROUP_BY' without disabling other sql_mode can be done like this.
SET SESSION sql_mode = CONCAT(REPLACE(@@sql_mode, ',ONLY_FULL_GROUP_BY', ''));
p.s
Keep in mind that ONLY_FULL_GROUP_BY is enabled with a reason in the modern MySQL versions, i don't advice to disable it.
1 Comment
derrickrozay
I ran
SELECT @@sql_mode; before and after disconnecting and it it was indeed just for the current connection. Disabling ONLY_FULL_GROUP_BY the way you suggested also worked. ThanksIt's all walking around, because it may change from version to vesrion. To reset it you should use standard practice:
set global sql_mode=default
set session sql_mode=default
After that you can check it by:
SELECT @@GLOBAL.sql_mode
SELECT @@SESSION.sql_mode
Comments
for default inn 5.7 you should set
sql_mode = "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"
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
check for your real mysql version and subversion for other param
