Newsletter::send does not work with MySQL 5.7.5+
Closed this issue · 3 comments
As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY which means when you are grouping rows and then selecting something out of that groups, you need to explicitly say which row should that selection be made from.
https://stackoverflow.com/a/38551525/374996
In Contao, the following error will occur with MySQL 5.7.5+
Fatal error: Uncaught exception Exception with message Query error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_name.r.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SELECT *, r.email FROM tl_newsletter_recipients r LEFT JOIN tl_member m ON(r.email=m.email) WHERE r.pid='1' AND r.active=1 GROUP BY r.email ORDER BY r.email LIMIT 0,10) thrown in system/modules/core/library/Contao/Database/Statement.php on line 295
#0 system/modules/core/library/Contao/Database/Statement.php(264): Contao\Database\Statement->query()
#1 system/modules/newsletter/classes/Newsletter.php(149): Contao\Database\Statement->execute('1')
#2 system/modules/core/classes/Backend.php(430): Contao\Newsletter->send(Object(Contao\DC_Table))
#3 system/modules/core/controllers/BackendMain.php(131): Contao\Backend->getBackendModule('newsletter')
#4 contao/main.php(20): Contao\BackendMain->run()
#5 {main}
See also terminal42/contao-leads#49
The problematic query is this:
@contao/developers IMO the GROUP BY r.email can be omitted. It is there to prevent sending a newsletter several times to the same recipient, but since we have improved the CSV import to recognize existing entries, it is no longer possible to subscribe the same e-mail address to the same channel multiple times.
Couldn’t we add an unique index for (pid, email) to prevent multiple addresses?