Search Returns error but only for images?

#1

I am getting this error when I search for all types but the normal search works when excluding images.

!
|Query Error|Expression #40 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘razuna_durants.x.category’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by|
|—|---|
|Datasource|MYSQL|
|Native Error Code|1055|
|SQL State|42000|
|SQL|SELECT * FROM ( SELECT /* 48461B369B774942AC7891797A9B9900imgSearch / i.img_id id, i.img_filename filename, i.folder_id_r, i.img_group groupid, i.thumb_extension ext, i.img_filename_org filename_org, ‘img’ as kind, i.is_available, i.img_create_time date_create, i.img_change_date date_change, i.link_kind, i.link_path_url, i.path_to_asset, i.cloud_url, i.cloud_url_org, i.in_trash, it.img_description description, it.img_keywords keywords, ‘0’ as vwidth, ‘0’ as vheight, ‘0’ isalias, ( SELECT so.asset_format FROM raz1_share_options so WHERE i.img_id = so.group_asset_id AND so.folder_id_r = i.folder_id_r AND so.asset_type = ‘img’ AND so.asset_selected = ? ) AS theformat, i.img_filename filename_forsort, cast(i.img_size as decimal(12,0)) size, i.hashtag, fo.folder_name, ‘’ as labels, i.img_width width, i.img_height height, x.xres xres, x.yres yres, x.colorspace colorspace, CASE WHEN NOT (i.img_group is null OR i.img_group=’’) THEN (SELECT expiry_date FROM raz1_images WHERE img_id=i.img_group) ELSE i.expiry_date END expiry_date_actual, ‘X’ as permfolder , concat(i.img_id,’-img’) as listid ,i .img_id AS cs_img_id ,i .img_width AS cs_img_width ,i .img_height AS cs_img_height ,i .img_size AS cs_img_size ,x .category AS cs_img_category ,’’ AS cs_vid_size FROM raz1_images i LEFT JOIN raz1_xmp x ON i.img_id = x.id_r AND i.host_id = x.host_id LEFT JOIN raz1_folders fo ON fo.folder_id = i.folder_id_r AND i.host_id = fo.host_id LEFT JOIN raz1_images_text it ON i.img_id = it.img_id_r AND it.lang_id_r = ? AND i.host_id = it.host_id WHERE i.img_id IN (‘0’,‘32DCFAF0E28E423D8D89B37E7110B52A’,‘5C8A8660B0274644B761BE58FB07B172’,‘116DABE7187246ABA362DD474BF4EDC2’,‘22FE996F3DCE412F8FD6E76EEEFF3287’,‘2F335F65AFE44650846DA080CCDCEA8E’,‘63CE566D457A40B89FBDF10051652D17’,‘353A157E58E24958BB3EA3585526EBDC’,‘6B09CF05B1AD422C815B153F5CE0CCD1’,‘38F1E0B1243146468C134D54DED8BED9’,‘0D0C1E128D414356863ECB11A7503086’,‘26D571EB1445447394DD9369FCCDE3D2’,‘45B4331B01CC4585BF0A06C94141B4C2’,‘4AE9483FDB2F4825886EE284DE82F641’,‘52689EAED32F44A6869D907908CD9F8B’,‘68E75BFD7DD04A638C9491B03F5745C5’,‘8104841553D4438C9C3C4114FC583E01’,‘81B06F22CCF04728BDF2CF278701E138’,‘377D8D8E25514BFAAD16D6E74E1164F5’,‘3CE9105D73F34C28941BBD0F490BF4C6’,‘3E28516B221D4002A2A657B0F8A71758’,‘0467B8DA11B94F0781C8E0EBA7C9878D’,‘611BA6AE8A5941DDA60DBF9FAF61FCFD’,‘639EC3F0092D46A6B195C86BC675C975’,‘66F212BF93DB423BB1EBC6833B60744A’,‘9383C4B1E6BB4E5EA65B920FFADEB82B’) AND i.host_id = ? AND i.in_trash = ? AND CASE WHEN EXISTS (SELECT 1 FROM ct_groups_users WHERE ct_g_u_user_id =‘F12816FA-F80B-4339-8FF9C94A6E9AAF62’ and ct_g_u_grp_id in (‘1’,‘2’)) THEN 1 WHEN EXISTS (SELECT 1 FROM ct_groups_users c, raz1_folders_groups f WHERE ct_g_u_user_id =‘F12816FA-F80B-4339-8FF9C94A6E9AAF62’ AND i.folder_id_r = f.folder_id_r AND c.ct_g_u_grp_id = f.grp_id_r AND grp_permission NOT IN (‘W’,‘X’) AND i.expiry_date < ?) THEN 0 WHEN NOT (i.img_group is null OR i.img_group=’’) THEN CASE WHEN EXISTS (SELECT 1 FROM ct_groups_users c, raz1_folders_groups f WHERE ct_g_u_user_id =‘F12816FA-F80B-4339-8FF9C94A6E9AAF62’ AND i.folder_id_r = f.folder_id_r AND c.ct_g_u_grp_id = f.grp_id_r AND grp_permission NOT IN (‘W’,‘X’) AND (SELECT expiry_date FROM raz1_images WHERE img_id = i.img_group) < ?) THEN 0 ELSE 1 END ELSE 1 END = 1 GROUP BY i.img_id, i.img_filename, i.folder_id_r, i.thumb_extension, i.img_filename_org, i.is_available, i.img_create_time, i.img_change_date, i.link_kind, i.link_path_url, i.path_to_asset, i.cloud_url, i.cloud_url_org, it.img_description, it.img_keywords, i.img_size, i.img_width, i.img_height, x.xres, x.yres, x.colorspace, i.hashtag, fo.folder_name, i.img_group, fo.folder_of_user, fo.folder_owner, i.in_trash, i.img_upc_number, i.expiry_date UNION ALL SELECT / 48461B369B774942AC7891797A9B9900imgSearchAlias */ i.img_id id, i.img_filename filename, ct.folder_id_r, i.img_group groupid, i.thumb_extension ext, i.img_filename_org filename_org, ‘img’ as kind, i.is_available, i.img_create_time date_create, i.img_change_date date_change, i.link_kind, i.link_path_url, i.path_to_asset, i.cloud_url, i.cloud_url_org, i.in_trash, it.img_description description, it.img_keywords keywords, ‘0’ as vwidth, ‘0’ as vheight, ‘1’ isalias, ( SELECT so.asset_format FROM raz1_share_options so WHERE i.img_id = so.group_asset_id AND so.folder_id_r = max(i.folder_id_r) AND so.asset_type = ‘img’ AND so.asset_selected = ? ) AS theformat, i.img_filename filename_forsort, cast(i.img_size as decimal(12,0)) size, i.hashtag, fo.folder_name, ‘’ as labels, i.img_width width, i.img_height height, x.xres xres, x.yres yres, x.colorspace colorspace, CASE WHEN NOT (i.img_group is null OR i.img_group=’’) THEN (SELECT expiry_date FROM raz1_images WHERE img_id=i.img_group) ELSE i.expiry_date END expiry_date_actual, ‘X’ as permfolder , concat(i.img_id,’-img’) as listid ,i .img_id AS cs_img_id ,i .img_width AS cs_img_width ,i .img_height AS cs_img_height ,i .img_size AS cs_img_size ,x .category AS cs_img_category ,’’ AS cs_vid_size FROM raz1_images i INNER JOIN ct_aliases ct ON i.img_id = ct.asset_id_r LEFT JOIN raz1_xmp x ON i.img_id = x.id_r AND i.host_id = x.host_id LEFT JOIN raz1_folders fo ON fo.folder_id = ct.folder_id_r AND i.host_id = fo.host_id LEFT JOIN raz1_images_text it ON i.img_id = it.img_id_r AND it.lang_id_r = ? AND i.host_id = it.host_id WHERE i.img_id IN (‘0’,‘32DCFAF0E28E423D8D89B37E7110B52A’,‘5C8A8660B0274644B761BE58FB07B172’,‘116DABE7187246ABA362DD474BF4EDC2’,‘22FE996F3DCE412F8FD6E76EEEFF3287’,‘2F335F65AFE44650846DA080CCDCEA8E’,‘63CE566D457A40B89FBDF10051652D17’,‘353A157E58E24958BB3EA3585526EBDC’,‘6B09CF05B1AD422C815B153F5CE0CCD1’,‘38F1E0B1243146468C134D54DED8BED9’,‘0D0C1E128D414356863ECB11A7503086’,‘26D571EB1445447394DD9369FCCDE3D2’,‘45B4331B01CC4585BF0A06C94141B4C2’,‘4AE9483FDB2F4825886EE284DE82F641’,‘52689EAED32F44A6869D907908CD9F8B’,‘68E75BFD7DD04A638C9491B03F5745C5’,‘8104841553D4438C9C3C4114FC583E01’,‘81B06F22CCF04728BDF2CF278701E138’,‘377D8D8E25514BFAAD16D6E74E1164F5’,‘3CE9105D73F34C28941BBD0F490BF4C6’,‘3E28516B221D4002A2A657B0F8A71758’,‘0467B8DA11B94F0781C8E0EBA7C9878D’,‘611BA6AE8A5941DDA60DBF9FAF61FCFD’,‘639EC3F0092D46A6B195C86BC675C975’,‘66F212BF93DB423BB1EBC6833B60744A’,‘9383C4B1E6BB4E5EA65B920FFADEB82B’) AND i.host_id = ? AND i.in_trash = ? AND CASE WHEN EXISTS (SELECT 1 FROM ct_groups_users WHERE ct_g_u_user_id =‘F12816FA-F80B-4339-8FF9C94A6E9AAF62’ and ct_g_u_grp_id in (‘1’,‘2’)) THEN 1 WHEN EXISTS (SELECT 1 FROM ct_groups_users c, raz1_folders_groups f WHERE ct_g_u_user_id =‘F12816FA-F80B-4339-8FF9C94A6E9AAF62’ AND ct.folder_id_r = f.folder_id_r AND c.ct_g_u_grp_id = f.grp_id_r AND grp_permission NOT IN (‘W’,‘X’) AND i.expiry_date < ?) THEN 0 WHEN NOT (i.img_group is null OR i.img_group=’’) THEN CASE WHEN EXISTS (SELECT 1 FROM ct_groups_users c, raz1_folders_groups f WHERE ct_g_u_user_id =‘F12816FA-F80B-4339-8FF9C94A6E9AAF62’ AND ct.folder_id_r = f.folder_id_r AND c.ct_g_u_grp_id = f.grp_id_r AND grp_permission NOT IN (‘W’,‘X’) AND (SELECT expiry_date FROM raz1_images WHERE img_id = i.img_group) < ?) THEN 0 ELSE 1 END ELSE 1 END = 1 GROUP BY i.img_id, i.img_filename, ct.folder_id_r, i.thumb_extension, i.img_filename_org, i.is_available, i.img_create_time, i.img_change_date, i.link_kind, i.link_path_url, i.path_to_asset, i.cloud_url, i.cloud_url_org, it.img_description, it.img_keywords, i.img_size, i.img_width, i.img_height, x.xres, x.yres, x.colorspace, i.hashtag, fo.folder_name, i.img_group, fo.folder_of_user, fo.folder_owner, i.in_trash, i.img_upc_number, i.expiry_date ) as t WHERE kind IS NOT NULL AND permfolder IS NOT NULL ORDER BY filename_forsort|

#2

The error lets you know what the issue is on the first line!

#3

Thank you for the feedback. I was hoping for a solution. Seems to be a scripting/SQL issue. The generated SQL includes x.category which is not in the SQL Group by part of

the statement, that much I understand. But what’s the cause?

#4

If you are not paying for support and run your own systems, it’s expected that one knows about the database and operating systems. At least search on your favorite search engine for a solution!

This is NOT a scripting issue but how your MySQL server is configured. As mentioned, the error is described in the first line. Here is a hint "… his is incompatible with sql_mode=only_full_group_by … "

#5

Super that worked. I had no idea this was a configurable option and I presumed it was an SQL statement issue.

Didn’t find it any references here: Recommended MySQL settings

These might be useful for anyone else who encounters this.