Announcement

  •  » Beta testing
  •  » PostgreSQL installation problem [2.1.1]

#1 2010-06-03 11:34:15

kult
Guest

PostgreSQL installation problem [2.1.1]

Just try to install piwigo with PG and got this message  and I'm unable to access the interface:

Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "ui.nb_image_line" must appear in the GROUP BY clause or be used in an aggregate function in /var/www/piwigo/include/dblayer/functions_pgsql.inc.php on line 122
SELECT COUNT(1) AS counter, ui.*, uc.*, t.name AS theme_name FROM piwigo_user_infos AS ui LEFT JOIN piwigo_user_cache AS uc ON ui.user_id = uc.user_id LEFT JOIN piwigo_themes AS t ON t.id = ui.theme WHERE ui.user_id = '2' GROUP BY ui.user_id ;
ERROR: column "ui.nb_image_line" must appear in the GROUP BY clause or be used in an aggregate function

 

#2 2010-06-04 20:59:06

nicolas
Former Piwigo Team
2004-12-30
1170

Offline

 

#3 2010-06-09 22:27:56

nicolas
Former Piwigo Team
2004-12-30
1170

Re: PostgreSQL installation problem [2.1.1]

The bug have been fixed. Next release (2.1.2) will include that bug fix.

Offline

 

#4 2010-06-21 16:17:04

Kult
Guest

Re: PostgreSQL installation problem [2.1.1]

nicolas wrote:

The bug have been fixed. Next release (2.1.2) will include that bug fix.

Good, I'm looking forward to testing it again :D.

Thanks folks

 

#5 2010-06-29 18:18:23

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

I just tried mixing piwigo 2.1.1 and the fixes in subversion, downloading:

functions_pgsql.inc.php

functions_user.inc.php

config_default.inc.php

but the error remains....

 

#6 2010-06-29 19:31:04

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

Dropped  database, to start afresh

installed 2.1.1 plus the three files from subversion

Warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "cat_id" LINE 1: SELECT c.id cat_id, global_rank, ^ in (path) functions_pgsql.inc.php on line 122
SELECT c.id cat_id, global_rank, MAX(date_available) date_last, COUNT(date_available) nb_images FROM piwigo_categories as c LEFT JOIN piwigo_image_category AS ic ON ic.category_id = c.id LEFT JOIN piwigo_images AS i ON ic.image_id = i.id AND i.level<=8 GROUP BY c.id, c.global_rank
ERROR: syntax error at or near "cat_id" LINE 1: SELECT c.id cat_id, global_rank, ^

 

#7 2010-06-29 20:53:20

nicolas
Former Piwigo Team
2004-12-30
1170

Re: PostgreSQL installation problem [2.1.1]

Gijs wrote:

Dropped  database, to start afresh

installed 2.1.1 plus the three files from subversion

Warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "cat_id" LINE 1: SELECT c.id cat_id, global_rank, ^ in (path) functions_pgsql.inc.php on line 122
SELECT c.id cat_id, global_rank, MAX(date_available) date_last, COUNT(date_available) nb_images FROM piwigo_categories as c LEFT JOIN piwigo_image_category AS ic ON ic.category_id = c.id LEFT JOIN piwigo_images AS i ON ic.image_id = i.id AND i.level<=8 GROUP BY c.id, c.global_rank
ERROR: syntax error at or near "cat_id" LINE 1: SELECT c.id cat_id, global_rank, ^

I think you run a postgreSQL release that doesn't accept the keyword "as" to be ommited in the select  fields.

Offline

 

#8 2010-06-30 07:35:08

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

postgresql-8.3

 

#9 2010-06-30 16:02:15

kult
Guest

Re: PostgreSQL installation problem [2.1.1]

Start from a fresh install and a clean DB, and I also got the same error message as Gijs.
I'm running the 8.3.11 version of postgresql on a stable Debian (Lenny) with the 2.1.2 version of piwigo.

I can provide more information if requested.

 

#10 2010-06-30 20:48:16

nicolas
Former Piwigo Team
2004-12-30
1170

Re: PostgreSQL installation problem [2.1.1]

kult wrote:

Start from a fresh install and a clean DB, and I also got the same error message as Gijs.
I'm running the 8.3.11 version of postgresql on a stable Debian (Lenny) with the 2.1.2 version of piwigo.

I can provide more information if requested.

I cannot reproduce the problem. Can you help me to find it. When did you have the problem ?

Offline

 

#11 2010-07-01 11:20:04

Kult
Guest

Re: PostgreSQL installation problem [2.1.1]

nicolas wrote:

I cannot reproduce the problem. Can you help me to find it. When did you have the problem ?

During the installation process. I've set up all fields [Basic configuration, Database configuration, Admin configuration] click on the [Start Installation] button and I get this error.

I also try the query in psql and I also get the same error, until I place the AS keyword.

 

#12 2010-07-01 15:47:23

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

And this is using postgresql 8.3.8-1 from Debian. This is also on a clean system..

 

#13 2010-07-01 16:03:46

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

trying to make hay of the error message: notice this:

piwigo_categories as c LEFT JOIN piwigo

should that not be

piwigo_categories **AS** c LEFT JOIN piwigo



The docs of postgresql 8.3 seem to suggest SELECT can use aliases.

 

#14 2010-07-01 16:27:19

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

I managed to fix the error by making three changes in the include/functions_user.inc.php



line 681
<    $query = 'SELECT c.id cat_id, global_rank';
>    $query = 'SELECT c.id AS cat_id, global_rank';

line 684
<   MAX(date_available) date_last, COUNT(date_available) nb_images
>   MAX(date_available) AS date_last, COUNT(date_available) AS nb_images

This allows me to complete the setup. I assume I will find other errors however :-/

Hope this helps others.

 

#15 2010-07-01 16:51:45

nicolas
Former Piwigo Team
2004-12-30
1170

Re: PostgreSQL installation problem [2.1.1]

Kult wrote:

nicolas wrote:

I cannot reproduce the problem. Can you help me to find it. When did you have the problem ?

During the installation process. I've set up all fields [Basic configuration, Database configuration, Admin configuration] click on the [Start Installation] button and I get this error.

I also try the query in psql and I also get the same error, until I place the AS keyword.

It seems that AS keyword is optionnal since release 8.4 and I used release 8.4.4.  So two solutions :

1) add the AS keyword in all queries where it's needed
2) change PostgreSQL required version to 8.4

I think the first one is the better one.

Offline

 
  •  » Beta testing
  •  » PostgreSQL installation problem [2.1.1]

Board footer

Powered by FluxBB

github twitter facebook google+ newsletter Donate Piwigo.org © 2002-2018 · Contact