We use cookies to improve your experience. No personal information is gathered and we don't serve ads. Cookies Policy.

ExpressionEngine Logo ExpressionEngine
Features Pricing Support Find A Developer
Partners Upgrades
Blog Add-Ons Learn
Docs Forums University
Log In or Sign Up
Log In Sign Up
ExpressionEngine Logo
Features Pro new Support Find A Developer
Partners Upgrades
Blog Add-Ons Learn
Docs Forums University Blog
  • Home
  • Forums

Is ExpressionEngine 6 Compatible with MySQL Strict Mode?

News and General

afarsh's avatar
afarsh
23 posts
one year ago
afarsh's avatar afarsh

I have checked the documentation and this forum and am not able to find the answer to this question.

Is EE 6 compatible with strict MySQL?

       
Andy McCormick's avatar
Andy McCormick
322 posts
one year ago
Andy McCormick's avatar Andy McCormick

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.

       
afarsh's avatar
afarsh
23 posts
one year ago
afarsh's avatar afarsh

Great! Thank you Andy.

       
afarsh's avatar
afarsh
23 posts
one year ago
afarsh's avatar afarsh

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.

       
Andy McCormick's avatar
Andy McCormick
322 posts
one year ago
Andy McCormick's avatar Andy McCormick

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

       
afarsh's avatar
afarsh
23 posts
one year ago
afarsh's avatar afarsh

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.

       
Andy McCormick's avatar
Andy McCormick
322 posts
one year ago
Andy McCormick's avatar Andy McCormick

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

       
afarsh's avatar
afarsh
23 posts
one year ago
afarsh's avatar afarsh

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!

👍 1
       
Andy McCormick's avatar
Andy McCormick
322 posts
one year ago
Andy McCormick's avatar Andy McCormick

definitely will add this to the troubleshooting section. thanks

       
afarsh's avatar
afarsh
23 posts
one year ago
afarsh's avatar afarsh

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/

       

Reply

Sign In To Reply

ExpressionEngine Home Features Pro Contact Version Support
Learn Docs University Forums
Resources Support Add-Ons Partners Blog
Privacy Terms Trademark Use License

Packet Tide owns and develops ExpressionEngine. © Packet Tide, All Rights Reserved.