MySQL Bugs: #3611: Illegal mix of collations error

Bug #3611	Illegal mix of collations error
Submitted:	30 Apr 2004 14:24	Modified:	12 Jul 2004 23:24
Reporter:	Oleg Ivanov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.1-alpha	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

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

This issue still exist in MySQL server version 5.1.41
I did a sub-query in FROM clause and used WHERE clause to filter results.
The following query works fine without WHERE clause. If you add WHERE clause you will receive an error code: "1267 Illegal mix of collations (latin1_swedish_ci, IMPLICIT) and (utf8_general_ci, COERCIBLE) for operation '='" 

This example is taken from the book I was reading:

SELECT PLAYERNO, NAME, PLAYERS.TOWN, NUMBER * 1000
FROM PLAYERS,
(SELECT 'Stratford' AS TOWN, 4 AS NUMBER
UNION
SELECT 'Plymouth', 6
UNION
SELECT 'Inglewood', 1
UNION
SELECT 'Douglas', 2) AS TOWNS
WHERE PLAYERS.TOWN = TOWNS.TOWN
ORDER BY PLAYERNO