Search code examples
mysqlhomestead

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

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

Solution

  • 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.