SQL Error: "Grouping error: column "pages.sorting" must appear in the GROUP BY clause or be used in an aggregate function" in info
I was trying out the "Info" page on the Backend panel, as provided by the Info builtin extension. I changed some of the dropdown options while one of the first-level pages (below the root page) was selected. This page had no children and was a standard enabled page not visible in menus.
Then, after first changing the nesting level on info reporting to something else than "this page" (I cannot remember the exact menu names, forgive me), the following error occurred.
(1/3) Doctrine\DBAL\Exception\DriverException An exception occurred while executing 'SELECT COUNT("uid") FROM "pages" WHERE ("pid" = ?) AND ("sys_language_uid" = ?) AND ( 1=1) AND (("pages"."deleted" = 0) AND (("pages"."t3ver_wsid" = 0) AND ("pages"."t3ver_oid" = 0))) ORDER BY "sorting" ASC' with params [98, 0]: SQLSTATE: Grouping error: 7 ERROR: column "pages.sorting" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...id" = 0) AND ("pages"."t3ver_oid" = 0))) ORDER BY "sorting" ... ^ [...] at Doctrine\DBAL\Connection->executeQuery('SELECT COUNT("uid") FROM "pages" WHERE ("pid" = ?) AND ("sys_language_uid" = ?) AND ( 1=1) AND (("pages"."deleted" = 0) AND (("pages"."t3ver_wsid" = 0) AND ("pages"."t3ver_oid" = 0))) ORDER BY "sorting" ASC', array(89, 0), array(1, 1)) in /SITE_ROOT/vendor/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php line 205
(If necessary, I can provide more of the stack trace / error log)
I once managed to return to the info page itself and select another nesting level, but the exact error occurred again. After that, I was unable to open the info panel again, even after closing and re-opening different backend sections. After closing the entire website (or navigating to a different one) and re-opening the Admin panel, I was able to open the starting page of the info panel again, until I selected a page to actually view info, upon which the error repeated.
My website is pretty vanilla with only a modified Bootstrap Theme in use and no extensions apart from Bootstrap, Introduction, and a custom Sitepackage which is disabled. At the bottom of my post I have included more information about my system, in particular, I am running TYPO3 on NGINX and with a PostgreSQL database. I have not had any other database errors except the occasional:
Database Error SQLSTATE: Undefined column: 7 ERROR: column tt_content.ctype does not exist LINE 1: ...tt_content", "pages" WHERE (tt_content.pid=91 AND tt_content... ^ HINT: Perhaps you meant to reference the column "tt_content.CType".. A SQL error occurred. This may indicate a schema mismatch between TCA and the database. Try running database compare in the Install Tool.
This appears as a red banner over the Page content edit menu when I save some page contents (the particular example was from an accordion element). It did not cause any permanent issues, so I ignored it, but it may be related. I ran "Analyze Database Structure" and nothing was found. There are no further issues in my system.
This issue is not very important to me, as my page is small and the statistics are merely informational and do not impact the correct site behavior, both frontend viewing, and backend modification. However, I would either like help in fixing it or make you aware of a possible error in TYPO3 that needs to be fixed.
TYPO3 Version 10.4.9
PHP Version 7.3.19-1~deb10u1
Database (Default) PostgreSQL 11.7 (Raspbian 11.7-0+deb10u1)
Application Context Production
Composer mode Enabled
Operating System Linux 5.4.51-v7+
Bootstrap Package 11.0.2
Updated by Stephan Großberndt 9 months ago
Thanks for reporting that issue. I am quite certain this is not a TYPO3 core issue but one of
EXT:bootstrap_package. There has not been a release since 2019-12 for that extension and there are several patches fixing issues regarding missing escaping of database column names which have been merged since the last release, e.g.:
- [BUGFIX] Change TCA escaping for database compatibility
- [BUGFIX] Ensure correct field casing and escaping in TCA for database compatibility
Could you please test whether using the branch BP_11-0 of
EXT:bootstrap_package fixes the issue for you?
A new release is apparently on the way btw.: https://github.com/benjaminkott/bootstrap_package/issues/940