How to reset mysql modes to default after doing SET sql_mode = ''

user_6307745 0
Viewed 3238

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

homestead mysql
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.

user_25481470

It'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
user_75499460

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

user_35223120
Related