MySQL Bugs: #3611: Illegal mix of collations error

Bug #3611	Illegal mix of collations error
Submitted:	30 Apr 2004 16:24	Modified:	13 Jul 2004 1:24
Reporter:	Oleg Ivanov
Status:	Duplicate
Category:	Connector/J	Severity:	S3 (Non-critical)
Version:	3.1.1-alpha	OS:	Microsoft Windows (Windows XP)
Assigned to:		Target Version:

Description:
After migrating from 3.0.9 driver to 3.1.1alpha some SELECTS could not be executed due to
error: "Illegal mix of collations.."
If it is a bug it must be solved, because jdbc driver are COMPLETELY broken. If it is a
"feature", it must be documented. It is not a good practice when switching a jdbc drivers
goes application unusable.

How to repeat:
Simple query:
rset = stmt.executeQuery("SELECT A, B, C FROM TABS WHERE EMAIL=LOWER('"+user+"')");

return an exception:
General error message from server: "Illegal mix of collations
(cp1251_bulgarian_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='"

The same error is reported in list http://lists.mysql.com/java/7373

This is a duplicate of #2177.

The error is caused by changes needed to support MySQL-4.1, which were made when the
charsets functionality in MySQL-4.1 was still in flux.

You will need to download the latest nightly snapshot of Connector/J 3.1 from
http://downloads.mysql.com/snapshots.php to see the fix, or wait for the release of
Connector/J 3.1.2.

would you please tell me which build fix the problem, I have the similiar issue on
3.1.3-beta.

If you're still getting 'Illegal Mix of Collations' errors, it is most likely due to a
mis-configuration or an issue with how you've defined the character sets for your
database and/or tables.

You will need to provide a repeatable testcase, with schema and your connection
attributes to tell for sure.

see http://dev.mysql.com/doc/mysql/en/Charset-server.html
      http://dev.mysql.com/doc/mysql/en/Charset-map.html
      http://dev.mysql.com/doc/mysql/en/Option_files.html

you should write your character and collation config in my.cnf,
then restart mysqld.

Good Luck!

nightly snapshot i downloaded as suggested have solved a problem completely.

I just wonder that official distributions MySql+ConnectorJ did not worked together. Now
there is no such problem with current distributions.

I have mysql 4.1.3 beta, and i'm getting the same problem. Tried 3.0.14, 3.1.3beta,
downloaded the latest night build of Connector/J
(mysql-connector-java-3.0-nightly-20040722.zip) and the problem remains. connection
string: 
connection =
DriverManager.getConnection("jdbc:mysql://"+server+"/"+dbName+"?user="+userName+"&password="+password+"&useUnicode=true&characterEncoding=Cp1257");
try to execute:
PreparedStatement psMonth = connection().prepareStatement("SELECT DISTINCT month FROM
jobs WHERE month >= ? AND month <= ? ORDER BY month");
psMonth.setString(1, Utils.formatMonth(from));
psMonth.setString(2, Utils.formatMonth(to));
ResultSet rsMonth = psMonth.executeQuery();
exception:
java.sql.SQLException: General error,  message from server: "Illegal mix of collations
(latin1_swedish_ci,IMPLICIT) and (cp1257_general_ci,COERCIBLE) for operation '>='"
The table in database has cp1257 encoding (cp1257_general_ci). Tried both MyISAM and
InnoDB, tried changing table encoding - the error is always the same.

I encountered this problem myself, just today, and it appeared to result from column-based
settings on collation that are in the database itself (i.e. not the constants you are
passing in the SQL query.

You can diagnose this by "show full columns from <table1>" and "show full columns from
<table2>".

The output may show you (as it did me) that the collation is set to "latin1_bin" in one
table and "latin1_swedish_ci" on the other.

I had to manually type

   alter table <table1> modify column <col1> varchar(32) character set latin1;

for each latin1_bin column to change them to "latin1_swedish_ci".  

The creator for these tables, by the way, was jbosscmp-jdbc.xml (JBoss 3.2.5).  I am
going to try putting 

     * @ejb.persistance jdbc-type="VARCHAR" sql-type="VARCHAR(32) CHARACTER SET latin1"

.. in my xdoclet definitions for my entity beans, hoping that fixes things.

Hope this helps.  Good luck.

A bit more on this.  Due to bugs in xdoclet (pretty obscure: composite primary key does
not interact well with @ejb.persistence sql-type...), I had to use this construct in my
xdoclet ejb instead:

     * @jboss.sql-type type="VARCHAR(32) CHARACTER SET latin1"

Hope this helps.

reply to this problem :
exception:
java.sql.SQLException: General error,  message from server: "Illegal mix of
collations (latin1_swedish_ci,IMPLICIT) and (cp1257_general_ci,COERCIBLE) for
operation '>='"
The table in database has cp1257 encoding (cp1257_general_ci). Tried both MyISAM
and InnoDB, tried changing table encoding - the error is always the same.
my opinion: perhaps your mysql default collation is latin1_swedish_ci, but the table
columns are cp1257_general_ci; have a check!

Damm,

This problem still exist jan 2009 in mysql 5.1.30 !! 

Running the latest QueryBrowser I get

mysql error: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation '='

Louis