Is ExpressionEngine 6 Compatible with MySQL Strict Mode?

Yes. ExpressionEngine works fine with strict mode. I have run into issues with exporting/importing EE databases of larger sites when strict mode is enabled, but you can install EE from scratch fine with strict mode enabled.

Great! Thank you Andy.

Andy,

I think I may have found one incompatibility with MySQL strict mode. I am on EE 6.0.6 and using the following tag returns error:

{exp:channel:entries channel="REDACTED" category="REDACTED" orderby="view_count_one" sort="desc" limit="15"}

Gives the following error:

SQLSTATE[HY000]: General error: 3065 Expression #3 of ORDER BY clause is not in SELECT list,
references column 'REDACTED.t.entry_date' which is not in SELECT list; this is incompatible with DISTINCT: ...

Am I doing something wrong?

In the above when I remove ‘orderby=”view_count_one”’ it works no problem.

hm. Is there more to that SQL error than what you posted above? I’m not able to replicate it locally. You can see the screenshots below that the tag outputs and that I’m currently in strict mode.

{exp:channel:entries channel="blog" category="5" orderby="view_count_one" sort="desc" limit="15"}
{title}- count: {view_count_one}

{/exp:channel:entries}

Can you let me know what database server and version you’re running and the output of SHOW VARIABLES LIKE 'sql_mode'; thanks

Sure. Here are additional details.

EE statement:

{exp:channel:entries channel="blogs|news" category="6" orderby="view_count_one" sort="desc" limit="5"}
  ...
{/exp:channel:entries}

SQL error:

SQLSTATE[HY000]: General error: 3065 Expression #3 of ORDER BY clause is not in SELECT list, references column 'REDACTED.t.entry_date' which is not in SELECT list; this is incompatible with DISTINCT:
SELECT DISTINCT t.entry_id , exp_channels.channel_id , t.sticky , t.view_count_one FROM exp_channel_titles AS t LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id WHERE t.entry_id != '' AND t.site_id IN ('1') AND t.entry_date < 1635948804 AND (t.expiration_date = 0 OR t.expiration_date > 1635948804) AND t.channel_id IN (1,6) AND exp_categories.cat_id = '6' AND t.status = 'open' ORDER BY t.sticky desc, t.view_count_one desc, t.entry_date desc, t.entry_id desc LIMIT 0, 5
ee/legacy/database/drivers/mysqli/mysqli_connection.php:114

MySQL version:

mysql  Ver 8.0.27-0ubuntu0.21.04.1 for Linux on x86_64 ((Ubuntu))

SHOW VARIABLES LIKE ‘sql_mode’;

+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+

Also noticed this remedy in my test: Given that MySQL is complaining about the use of “ORDER BY” usage when I use the following in the ‘exp:channel:entries’ statement above, it works:

orderby=”view_count_one|cat_id”

but fails if ‘cat_id’ is not included in the ‘orderby’ parameter.

Ah. thanks. This appears to be an issue with the ONLY_FULL_GROUP_BY mode which is set. Here is a reference I found https://stackoverflow.com/questions/36829911/how-to-resolve-order-by-clause-is-not-in-select-list-caused-mysql-5-7-with-sel.

I set up an instance of MySQL 8.0 and was able to replicate your issue. Before I was running MariaDB, MySQL seems to add only_full_group_by by default.

For this test I was able to run SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); To remove only_full_group_by . After doing this I was able to load the page with the {exp:channel:entries} tag that was breaking before.

Please review and let me know if this is able to resolve the issue. thanks

Yup. This solved the problem!

I think it might be helpful to add a note in EE documentation for those running MySQL 8 or those that have MySQL strict mode fully enabled to disable ‘ONLY_FULL_GROUP_BY’ to avoid EE issues width Tags.

Thank you again Andy!

definitely will add this to the troubleshooting section. thanks

Also, one other thing to note is that using the command below seems to be temporary. Anytime MySQL is restarted it seems to default back to using ‘ONLY_FULL_GROUP_BY’ again:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

This page offered a more permanent fix: https://www.randomhacks.co.uk/disabling-only_full_group_by-in-ubuntu/