8.4.2 Adding Document Library breaks empty site
Permalink
I have raised this here http://www.concrete5.org/developers/bugs/8-4-2/document-library-bre... but now I have a second client that is asking about this and if I can add a document Library to a new site he wants built, given this I need to raise the profile on this to try to get an answer.
To summarise, I had an issue when upgrading a site from 8.2.1 to 8.4.2, everything worked perfectly, but when I added the inbuilt document library I get the error
An exception occurred while executing 'SELECT distinct n.treeNodeID, if(nt.treeNodeTypeHandle='file', fv.fvTitle, n.treeNodeName) as folderItemName, if(nt.treeNodeTypeHandle='file', fv.fvDateAdded, n.dateModified) as folderItemModified, case when nt.treeNodeTypeHandle='search_preset' then 1 when nt.treeNodeTypeHandle='file_folder' then 2 else (10 + fvType) end as folderItemType, fv.fvSize as folderItemSize 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 FileSearchIndexAttributes fis ON fv.fID = fis.fID WHERE n.treeNodeParentID = ? ORDER BY fv.fvTitle asc LIMIT 20 OFFSET 0' with params ["7"]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'iigi.fv.fvTitle' which is not in SELECT list; this is incompatible with DISTINCT
So I tried this in a brand new 8.4.2 install with the same issue
An exception occurred while executing 'SELECT distinct n.treeNodeID, if(nt.treeNodeTypeHandle='file', fv.fvTitle, n.treeNodeName) as folderItemName, if(nt.treeNodeTypeHandle='file', fv.fvDateAdded, n.dateModified) as folderItemModified, case when nt.treeNodeTypeHandle='search_preset' then 1 when nt.treeNodeTypeHandle='file_folder' then 2 else (10 + fvType) end as folderItemType, fv.fvSize as folderItemSize 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 FileSearchIndexAttributes fis ON fv.fID = fis.fID WHERE n.treeNodeParentID = ? ORDER BY fv.fvTitle asc LIMIT 20 OFFSET 0' with params ["7"]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'iigi2.fv.fvTitle' which is not in SELECT list; this is incompatible with DISTINCT
There is a bit more detail in the Bug report, has anyone experienced this and how was it fixed?
Thanks
Martyn
To summarise, I had an issue when upgrading a site from 8.2.1 to 8.4.2, everything worked perfectly, but when I added the inbuilt document library I get the error
An exception occurred while executing 'SELECT distinct n.treeNodeID, if(nt.treeNodeTypeHandle='file', fv.fvTitle, n.treeNodeName) as folderItemName, if(nt.treeNodeTypeHandle='file', fv.fvDateAdded, n.dateModified) as folderItemModified, case when nt.treeNodeTypeHandle='search_preset' then 1 when nt.treeNodeTypeHandle='file_folder' then 2 else (10 + fvType) end as folderItemType, fv.fvSize as folderItemSize 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 FileSearchIndexAttributes fis ON fv.fID = fis.fID WHERE n.treeNodeParentID = ? ORDER BY fv.fvTitle asc LIMIT 20 OFFSET 0' with params ["7"]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'iigi.fv.fvTitle' which is not in SELECT list; this is incompatible with DISTINCT
So I tried this in a brand new 8.4.2 install with the same issue
An exception occurred while executing 'SELECT distinct n.treeNodeID, if(nt.treeNodeTypeHandle='file', fv.fvTitle, n.treeNodeName) as folderItemName, if(nt.treeNodeTypeHandle='file', fv.fvDateAdded, n.dateModified) as folderItemModified, case when nt.treeNodeTypeHandle='search_preset' then 1 when nt.treeNodeTypeHandle='file_folder' then 2 else (10 + fvType) end as folderItemType, fv.fvSize as folderItemSize 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 FileSearchIndexAttributes fis ON fv.fID = fis.fID WHERE n.treeNodeParentID = ? ORDER BY fv.fvTitle asc LIMIT 20 OFFSET 0' with params ["7"]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'iigi2.fv.fvTitle' which is not in SELECT list; this is incompatible with DISTINCT
There is a bit more detail in the Bug report, has anyone experienced this and how was it fixed?
Thanks
Martyn
If this happens on the latest core, you should copy that into an issue on GitHub
As you suggested reported as github issue #7311
having thought overnight I have retested with the latest development branch and get
An exception occurred while executing 'SELECT distinct n.treeNodeID, if(nt.treeNodeTypeHandle='file', fv.fvTitle, n.treeNodeName) as folderItemName, if(nt.treeNodeTypeHandle='file', fv.fvDateAdded, n.dateModified) as folderItemModified, case when nt.treeNodeTypeHandle='search_preset' then 1 when nt.treeNodeTypeHandle='file_folder' then 2 else (10 + fvType) end as folderItemType, fv.fvSize as folderItemSize 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 FileSetFiles fsf ON tf.fID = fsf.fID LEFT JOIN FileSearchIndexAttributes fis ON fv.fID = fis.fID WHERE ((nt.treeNodeTypeHandle = "file_folder") OR (fsf.fsID = 1)) AND (n.treeNodeParentID = ?) ORDER BY fv.fvTitle asc LIMIT 20 OFFSET 0' with params ["7"]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'c5dev.fv.fvTitle' which is not in SELECT list; this is incompatible with DISTINCT
An exception occurred while executing 'SELECT distinct n.treeNodeID, if(nt.treeNodeTypeHandle='file', fv.fvTitle, n.treeNodeName) as folderItemName, if(nt.treeNodeTypeHandle='file', fv.fvDateAdded, n.dateModified) as folderItemModified, case when nt.treeNodeTypeHandle='search_preset' then 1 when nt.treeNodeTypeHandle='file_folder' then 2 else (10 + fvType) end as folderItemType, fv.fvSize as folderItemSize 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 FileSetFiles fsf ON tf.fID = fsf.fID LEFT JOIN FileSearchIndexAttributes fis ON fv.fID = fis.fID WHERE ((nt.treeNodeTypeHandle = "file_folder") OR (fsf.fsID = 1)) AND (n.treeNodeParentID = ?) ORDER BY fv.fvTitle asc LIMIT 20 OFFSET 0' with params ["7"]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'c5dev.fv.fvTitle' which is not in SELECT list; this is incompatible with DISTINCT
Comment from Andrew Embler on github answering this issue
Yeah, this is due to stricter mysql configurations on some newer systems and some newer versions of MySQL. It's the only_full_group_by setting in MySQL. To get around this for the time being you can turn that setting off – but this is something we should fix.
I have tested this and can confirm this workaround works.
Yeah, this is due to stricter mysql configurations on some newer systems and some newer versions of MySQL. It's the only_full_group_by setting in MySQL. To get around this for the time being you can turn that setting off – but this is something we should fix.
I have tested this and can confirm this workaround works.
