Mautic 500 | SQLSTATE[42000] | GROUP BY

Written by
on November 28, 2017

Recently we had a new installation on a new Debian 9 / MariaDB server crash with a 500 error immediately after install. The nginx logs didn’t show any problems, but the Mautic log showed the following:

[2017-11-29 03:23:27] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: “An exception occurred while executing ‘SELECT COUNT(t.date_added) AS leads, ll.id, ll.name, ll.alias FROM lead_lists_leads t INNER JOIN lead_lists ll ON ll.id = t.leadlist_id WHERE (ll.is_published = ?) AND (t.date_added BETWEEN ? AND ?) GROUP BY ll.id ORDER BY leads DESC LIMIT 9’ with params [true, “2017-10-30 00:00:00”, “2017-11-29 03:23:27″]: SQLSTATE[42000]: Syntax error or access violation: 1055 ‘sitename.ll.name’ isn’t in GROUP BY” at /var/www/theatereleven.com/public_html/studio/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 COUNT(t.date_added) AS leads, ll.id, ll.name, ll.alias FROM lead_lists_leads t INNER JOIN lead_lists ll ON ll.id = t.leadlist_id WHERE (ll.is_published = ?) AND (t.date_added BETWEEN ? AND ?) GROUP BY ll.id ORDER BY leads DESC LIMIT 9’ with params [true, \”2017-10-30 00:00:00\”, \”2017-11-29 03:23:27\”]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1055 ‘sitename.ll.name’ isn’t in GROUP BY at /var/www/theatereleven.com/public_html/studio/vendor/doctrine/dbal/lib/
Doctrine/DBAL/Driver/AbstractMySQLDriver.php:115, Doctrine\\DBAL\\Driver\\PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 ‘sitename.ll.name’ isn’t in GROUP BY at /var/www/theatereleven.com/public_html/studio/vendor/doctrine/dbal/lib/
Doctrine/DBAL/Driver/PDOStatement.php:107, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 ‘sitename.ll.name’ isn’t in GROUP BY at /var/www/theatereleven.com/public_html/studio/vendor/doctrine/dbal/lib/
Doctrine/DBAL/Driver/PDOStatement.php:105)”} []

Upon searching, we found a Mautic github thread discussing: https://github.com/mautic/mautic/issues/3969.

We had to add the following line to our mariadb.cnf (my.cnf) file and restart the server:

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Once this was in place, problem solved!

 

Leave a Reply

Your email address will not be published. Required fields are marked *