Dashboard Unaccessible after editing Widgets - Error 500 (not #2054)

[Baregon]

Please DO NOT report security vulnerabilities here. Send them to security@mautic.com instead.

What type of report is this:

Q	A
Bug report?	Yes

Description:

After editing the dashboard widgets, the dashboard page is no longer accessible (Internal Server Error 500) by clicking links inside mautic, or by navigating to the dashboard url in browser. All other pages are accessible. I have referenced this error and it is not the "change links to contacts error" in issue #2027 #2027

If a bug:

Q	A
Mautic version	2.15.0
PHP version	7.2.15

Steps to reproduce:

1. Add Dashboard Widget "Emails Sent"

Log errors:

I left my full path due to part of this log showing a partial domain in the error and unsure if that is related.

mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'SELECT s.id, s.lead_id, s.email_address, s.is_read, s.email_id, s.date_sent, s.date_read, e.name AS email_name, COUNT(ph.id) AS link_hits, c.id AS company_id, c.companyname AS company_name, campaign.id AS campaign_id, campaign.name AS campaign_name, ll.id AS segment_id, ll.name AS segment_name FROM email_stats s LEFT JOIN emails e ON s.email_id = e.id LEFT JOIN page_hits ph ON ph.source = "email" and ph.source_id = s.email_id and ph.lead_id = s.lead_id LEFT JOIN companies_leads cl ON (s.lead_id = cl.lead_id) AND (cl.is_primary = 1) LEFT JOIN companies c ON cl.company_id = c.id LEFT JOIN campaign_events ce ON s.source = "campaign.event" and s.source_id = ce.id LEFT JOIN lead_lists ll ON s.list_id = ll.id LEFT JOIN campaigns campaign ON ce.campaign_id = campaign.id WHERE (e.created_by = ?) AND (s.date_sent BETWEEN ? AND ?) GROUP BY s.id ORDER BY s.id DESC LIMIT 6' with params [1, "2019-01-23 00:00:00", "2019-02-23 00:00:00"]: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cttech_mautic.c.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/cttechcorp/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 115 {"exception":"[object] (Doctrine\DBAL\Exception\DriverException(code: 0): An exception occurred while executing 'SELECT s.id, s.lead_id, s.email_address, s.is_read, s.email_id, s.date_sent, s.date_read, e.name AS email_name, COUNT(ph.id) AS link_hits, c.id AS company_id, c.companyname AS company_name, campaign.id AS campaign_id, campaign.name AS campaign_name, ll.id AS segment_id, ll.name AS segment_name FROM email_stats s LEFT JOIN emails e ON s.email_id = e.id LEFT JOIN page_hits ph ON ph.source = "email" and ph.source_id = s.email_id and ph.lead_id = s.lead_id LEFT JOIN companies_leads cl ON (s.lead_id = cl.lead_id) AND (cl.is_primary = 1) LEFT JOIN companies c ON cl.company_id = c.id LEFT JOIN campaign_events ce ON s.source = "campaign.event" and s.source_id = ce.id LEFT JOIN lead_lists ll ON s.list_id = ll.id LEFT JOIN campaigns campaign ON ce.campaign_id = campaign.id WHERE (e.created_by = ?) AND (s.date_sent BETWEEN ? AND ?) GROUP BY s.id ORDER BY s.id DESC LIMIT 6' with params [1, "2019-01-23 00:00:00", "2019-02-23 00:00:00"]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1055 Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cttech_mautic.c.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/cttechcorp/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:115, Doctrine\DBAL\Driver\PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cttech_mautic.c.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/cttechcorp/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:107, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cttech_mautic.c.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/cttechcorp/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:105)

[Baregon]

Found a fix for errors:

sudo nano

/etc/mysql/my.cnf
or
/etc/mysql/mysql.conf.d/mysqld.cnf

Add this to the end of the file

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sudo service mysql restart to restart MySQL

This will disable ONLY_FULL_GROUP_BY for ALL users

Seems to be resolved now.