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

[Aleksandra]

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.

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.

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

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

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!

Regards,
Aleksandra

[Nibbler]

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

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

[pokk]

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

[Nibbler]

Find

Code Select Expand

    $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 Expand

    $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;";

[Aleksandra]

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

Critical error

There was an error while processing a database query.

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

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

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

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?

[Joachim Müller]

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

[pokk]

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

[Nibbler]

The codes are different.

Try this version instead if you have MySQL >= 4.1

Code Select Expand


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

[pokk]

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.

[Aleksandra]

The codes are different.

Try this version instead if you have MySQL >= 4.1

Code Select Expand


$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!