MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it was not).

There is a good overview of the changes here.

Unfortunately, this affects some core queries that Craft uses when fetching elements.

The good news is that this has been fixed already in Craft 3 since we refactored the way that elements query grabs data and it no longer uses a GROUP BY.

The bad news is that it’s going to be pretty impractical to backport that fix into Craft 2.

The workaround for Craft 2, if you’re running MySQL 5.7.5+ is to edit your my.cnf file and remove the ONLY_FULL_GROUP_BY option from sql_mode. That will change GROUP BY behavior back to its pre-MySQL 5.7.5 behavior.

So, steps to success:

  1. sudo vim /etc/mysql/my.cnf
  2. Scroll to the bottom of file and A to enter insert mode
  3. Copy and paste
    [mysqld]
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION to the bottom of the file
  4. esc to exit input mode
  5. :wq to save and close vim.
  6. sudo service mysql restart to restart MySQL.
  7. Done!

Comments


MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it...

Read Post

MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it...

Read Post

Leave a Reply

Your email address will not be published. Required fields are marked *