There was an error while processing a database query

[schlicht]

Hi there,

I get: "There was an error while processing a database query"
when I try to enter User galleries by clicking the "User galleries"-Link on the Index-page.

Debug infos:

Code Select Expand

While executing query "SELECT (category - 10000) as user_id,        '???' as user_name,        COUNT(DISTINCT a.aid) as alb_count,        COUNT(DISTINCT pid) as pic_count,        MAX(pid) as thumb_pid FROM cpg11d_albums AS a INNER JOIN cpg11d_pictures AS p ON p.aid = a.aid WHERE approved = 'YES' AND category > 10000 AND  GROUP BY category ORDER BY category " on 0

mySQL error: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY category ORDER BY category' at line 1


but... it is possible to upload pictures to personal galleries and they come also into the "newest Picture" Box...

Does anyone of you have an idea?

Details:
- I am running cp with phpbb 2.06.
- Page: http://www.cameleonline.ch/gallery/

Regards,
Michel

[Joachim Müller]

I'm not sure, I think I have come across this before, but I don't remember where (and if a fix has been posted). Please search the board.

GauGau

[schlicht]

I tried to search before... but did not find any satisfying answer  

[schlicht]

Hello!

...so I tried to copy/paste the query to put it directlty into phpmyadmin, result:

Code Select Expand


Error

SQL-query :  

SELECT (

category -10000
) AS user_id, '???' AS user_name, COUNT( DISTINCT a.aid ) AS alb_count, COUNT( DISTINCT pid ) AS pic_count, MAX( pid ) AS thumb_pid
FROM cpg11d_albums AS a
INNER JOIN cpg11d_pictures AS p ON p.aid = a.aid
WHERE approved = 'YES' AND category >10000 AND
GROUP BY category
ORDER BY category
LIMIT 0 , 30

MySQL said:


#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP  BY category
ORDER  BY category LIMIT 0, 30' at line 5


Line 5 is:

Code Select Expand

INNER JOIN cpg11d_pictures AS p ON p.aid = a.aid

[Joachim Müller]

what version of mySQL are you running (server version, not client api)?

GauGau

[schlicht]

MySQL 4.0.15-standard
together with PHP 4.3.4

Why are you asking? Do you have any idea?

[schlicht]

interessting, when I log in as admin, then change to user mode and the try to go to the "album overview-> User Galleries" I get an error like the one above:

Code Select Expand

SELECT (category - 10000) as user_id,        '???' as user_name,        COUNT(DISTINCT a.aid) as alb_count,        COUNT(DISTINCT pid) as pic_count,        MAX(pid) as thumb_pid FROM cpg11d_albums AS a INNER JOIN cpg11d_pictures AS p ON p.aid = a.aid WHERE approved = 'YES' AND category > 10000 AND AND p.aid NOT IN (14)  GROUP BY category ORDER BY category

BUT, there two ANDs ([...]> 10000 AND AND p.aid [...])-> if I correct this and copy the query into phpmyadmin, the query is successful!

Do you have any Idea where I do have to correct this?

[Nibbler]

Hi,

I came across this bug in 1.3 beta, there is a post in that forum about it. Are you using files from the beta or CVS or from the 1.2.1. zip ?

It is caused by the set of albums you are blocked from seeing being empty, so there is nothing between the 2 ANDs instead of there being the restrictions.

That query is in the phpbb bridge file if you want to have a look.

[schlicht]

I came across this bug in 1.3 beta, there is a post in that forum about it.

I am not able to search/read that forum - no user permissions.

Are you using files from the beta or CVS or from the 1.2.1. zip?

I am using 1.2 final.

It is caused by the set of albums you are blocked from seeing being empty, so there is nothing between the 2 ANDs instead of there being the restrictions.

OK - This is the second problem...
The first Error Message is without two ANDs and does not work either.

There were some postings about empty spaces before column names - I did search throught the tables with phpmyadmin but I didn't find any...

[Joachim Müller]

@Nibbler: the current cpg1.3.0 is non-public, "regular" users can't access that board. Please don't spread the word yet.

@schlicht: there's no such thing as cpg1.2final - probably you meant cpg1.2.0final  :wink: (and yes, the numbering of versions has to improve). Update to cpg1.2.1 is recommended.

GauGau

[schlicht]

@gaugau: Yes, 1.2.0 final   :wink:
Update is planed, is there any instruction to update from 1.2.0 to 1.2.1?
Will this solve my problem?

seems like I've got same problems with user gallery in the demo:
http://coppermine.sourceforge.net/demo/

link: http://coppermine.sourceforge.net/demo/index.php?cat=1

gives me:
There was an error while processing a database query
- when I am logged in
- when I am not logged in

[Joachim Müller]

Yes, I'm aware of that and I know that this has been fixed for the dev version, but I don't remember if (and where) the fix was posted. I'd have to go through all commits in the cvs and diff-view them to check this - which would be very time-consuming, so I'd ask you to wait for the release of cpg1.3.0 which is in pre-release testing state.

Maybe someone remembers the thread where the fix was posted and could give a hint. Thanks.

GauGau