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 :
this is not an error message, this is the debug output. Post the actual error message you get.
Joachim
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
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
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.