This topic contains 1 reply, has 2 voices, and was last updated by Sergey 7 years, 6 months ago.
- Topic
Hi,
I’m seeing an SQL query error when opening emails (“My Emails” feature) on MySQL 5.7. This is on OroCRM 1.10.2 code. Not exactly sure how it got to this state as its not easy to reproduce. The temporary workaround was to restart the mySQL 5.7 with1sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONThe more permanent solution is possibly to make the SQL query in OROCrm compliant with the SQL-99 changes introduced in MySQL 5.7.
1[2016-09-12 21:47:40] request.CRITICAL: Uncaught PHP Exception Twig_Error_Runtime: "An exception has been thrown during the rendering of a template ("An exception occurred while executing 'SELECT o0_.body AS body_0, (SELECT COUNT(o1_.id) AS dctrn__1 FROM oro_email o1_ WHERE o1_.thread_id = o2_.thread_id) AS sclr_1, CASE WHEN o2_.thread_id IS NULL THEN o0_.has_attachments WHEN EXISTS (SELECT 1 AS sclr_3 FROM oro_email_attachment o3_ INNER JOIN oro_email_body o4_ ON o3_.body_id = o4_.id INNER JOIN oro_email o5_ ON o4_.id = o5_.email_body_id INNER JOIN oro_email_thread o6_ ON o5_.thread_id = o6_.id WHERE o6_.id = o2_.thread_id) THEN 1 ELSE 0 END AS sclr_2, CASE WHEN o7_.is_seen = 1 THEN 0 ELSE 1 END AS sclr_4, o7_.id AS id_5, o2_.id AS id_6, o2_.created AS created_7, o2_.subject AS subject_8, o2_.from_name AS from_name_9, o2_.sent AS sent_10, o2_.importance AS importance_11, o2_.internaldate AS internaldate_12, o2_.is_head AS is_head_13, o2_.message_id AS message_id_14, o2_.multi_message_id AS multi_message_id_15, o2_.x_message_id AS x_message_id_16, o2_.x_thread_id AS x_thread_id_17, o2_.refs AS refs_18, o2_.acceptLanguageHeader AS acceptLanguageHeader_19, o2_.body_synced AS body_synced_20, o8_.id AS id_21, o8_.name AS name_22, o8_.type AS type_23, o9_.id AS id_24, o9_.type AS type_25, o7_.organization_id AS organization_id_26, o7_.user_owner_id AS user_owner_id_27, o7_.mailbox_owner_id AS mailbox_owner_id_28, o7_.origin_id AS origin_id_29, o7_.email_id AS email_id_30, o2_.thread_id AS thread_id_31, o2_.email_body_id AS email_body_id_32, o2_.from_email_address_id AS from_email_address_id_33, o8_.email_id AS email_id_34, o8_.email_address_id AS email_address_id_35, o9_.parent_folder_id AS parent_folder_id_36, o9_.origin_id AS origin_id_37 FROM oro_email_user o7_ LEFT JOIN oro_email o2_ ON o7_.email_id = o2_.id LEFT JOIN oro_email_mailbox o10_ ON o7_.mailbox_owner_id = o10_.id LEFT JOIN oro_email_recipient o8_ ON o2_.id = o8_.email_id LEFT JOIN oro_email_user_folders o11_ ON o7_.id = o11_.email_user_id LEFT JOIN oro_email_folder o9_ ON o9_.id = o11_.folder_id LEFT JOIN oro_email_origin o12_ ON o9_.origin_id = o12_.id AND o12_.name IN ('emailorigin', 'internalemailorigin', 'useremailorigin') LEFT JOIN oro_email_body o0_ ON o2_.email_body_id = o0_.id WHERE o12_.isActive = 1 AND o2_.is_head = 1 AND ((o7_.user_owner_id = ? AND o7_.organization_id = ?) OR o7_.mailbox_owner_id IN (?)) AND o7_.organization_id = 1 GROUP BY o2_.sent, o7_.id ORDER BY o2_.sent DESC LIMIT 1000 OFFSET 0' with params [1, 1, 1]: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #21 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sss_oroprod_db.o8_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by") in "OroEntityPaginationBundle:Placeholder:entityPagination.html.twig" at line 7." at /var/www/html/orocrm/vendor/twig/twig/lib/Twig/Template.php line 402 {"exception":"[object] (Twig_Error_Runtime: An exception has been thrown during the rendering of a template (\"An exception occurred while executing 'SELECT o0_.body AS body_0, (SELECT COUNT(o1_.id) AS dctrn__1 FROM oro_email o1_ WHERE o1_.thread_id = o2_.thread_id) AS sclr_1, CASE WHEN o2_.thread_id IS NULL THEN o0_.has_attachments WHEN EXISTS (SELECT 1 AS sclr_3 FROM oro_email_attachment o3_ INNER JOIN oro_email_body o4_ ON o3_.body_id = o4_.id INNER JOIN oro_email o5_ ON o4_.id = o5_.email_body_id INNER JOIN oro_email_thread o6_ ON o5_.thread_id = o6_.id WHERE o6_.id = o2_.thread_id) THEN 1 ELSE 0 END AS sclr_2, CASE WHEN o7_.is_seen = 1 THEN 0 ELSE 1 END AS sclr_4, o7_.id AS id_5, o2_.id AS id_6, o2_.created AS created_7, o2_.subject AS subject_8, o2_.from_name AS from_name_9, o2_.sent AS sent_10, o2_.importance AS importance_11, o2_.internaldate AS internaldate_12, o2_.is_head AS is_head_13, o2_.message_id AS message_id_14, o2_.multi_message_id AS multi_message_id_15, o2_.x_message_id AS x_message_id_16, o2_.x_thread_id AS x_thread_id_17, o2_.refs AS refs_18, o2_.acceptLanguageHeader AS acceptLanguageHeader_19, o2_.body_synced AS body_synced_20, o8_.id AS id_21, o8_.name AS name_22, o8_.type AS type_23, o9_.id AS id_24, o9_.type AS type_25, o7_.organization_id AS organization_id_26, o7_.user_owner_id AS user_owner_id_27, o7_.mailbox_owner_id AS mailbox_owner_id_28, o7_.origin_id AS origin_id_29, o7_.email_id AS email_id_30, o2_.thread_id AS thread_id_31, o2_.email_body_id AS email_body_id_32, o2_.from_email_address_id AS from_email_address_id_33, o8_.email_id AS email_id_34, o8_.email_address_id AS email_address_id_35, o9_.parent_folder_id AS parent_folder_id_36, o9_.origin_id AS origin_id_37 FROM oro_email_user o7_ LEFT JOIN oro_email o2_ ON o7_.email_id = o2_.id LEFT JOIN oro_email_mailbox o10_ ON o7_.mailbox_owner_id = o10_.id LEFT JOIN oro_email_recipient o8_ ON o2_.id = o8_.email_id LEFT JOIN oro_email_user_folders o11_ ON o7_.id = o11_.email_user_id LEFT JOIN oro_email_folder o9_ ON o9_.id = o11_.folder_id LEFT JOIN oro_email_origin o12_ ON o9_.origin_id = o12_.id AND o12_.name IN ('emailorigin', 'internalemailorigin', 'useremailorigin') LEFT JOIN oro_email_body o0_ ON o2_.email_body_id = o0_.id WHERE o12_.isActive = 1 AND o2_.is_head = 1 AND ((o7_.user_owner_id = ? AND o7_.organization_id = ?) OR o7_.mailbox_owner_id IN (?)) AND o7_.organization_id = 1 GROUP BY o2_.sent, o7_.id ORDER BY o2_.sent DESC LIMIT 1000 OFFSET 0' with params [1, 1, 1]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1055 Expression #21 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sss_oroprod_db.o8_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\") in \"OroEntityPaginationBundle:Placeholder:entityPagination.html.twig\" at line 7. at /var/www/html/orocrm/vendor/twig/twig/lib/Twig/Template.php:402, Doctrine\\DBAL\\Exception\\DriverException: An exception occurred while executing 'SELECT o0_.body AS body_0, (SELECT COUNT(o1_.id) AS dctrn__1 FROM oro_email o1_ WHERE o1_.thread_id = o2_.thread_id) AS sclr_1, CASE WHEN o2_.thread_id IS NULL THEN o0_.has_attachments WHEN EXISTS (SELECT 1 AS sclr_3 FROM oro_email_attachment o3_ INNER JOIN oro_email_body o4_ ON o3_.body_id = o4_.id INNER JOIN oro_email o5_ ON o4_.id = o5_.email_body_id INNER JOIN oro_email_thread o6_ ON o5_.thread_id = o6_.id WHERE o6_.id = o2_.thread_id) THEN 1 ELSE 0 END AS sclr_2, CASE WHEN o7_.is_seen = 1 THEN 0 ELSE 1 END AS sclr_4, o7_.id AS id_5, o2_.id AS id_6, o2_.created AS created_7, o2_.subject AS subject_8, o2_.from_name AS from_name_9, o2_.sent AS sent_10, o2_.importance AS importance_11, o2_.internaldate AS internaldate_12, o2_.is_head AS is_head_13, o2_.message_id AS message_id_14, o2_.multi_message_id AS multi_message_id_15, o2_.x_message_id AS x_message_id_16, o2_.x_thread_id AS x_thread_id_17, o2_.refs AS refs_18, o2_.acceptLanguageHeader AS acceptLanguageHeader_19, o2_.body_synced AS body_synced_20, o8_.id AS id_21, o8_.name AS name_22, o8_.type AS type_23, o9_.id AS id_24, o9_.type AS type_25, o7_.organization_id AS organization_id_26, o7_.user_owner_id AS user_owner_id_27, o7_.mailbox_owner_id AS mailbox_owner_id_28, o7_.origin_id AS origin_id_29, o7_.email_id AS email_id_30, o2_.thread_id AS thread_id_31, o2_.email_body_id AS email_body_id_32, o2_.from_email_address_id AS from_email_address_id_33, o8_.email_id AS email_id_34, o8_.email_address_id AS email_address_id_35, o9_.parent_folder_id AS parent_folder_id_36, o9_.origin_id AS origin_id_37 FROM oro_email_user o7_ LEFT JOIN oro_email o2_ ON o7_.email_id = o2_.id LEFT JOIN oro_email_mailbox o10_ ON o7_.mailbox_owner_id = o10_.id LEFT JOIN oro_email_recipient o8_ ON o2_.id = o8_.email_id LEFT JOIN oro_email_user_folders o11_ ON o7_.id = o11_.email_user_id LEFT JOIN oro_email_folder o9_ ON o9_.id = o11_.folder_id LEFT JOIN oro_email_origin o12_ ON o9_.origin_id = o12_.id AND o12_.name IN ('emailorigin', 'internalemailorigin', 'useremailorigin') LEFT JOIN oro_email_body o0_ ON o2_.email_body_id = o0_.id WHERE o12_.isActive = 1 AND o2_.is_head = 1 AND ((o7_.user_owner_id = ? AND o7_.organization_id = ?) OR o7_.mailbox_owner_id IN (?)) AND o7_.organization_id = 1 GROUP BY o2_.sent, o7_.id ORDER BY o2_.sent DESC LIMIT 1000 OFFSET 0' with params [1, 1, 1]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1055 Expression #21 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sss_oroprod_db.o8_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /var/www/html/orocrm/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:115, Doctrine\\DBAL\\Driver\\PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #21 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sss_oroprod_db.o8_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /var/www/html/orocrm/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:93, PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #21 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sss_oroprod_db.o8_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /var/www/html/orocrm/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:91)"} []Can you please look in possibility of a fix in near future?
The forum ‘OroCRM – Installation/Technical Issues or Problems’ is closed to new topics and replies.