MySQL Bugs: #2177: Illegal mix of collations when using latin2

Bug #2177	Illegal mix of collations when using latin2
Submitted:	21 Dec 2003 1:41	Modified:	28 Mar 2014 13:44
Reporter:	Juhasz Istvan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.0.9	OS:	Linux (Linux, Windows)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
Failed to using latin2 encoding. Database has default character set latin2. Tables have implicit default character set latin2. Using Connector/J 3.0.9 version. Connection string contains useUnicode=true and characterEncoding=latin2.

Exception:
java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin2_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1825)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1020)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2030)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1563)

The new "SET NAMES %enc%" instruction never processed. profileSQL=true, Trace on etc. When i'm using characterEncoding=UTF-8 in the connection string, the instruction "SET NAMES ..." has processed (reported). 

I tried to figure out what's wrong. 

Code fragment from 
@version $Id: Connection.java,v 1.31.2.38 2003/10/03 16:24:38 mmatthew Exp $

//
// Setup client character set for MySQL-4.1 and newer
//
        
if (this.io.versionMeetsMinimum(4, 1, 0) 
        && useUnicode()
	&& getEncoding() != null) {
        	 
	if (getEncoding().equalsIgnoreCase("UTF-8")) {
		// charset names are case-sensitive
		execSQL("SET NAMES utf8", -1, this.database);
	} else {
		String mysqlEncoding = (String)CharsetMapping.JAVA_TO_MYSQL_CHARSET_MAP.get(getEncoding());
        		
		if (mysqlEncoding != null) {
			execSQL("SET NAMES " + mysqlEncoding, -1, this.database);
		}
	}
}

Variable mysqlEncoding always set to null, execSQL("SET NAMES latin2") never processed => CharsetMapping.java

MySQL status:

mysql  Ver 14.3 Distrib 4.1.1-alpha, for Win95/Win98 (i32)
Connection id:           | 2
Current database:        | mysql
Current user:            | ODBC@localhost
SSL:                     | Not in use
Using delimiter:         | ;
Server version:          | 4.1.1-alpha-max-nt-log
Protocol version:        | 10
Connection:              | localhost via TCP/IP
Client characterset:     | latin2_general_ci
Server characterset:     | latin2_general_ci
TCP port:                | 3306
Uptime:                  | 1 hour 5 min 42 sec

Variables:

character_set_server     | latin2
character_set_system     | utf8
character_set_database   | latin2
character_set_client     | latin2
character_set_connection | latin2
character-sets-dir       | C:\mysql\share\charsets/
character_set_results    | latin2
collation_connection     | latin2_general_ci
collation_database       | latin2_general_ci
collation_server         | latin2_general_ci
table_type               | MYISAM

How to repeat:
Compile and run the test above:
package testsuite.simple;

import com.mysql.jdbc.NotUpdatable;
import testsuite.BaseTestCase;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

/**
 * Tests for select using latin2 encoding
 * @author  Jim
 * @version $Id: Latin2SelectTest.java,v 1.0.0.0 2003/12/21 10:18:00 jim Exp $
 */
public class Latin2SelectTest extends BaseTestCase {
    
    public Latin2SelectTest(String name) {
        super(name);
    }
    
    public static void main(String[] args) {
        new Latin2SelectTest("testLatin2Select").run();
    }
    
    public void setUp() throws Exception {
        super.setUp();
        createTestTable();
    }
    
    public void testLatin2Select() throws SQLException {
        Statement scrollableStmt = null;
        try {
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM MYUSER");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println("Record fetched (prepareStatement): "+rs.getString("NAME"));
            }
            pstmt = conn.prepareStatement("SELECT * FROM MYUSER WHERE NAME ='Õrmester_1'");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println("Record fetched (prepareStatement): "+rs.getString("NAME"));
            }
        } finally {
            if (scrollableStmt != null) {
                try {
                    scrollableStmt.close();
                } catch (SQLException sqlEx) {
                    ;
                }
            }
        }
    }
    
    private void createTestTable() throws SQLException {
        try {
            stmt.executeUpdate("DROP TABLE MYUSER");
        } catch (SQLException SQLE) {
            ;
        }
        stmt.executeUpdate(
        "CREATE TABLE MYUSER (ID int(6) not null, NAME varchar(255) not null, PRIMARY KEY (id))");
        for (int i = 0; i < 10; i++) {
            stmt.executeUpdate("INSERT INTO MYUSER VALUES ("+i+",'Õrmester_"+i+"')");
        }
    }
}

... and extend the dbUrl in the file BaseTestCase.java
dbUrl = "jdbc:mysql:///test?useUnicode=true&characterEncoding=latin2";

Results:

.Record fetched (prepareStatement): Õrmester_0
Record fetched (prepareStatement): Õrmester_1
Record fetched (prepareStatement): Õrmester_2
Record fetched (prepareStatement): Õrmester_3
Record fetched (prepareStatement): Õrmester_4
Record fetched (prepareStatement): Õrmester_5
Record fetched (prepareStatement): Õrmester_6
Record fetched (prepareStatement): Õrmester_7
Record fetched (prepareStatement): Õrmester_8
Record fetched (prepareStatement): Õrmester_9
E
Time: 0,441
There was 1 error:
1) testLatin2Select(testsuite.simple.Latin2SelectTest)
java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin2_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='"
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1825)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1020)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2030)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1563)
        at testsuite.simple.Latin2SelectTest.testLatin2Select(Latin2SelectTest.java:40)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

FAILURES!!!
Tests run: 1,  Failures: 0,  Errors: 1

Suggested fix:
CharsetMapping.java usage

testcase

Attachment: Latin2SelectTest.java (text/plain), 2.12 KiB.

Modified dbUrl with characterEncoding=latin2

Attachment: BaseTestCase.java (text/plain), 4.90 KiB.

You will most likely have to use nightly snapshot builds of Connector/J

The encoding name to use in the URL is a _Java_ encoding name. There is no encoding named 'latin2' in Java, you need to use the Java name 'ISO8859_2'. See http://java.sun.com/j2se/1.4.2/docs/guide/intl/encoding.doc.html for a list of valid encoding names in Java.

hi again,

ty Mark for your suggestion (e.g. ISO8859_2).
Here is the result produced by the Latin2SelecTtest using characterEncoding=ISO8859_2 in the dbUrl:

.E
Time: 0,3
There was 1 error:
1) testLatin2Select(testsuite.simple.Latin2SelectTest)
java.sql.SQLException: Syntax error or access violation,  message from server: "Unknown character set: 'czech'"
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1825)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1020)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109)
        at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1070)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2027)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:1996)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:1977)
        at com.mysql.jdbc.Connection.initializePropsFromServer(Connection.java:2680)
        at com.mysql.jdbc.Connection.createNewIO(Connection.java:1750)
        at com.mysql.jdbc.Connection.<init>(Connection.java:562)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:361)
        at java.sql.DriverManager.getConnection(DriverManager.java:512)
        at java.sql.DriverManager.getConnection(DriverManager.java:193)
        at testsuite.BaseTestCase.setUp(BaseTestCase.java:100)
        at testsuite.simple.Latin2SelectTest.setUp(Latin2SelectTest.java:27)

FAILURES!!!
Tests run: 1,  Failures: 0,  Errors: 1

the same error. jdbc driver is BROKEN!!!

Please try the latest nightly build of 3.0 from http://downloads.mysql.com/snapshots.php as there has been some charsets-related work that should fix this issue.

Fixed in 3.0.12