Skip to content

Instantly share code, notes, and snippets.

@danielpowell4

danielpowell4/off_button_for_ONLY_FULL_GROUP_BY.md

Created January 3, 2018 21:13
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save danielpowell4/e3bb41579777b7ae57bd2445e8ca1257 to your computer and use it in GitHub Desktop.
Save danielpowell4/e3bb41579777b7ae57bd2445e8ca1257 to your computer and use it in GitHub Desktop.
Download ZIP
Turning off ONLY_FULL_GROUP_BY mode in MySQL 5.7
Raw
off_button_for_ONLY_FULL_GROUP_BY.md

Turn off ONLY_FULL_GROUP_BY

If you're like me you know that it should be on, that's the default it makes SQL work properly yada yada.

You also have an app to run and it hasn't been working unexpectedly to date. In moving from 5.6 to 5.7 the strategy is something along the lines of "monkey patch for now and ... hopefully fix the handful of odd queries later"

Create a /etc/my.cnf file

Deep in your OS X computer there is a /etc directory.

Inside of it add a my.cnf file with the following contents

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

If you're using vagrant with a Ruby on Rails app

Add a similar file at config/mysql.vagrant.cnf The comments help those who see it in the config and think "WTF!?"

# This is a development only my.cnf to temporarily remove the ONLY_FULL_GROUP_BY
# from mode from MySQL 5.7 because the app does not comply
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Other things I did that may help you

Run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql ignoring the warnings

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment