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

[30 Sep 2005 5:10] Murali Sridharan
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 '='
[30 Sep 2005 10:55] Murali Sridharan
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
[5 Oct 2005 8:36] Valeriy Kravchuk
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...
[9 Oct 2005 9:04] William Leung
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
[12 Oct 2005 1:25] William Leung
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 '='
[6 Nov 2005 0:00] Bugs System
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".