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

Question

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

Answer 1

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.

Answer 2

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