Advanced search  

News:

CPG Release 1.6.26
Correct PHP8.2 issues with user and language managers.
Additional fixes for PHP 8.2
Correct PHP8 error with SMF 2.0 bridge.
Correct IPTC supplimental category parsing.
Download and info HERE

Pages: [1]   Go Down

Author Topic: Weird MySQL errors in the database.log  (Read 74387 times)

0 Members and 1 Guest are viewing this topic.

netb

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 82
Weird MySQL errors in the database.log
« on: February 10, 2015, 10:35:03 pm »

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]
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]
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]
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]
?? 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]
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]
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.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Weird MySQL errors in the database.log
« Reply #1 on: February 10, 2015, 10:38:51 pm »

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.
Logged

netb

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 82
Re: Weird MySQL errors in the database.log
« Reply #2 on: February 10, 2015, 11:03:41 pm »

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

phill104

  • Administrator
  • Coppermine addict
  • *****
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 4885
    • Windsurf.me
Re: Weird MySQL errors in the database.log
« Reply #3 on: February 11, 2015, 01:59:15 pm »

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.
Logged
It is a mistake to think you can solve any major problems just with potatoes.

netb

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 82
Re: Weird MySQL errors in the database.log
« Reply #4 on: February 11, 2015, 10:39:18 pm »

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. :)
Logged

netb

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 82
Re: Weird MySQL errors in the database.log
« Reply #5 on: February 24, 2015, 12:42:12 pm »

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]
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:
Quote
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
Logged

netb

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 82
Re: Weird MySQL errors in the database.log
« Reply #6 on: February 24, 2015, 12:44:38 pm »

And also I have no idea why the month sometimes is in chinese like that
Quote
二月 19, 2015
Logged

Krys

  • Translator
  • Coppermine newbie
  • **
  • Country: fr
  • Offline Offline
  • Gender: Male
  • Posts: 15
Re: Weird MySQL errors in the database.log
« Reply #7 on: February 24, 2015, 08:36:59 pm »

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.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Weird MySQL errors in the database.log
« Reply #8 on: March 31, 2015, 10:02:08 am »

I just had a closer look at the code in include/functions.inc.php:
Code: [Select]
        $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.
Logged

netb

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 82
Re: Re: Weird MySQL errors in the database.log
« Reply #9 on: April 10, 2015, 05:39:03 pm »

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]
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
Quote
AND pid <


Code: [Select]
[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
(
)

==========================
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Weird MySQL errors in the database.log
« Reply #10 on: April 10, 2015, 08:19:39 pm »

Open include/functions.inc.php, find
Code: [Select]
$result = cpg_db_query("SELECT filename, title, pid, position, ctime FROM {$CONFIG['TABLE_PICTURES']} WHERE pid = $pid");and below, add
Code: [Select]
        if (mysql_num_rows($result) == 0) {
            global $lang_errors;
            cpg_die(ERROR, $lang_errors['non_exist_ap'], __FILE__, __LINE__);
        }
Logged

netb

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 82
Re: Weird MySQL errors in the database.log
« Reply #11 on: April 10, 2015, 08:33:22 pm »

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



Code: [Select]
Error

The selected album/file does not exist!

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

Code: [Select]
   [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.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Weird MySQL errors in the database.log
« Reply #12 on: April 10, 2015, 08:38:48 pm »

Fix committed in SVN revision 8777.
Logged

Krys

  • Translator
  • Coppermine newbie
  • **
  • Country: fr
  • Offline Offline
  • Gender: Male
  • Posts: 15
Re: Weird MySQL errors in the database.log
« Reply #13 on: July 24, 2015, 07:41:41 pm »

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:

Quote
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.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Weird MySQL errors in the database.log
« Reply #14 on: July 27, 2015, 03:07:21 pm »

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 8)).
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Weird MySQL errors in the database.log
« Reply #15 on: July 27, 2015, 04:07:17 pm »

Fixed in SVN revision 8799.
Logged

Krys

  • Translator
  • Coppermine newbie
  • **
  • Country: fr
  • Offline Offline
  • Gender: Male
  • Posts: 15
Re: Weird MySQL errors in the database.log
« Reply #16 on: August 05, 2015, 11:41:56 pm »

Hello,

Thanks a lot André :)
Logged
Pages: [1]   Go Up
 

Page created in 0.029 seconds with 20 queries.