search

gantry / gantry5

:rocket: Next Generation Template / Theme Framework
http://gantry.org
1.03k stars 203 forks source link

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

Open RomainPruvostMHH opened 2 years ago

RomainPruvostMHH commented 2 years ago

Hello,

I posted an issue on joomla site which identifies that my issue concerns gantry (https://forum.joomla.org/viewtopic.php?f=707&t=995579&p=3667046#p3667046).

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 : image image

the source file related to the image above 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.

Could you explain why I got the error with this sql request in pgsql ?

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

mahagr commented 2 years ago

Gantry doesn't support PostgreSQL right now.