d60eba
asked on
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
Hi,
I have upgraded to a new server, running MySQL 4.1.7 and using phpMyAdmin 2.6.1-pl3.
On running a simple query:
SELECT *
FROM pi_sms_message_queue
WHERE message_sent = 'n'
LIMIT 0 , 30
I receive the following error in phpMyAdmin:
#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICI T) and (utf8_general_ci,COERCIBLE ) for operation '='
The collation of that message_sent column is latin1_swedish_ci. Running this query:
SHOW VARIABLES LIKE 'coll%'
Produces:
Variable_name Value
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server utf8_general_ci
And /etc/my.cnf is as follows:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysq l.sock
default-character-set=utf8
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.lo g
pid-file=/var/run/mysqld/m ysqld.pid
-----------
So...any ideas how I can fix this problem? I have googled but with no luck.
Cheers,
Leon
I have upgraded to a new server, running MySQL 4.1.7 and using phpMyAdmin 2.6.1-pl3.
On running a simple query:
SELECT *
FROM pi_sms_message_queue
WHERE message_sent = 'n'
LIMIT 0 , 30
I receive the following error in phpMyAdmin:
#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICI
The collation of that message_sent column is latin1_swedish_ci. Running this query:
SHOW VARIABLES LIKE 'coll%'
Produces:
Variable_name Value
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server utf8_general_ci
And /etc/my.cnf is as follows:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysq
default-character-set=utf8
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.lo
pid-file=/var/run/mysqld/m
-----------
So...any ideas how I can fix this problem? I have googled but with no luck.
Cheers,
Leon
Try to change the database collation to utf8.
ASKER
Any ideas how I could?
I thought having default-character-set=utf8 in the my.cnf would achieve that, but it doesn't seem to work.
I thought having default-character-set=utf8
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
That did work, and is useful to know, although I prefered to not have to put "_latin" many times in each query!
In the end I did this:
Put: "default-character-set=lat in1" in my.conf
In phpMyAdmin, click on the little Home icon in the left bar, and then in the right bar changed MySQL connection collation to latin_swedish_ci.
All now works!
Cheers,
Leon
That did work, and is useful to know, although I prefered to not have to put "_latin" many times in each query!
In the end I did this:
Put: "default-character-set=lat
In phpMyAdmin, click on the little Home icon in the left bar, and then in the right bar changed MySQL connection collation to latin_swedish_ci.
All now works!
Cheers,
Leon
well i gave this solution because it seemed that you DID want to have a default character set to utf8 :)
yeah those things are annoying.. and i think they're even going to change the default from latin_swedish_ci to latin_general_ci, if not done yet.
yeah those things are annoying.. and i think they're even going to change the default from latin_swedish_ci to latin_general_ci, if not done yet.