Document Library blocks report SQL error after upgrade from V9.1.3 to V9.2.6

jphoward · March 4, 2024, 11:01am

Hi there,
Just testing an upgrade from 9.1.3 to V9.2.6 on our DEV site. Have found that our Document Library blocks now report “an unexpected error occurred.” Looking into the logs we find this SQL error:

Exception Occurred: /home/ifiv5be6/public_html/updates/concrete-cms/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128 An exception occurred while executing ‘SELECT distinct n.treeNodeID, if(nt.treeNodeTypeHandle=‘file’, fv.fvTitle, n.treeNodeName) as name, if(nt.treeNodeTypeHandle=‘file’, fv.fvDateAdded, n.dateModified) as dateModified, case when nt.treeNodeTypeHandle=‘file_folder’ then 1 else (10 + fvType) end as type, fv.fvSize as size, fv.fvTitle FROM TreeNodes n INNER JOIN TreeNodeTypes nt ON nt.treeNodeTypeID = n.treeNodeTypeID LEFT JOIN TreeFileNodes tf ON tf.treeNodeID = n.treeNodeID LEFT JOIN FileVersions fv ON tf.fID = fv.fID and fv.fvIsApproved = 1 LEFT JOIN (SELECT count(distinct fsf.fsID) as sets, fsf.fID, fsf.fsID, fsf.fsDisplayOrder FROM FileSetFiles fsf WHERE fsf.fsID in (?) GROUP BY fsf.fID) fsf ON tf.fID = fsf.fID LEFT JOIN Files f ON fv.fID = f.fID LEFT JOIN Users u ON f.uID = u.uID LEFT JOIN FileSearchIndexAttributes fsi ON f.fID = fsi.fID WHERE (fsf.sets=?) AND (fsf.sets > 0) AND (n.treeNodeParentID = ?) ORDER BY fv.fvTitle desc LIMIT 20’ with params [9, 1, “690”]:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘ifiv5be6_cmw_triple8.fsf.fsDisplayOrder’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (0)

We are still running PHP 7.4.33 (with plans to upgrade to 8.3). Has anyone encountered this error with Document Library blocks ?
Thanks & regards,
Jeff.

JohntheFish · March 4, 2024, 11:35am

I suspect you have come across another variation of this bug

github.com/concretecms/concretecms

SQL Error when database configuration is set sql_mode=ONLY_FULL_GROUP_BY

opened 03:37PM - 27 Jul 23 UTC

JohntheFish

Type:Bug Status:Available Bug Priority:Low Product Areas:Dashboard Bug:Level of Effort:Unknown

### Affected Version of Concrete CMS 9.x ### Description dashboard/users/sear…ch When MySQL is configured with sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY The dashboard users page results in a SQL whoops. An exception occurred while executing 'SELECT u.uID, u.uDateAdded FROM Users u LEFT JOIN UserSearchIndexAttributes ua ON u.uID = ua.uID WHERE (u.uIsActive = ?) AND (u.uIsValidated != 0) GROUP BY u.uID ORDER BY u.uDateAdded desc, u.uID desc LIMIT 10' with params [true]: SQLSTATE[42000]: Syntax error or access violation: 1055 'dev_920.u.uDateAdded' isn't in GROUP BY ### How to reproduce Configure MySQL with sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY Restart database Visit dashboard page for Members at dashboard/users/search ### Possible Solution The SQL for this search needs to be re-written to include u.uDateAdded in the Group By clause. ### Additional Context I don't usually have my databases configured with ONLY_FULL_GROUP_BY. A customer came across an issue in one of my addons that I traced to their database settings including ONLY_FULL_GROUP_BY. This was the default setting of their host. To track and test the addon bug, I reconfigured one of my test databases for ONLY_FULL_GROUP_BY (and fixed the addon bug). A week later I needed to check the members list on a connected site and came across this bug at dashboard/users/search. As similar code is used across other dashboard listing pages there could be similar bugs on other dashboard pages. I have not found any such similar bugs yet. I have not checked any v8 sites for this bug.

a) Please add your report to the GitHub issue

b) I expect you can dodge the bug by changing your server MySQL configuration to not set ‘ONLY_FULL_GROUP_BY’

jphoward · March 4, 2024, 10:00pm

Thanks John,

Have noticed that you have already added a comment to your GitHub post about this specific problem.
Will look into changing our SQL configuration with our host and report back.

Regards,

Jeff.

jphoward · March 5, 2024, 9:29pm

After our host disabled ‘ONLY_FULL_GROUP_BY’ , our Document Library blocks now display without error under V9.2.6.

Jeff.