export does not work under MySQL 5.7.5+
Closed this issue · 7 comments
In the newest MySQL (starting from 5.7.5) and MariaDB versions, the following config is set by default:
sql_mode=only_full_group_by
This causes the following error, when trying to export a lead:
Doctrine\DBAL\Exception\DriverException:
An exception occurred while executing 'SELECT * FROM (
SELECT
tl_lead_data.field_id AS id,
IFNULL(tl_form_field.name, tl_lead_data.name) AS name,
IF(tl_form_field.label IS NULL OR tl_form_field.label='', tl_lead_data.name, tl_form_field.label) AS label,
tl_form_field.type,
tl_form_field.options,
tl_lead_data.field_id,
tl_lead_data.master_id,
tl_lead_data.sorting
FROM tl_lead_data
LEFT JOIN tl_form_field ON tl_form_field.id=tl_lead_data.field_id
LEFT JOIN tl_lead ON tl_lead_data.pid=tl_lead.id
WHERE tl_lead.master_id=2 AND tl_lead_data.field_id IN (0,14,16,19,22,26,40,43,41,44,46)
ORDER BY tl_lead.master_id!=tl_lead.form_id
) result_set
GROUP BY field_id
ORDER BY FIND_IN_SET(field_id, '0,14,16,19,22,26,40,43,41,44,46')':
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'result_set.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:121
at Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('…', object(PDOException))
(vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:176)
at Doctrine\DBAL\DBALException::wrapException(object(Driver), object(PDOException), '…')
(vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:150)
at Doctrine\DBAL\DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), '…')
(vendor/contao/core-bundle/src/Resources/contao/library/Contao/Database/Statement.php:284)
at Contao\Database\Statement->query()
(vendor/contao/core-bundle/src/Resources/contao/library/Contao/Database/Statement.php:257)
at Contao\Database\Statement->execute(2)
(vendor/terminal42/contao-leads/library/Leads/DataCollector.php:218)
at Leads\DataCollector->getFieldsData()
(vendor/terminal42/contao-leads/library/Leads/DataCollector.php:290)
at Leads\DataCollector->getHeaderFields()
(vendor/terminal42/contao-leads/library/Leads/Exporter/AbstractExporter.php:113)
at Leads\Exporter\AbstractExporter->prepareDefaultHeaderFields(object(Result), object(DataCollector))
(vendor/terminal42/contao-leads/library/Leads/Exporter/Csv.php:49)
at Leads\Exporter\Csv->export(object(Result), null)
(vendor/terminal42/contao-leads/library/Leads/Leads.php:361)
at Leads\Leads::export('1', null)
(vendor/terminal42/contao-leads/dca/tl_lead.php:494)
at tl_lead->exportAndCatchExceptions('1', null)
(vendor/terminal42/contao-leads/dca/tl_lead.php:362)
at tl_lead->export(object(DC_Table))
(vendor/contao/core-bundle/src/Resources/contao/classes/Backend.php:437)
at Contao\Backend->getBackendModule('lead', null)
(vendor/contao/core-bundle/src/Resources/contao/controllers/BackendMain.php:131)
at Contao\BackendMain->run()
(vendor/contao/core-bundle/src/Controller/BackendController.php:55)
at Contao\CoreBundle\Controller\BackendController->mainAction()
at call_user_func_array(array(object(BackendController), 'mainAction'), array())
(vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php:153)
at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
(vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php:68)
at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
(vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php:171)
at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
(web/app_dev.php:62)
It appears the contao/newsletter-bundle has the same problem: https://community.contao.org/de/showthread.php?68265-Newletter-Versand-schlägt-fehl
That's correct, can you provide a fix for that?
One solution would be to use
$data = array();
$db = \Database::getInstance()->prepare("
- SELECT * (
+ SELECT id, ANY_VALUE(name), ANY_VALUE(label), type, options, field_id, ANY_VALUE(master_id), ANY_VALUE(sorting) FROM (
SELECT
tl_lead_data.field_id AS id,
IFNULL(tl_form_field.name, tl_lead_data.name) AS name,
IF(tl_form_field.label IS NULL OR tl_form_field.label='', tl_lead_data.name, tl_form_field.label) AS label,
tl_form_field.type,
tl_form_field.options,
tl_lead_data.field_id,
tl_lead_data.master_id,
tl_lead_data.sorting
FROM tl_lead_data
LEFT JOIN tl_form_field ON tl_form_field.id=tl_lead_data.field_id
LEFT JOIN tl_lead ON tl_lead_data.pid=tl_lead.id
WHERE " . implode(' AND ', $where) . "
ORDER BY tl_lead.master_id!=tl_lead.form_id
) result_set
GROUP BY field_id
ORDER BY " . (!empty($this->fieldIds) ? \Database::getInstance()->findInSet('field_id', $this->fieldIds) : 'sorting')
)->execute($this->formId);but that does not seem very elegant.
Using ANY_VALUE is not a good solution I guess. It chooses - as it says - any value of the multiple rows that are returned before GROUP BY was applied. I guess the whole query has to be rewritten/optimized and maybe even split in multiple queries.
Using ANY_VALUE is not a good solution I guess.
Yes, but that's what's happening right now anyway automatically (when using MySQL <5.7.5). Imho it's fine to do that, if you know you can rely on it.
Actually I have a different opinion about this issue. The new MySQL setting raised an issue in the query to the surface and it should be fixed properly instead of restoring the "old" behavior.
We have also Problems with the new Option sql_mode=only_full_group_by
Is there any proper solution?
Yes, the proper solution would be to rewrite the query (not using ANY()) or potentially split it up in multiple queries. The one that's in place right now is falsy so it has to be fixed by whomever finds time to do so 😄