There was an error while processing a database query

[ksoliz]

Ok so I searched first before posting this and found several related posts addressing this problem, however none of them seemed an exact match. Basically I got the "There was an error while processing a database query" error when attempting to delete a "gallery/album." CPG has been installed for about a week humming along on a win2003 standard,MySQL4.0,PHP4.3,IIS6 server working fine with this one exception.

Some of my own troubleshooting...

1. I noticed the user for the MySQL connection didnt have the permissions you would have expected it to have such as "ALTER." I went ahead and changed that and it didnt make a difference.

2. Double checked the permissions for the album folders so the ISUR account had write permissions. (It dose)

3. Lastly Im not sure how to interpret the debug info... I dont really understand the MySQL error it references.


Now on to the error debug...

Critical ERROR:

ERROR: There was an error while processing a database query.

DEBUG output...

While executing query "DELETE FROM cpg132_exif WHERE filename='albums/Christmas 2004/Dad's camera Xmas 04 001.jpg' LIMIT 1" on 0

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 's camera Xmas 04 001.jpg' LIMIT 1' at line 1

File: E:\WebServer\wwwroot\elsoliz.com\picgallery\include\functions.inc.php - Line: 105


DEBUG INFO:

USER:
------------------
Array
(
   [ID] => cbcda1194f4bd4f079ce8bac52f0de13
   [am] => 1
)

==========================
USER DATA:
------------------
Array
(
   [user_id] => 1
   [user_group] => 1
   [user_active] => YES
   [user_name] => ksoliz
   [user_password] => ********
   [user_lastvisit] => 2005-01-31 13:28:54
   [user_regdate] => 2005-01-20 10:24:10
   [user_group_list] =>
   [user_email] =>
   [user_website] =>
   [user_location] =>
   [user_interests] =>
   [user_occupation] =>
   [user_actkey] =>
   [user_favpics] =>
   [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 cpg132_filetypes;
     [1] => SELECT * FROM cpg132_users WHERE user_id='1'AND user_active = 'YES' AND user_password != '' AND BINARY MD5(user_password) = '29cd7ef0dfd140e374f98c835510d2b3'
     [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 cpg132_usergroups WHERE group_id in (1)
     [3] => SELECT group_name FROM  cpg132_usergroups WHERE group_id= 1
     [4] => DELETE FROM cpg132_banned WHERE expiry < 1107203052
     [5] => SELECT * FROM cpg132_banned WHERE ip_addr='129.106.7.157' OR ip_addr='129.106.7.157' OR user_id=1
     [6] => SELECT title, category FROM cpg132_albums WHERE aid ='2'
     [7] => SELECT pid FROM cpg132_pictures WHERE aid='2'
     [8] => SELECT aid, filepath, filename FROM cpg132_pictures WHERE pid='2'
     [9] => DELETE FROM cpg132_comments WHERE pid='2'
     [10] => DELETE FROM cpg132_exif WHERE filename='albums/Christmas 2004/Dad's camera Xmas 04 001.jpg' LIMIT 1
  )
  
  ==========================
  GET :
  ------------------
  Array
  (
     [id] => 2
     [what] => album
  )
  
  ==========================
  POST :
  ------------------
  Array
  (
  )
  
  ==========================
  VERSION INFO :
  ------------------
  PHP version: 4.3.7 - OK
  ------------------
  mySQL version: 4.0.20a-nt
  ------------------
  Coppermine version: 1.3.2
  ==========================
  Module: gd
  ------------------
  GD Support enabled
  GD Version bundled (2.0.15 compatible)
  FreeType Support enabled
  FreeType Linkage with freetype
  GIF Read 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 3.23.49
  ==========================
  Module: zlib
  ------------------
  ZLib Support enabled
  Compiled Version 1.1.4
  Linked Version 1.1.4
  ==========================
  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 | .;c:\php4\pear | .;c:\php4\pear
  open_basedir | no value | no value
  ==========================
  email
  ------------------
  Directive | Local Value | Master Value
  sendmail_from | me@localhost.com | me@localhost.com
  sendmail_path | no value | no value
  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.662 seconds - 11 queries in 0.005 seconds - Album set :
  
  
  
  Any help on this issue would be greatly appreciated.

[Tranz]

I think this

Code Select Expand

'albums/Christmas 2004/Dad's camera Xmas 04 001.jpg'
is the problem. The apostrophe in the file name wasn't escaped. So it is being read as if it were the closing apostrophe. I guess you can edit the database entry to remove the apostrophe, and rename the file.

edit: I hope you didn't read this post when I said to remove the file. I meant to remove the apostrophe from the filename.

[ksoliz]

I was just re-reading your post and thought to myself..."I wonder if he means to delete the file?"

I havent done anything yet because I have a few questions...

Can I simply rename all the files in that albums to remove the ' ? I pretty sure that would mess up with the DB entries. I would just assume delete the tables in the DB then rename the files and try to create/delete the album again.

Thanks,
Kevin

[Joachim Müller]

I think the problem with the unescaped single quote has been fixed, try getting the most recent version of searchnex.php (the file for batch-adding) from the web cvs and replace the one on your server with it: http://cvs.sourceforge.net/viewcvs.py/coppermine/stable/searchnew.php

Joachim

P.S.

"I wonder if he means to delete the file?"

he -> she