Admin page "Pages" gives 500 error, clean installation

[Caroga]

Hi,

I've been trying for some time to get this to work but all my attempts go without success. I did managed to run the project within docker after several hours, which is good.

Anyway, here are some more details:

• Installation all goes without errors.
• Other pages are working: media, form submissions, settings.
• 500 error translates to log as showed below.

What would I be missing?

[2016-03-13 18:10:09] app.DEBUG: Loading translation resources from cache file. [] {"file":"/var/www/symfony/vendor/kunstmaan/bundles-cms/src/Kunstmaan/TranslatorBundle/Service/Translator/ResourceCacher.php","line":41,"class":"Kunstmaan\TranslatorBundle\Service\Translator\ResourceCacher","function":"getCachedResources"} [2016-03-13 18:10:09] request.INFO: Matched route "KunstmaanNodeBundle_nodes". {"route_parameters":{"_controller":"Kunstmaan\NodeBundle\Controller\NodeAdminController::indexAction","_locale":"en","_route":"KunstmaanNodeBundle_nodes"},"request_uri":"http://symfony.dev/en/admin/nodes/"} {"file":"/var/www/symfony/app/cache/prod/classes.php","line":2589,"class":"Symfony\Component\HttpKernel\EventListener\RouterListener","function":"onKernelRequest"} [2016-03-13 18:10:09] security.DEBUG: Read existing security token from the session. {"key":"_security_main"} {"file":"/var/www/symfony/vendor/symfony/symfony/src/Symfony/Component/Security/Http/Firewall/ContextListener.php","line":87,"class":"Symfony\Component\Security\Http\Firewall\ContextListener","function":"handle"} [2016-03-13 18:10:09] security.DEBUG: User was reloaded from a user provider. {"username":"admin","provider":"FOS\UserBundle\Security\UserProvider"} {"file":"/var/www/symfony/vendor/symfony/symfony/src/Symfony/Component/Security/Http/Firewall/ContextListener.php","line":159,"class":"Symfony\Component\Security\Http\Firewall\ContextListener","function":"refreshUser"} [2016-03-13 18:10:09] request.CRITICAL: Uncaught PHP Exception Twig_Error_Runtime: "An exception has been thrown during the rendering of a template ("An exception occurred while executing 'SELECT n.id, n.parent_id AS parent, t.url, t.id AS nt_id, IF(t.weight IS NULL, v.weight, t.weight) AS weight, IF(t.title IS NULL, v.title, t.title) AS title, IF(t.online IS NULL, 0, t.online) AS online, n.hidden_from_nav AS hidden, n.ref_entity_name AS ref_entity_name FROM kuma_nodes n LEFT JOIN kuma_node_translations t ON (t.node_id = n.id AND t.lang = :lang) LEFT JOIN (SELECT lang, title, weight, node_id, url FROM kuma_node_translations GROUP BY node_id ORDER BY id ASC) v ON (v.node_id = n.id AND v.lang <> :lang) INNER JOIN (SELECT DISTINCT o.object_identifier as id FROM wijkbbq.acl_object_identities as o INNER JOIN wijkbbq.acl_classes c ON c.id = o.class_id LEFT JOIN wijkbbq.acl_entries e ON ( e.class_id = o.class_id AND (e.object_identity_id = o.id OR e.object_identity_id IS NULL) ) LEFT JOIN wijkbbq.acl_security_identities s ON ( s.id = e.security_identity_id ) WHERE c.class_type = "Kunstmaan\NodeBundle\Entity\Node" AND (s.identifier = "IS_AUTHENTICATED_ANONYMOUSLY" OR s.identifier = "ROLE_SUPER_ADMIN" OR s.identifier = "ROLE_PERMISSIONMANAGER" OR s.identifier = "ROLE_ADMIN" OR s.identifier = "ROLE_ALLOWED_TOSWITCH" OR s.identifier = "Kunstmaan\AdminBundle\Entity\User-admin") AND e.mask & 1 > 0) perms ON perms_.id = n.id WHERE n.deleted = 0 GROUP BY n.id ORDER BY t.weight ASC, t.title ASC' with params ["en"]: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wijkbbq.kuma_node_translations.lang' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by") in "KunstmaanAdminBundle:Default:app_header.html.twig" at line 35." at /var/www/symfony/app/cache/prod/classes.php line 5438 {"exception":"[object](Twig_Error_Runtime%28code: 0%29: An exception has been thrown during the rendering of a template %28"An exception occurred while executing) in \"KunstmaanAdminBundle:Default:app_header.html.twig\" at line 35. at /var/www/symfony/app/cache/prod/classes.php:5438, Doctrine\DBAL\Exception\DriverException(code: 0): An exception occurred while executing 'SELECT n.id, n.parent_id AS parent, t.url, t.id AS nt_id,\nIF(t.weight IS NULL, v.weight, t.weight) AS weight,\nIF(t.title IS NULL, v.title, t.title) AS title,\nIF(t.online IS NULL, 0, t.online) AS online,\nn.hidden_from_nav AS hidden,\nn.ref_entity_name AS ref_entity_name FROM kuma_nodes n LEFT JOIN kuma_node_translations t ON (t.node_id = n.id AND t.lang = :lang) LEFT JOIN (SELECT lang, title, weight, node_id, url FROM kuma_node_translations GROUP BY node_id ORDER BY id ASC) v ON (v.node_id = n.id AND v.lang <> :lang) INNER JOIN (SELECT DISTINCT o.object_identifier as id FROM wijkbbq.acl_object_identities as o\nINNER JOIN wijkbbq.acl_classes c ON c.id = o.class_id\nLEFT JOIN wijkbbq.acl_entries e ON (\n e.class_id = o.class_id AND (e.object_identity_id = o.id\n OR e.object_identity_id IS NULL)\n)\nLEFT JOIN wijkbbq.acl_security_identities s ON (\ns.id = e.security_identity_id\n)\nWHERE c.class_type = \"Kunstmaan\NodeBundle\Entity\Node\"\nAND (s.identifier = \"IS_AUTHENTICATED_ANONYMOUSLY\" OR s.identifier = \"ROLE_SUPER_ADMIN\" OR s.identifier = \"ROLE_PERMISSIONMANAGER\" OR s.identifier = \"ROLE_ADMIN\" OR s.identifier = \"ROLE_ALLOWED_TOSWITCH\" OR s.identifier = \"Kunstmaan\AdminBundle\Entity\User-admin\")\nAND e.mask & 1 > 0) perms ON perms_.id = n.id WHERE n.deleted = 0 GROUP BY n.id ORDER BY t.weight ASC, t.title ASC' with params [\"en\"]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wijkbbq.kuma_node_translations.lang' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /var/www/symfony/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 #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wijkbbq.kuma_node_translations.lang' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /var/www/symfony/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:93, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wijkbbq.kuma_node_translations.lang' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /var/www/symfony/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:91)"} {"user":{"username":"admin","roles":{"0":"ROLE_SUPER_ADMIN","1":"ROLE_PERMISSIONMANAGER","2":"ROLE_ADMIN","4":"ROLE_USER"},"is_account_non_expired":true,"is_account_non_locked":true,"is_credentials_non_expired":true,"is_enabled":true},"file":"/var/www/symfony/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/EventListener/ExceptionListener.php","line":89,"class":"Symfony\Component\HttpKernel\EventListener\ExceptionListener","function":"logException"} [2016-03-13 18:10:11] security.DEBUG: Stored the security token in the session. {"key":"_security_main"} {"user":{"username":"admin","roles":{"0":"ROLE_SUPER_ADMIN","1":"ROLE_PERMISSIONMANAGER","2":"ROLE_ADMIN","4":"ROLE_USER"},"is_account_non_expired":true,"is_account_non_locked":true,"is_credentials_non_expired":true,"is_enabled":true},"file":"/var/www/symfony/vendor/symfony/symfony/src/Symfony/Component/Security/Http/Firewall/ContextListener.php","line":132,"class":"Symfony\Component\Security\Http\Firewall\ContextListener","function":"onKernelResponse"}

==> ../docker-symfony/logs/nginx/symfony_access.log <== 172.17.0.1 - - [13/Mar/2016:18:10:11 +0000] "GET /en/admin/nodes/ HTTP/1.1" 500 4130 "http://symfony.dev/en/admin/formsubmissions/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.97 Safari/537.36" 172.17.0.1 - - [13/Mar/2016:18:10:12 +0000] "GET /frontend/css/style.min.css?v1 HTTP/1.1" 304 0 "http://symfony.dev/en/admin/nodes/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.97 Safari/537.36"

[denbatte]

Might be that you know/did this already but I will write it down anyway :)

There is something going wrong with your mysql tables. Check if all the tables are there, check for correct access rights. If not, make a migration file and write it to your database.

[Caroga]

Hi @denbatte thanks. I actually started noticing this problem on different pages as well. I have not noticed the sql_mode, which I will take a look at now. I think that might be blocking some mysql routines, Ill report back if found.

In the meantime of anyone has a suggestion for me as well, gladly accepting them!

[Caroga]

Thanks to @denbatte, I've found the misconfiguration. It turned out to be the new configuration for mysql modes, which enables only_full_group_by by default. Turning this off completely solved my issue. For the time being this is an acceptable solution.

Thank you for pointing this out to me @denbatte.

For the sake of science I'm just going to leave this little configuration here:

[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

[sjoerdmaessen]

I encountered the same issue today after installing the CMS. Instead of changing my MySQL configuration (which feels a bit dirty since it's shared among clients in my case). I went for an alternative option (which maybe dirty as well but does the trick till MySQL 5.7 is fully supported).

In my website bundle i changed the xxxWebsiteBundle.php as follows:

/**
     * Extended to support MySQL 5.7 and preventing errors about
     * selecting nonaggregated columns that are not included in a group by clause.
     */
    public function boot()
    {
        parent::boot();

        /** @var Connection $db */
        $db = $this->container->get('doctrine')->getManager()->getConnection();
        $db->executeQuery("SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'");
    }

Hope this helps other people as well, of course this is a workaround which executes an additional query each boot.

[vmaillot-zz]

Thanks! sjoerdmaessen