Critical error There was an error while processing a database query

[KOREntertainment]

J I just updated my gallery and I get the error message "Critical error
There was an error while processing a database query" but it's not always there, some times the gallery is fone and some times I get the error mesage.  korentertainment.net/gallery2

[Αndré]

Enable debug mode and try to figure out when/why the error message appears.

Moving (again ) from install to misc.

[KOREntertainment]

Seems to happen only when I am logged in.  When I log out I can view teh gallery just fine.

[Αndré]

Please post the error message with debug mode enabled.

[KOREntertainment]

How do I enable debug mode?

[Αndré]

Please read the docs! http://documentation.coppermine-gallery.net/en/configuration.htm#debug_mode

[KOREntertainment]

While executing query 'SELECT COUNT(*) FROM cpg14x_comments WHERE pid = ' in include/functions.inc.php on line 2445

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 '' at line 1

[Αndré]

When/where does it happen? Which page do you try to view?

[Pacjack]

Some extra info as I've got the exact same issue after upgrading from 1.4.25 to 1.5.10.

Critical error
There was an error while processing a database query.

Code Select Expand

While executing query 'SELECT COUNT(*) FROM cpg11d_comments WHERE pid = ' in include/functions.inc.php on line 2445

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 '' at line 1
File: /var/www/cm/include/functions.inc.php - Line: 270

The weird thing is that the error only happens when trying to open one specific category: http://pacjack.nl/cm/index.php?cat=11
No problem when opening other categories.

Notices

Code Select Expand


/include/logger.inc.php

    * Warning line 159: unlink(logs/global.log.php) [function.unlink]: Permission denied
    * Warning line 64: fopen(logs/global.log.php) [function.fopen]: failed to open stream: Permission denied
    * Warning line 66: fwrite(): supplied argument is not a valid stream resource
    * Warning line 67: fclose(): supplied argument is not a valid stream resource
    * Warning line 64: fopen(logs/database.log.php) [function.fopen]: failed to open stream: Permission denied
    * Warning line 65: fwrite(): supplied argument is not a valid stream resource
    * Warning line 66: fwrite(): supplied argument is not a valid stream resource
    * Warning line 67: fclose(): supplied argument is not a valid stream resource

/include/functions.inc.php

    * Notice line 1912: Undefined index: 41277
    * Notice line 1028: Undefined index: pid

[Pacjack]

I have tried disabling the setting "Display number of comments below the thumbnail" and that helped me to work around this error. Found that trick in http://forum.coppermine-gallery.net/index.php/topic,67596.0.html

Now the cause of this error was an album with a nonexistent pid as thumb.

1. Found that out with a part of the debug info:

Code Select Expand

    [90] => SELECT pid FROM cpg11d_pictures WHERE ((aid = '561' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1886] (0 ms)
    [91] => SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id, pic_raw_ip, pic_hdr_ip
                    FROM cpg11d_pictures AS r
                    WHERE approved = 'YES'
                    AND r.pid IN (47964,47901,44615,42342,41642,41438,41277,41173) [include/functions.inc.php:1902] (1 ms)
    [92] => SELECT COUNT(*) FROM cpg11d_comments WHERE pid = 47964 [include/functions.inc.php:2445] (0 ms)
    [93] => SELECT COUNT(*) FROM cpg11d_comments WHERE pid = 47901 [include/functions.inc.php:2445] (0 ms)
    [94] => SELECT COUNT(*) FROM cpg11d_comments WHERE pid = 44615 [include/functions.inc.php:2445] (0 ms)
    [95] => SELECT COUNT(*) FROM cpg11d_comments WHERE pid = 42342 [include/functions.inc.php:2445] (0 ms)
    [96] => SELECT COUNT(*) FROM cpg11d_comments WHERE pid = 41642 [include/functions.inc.php:2445] (0 ms)
    [97] => SELECT COUNT(*) FROM cpg11d_comments WHERE pid = 41438 [include/functions.inc.php:2445] (0 ms)
    [98] => SELECT COUNT(*) FROM cpg11d_comments WHERE pid =  [include/functions.inc.php:2445] (0 ms)
    [99] => DELETE FROM cpg11d_temp_messages WHERE time < 1293982187 [include/functions.inc.php:4927] (0 ms)
2. You can see a list of pids in the first query above: "AND r.pid IN (47964,47901,44615,42342,41642,41438,41277,41173) [include/functions.inc.php:1902] "
3. The eighth query from above seems wrong because of the empty "WHERE pid = " condition.
Should have been WHERE pid = 41277
4. I have checked in my database for pid 41277, this picture does not exist anymore in the table _pictures;
SELECT *
FROM `cpg11d_pictures`
WHERE `pid` = 41277;

Now to find out which album has this pid as thumbnail:
5. Query the _albums table like so:
SELECT *
FROM `cpg11d_albums`
WHERE `thumb` = 41277;

Next, edit the found record(s), set thumb=0.

Fixed!

[Αndré]

Imo, the real fix would be to check if the thumbnail exists in the pictures table. If not, set it to '0' resulting in the last uploaded file will be used as thumbnail. I haven't tested the performance penalty.

Attached is a file that should fix the issue with just 1 click

[Αndré]

Imo, the real fix would be to check if the thumbnail exists in the pictures table. If not, set it to '0' resulting in the last uploaded file will be used as thumbnail.

Added suggested check in svn revision 8223 (and some related fixes in revision 8224 & 8226).