MySQL Bugs: #13633: character set and collations problem.

Bug #13633	character set and collations problem.
Submitted:	30 Sep 2005 5:10	Modified:	5 Nov 2005 8:36
Reporter:	Murali Sridharan	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.14	OS:	Linux (Fedora core 3)
Assigned to:		CPU Architecture:	Any

Description:
   I just upgraded the mysql from 4.1.13 to 4.1.14. i am using one sample application, that sample application uses mysql in UTF-8 format, but mysql default encoding is latin1 format. In mysql 4.1.13, i didn't face any problem by using that application. 

  If i try to use mysql 4.1.14 for that application, i am getting this error.

java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1085)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:670)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024) 

i just tried to change the mysql default encoding latin1 to UTF format..then also i getting the same error. 

I changed the UTF encoding in this level.
1. server
2.Database
3.Table
4.Column

still i am getting the same error.... is it posssible to change the default encoding of mysql to some other encoding format....

Please help me....i want the solution immediately....

Thanks
Murali.S

How to repeat:

java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

I just created one sample application, database i used for that application was mysql 4.1.13 (deafult latin1 format) and mysql-connector-j 3.1.10, and i tried to connect with database as UTF-8 format, that was worked fine without any problem.

  But, now i am trying to use mysql 4.1.14(default latin1 format) with mysql-connector-j 3.1.10 for the same application in UTF-8 format, it is not working, i am getting the exception like this...

java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (utf8_general_ci,COERCIBLE) for operation '='
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
        at
com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.jav
a:1085)
        at
com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.j
ava:670)
        at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024) 

-----------------------------------------------------------------------------------------------------------
Is the mysql 4.1.14 version support the mysql-connector-j 3.1.10 ?

Mysql 4.1.14 default encoding is latin1 format, which will accept the UTF-8 format for jdbc connection?

Please reply me...

Regards
Murali.S

Thank you for a problem report.

Please, send the minimal complete code and table structures that demonstrates the problem. At leats, the results of SHOW CREATE TABLE for table used in your prepared statement, as well as the statement itself are needed.

Can you perform the same actions in mysql command line client. I am trying to figure out the appropriate category for this bug report...

I have this problem either.

And I notice the problem is already reported at Bug #12371

But I am really confused that the bug can only be reproduced in server 4.1.14, in server 4.1.12, there is no such problem

Here copied the "How to repeat" from bug #12371
(It's all ok in mysql 4.1.12/4.1.13 for the same operation):
set names utf8;

create table t1 (a char(3), b varchar(10));
insert into t1 values ('bar','kostja');
prepare my_stmt from "select * from t1 where a=?";
set @a:='bar';
execute my_stmt using @a;
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '='

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".