Search code examples
javamysqlresultsetcollation

ResultSet.updateRow() produces "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<=>'"

I have the following table with name being LATIN1 and the rest being UTF8.

CREATE TABLE `test_names` (
  `name` varchar(500) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `other_stuff_1` int DEFAULT NULL,
  `other_stuff_2` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I encounter the following problem in Java:

I SELECT ... FOR UPDATE. Then I call updateInt(2, 1) and updateRow() on its ResultSet and get Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<=>'.

How can I make this work without changing the table’s / connection’s charset?

Thanks a lot.

--- UPDATE ---

I use SELECT name, other_stuff_1 FROM test_names LIMIT 1 FOR UPDATE; and the connection string is DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + db + "?allowMultiQueries=true", user, password);.

The exact stack trace is:

java.sql.SQLException: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<=>'
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2441)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2366)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2350)
    at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2405)
Solution

  • From my side, I can give you some suggestions

    1. First update your Connector/J latest version.
    2. Run this query
    SET NAMES='utf8'

    1. Add &characterEncoding=UTF-8 onto your JDBC connect string. Hope you have done it already.

    2. Use convert() for insert or update and cast() for select query. For more details http://dev.mysql.com/doc/refman/5.7/en/charset-convert.html

    3. For "Illegal mix of collations" related issue, you can follow up Troubleshooting "Illegal mix of collations" error in mysql