mysql 4.1 problems with batch add

[thimble]

while trying to do a batch add i get this error in the debug window

While executing query "SELECT aid, CONCAT('(', user_name, ') ', title) AS title FROM cpg132_albums AS a INNER JOIN cpg132_users AS u ON category = (10000 + user_id) ORDER BY title" on 0

mySQL error: Illegal mix of collations for operation 'concat'

here's my debug info

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

==========================
USER DATA:
------------------
Array
(
    [user_id] => 1
    [user_group] => 1
    [user_active] => YES
    [user_name] => curtis
    [user_password] => ********
    [user_lastvisit] => 2004-10-16 11:21:57
    [user_regdate] => 2004-10-16 11:21:41
    [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 cpg132_filetypes;
      [1] => SELECT * FROM cpg132_users WHERE user_id='1'AND user_active = 'YES' AND user_password != '' AND BINARY MD5(user_password) = 'a008a5e02ec40d9facee2ea028073662'
      [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 < 1097952827
      [5] => SELECT * FROM cpg132_banned WHERE ip_addr='64.229.228.79' OR ip_addr='64.229.228.79' OR user_id=1
      [6] => SELECT filepath, filename FROM cpg132_pictures WHERE filepath LIKE 'fiji%'
      [7] => SELECT aid, title FROM cpg132_albums WHERE category = 0 ORDER BY title
      [8] => SELECT DISTINCT a.aid as aid, a.title as title, c.name as cname FROM cpg132_albums as a, cpg132_categories as c WHERE a.category = c.cid AND a.category < '10000' ORDER BY cname,title
      [9] => SELECT aid, CONCAT('(', user_name, ') ', title) AS title FROM cpg132_albums AS a INNER JOIN cpg132_users AS u ON category = (10000 + user_id) ORDER BY title
  )
  
  ==========================
  GET :
  ------------------
  Array
  (
      [startdir] => fiji
  )
  
  ==========================
  POST :
  ------------------
  Array
  (
  )
  
  ==========================
  VERSION INFO :
  ------------------
  PHP version: 5.0.2 - OK
  ------------------
  mySQL version: 4.1.4-gamma-Max-log
  ------------------
  Coppermine version: 1.3.2
  ==========================
  Module: gd
  ------------------
  GD Support enabled
  GD Version bundled (2.0.28 compatible)
  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.4-gamma
  MYSQL_MODULE_TYPE external
  MYSQL_SOCKET /var/lib/mysql/mysql.sock
  MYSQL_INCLUDE -I/usr//include/mysql
  MYSQL_LIBS -L/usr//lib -lmysqlclient 
  ==========================
  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 | .:/usr/local/lib/php | .:/usr/local/lib/php
  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 | -1 | -1
  upload_max_filesize | 5M | 5M
  post_max_size | 8M | 8M
  ==========================
  Page generated in 0.06 seconds - 10 queries in 0.003 seconds - Album set :

[Joachim Müller]

this is not an error message, this is the debug output. Post the actual error message you get.

Joachim

[thimble]

ok the error was not helpful that's why i included this debug info

here's what the page said

Critical error

There was an error while processing a database query.


File: /home/wetherly/jakarta-tomcat-5.0.27/webapps/ROOT/photos/include/functions.inc.php - Line: 105

[thimble]

Looks like coppermine has a problem with my mysql4.1 setup. Complaining about character sets. I can run the query in phpMyAdmin and it works fine, so it must be a problem with php sending the string to mysql in an incorrect characterset maybe? Not sure.

Anyways the fix is to remove the concat function and just have the title field be title instead of CONCAT('(', user_name, ') ", title) as title

[kegobeer]

This appears to be a unicode character set issue.  It's been reported with other MySQL databases - do a search for Illegal mix of collations for operation 'concat' on google.  Sometimes when a client has one character set and the database has another, this can happen.  Not much of a solution for you, but at least it helps explain the problem.  If you have a utf8 language file as the default, try switching to a non-utf8 file.  You can also bring this issue to your webhost's attention and they may be able to assist you better.