column "a.publish_up" must appear in the GROUP BY clause after migrating from mysql to postgresql

Need help installing Joomla! 3.x? For all Joomla! 3.x installation issues please use this forum.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10
Locked
leprof776
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Fri Jul 29, 2022 2:08 pm

column "a.publish_up" must appear in the GROUP BY clause after migrating from mysql to postgresql

Post by leprof776 » Tue Aug 09, 2022 11:36 am

Hi,

I'm moving Joomla 3.9 to a new server and i'm migrating the mysql database v5.7.9 to postgreSQL v13.
I used pgLoader to migrate from mysql to postgresql. The command line for migrate from mysql to pgsl is the following :
pgloader --cast 'type tinyint to smallint drop typemod' --with "quote identifiers" mysql://login:pwd@host_mysql:3306/db_mysql postgresql://login_pgsql:pwd@host_pgsql:5432/db_pgsql

And when I try to access to my joomla site, I got the following error on the web page :
picture_1_error.png
picture_2_error.png
the source file is /var/www/html/joomla/media/gantry5/engines/nucleus/particles/contentarray.html.twig

And it seems that the sql request below causes the error in postgresql :

EXPLAIN SELECT COUNT(*) FROM joomla_content AS a INNER JOIN joomla_categories AS c ON c.id = a.catid WHERE "a"."catid" NOT IN (16) AND (a.publish_up = '1970-01-01 00:00:00' OR a.publish_up <= '2022-08-09 10:03:45') AND (a.publish_down = '1970-01-01 00:00:00' OR a.publish_down >= '2022-08-09 10:03:45') AND a.state >= 1 AND "a"."access" IN (1,1,5) AND "c"."access" IN (1,1,5) AND "a"."state" IN (1) AND "a"."language" IN ('fr-FR','*') AND "a"."featured" = 0 AND "a"."catid" IN (13) ORDER BY a."publish_up" DESC;
ERREUR: la colonne « a.publish_up » doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat
LIGNE 1 : ..."."featured" = 0 AND "a"."catid" IN (13) ORDER BY a."publish...

The same request executed well in the original mysql database.

First, could you validate my pgloader command line to migrate from mysql 5 to postgresql 13 ?
The, could you explain why I got the error with this sql request in pgsql ?

Any help would be appreciate ?

Kind regards,
You do not have the required permissions to view the files attached to this post.

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 30863
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: column "a.publish_up" must appear in the GROUP BY clause after migrating from mysql to postgresql

Post by Per Yngve Berg » Tue Aug 09, 2022 1:38 pm

Why are you running 3.9?

The current version is 3.10.10

leprof776
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Fri Jul 29, 2022 2:08 pm

Re: column "a.publish_up" must appear in the GROUP BY clause after migrating from mysql to postgresql

Post by leprof776 » Tue Aug 09, 2022 2:15 pm

Because it's the version in my client environment. The next step will be to upgrade joomla.

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 30863
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: column "a.publish_up" must appear in the GROUP BY clause after migrating from mysql to postgresql

Post by Per Yngve Berg » Tue Aug 09, 2022 6:20 pm

Gantry5 is a 3. party template framework.

leprof776
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Fri Jul 29, 2022 2:08 pm

Re: column "a.publish_up" must appear in the GROUP BY clause after migrating from mysql to postgresql

Post by leprof776 » Wed Aug 10, 2022 2:34 pm

I didn't understand the last reply.

SharkyKZ
Joomla! Hero
Joomla! Hero
Posts: 2873
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: column "a.publish_up" must appear in the GROUP BY clause after migrating from mysql to postgresql

Post by SharkyKZ » Wed Aug 10, 2022 3:02 pm

Enable debug and post stack trace.

leprof776
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Fri Jul 29, 2022 2:08 pm

Re: column "a.publish_up" must appear in the GROUP BY clause after migrating from mysql to postgresql

Post by leprof776 » Thu Aug 11, 2022 8:01 am

I enabled debug in configuration.php and below is the stack strace :

Twig\Error\RuntimeError thrown with message "An exception has been thrown during the rendering of a template ("00000, , ")."

Stacktrace:
#43 Twig\Error\RuntimeError in /var/www/html/joomla/media/gantry5/engines/nucleus/particles/contentarray.html.twig:31
#42 JDatabaseExceptionExecuting in /var/www/html/joomla/libraries/joomla/database/driver/pdo.php:481
#41 JDatabaseDriverPdo:execute in /var/www/html/joomla/libraries/joomla/database/driver.php:1740
#40 JDatabaseDriver:loadResult in /var/www/html/joomla/libraries/gantry5/classes/Gantry/Joomla/Object/Finder.php:223
#39 Gantry\Joomla\Object\Finder:count in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:702
#38 Twig\Template:getAttribute in /var/www/html/joomla/cache/gantry5/g5_hydrogen/twig/1e/1edf68c0ab454ececf19aba0d078fa4f103ebff9886222352a08e3e5120a031d.php:77
#37 __TwigTemplate_c608fbf6e0080c6430c0774277b8f4c2d343f4d1b456a8806d582f141b048a7d:doDisplay in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:455
#36 Twig\Template:displayWithErrorHandling in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:422
#35 Twig\Template:display in /var/www/html/joomla/cache/gantry5/g5_hydrogen/twig/4b/4b3dbda5cc5b7e77233bec2159eede1309a5d0a1ca227ac7b716edbc175c94ec.php:71
#34 __TwigTemplate_5a73f674c20839cd4764e798cb0a76d0d641a6540087608cb8377c17b6a6e38d:doDisplay in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:455
#33 Twig\Template:displayWithErrorHandling in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:422
#32 Twig\Template:display in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:434
#31 Twig\Template:render in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/TemplateWrapper.php:47
#30 Twig\TemplateWrapper:render in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Environment.php:384
#29 Twig\Environment:render in /var/www/html/joomla/libraries/gantry5/classes/Gantry/Component/Theme/AbstractTheme.php:163
#28 Gantry\Component\Theme\AbstractTheme:render in /var/www/html/joomla/libraries/gantry5/classes/Gantry/Component/Theme/ThemeTrait.php:767
#27 Gantry\Framework\Theme:getContent in /var/www/html/joomla/libraries/gantry5/classes/Gantry/Component/Theme/ThemeTrait.php:666
#26 Gantry\Framework\Theme:renderContent in /var/www/html/joomla/libraries/gantry5/classes/Gantry/Component/Theme/ThemeTrait.php:593
#25 Gantry\Framework\Theme:prepareLayout in /var/www/html/joomla/libraries/gantry5/classes/Gantry/Component/Theme/ThemeTrait.php:579
#24 Gantry\Framework\Theme:prepareLayout in /var/www/html/joomla/libraries/gantry5/classes/Gantry/Component/Theme/ThemeTrait.php:579
#23 Gantry\Framework\Theme:prepareLayout in /var/www/html/joomla/libraries/gantry5/classes/Gantry/Component/Theme/ThemeTrait.php:579
#22 Gantry\Framework\Theme:prepareLayout in /var/www/html/joomla/libraries/gantry5/classes/Gantry/Component/Theme/ThemeTrait.php:422
#21 Gantry\Framework\Theme:segments in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:702
#20 Twig\Template:getAttribute in /var/www/html/joomla/cache/gantry5/g5_hydrogen/twig/a9/a9420f6c9c205c404222d0591c5584a8ebf3fb6de5b14efd8b2800f64a340c62.php:47
#19 __TwigTemplate_8aa51dd43347d72637a4795ea9b21785733f56e7ebc790d854cbf2cd09a5e315:doDisplay in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:455
#18 Twig\Template:displayWithErrorHandling in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:422
#17 Twig\Template:display in /var/www/html/joomla/cache/gantry5/g5_hydrogen/twig/66/66b28bfd1df500a0c939fab632b4925e615d84e0281f22cfd6ebcbb3a569941a.php:35
#16 __TwigTemplate_78fd03f64e40164dc065e29517242e76ac96f04a0e5115c1ed36ed9c10c2da00:doDisplay in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:455
#15 Twig\Template:displayWithErrorHandling in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:422
#14 Twig\Template:display in /var/www/html/joomla/cache/gantry5/g5_hydrogen/twig/32/3214ec0aa5f46ca62b7e64eba73c1c36cd31be1c093e6518f5ae6da55b4f1946.php:35
#13 __TwigTemplate_29ac5a4c3ef4611262c8e1f9b6cc4e20526a9dac9ac3b4ceb1f115a7f4ead833:doDisplay in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:455
#12 Twig\Template:displayWithErrorHandling in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:422
#11 Twig\Template:display in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Template.php:434
#10 Twig\Template:render in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/TemplateWrapper.php:47
#9 Twig\TemplateWrapper:render in /var/www/html/joomla/libraries/gantry5/vendor/twig/twig/src/Environment.php:384
#8 Twig\Environment:render in /var/www/html/joomla/libraries/gantry5/classes/Gantry/Component/Theme/AbstractTheme.php:163
#7 Gantry\Component\Theme\AbstractTheme:render in /var/www/html/joomla/templates/g5_hydrogen/index.php:23
#6 require in /var/www/html/joomla/libraries/src/Document/HtmlDocument.php:668
#5 Joomla\CMS\Document\HtmlDocument:_loadTemplate in /var/www/html/joomla/libraries/src/Document/HtmlDocument.php:730
#4 Joomla\CMS\Document\HtmlDocument:_fetchTemplate in /var/www/html/joomla/libraries/src/Document/HtmlDocument.php:545
#3 Joomla\CMS\Document\HtmlDocument:parse in /var/www/html/joomla/libraries/src/Application/CMSApplication.php:1030
#2 Joomla\CMS\Application\CMSApplication:render in /var/www/html/joomla/libraries/src/Application/SiteApplication.php:778
#1 Joomla\CMS\Application\SiteApplication:render in /var/www/html/joomla/libraries/src/Application/CMSApplication.php:202
#0 Joomla\CMS\Application\CMSApplication:execute in /var/www/html/joomla/index.php:45

gws
Joomla! Champion
Joomla! Champion
Posts: 5913
Joined: Tue Aug 23, 2005 1:56 pm
Location: South coast, UK
Contact:

Re: column "a.publish_up" must appear in the GROUP BY clause after migrating from mysql to postgresql

Post by gws » Thu Aug 11, 2022 8:23 am

As @per told you your problems are coming from Rockettheme template framework Gantry, ask on rocketthemes support,their support is legendary.

SharkyKZ
Joomla! Hero
Joomla! Hero
Posts: 2873
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: column "a.publish_up" must appear in the GROUP BY clause after migrating from mysql to postgresql

Post by SharkyKZ » Fri Aug 12, 2022 5:33 am

You can report this directly to Gantry repository https://github.com/gantry/gantry5. Or even submit a patch if you know how to fix this. Do note that very few extension developers bother supporting PostgreSQL.


Locked

Return to “Installation Joomla! 3.x”