Weird MySQL errors in the database.log

[netb]

Okay I have seen some strange errors from time to time in the log but I guessed it was due to experimenting with installing plugins which maybe are outdated like these errors:

Code Select Expand


While executing query 'SELECT * FROM cpg15x_pictures AS r LEFT JOIN cpg15x_albums ON cpg15x_albums.aid = r.aid WHERE (1) AND filename like '%.jpg' AND approved = 'YES' AND filename not like 'youtube_%' ORDER BY RAND() LIMIT ' in plugins/photocols/codebase.php on line 300 the following error was encountered:
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


Code Select Expand


While executing query 'SELECT * FROM cpg15x_pictures AS r LEFT JOIN cpg15x_albums ON cpg15x_albums.aid = r.aid WHERE (1) AND filename like '%.jpg' AND approved = 'YES' AND filename not like 'youtube_%' AND p.votes >= '1' ORDER BY pic_rating DESC, r.votes DESC, pid DESC LIMIT 30' in plugins/collageplus/codebase.php on line 253 the following error was encountered:
Unknown column 'p.votes' in 'where clause'


Previously I just thought that the plugins aren't working on the latest version but there have been users on the forum who run them, so maybe there is something wrong with me. I've got several other kinds of errors, here are a few which happen perodically but aren't always the same:

Code Select Expand


25. September 2014 um 06:08 - While executing query '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
FROM cpg15x_pictures AS r
INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 5 AND 6)
AND approved = 'YES'
ORDER BY ctime DESC, pid DESC
LIMIT 0 ,10' in include/functions.inc.php on line 1504 the following error was encountered:
Got error -1 from storage engine
Sep 26, 2014 at 05:24 AM - While executing query 'SELECT COUNT(*) FROM cpg15x_pictures AS p
INNER JOIN cpg15x_albums AS r ON r.aid = p.aid
WHERE (1)
AND approved = 'YES'
AND (ctime >
OR ctime = AND pid > 1514)' in include/functions.inc.php on line 2160 the following error was encountered:
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 'OR ctime = AND pid > 1514)' at line 6


Code Select Expand


?? 08, 2014 at 01:46 PM - While executing query '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 FROM cpg15x_pictures AS r
WHERE ((aid = 2 ) )AND approved='YES'
ORDER BY ctime DESC, pid DESC
LIMIT 0 ,-141' in include/functions.inc.php on line 1307 the following error was encountered:
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 '-141' at line 4


Code Select Expand


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 'OR hits = AND pid < 6217)' at line 6
Dec 20, 2014 at 03:46 AM - While executing query 'SELECT COUNT(*) FROM cpg15x_pictures
WHERE ((aid='240' AND aid NOT IN (416)) ) AND approved='YES'
AND (ctime < '' OR ctime = '' AND pid < )' in include/functions.inc.php on line 2089 the following error was encountered:
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 3


Code Select Expand


Jan 03, 2015 at 12:21 PM - While executing query 'SELECT COUNT(*) FROM cpg15x_pictures AS p
INNER JOIN cpg15x_albums AS r ON r.aid = p.aid
WHERE (r.aid = 387) AND r.aid NOT IN (416)
AND approved = 'YES'
AND (hits >
OR hits = AND pid < 6233)' in include/functions.inc.php on line 2212 the following error was encountered:
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 'OR hits = AND pid < 6233)' at line 6


Any hints why this is happening and does it need fixing? My PHP version is 5.3.13 and the MySQL is 5.5.32. I haven't edited anything in functions.inc.php or any other of the core files.

[Αndré]

Please post a link to your gallery. Some of the errors are quite old, so they may have been fixed in the latest Coppermine version.

[netb]

It's http://greatmusclebodies.com/ and yes some are quite old but there are some almost every month.

[phill104]

Could you start by updating to the latest version (currently CPG 1.5.34 ) and if the errors still remain we can begin looking at debugging from there.

[netb]

Yes, I did that today at last and will write again if anything like this appears in the logs. But I have seen similar errors as back as over 1 year ago. I guess I will mark it as solved for now.

[netb]

Hello again, the errors in the log still appear, I'm not sure why and if they are harmless but still. Here are some:

Code Select Expand


Feb 14, 2015 at 06:25 PM - While executing query 'SELECT COUNT(*) FROM cpg15x_pictures
WHERE ((aid='100' AND aid NOT IN (416)) ) AND approved='YES'
AND (ctime < '' OR ctime = '' AND pid < )' in include/functions.inc.php on line 2089 the following error was encountered:
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 3

Feb 14, 2015 at 06:28 PM - While executing query 'SELECT COUNT(*) FROM cpg15x_pictures
WHERE ((aid='100' ) )
AND (ctime < '' OR ctime = '' AND pid < )' in include/functions.inc.php on line 2089 the following error was encountered:
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 3

Feb 15, 2015 at 03:35 AM - While executing query 'SELECT COUNT(*) FROM cpg15x_pictures AS p
INNER JOIN cpg15x_albums AS r ON r.aid = p.aid
WHERE (1) AND r.aid NOT IN (416)
AND approved = 'YES'
AND (ctime >
OR ctime = AND pid > 8655)' in include/functions.inc.php on line 2161 the following error was encountered:
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 'OR ctime = AND pid > 8655)' at line 6

Feb 19, 2015 at 08:17 AM - While executing query 'SELECT COUNT(*) FROM cpg15x_pictures AS p
INNER JOIN cpg15x_albums AS r ON r.aid = p.aid
WHERE (r.aid = 387) AND r.aid NOT IN (416)
AND approved = 'YES'
AND (hits >
OR hits = AND pid < 6205)' in include/functions.inc.php on line 2212 the following error was encountered:
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 'OR hits = AND pid < 6205)' at line 6

Feb 19, 2015 at 08:17 AM - While executing query '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
FROM cpg15x_pictures AS r
INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
WHERE (r.aid = 387) AND r.aid NOT IN (416)
AND approved = 'YES'
AND hits > 0
ORDER BY hits ASC, pid DESC
LIMIT 0 ,-48' in include/functions.inc.php on line 1612 the following error was encountered:
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 '-48' at line 8

二月 19, 2015 at 01:05 PM - While executing query '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 FROM cpg15x_pictures AS r
WHERE ((aid = 2 AND aid NOT IN (416) ) )AND approved='YES'
ORDER BY ctime DESC, pid DESC
LIMIT 0 ,-110' in include/functions.inc.php on line 1307 the following error was encountered:
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 '-110' at line 4


I have the following plugins activated at the moment in this order from top to bottom:

Image Manipulation (image_manipulation): v2.3
Picture Annotation (annotate): v3.0
Favorite Button (fav_button): v1.3
AJAX reCaptcha (recaptcha): v2.1
File replacer (file_replacer): v1.7
Sitemap Generator (sitemap_generator): v0.3
Short URL (shorturl): v1.4
HTML5 Upload (html5upload): v1.3
More meta albums (more_meta_albums): v1.10
Keyboard Navigation (keyboard_navigation): v2.2
Picture Navigation (picture_navigation): v2.1
JSmin (jsmin): v1.2
Quick tag (quick_tag): v1.1
iScroll for Coppermine (iscroll): v2.1
Check files (check_files): v0.2
FileMove (FileMove): v1.3

[netb]

And also I have no idea why the month sometimes is in chinese like that

二月 19, 2015

[Krys]

Hello,

I often have the same type of errors, but I ended up ignoring them.

Regarding chinese months, I guess your site was crawled by a chinese search engine like Baidu, so Coppermine displayed content in the appropriate language.

[Αndré]

I just had a closer look at the code in include/functions.inc.php:

Code Select Expand

        $result = cpg_db_query("SELECT filename, title, pid, position, ctime FROM {$CONFIG['TABLE_PICTURES']} WHERE pid = $pid");

        $pic = mysql_fetch_assoc($result);
        $pic['title'] = mysql_real_escape_string($pic['title']);

        $sort_array = array(
            'na' => "(filename < '{$pic['filename']}' OR filename = '{$pic['filename']}' AND pid < {$pic['pid']})",
            'nd' => "(filename > '{$pic['filename']}' OR filename = '{$pic['filename']}' AND pid > {$pic['pid']})",
            'ta' => "(title < '{$pic['title']}' OR title = '{$pic['title']}' AND pid < {$pic['pid']})",
            'td' => "(title > '{$pic['title']}' OR title = '{$pic['title']}' AND pid > {$pic['pid']})",
            'da' => "(ctime < '{$pic['ctime']}' OR ctime = '{$pic['ctime']}' AND pid < {$pic['pid']})",
            'dd' => "(ctime > '{$pic['ctime']}' OR ctime = '{$pic['ctime']}' AND pid > {$pic['pid']})",
            'pa' => "(position < {$pic['position']} OR position = {$pic['position']} AND pid < {$pic['pid']})",
            'pd' => "(position > {$pic['position']} OR position = {$pic['position']} AND pid > {$pic['pid']})",
        );
        $sort_code  = isset($USER['sort']) && $CONFIG['custom_sortorder_thumbs'] ? $USER['sort'] : $CONFIG['default_sort_order'];
        $sort_order = isset($sort_array[$sort_code]) ? $sort_array[$sort_code] : $sort_array[$CONFIG['default_sort_order']];

        $query = "SELECT COUNT(*) FROM {$CONFIG['TABLE_PICTURES']}
                    WHERE ((aid='$album' $forbidden_set_string) $keyword) $approved
                    AND $sort_order";


For some reason, the first query returns either no result or the result doesn't contain the required data (e.g. hits, ctime). Of course we could add a check if those values exist after the query and display an error message, but that won't fix the cause.

Can you please have a look at the access logs of your server to find out which pages (with which parameters) have been visited when the error occurs? It should be displayimage.php - the parameters are the important part. Maybe a non-existing file causes that issue, but I haven't checked the entire code yet.

[netb]

I just had a closer look at the code in include/functions.inc.php

For some reason, the first query returns either no result or the result doesn't contain the required data (e.g. hits, ctime). Of course we could add a check if those values exist after the query and display an error message, but that won't fix the cause.

Can you please have a look at the access logs of your server to find out which pages (with which parameters) have been visited when the error occurs? It should be displayimage.php - the parameters are the important part. Maybe a non-existing file causes that issue, but I haven't checked the entire code yet.

Hello Αndré, I missed your response here but I experienced a similar error again and was just going to post more info on the topic. So it seems to be related to a missing file as you mentioned, when you hit a link for a file which has been deleted or something. I have an example link here - http://greatmusclebodies.com/displayimage.php?album=100&pid=8655

Code Select Expand


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



While executing query 'SELECT COUNT(*) FROM cpg15x_pictures
                    WHERE ((aid='100' ) )
                    AND (ctime < '' OR ctime = '' AND pid < )' in include/functions.inc.php on line 2089

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 3


File: /hermes/bosnaweb04b/b1942/ipg.greatmusclebodiescom/include/functions.inc.php - Line: 272


There is a missing value after

AND pid <

Code Select Expand


[6] => SELECT MAX(group_quota) AS disk_max, MIN(group_quota) AS disk_min, MAX(can_rate_pictures) AS can_rate_pictures, MAX(can_send_ecards) AS can_send_ecards, MAX(can_post_comments) AS can_post_comments, MAX(can_upload_pictures) AS can_upload_pictures, MAX(can_create_albums) AS can_create_albums, MAX(has_admin_access) AS has_admin_access, MAX(access_level) AS access_level, MIN(pub_upl_need_approval) AS pub_upl_need_approval, MIN( priv_upl_need_approval) AS  priv_upl_need_approval FROM cpg15x_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:323] (0.31 ms)
    [7] => SELECT group_name FROM cpg15x_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:327] (0.22 ms)
    [8] => SELECT COUNT(*) FROM cpg15x_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:340] (0.2 ms)
    [9] => SELECT lang_id FROM cpg15x_languages WHERE enabled='YES' [include/init.inc.php:330] (0.42 ms)
    [10] => SELECT user_favpics FROM cpg15x_favpics WHERE user_id = 1 [include/init.inc.php:388] (0.57 ms)
    [11] => DELETE FROM cpg15x_banned WHERE expiry < '2015-04-10 10:44:25' [include/init.inc.php:444] (1.68 ms)
    [12] => SELECT null FROM cpg15x_banned WHERE (user_id=1 OR '130.204.2.219' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:460] (0.61 ms)
    [13] => SELECT title, keyword FROM cpg15x_albums WHERE aid = 100 [include/functions.inc.php:2354] (1.96 ms)
    [14] => SELECT filename, title, pid, position, ctime FROM cpg15x_pictures WHERE pid = 8655 [include/functions.inc.php:2067] (1.26 ms)
    [15] => SELECT COUNT(*) FROM cpg15x_pictures
                    WHERE ((aid='100' ) )
                    AND (ctime < '' OR ctime = '' AND pid < ) [include/functions.inc.php:2089] (0.79 ms)
    [16] => SELECT COUNT(*) FROM cpg15x_pictures WHERE approved = 'NO' [include/functions.inc.php:2422] (0.38 ms)
    [17] => SELECT lang_id, abbr FROM cpg15x_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2292] (0.26 ms)
)

==========================
GET :
------------------
Array
(
    [album] => 100
    [pid] => 8655
)

==========================
POST :
------------------
Array
(
)

==========================


[Αndré]

Open include/functions.inc.php, find

Code Select Expand

$result = cpg_db_query("SELECT filename, title, pid, position, ctime FROM {$CONFIG['TABLE_PICTURES']} WHERE pid = $pid");
and below, add

Code Select Expand

        if (mysql_num_rows($result) == 0) {
            global $lang_errors;
            cpg_die(ERROR, $lang_errors['non_exist_ap'], __FILE__, __LINE__);
        }

[netb]

I have added the code to functions.inc.php and now I just get this:

Code Select Expand


Error

The selected album/file does not exist!

File: /hermes/bosnaweb04b/b1942/ipg.greatmusclebodiescom/include/functions.inc.php - Line: 2071


Code Select Expand


   [6] => SELECT MAX(group_quota) AS disk_max, MIN(group_quota) AS disk_min, MAX(can_rate_pictures) AS can_rate_pictures, MAX(can_send_ecards) AS can_send_ecards, MAX(can_post_comments) AS can_post_comments, MAX(can_upload_pictures) AS can_upload_pictures, MAX(can_create_albums) AS can_create_albums, MAX(has_admin_access) AS has_admin_access, MAX(access_level) AS access_level, MIN(pub_upl_need_approval) AS pub_upl_need_approval, MIN( priv_upl_need_approval) AS  priv_upl_need_approval FROM cpg15x_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:323] (0.29 ms)
    [7] => SELECT group_name FROM cpg15x_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:327] (0.41 ms)
    [8] => SELECT COUNT(*) FROM cpg15x_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:340] (0.77 ms)
    [9] => SELECT lang_id FROM cpg15x_languages WHERE enabled='YES' [include/init.inc.php:330] (0.35 ms)
    [10] => SELECT user_favpics FROM cpg15x_favpics WHERE user_id = 1 [include/init.inc.php:388] (0.48 ms)
    [11] => DELETE FROM cpg15x_banned WHERE expiry < '2015-04-10 14:30:22' [include/init.inc.php:444] (0.49 ms)
    [12] => SELECT null FROM cpg15x_banned WHERE (user_id=1 OR '130.204.2.219' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:460] (0.28 ms)
    [13] => SELECT title, keyword FROM cpg15x_albums WHERE aid = 100 [include/functions.inc.php:2359] (1.92 ms)
    [14] => SELECT filename, title, pid, position, ctime FROM cpg15x_pictures WHERE pid = 8655 [include/functions.inc.php:2067] (0.68 ms)
    [15] => SELECT COUNT(*) FROM cpg15x_pictures WHERE approved = 'NO' [include/functions.inc.php:2427] (0.24 ms)
    [16] => SELECT lang_id, abbr FROM cpg15x_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2292] (0.46 ms)
)

==========================
GET :
------------------
Array
(
    [album] => 100
    [pid] => 8655
)

==========================
POST :
------------------
Array
(
)

==========================


Nothing appears in the database.log anymore.

[Αndré]

Fix committed in SVN revision 8777.

[Krys]

Hello,

Sorry to re-use this topic, however I still have this kind of SQL errors in the log after the 1.5.36 upgrade:

Juillet 23, 2015 at 06:39 AM - While executing query 'SELECT COUNT(*) FROM cpg15x_pictures AS p
INNER JOIN cpg15x_albums AS r ON r.aid = p.aid
WHERE (1)
AND approved = 'YES'
AND (ctime >
OR ctime = AND pid > 27045)' in include/functions.inc.php on line 2166 the following error was encountered:
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 'OR ctime = AND pid > 27045)' at line 6

I didn't have the time to look at it for now.

[Αndré]

Seems that we need to fix this also for each meta album inside function get_pic_pos (tested and confirmed at http://greatmusclebodies.com/displayimage.php?album=lastup&pid=8655 - sorry ).

[Αndré]

Fixed in SVN revision 8799.

[Krys]

Hello,

Thanks a lot André