SQL error on all reports

seanausti commented 7 years ago

Hi,

I'm getting SQL errors on all report pages (Google Analytics, Metrics per Activity, and Metrics per Location).

Here is one example, from the Google Analytics report:

An exception occurred while executing 'SELECT c0_.id AS id0, c0_.value AS value1, c0_.time AS time2, c1_.id AS id3, c1_.name AS name4, c1_.bundle AS bundle5, c2_.id AS id6, c2_.equalsOperation AS equalsOperation7, c2_.mustValidate AS mustValidate8, c2_.name AS name9, c2_.startDate AS startDate10, c2_.endDate AS endDate11, c2_.interval AS interval12, c2_.intervalStartDate AS intervalStartDate13, c2_.intervalNextRun AS intervalNextRun14, c2_.intervalEndDate AS intervalEndDate15, c2_.intervalEndOccurrence AS intervalEndOccurrence16, c2_.status AS status17, c2_.createdDate AS createdDate18, c2_.modifiedDate AS modifiedDate19, c3_.id AS id20, c3_.trackingId AS trackingId21, c3_.name AS name22, c3_.status AS status23, c3_.createdDate AS createdDate24, c3_.modifiedDate AS modifiedDate25, c0_.operation_id AS operation_id26, c0_.activity_id AS activity_id27, c0_.campaign_id AS campaign_id28, c0_.metric_id AS metric_id29, c2_.parent_id AS parent_id30, c2_.campaign_id AS campaign_id31, c2_.channel_id AS channel_id32, c2_.location_id AS location_id33, c2_.activityModule_id AS activityModule_id34, c2_.assignee AS assignee35, c2_.triggerHook_id AS triggerHook_id36, c3_.channelModule_id AS channelModule_id37 FROM campaignchain_report_analytics_activity_fact c0_ INNER JOIN campaignchain_report_analytics_activity_metric c1_ ON c0_.metric_id = c1_.id INNER JOIN campaignchain_activity c2_ ON c0_.activity_id = c2_.id INNER JOIN campaignchain_channel c3_ ON c2_.channel_id = c3_.id WHERE c0_.campaign_id = ? GROUP BY c0_.metric_id' with params ["1"]:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'campaignchain.c0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I have tried using composer to reinstall each report's package, as well as 'doctrine/orm' and 'doctrine/dbal', but no luck.

Any ideas?

seanausti commented 7 years ago

These errors make the application unusable for me, as I can't evaluate if it will meet our needs without the reporting features.

groganz commented 7 years ago

Did you install dev-master?

Did you start with a fresh install?

Did you connect locations already (e.g. Twitter, Facebook)?

Have any activities been executed already (e.g. a Tweet was posted)?

seanausti commented 7 years ago

Did you install dev-master: Yes

Did you start with a fresh install: I have deleted and re-installed CampaignChain several times due to a number of previous bugs and issues.

Did you connect locations already (e.g. Twitter, Facebook): Yes, I've connected Google Analytics and Twitter.

Have any activities been executed already (e.g. a Tweet was posted): Yes, I have one tweet that was sent out for one campaign.

seanausti commented 7 years ago

I just deleted everything (files, database, ~./composer) and reinstalled from scratch.

The SQL error still occurs as described above.

Here's the stacktrace:

[1] Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'SELECT c0_.id AS id0, c0_.value AS value1, c0_.time AS time2, c1_.id AS id3, c1_.identifier AS identifier4, c1_.image AS image5, c1_.url AS url6, c1_.name AS name7, c1_.status AS status8, c1_.createdDate AS createdDate9, c1_.modifiedDate AS modifiedDate10, c0_.location_id AS location_id11, c0_.metric_id AS metric_id12, c1_.parent_id AS parent_id13, c1_.locationModule_id AS locationModule_id14, c1_.channel_id AS channel_id15, c1_.operation_id AS operation_id16 FROM campaignchain_report_analytics_location_fact c0_ INNER JOIN campaignchain_location c1_ ON c0_.location_id = c1_.id GROUP BY c0_.location_id':

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'campaignchain.c0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    at n/a
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 115

    at Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An exception occurred while executing 'SELECT c0_.id AS id0, c0_.value AS value1, c0_.time AS time2, c1_.id AS id3, c1_.identifier AS identifier4, c1_.image AS image5, c1_.url AS url6, c1_.name AS name7, c1_.status AS status8, c1_.createdDate AS createdDate9, c1_.modifiedDate AS modifiedDate10, c0_.location_id AS location_id11, c0_.metric_id AS metric_id12, c1_.parent_id AS parent_id13, c1_.locationModule_id AS locationModule_id14, c1_.channel_id AS channel_id15, c1_.operation_id AS operation_id16 FROM campaignchain_report_analytics_location_fact c0_ INNER JOIN campaignchain_location c1_ ON c0_.location_id = c1_.id GROUP BY c0_.location_id':

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'campaignchain.c0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by', object(PDOException))
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 128

    at Doctrine\DBAL\DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), 'SELECT c0_.id AS id0, c0_.value AS value1, c0_.time AS time2, c1_.id AS id3, c1_.identifier AS identifier4, c1_.image AS image5, c1_.url AS url6, c1_.name AS name7, c1_.status AS status8, c1_.createdDate AS createdDate9, c1_.modifiedDate AS modifiedDate10, c0_.location_id AS location_id11, c0_.metric_id AS metric_id12, c1_.parent_id AS parent_id13, c1_.locationModule_id AS locationModule_id14, c1_.channel_id AS channel_id15, c1_.operation_id AS operation_id16 FROM campaignchain_report_analytics_location_fact c0_ INNER JOIN campaignchain_location c1_ ON c0_.location_id = c1_.id GROUP BY c0_.location_id', array())
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 855

    at Doctrine\DBAL\Connection->executeQuery('SELECT c0_.id AS id0, c0_.value AS value1, c0_.time AS time2, c1_.id AS id3, c1_.identifier AS identifier4, c1_.image AS image5, c1_.url AS url6, c1_.name AS name7, c1_.status AS status8, c1_.createdDate AS createdDate9, c1_.modifiedDate AS modifiedDate10, c0_.location_id AS location_id11, c0_.metric_id AS metric_id12, c1_.parent_id AS parent_id13, c1_.locationModule_id AS locationModule_id14, c1_.channel_id AS channel_id15, c1_.operation_id AS operation_id16 FROM campaignchain_report_analytics_location_fact c0_ INNER JOIN campaignchain_location c1_ ON c0_.location_id = c1_.id GROUP BY c0_.location_id', array(), array(), null)
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php line 50

    at Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(object(Connection), array(), array())
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 286

    at Doctrine\ORM\Query->_doExecute()
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 794

    at Doctrine\ORM\AbstractQuery->execute(null, '1')
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 574

    at Doctrine\ORM\AbstractQuery->getResult()
        in /home/devadmin/campaignchain/campaignchain/vendor/campaignchain/report-analytics-metrics-per-location/Util/Data.php line 133

    at CampaignChain\Report\Analytics\MetricsPerLocationBundle\Util\Data->getLocationFacts()
        in /home/devadmin/campaignchain/campaignchain/vendor/campaignchain/report-analytics-metrics-per-location/Util/Data.php line 95

    at CampaignChain\Report\Analytics\MetricsPerLocationBundle\Util\Data->getCampaignSeries()
        in /home/devadmin/campaignchain/campaignchain/vendor/campaignchain/report-analytics-metrics-per-location/Controller/PageController.php line 63

    at CampaignChain\Report\Analytics\MetricsPerLocationBundle\Controller\PageController->indexAction(object(Request))
        in  line 

    at call_user_func_array(array(object(PageController), 'indexAction'), array(object(Request)))
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 144

    at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), '1')
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 64

    at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), '1', true)
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/DependencyInjection/ContainerAwareHttpKernel.php line 69

    at Symfony\Component\HttpKernel\DependencyInjection\ContainerAwareHttpKernel->handle(object(Request), '1', true)
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php line 185

    at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
        in /home/devadmin/campaignchain/campaignchain/web/app_dev.php line 23

    at require('/home/devadmin/campaignchain/campaignchain/web/app_dev.php')
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Bundle/FrameworkBundle/Resources/config/router_dev.php line 40

[2] Doctrine\DBAL\Driver\PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'campaignchain.c0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    at n/a
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php line 106

    at Doctrine\DBAL\Driver\PDOConnection->query('SELECT c0_.id AS id0, c0_.value AS value1, c0_.time AS time2, c1_.id AS id3, c1_.identifier AS identifier4, c1_.image AS image5, c1_.url AS url6, c1_.name AS name7, c1_.status AS status8, c1_.createdDate AS createdDate9, c1_.modifiedDate AS modifiedDate10, c0_.location_id AS location_id11, c0_.metric_id AS metric_id12, c1_.parent_id AS parent_id13, c1_.locationModule_id AS locationModule_id14, c1_.channel_id AS channel_id15, c1_.operation_id AS operation_id16 FROM campaignchain_report_analytics_location_fact c0_ INNER JOIN campaignchain_location c1_ ON c0_.location_id = c1_.id GROUP BY c0_.location_id')
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 852

    at Doctrine\DBAL\Connection->executeQuery('SELECT c0_.id AS id0, c0_.value AS value1, c0_.time AS time2, c1_.id AS id3, c1_.identifier AS identifier4, c1_.image AS image5, c1_.url AS url6, c1_.name AS name7, c1_.status AS status8, c1_.createdDate AS createdDate9, c1_.modifiedDate AS modifiedDate10, c0_.location_id AS location_id11, c0_.metric_id AS metric_id12, c1_.parent_id AS parent_id13, c1_.locationModule_id AS locationModule_id14, c1_.channel_id AS channel_id15, c1_.operation_id AS operation_id16 FROM campaignchain_report_analytics_location_fact c0_ INNER JOIN campaignchain_location c1_ ON c0_.location_id = c1_.id GROUP BY c0_.location_id', array(), array(), null)
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php line 50

    at Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(object(Connection), array(), array())
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 286

    at Doctrine\ORM\Query->_doExecute()
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 794

    at Doctrine\ORM\AbstractQuery->execute(null, '1')
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 574

    at Doctrine\ORM\AbstractQuery->getResult()
        in /home/devadmin/campaignchain/campaignchain/vendor/campaignchain/report-analytics-metrics-per-location/Util/Data.php line 133

    at CampaignChain\Report\Analytics\MetricsPerLocationBundle\Util\Data->getLocationFacts()
        in /home/devadmin/campaignchain/campaignchain/vendor/campaignchain/report-analytics-metrics-per-location/Util/Data.php line 95

    at CampaignChain\Report\Analytics\MetricsPerLocationBundle\Util\Data->getCampaignSeries()
        in /home/devadmin/campaignchain/campaignchain/vendor/campaignchain/report-analytics-metrics-per-location/Controller/PageController.php line 63

    at CampaignChain\Report\Analytics\MetricsPerLocationBundle\Controller\PageController->indexAction(object(Request))
        in  line 

    at call_user_func_array(array(object(PageController), 'indexAction'), array(object(Request)))
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 144

    at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), '1')
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 64

    at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), '1', true)
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/DependencyInjection/ContainerAwareHttpKernel.php line 69

    at Symfony\Component\HttpKernel\DependencyInjection\ContainerAwareHttpKernel->handle(object(Request), '1', true)
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php line 185

    at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
        in /home/devadmin/campaignchain/campaignchain/web/app_dev.php line 23

    at require('/home/devadmin/campaignchain/campaignchain/web/app_dev.php')
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Bundle/FrameworkBundle/Resources/config/router_dev.php line 40

[3] PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'campaignchain.c0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    at n/a
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php line 104

    at PDO->query('SELECT c0_.id AS id0, c0_.value AS value1, c0_.time AS time2, c1_.id AS id3, c1_.identifier AS identifier4, c1_.image AS image5, c1_.url AS url6, c1_.name AS name7, c1_.status AS status8, c1_.createdDate AS createdDate9, c1_.modifiedDate AS modifiedDate10, c0_.location_id AS location_id11, c0_.metric_id AS metric_id12, c1_.parent_id AS parent_id13, c1_.locationModule_id AS locationModule_id14, c1_.channel_id AS channel_id15, c1_.operation_id AS operation_id16 FROM campaignchain_report_analytics_location_fact c0_ INNER JOIN campaignchain_location c1_ ON c0_.location_id = c1_.id GROUP BY c0_.location_id')
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php line 104

    at Doctrine\DBAL\Driver\PDOConnection->query('SELECT c0_.id AS id0, c0_.value AS value1, c0_.time AS time2, c1_.id AS id3, c1_.identifier AS identifier4, c1_.image AS image5, c1_.url AS url6, c1_.name AS name7, c1_.status AS status8, c1_.createdDate AS createdDate9, c1_.modifiedDate AS modifiedDate10, c0_.location_id AS location_id11, c0_.metric_id AS metric_id12, c1_.parent_id AS parent_id13, c1_.locationModule_id AS locationModule_id14, c1_.channel_id AS channel_id15, c1_.operation_id AS operation_id16 FROM campaignchain_report_analytics_location_fact c0_ INNER JOIN campaignchain_location c1_ ON c0_.location_id = c1_.id GROUP BY c0_.location_id')
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 852

    at Doctrine\DBAL\Connection->executeQuery('SELECT c0_.id AS id0, c0_.value AS value1, c0_.time AS time2, c1_.id AS id3, c1_.identifier AS identifier4, c1_.image AS image5, c1_.url AS url6, c1_.name AS name7, c1_.status AS status8, c1_.createdDate AS createdDate9, c1_.modifiedDate AS modifiedDate10, c0_.location_id AS location_id11, c0_.metric_id AS metric_id12, c1_.parent_id AS parent_id13, c1_.locationModule_id AS locationModule_id14, c1_.channel_id AS channel_id15, c1_.operation_id AS operation_id16 FROM campaignchain_report_analytics_location_fact c0_ INNER JOIN campaignchain_location c1_ ON c0_.location_id = c1_.id GROUP BY c0_.location_id', array(), array(), null)
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php line 50

    at Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(object(Connection), array(), array())
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 286

    at Doctrine\ORM\Query->_doExecute()
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 794

    at Doctrine\ORM\AbstractQuery->execute(null, '1')
        in /home/devadmin/campaignchain/campaignchain/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 574

    at Doctrine\ORM\AbstractQuery->getResult()
        in /home/devadmin/campaignchain/campaignchain/vendor/campaignchain/report-analytics-metrics-per-location/Util/Data.php line 133

    at CampaignChain\Report\Analytics\MetricsPerLocationBundle\Util\Data->getLocationFacts()
        in /home/devadmin/campaignchain/campaignchain/vendor/campaignchain/report-analytics-metrics-per-location/Util/Data.php line 95

    at CampaignChain\Report\Analytics\MetricsPerLocationBundle\Util\Data->getCampaignSeries()
        in /home/devadmin/campaignchain/campaignchain/vendor/campaignchain/report-analytics-metrics-per-location/Controller/PageController.php line 63

    at CampaignChain\Report\Analytics\MetricsPerLocationBundle\Controller\PageController->indexAction(object(Request))
        in  line 

    at call_user_func_array(array(object(PageController), 'indexAction'), array(object(Request)))
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 144

    at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), '1')
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 64

    at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), '1', true)
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/DependencyInjection/ContainerAwareHttpKernel.php line 69

    at Symfony\Component\HttpKernel\DependencyInjection\ContainerAwareHttpKernel->handle(object(Request), '1', true)
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php line 185

    at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
        in /home/devadmin/campaignchain/campaignchain/web/app_dev.php line 23

    at require('/home/devadmin/campaignchain/campaignchain/web/app_dev.php')
        in /home/devadmin/campaignchain/campaignchain/vendor/symfony/symfony/src/Symfony/Bundle/FrameworkBundle/Resources/config/router_dev.php line 40

seanausti commented 7 years ago

I just figured out the problem. I had to login to MySQL and set the global variable SQL_MODE from ONLY_FULL_GROUP_BY to ''.

USE campaignchain;
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Now the query can go ahead without error.

CampaignChain / campaignchain

SQL error on all reports #398