Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1]   Go Down

Author Topic: Critical error: There was an error while processing a database query  (Read 10400 times)

0 Members and 1 Guest are viewing this topic.

Aleksandra

  • Coppermine newbie
  • Offline Offline
  • Gender: Female
  • Posts: 14
  • Darth Minion
    • Grey-Wanderer.org

Alright, I just went through all 19 pages of support requests, but I couldn't find what I was looking for, so I guess I'm now allowed to post a support request. ^^

So here's the thing. I'm currently using Coppermine 1.3.5 for a gallery with well over 5000 pictures, located here (though registering is disabled and the gallery is members-only, so I'm not sure what use you might have of the url). Anyhow, I wasn't experiencing any problems with it (except for a minor bug which has been fixed in CPG 1.4 . The reason why I don't want to upgrade is that I like the older version better, but that's all beside the point ^^), until I moved the gallery onto another server a few weeks ago. When restoring the database, I got a some sort of PHP error saying the allowed memory size of 8 MB of something had been exceeded, but I didn't think much of it as when I inserted the old data into the MySQL database manually, using phpMyAdmin, it all went well. Oh, and I did switch from GD to ImageMagick, since that was giving me some trouble on the new server, too. However, since the troubles of the initial move, everything has worked fine. I've been uploading pictures, creating new albums and categories and the like, until today, when I stumbled across this error message when trying to access the usermgr.php through the CPG admin menu.

Quote
Critical error

There was an error while processing a database query.

This error had not occurred before, and I had not made any changes to either the database or the CPG coding since the move. I'm also positive that I have used the user manager since the move, as I recall making one of my members an administrator through it only two days back. So I enabled the debug mode and here's what I got.

Quote
While executing query "SELECT user_id, user_name, UNIX_TIMESTAMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, user_active, COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota FROM cpg135_users AS u INNER JOIN cpg135_usergroups AS g ON user_group = group_id LEFT JOIN cpg135_pictures AS p ON p.owner_id = u.user_id GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 25;" on 0

mySQL error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

Quote
File: /home/sites/daisymedia.net/public_html/gallery/include/functions.inc.php - Line: 105

Quote
USER:
------------------
Array
(
    [ID] => 592f5e08ac7671b374fa59aa2a21aa71
    [am] => 1
    [liv] => Array
        (
           
  • => 193
  • [1] => 5531
                [2] => 5534
            )

    )

    ==========================
    USER DATA:
    ------------------
    Array
    (
       
[user_id] => 1
    [user_group] => 1
    [user_active] => YES
    [user_name] => Sandra
    [user_password] => ********
    [user_lastvisit] => 2006-04-05 13:10:33
    [user_regdate] => 2005-11-06 18:25:24
    [user_group_list] =>
    [user_email] =>
    [user_website] =>
    [user_location] =>
    [user_interests] =>
    [user_occupation] =>
    [user_actkey] =>
    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 1
    [ufc_max] => 3
    [ufc_min] => 3
    [custom_user_upload] => 0
    [num_file_upload] => 5
    [num_URI_upload] => 3
    [can_post_comments] => 1
    [can_upload_pictures] => 1
    [can_create_albums] => 1
    [has_admin_access] => 1
    [pub_upl_need_approval] => 0
    [priv_upl_need_approval] => 0
    [group_name] => Administrators
    [upload_form_config] => 3
    [group_quota] => 0
    [can_see_all_albums] => 1
    [group_id] => 1
    [groups] => Array
        (
            [1] => 1
        )

)

==========================
Queries:
------------------
Array
(
   
  • => SELECT extension, mime, content FROM cpg135_filetypes;
  • [1] => SELECT * FROM cpg135_users WHERE user_id='1'AND user_active = 'YES' AND user_password != '' AND BINARY MD5(user_password) = '81ca975849805bcd3dff6c53cb955307'
        [2] => 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(upload_form_config) as ufc_max, MIN(upload_form_config) as ufc_min, MAX(custom_user_upload) as custom_user_upload, MAX(num_file_upload) as num_file_upload, MAX(num_URI_upload) as num_URI_upload, 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, MIN(pub_upl_need_approval) as pub_upl_need_approval, MIN( priv_upl_need_approval) as  priv_upl_need_approval FROM cpg135_usergroups WHERE group_id in (1)
        [3] => SELECT group_name FROM  cpg135_usergroups WHERE group_id= 1
        [4] => DELETE FROM cpg135_banned WHERE expiry < '2006-04-05 14:45:07'
        [5] => SELECT * FROM cpg135_banned WHERE ip_addr='137.163.21.52' OR ip_addr='10.11.3.119' OR user_id=1
        [6] => DELETE FROM cpg135_users WHERE user_name = '' LIMIT 1
        [7] => SELECT count(*) FROM cpg135_users WHERE 1
        [8] => SELECT user_id, user_name, UNIX_TIMESTAMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, user_active, COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota FROM cpg135_users AS u INNER JOIN cpg135_usergroups AS g ON user_group = group_id LEFT JOIN cpg135_pictures AS p ON p.owner_id = u.user_id GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 25;
    )

    ==========================
    GET :
    ------------------
    Array
    (
    )

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

    ==========================
    VERSION INFO :
    ------------------
    PHP version: 4.3.11 - OK
    ------------------
    mySQL version: 5.0.19-standard
    ------------------
    Coppermine version: 1.3.5
    ==========================
    Module: gd
    ------------------
    GD Support enabled
    GD Version bundled (2.0.28 compatible)
    FreeType Support enabled
    FreeType Linkage with freetype
    GIF Read Support enabled
    GIF Create Support enabled
    JPG Support enabled
    PNG Support enabled
    WBMP Support enabled
    XBM Support enabled
    ==========================
    Module: mysql
    ------------------
    Active Persistent Links 0
    Active Links 1
    Client API version 4.1.18
    MYSQL_MODULE_TYPE external
    MYSQL_SOCKET /var/lib/mysql/mysql.sock
    MYSQL_INCLUDE -I/usr/include/mysql
    MYSQL_LIBS -L/usr/lib/mysql -lmysqlclient 
    ==========================
    Module: zlib
    ------------------
    ZLib Support enabled
    Compiled Version 1.2.1.2
    Linked Version 1.2.1.2
    ==========================
    Server restrictions (safe mode)?
    ------------------
    Directive | Local Value | Master Value
    safe_mode | Off | Off
    safe_mode_exec_dir | no value | no value
    safe_mode_gid | Off | Off
    safe_mode_include_dir | no value | no value
    safe_mode_exec_dir | no value | no value
    sql.safe_mode | Off | Off
    disable_functions | no value | no value
    file_uploads | On | On
    include_path | .:/usr/share/pear | .:/usr/share/pear
    open_basedir | no value | no value
    ==========================
    email
    ------------------
    Directive | Local Value | Master Value
    sendmail_from | me@localhost.com | me@localhost.com
    sendmail_path | /usr/sbin/sendmail -t -i | /usr/sbin/sendmail -t -i
    SMTP | localhost | localhost
    smtp_port | 25 | 25
    ==========================
    Size and Time
    ------------------
    Directive | Local Value | Master Value
    max_execution_time | 30 | 30
    max_input_time | 60 | 60
    upload_max_filesize | 2M | 2M
    post_max_size | 8M | 8M
    ==========================
    Page generated in 0.044 seconds - 9 queries in 0.002 seconds - Album set :
I checked if other features were giving me a similar error, but admin tools, album/category mgrs, my profile etc. and uploading pictures seem to be working fine. I can only replicate the error when I try to access the user mgr - it gives me the same message each time. I'm using PHP 4.3.11, a Linux server and MySQL 4.x . I'm trying to give you as much information as I can, in case it helps. I'm sorry if this issue has been posted before, but I did go through all 19 pages of this type of "critical error"-related support requests, and couldn't find anything on this particular type. If you need any additional info, I'll give it to you, and any help is much appreciated. Thanks in advance! :D

Regards,
Aleksandra
« Last Edit: April 11, 2006, 03:44:43 pm by Nibbler »
Logged

Nibbler

  • Guest

Try http://forum.coppermine-gallery.net/index.php?topic=27540.msg129081#msg129081

The code will differ for 1.3, plus you'll need to find the code in usermgr itself.
Logged

pokk

  • Coppermine newbie
  • Offline Offline
  • Posts: 10

I have the same error! Can someone explain me souloution? I have 1.3.5 too.
Logged

Nibbler

  • Guest

Find

Code: [Select]
    $sql = "SELECT user_id, user_name, UNIX_TIMESTAMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, user_active, ".
           "COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota ".
           "FROM {$CONFIG['TABLE_USERS']} AS u ".
           "INNER JOIN {$CONFIG['TABLE_USERGROUPS']} AS g ON user_group = group_id ".
           "LEFT JOIN {$CONFIG['TABLE_PICTURES']} AS p ON p.owner_id = u.user_id ".
$searchstr.
           "GROUP BY user_id ".
           "ORDER BY " . $sort_codes[$sort] . " ".
           "LIMIT $lower_limit, $user_per_page;";

Change to

Code: [Select]
    $sql = "SELECT user_id, user_name, UNIX_TIMESTAMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, user_active, ".
           "COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota ".
           "FROM {$CONFIG['TABLE_USERS']} AS u ".
           "INNER JOIN {$CONFIG['TABLE_USERGROUPS']} AS g ON user_group = group_id ".
           "LEFT JOIN {$CONFIG['TABLE_PICTURES']} AS p FORCE INDEX ( owner_id ) ON p.owner_id = u.user_id ".
$searchstr.
           "GROUP BY user_id ".
           "ORDER BY " . $sort_codes[$sort] . " ".
           "LIMIT $lower_limit, $user_per_page;";
Logged

Aleksandra

  • Coppermine newbie
  • Offline Offline
  • Gender: Female
  • Posts: 14
  • Darth Minion
    • Grey-Wanderer.org

^ I tried your suggestion, but it didn't seem to have an effect on the situation. I still seem to be getting the same error, or at least I think it's the same one. Here's the debug information again, after editing the usermgr.php .

Quote
Critical error

There was an error while processing a database query.

Quote
While executing query "SELECT user_id, user_name, UNIX_TIMESTAMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, user_active, COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota FROM cpg135_users AS u INNER JOIN cpg135_usergroups AS g ON user_group = group_id LEFT JOIN cpg135_pictures AS p FORCE INDEX ( owner_id ) ON p.owner_id = u.user_id GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 25;" on 0

mySQL error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

Quote
File: /home/sites/daisymedia.net/public_html/gallery/include/functions.inc.php - Line: 105

Quote
Notice: Undefined variable: searchstr in /home/sites/daisymedia.net/public_html/gallery/usermgr.php on line 104

Quote
USER:
------------------
Array
(
    [ID] => 58d08fdaf28040050dd014270ca83690
    [am] => 1
    [liv] => Array
        (
           
  • => 5275
  • [1] => 1362
                [2] => 2378
                [3] => 2018
                [4] => 2019
            )

    )

    ==========================
    USER DATA:
    ------------------
    Array
    (
       
[user_id] => 1
    [user_group] => 1
    [user_active] => YES
    [user_name] => Sandra
    [user_password] => ********
    [user_lastvisit] => 2006-04-07 20:29:33
    [user_regdate] => 2005-11-06 18:25:24
    [user_group_list] =>
    [user_email] =>
    [user_website] =>
    [user_location] =>
    [user_interests] =>
    [user_occupation] =>
    [user_actkey] =>
    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 1
    [ufc_max] => 3
    [ufc_min] => 3
    [custom_user_upload] => 0
    [num_file_upload] => 5
    [num_URI_upload] => 3
    [can_post_comments] => 1
    [can_upload_pictures] => 1
    [can_create_albums] => 1
    [has_admin_access] => 1
    [pub_upl_need_approval] => 0
    [priv_upl_need_approval] => 0
    [group_name] => Administrators
    [upload_form_config] => 3
    [group_quota] => 0
    [can_see_all_albums] => 1
    [group_id] => 1
    [groups] => Array
        (
            [1] => 1
        )

)

==========================
Queries:
------------------
Array
(
   
  • => SELECT extension, mime, content FROM cpg135_filetypes;
  • [1] => SELECT * FROM cpg135_users WHERE user_id='1'AND user_active = 'YES' AND user_password != '' AND BINARY MD5(user_password) = '81ca975849805bcd3dff6c53cb955307'
        [2] => 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(upload_form_config) as ufc_max, MIN(upload_form_config) as ufc_min, MAX(custom_user_upload) as custom_user_upload, MAX(num_file_upload) as num_file_upload, MAX(num_URI_upload) as num_URI_upload, 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, MIN(pub_upl_need_approval) as pub_upl_need_approval, MIN( priv_upl_need_approval) as  priv_upl_need_approval FROM cpg135_usergroups WHERE group_id in (1)
        [3] => SELECT group_name FROM  cpg135_usergroups WHERE group_id= 1
        [4] => DELETE FROM cpg135_banned WHERE expiry < '2006-04-08 17:53:52'
        [5] => SELECT * FROM cpg135_banned WHERE ip_addr='84.231.39.168' OR ip_addr='84.231.39.168' OR user_id=1
        [6] => DELETE FROM cpg135_users WHERE user_name = '' LIMIT 1
        [7] => SELECT count(*) FROM cpg135_users WHERE 1
        [8] => SELECT user_id, user_name, UNIX_TIMESTAMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, user_active, COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota FROM cpg135_users AS u INNER JOIN cpg135_usergroups AS g ON user_group = group_id LEFT JOIN cpg135_pictures AS p FORCE INDEX ( owner_id ) ON p.owner_id = u.user_id GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 25;
    )

    ==========================
    GET :
    ------------------
    Array
    (
    )

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

    ==========================
    VERSION INFO :
    ------------------
    PHP version: 4.3.11 - OK
    ------------------
    mySQL version: 5.0.19-standard
    ------------------
    Coppermine version: 1.3.5
    ==========================
    Module: gd
    ------------------
    GD Support enabled
    GD Version bundled (2.0.28 compatible)
    FreeType Support enabled
    FreeType Linkage with freetype
    GIF Read Support enabled
    GIF Create Support enabled
    JPG Support enabled
    PNG Support enabled
    WBMP Support enabled
    XBM Support enabled
    ==========================
    Module: mysql
    ------------------
    Active Persistent Links 0
    Active Links 1
    Client API version 4.1.18
    MYSQL_MODULE_TYPE external
    MYSQL_SOCKET /var/lib/mysql/mysql.sock
    MYSQL_INCLUDE -I/usr/include/mysql
    MYSQL_LIBS -L/usr/lib/mysql -lmysqlclient 
    ==========================
    Module: zlib
    ------------------
    ZLib Support enabled
    Compiled Version 1.2.1.2
    Linked Version 1.2.1.2
    ==========================
    Server restrictions (safe mode)?
    ------------------
    Directive | Local Value | Master Value
    safe_mode | Off | Off
    safe_mode_exec_dir | no value | no value
    safe_mode_gid | Off | Off
    safe_mode_include_dir | no value | no value
    safe_mode_exec_dir | no value | no value
    sql.safe_mode | Off | Off
    disable_functions | no value | no value
    file_uploads | On | On
    include_path | .:/usr/share/pear | .:/usr/share/pear
    open_basedir | no value | no value
    ==========================
    email
    ------------------
    Directive | Local Value | Master Value
    sendmail_from | me@localhost.com | me@localhost.com
    sendmail_path | /usr/sbin/sendmail -t -i | /usr/sbin/sendmail -t -i
    SMTP | localhost | localhost
    smtp_port | 25 | 25
    ==========================
    Size and Time
    ------------------
    Directive | Local Value | Master Value
    max_execution_time | 30 | 30
    max_input_time | 60 | 60
    upload_max_filesize | 2M | 2M
    post_max_size | 8M | 8M
    ==========================
    Page generated in 0.043 seconds - 9 queries in 0.002 seconds - Album set :
Help, anyone? :)
Logged

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47843
  • aka "GauGau"
    • gaugau.de

don't post debug_output unless requested, it clutters the thread.
Logged

pokk

  • Coppermine newbie
  • Offline Offline
  • Posts: 10

I have the same error still, probably because your both codes which you posted are same.
Logged

Nibbler

  • Guest

The codes are different.

Try this version instead if you have MySQL >= 4.1

Code: [Select]
$sql = "SELECT user_id, user_name, UNIX_TIMESTAMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, user_active, ".

           "(SELECT COUNT(pid)
FROM {$CONFIG['TABLE_PICTURES']} AS p
WHERE owner_id = user_id
) AS pic_count,

(SELECT ROUND( SUM( total_filesize ) /1024 )
FROM {$CONFIG['TABLE_PICTURES']} AS p
WHERE owner_id = user_id
) AS disk_usage, group_name, group_quota ".

           "FROM {$CONFIG['TABLE_USERS']} AS u ".
           "INNER JOIN {$CONFIG['TABLE_USERGROUPS']} AS g ON user_group = group_id ".
$searchstr.
           "GROUP BY user_id ".
           "ORDER BY " . $sort_codes[$sort] . " ".
           "LIMIT $lower_limit, $user_per_page;";

I don't know if it will work, but it's worth a try.
Logged

pokk

  • Coppermine newbie
  • Offline Offline
  • Posts: 10

I tried that but it don't still work. I have MySQL 4.1.18-standard. I'll try to repair MySQL table, I read that it may help.
Logged

Aleksandra

  • Coppermine newbie
  • Offline Offline
  • Gender: Female
  • Posts: 14
  • Darth Minion
    • Grey-Wanderer.org

The codes are different.

Try this version instead if you have MySQL >= 4.1

Code: [Select]
$sql = "SELECT user_id, user_name, UNIX_TIMESTAMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, user_active, ".

           "(SELECT COUNT(pid)
FROM {$CONFIG['TABLE_PICTURES']} AS p
WHERE owner_id = user_id
) AS pic_count,

(SELECT ROUND( SUM( total_filesize ) /1024 )
FROM {$CONFIG['TABLE_PICTURES']} AS p
WHERE owner_id = user_id
) AS disk_usage, group_name, group_quota ".

           "FROM {$CONFIG['TABLE_USERS']} AS u ".
           "INNER JOIN {$CONFIG['TABLE_USERGROUPS']} AS g ON user_group = group_id ".
$searchstr.
           "GROUP BY user_id ".
           "ORDER BY " . $sort_codes[$sort] . " ".
           "LIMIT $lower_limit, $user_per_page;";

I don't know if it will work, but it's worth a try.

Ahh, thank you so much for your help. I'm running MySQL 4.1.18 and as you suggested I replaced that one part of the coding with the one you suggested just now, and it worked! The user manager seems to be functioning again. Thanks so much for your assistance! :D
Logged
Pages: [1]   Go Up
 

Page created in 0.039 seconds with 20 queries.