3

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)
Adi Prasetyo
  • 1,006
  • 1
  • 15
  • 41
dotwin
  • 1,302
  • 2
  • 11
  • 31
  • Please provide the entire `SELECT` statement, plus the other statements in the transaction. Please provide the connection parameters used when connecting to MySQL. – Rick James Mar 01 '16 at 21:53
  • Updated. Thank you for your help, @RickJames. – dotwin Mar 01 '16 at 22:00
  • Change `SELECT` statement to `SELECT name, other_stuff_1 FROM test_names LIMIT 1 ORDER BY name COLLATE latin1_bin FOR UPDATE`. I didn't test the statement, it required MySQL 5.7 or higher version. – Beck Yang Mar 04 '16 at 09:04
  • Thank you very much for your answer, @beckyang. I tried `ORDER BY name COLLATE latin1_bin LIMIT 1 FOR UPDATE` (ORDER BY goes in front of LIMIT), but unfortunately still receive `java.sql.SQLException: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<=>'`. – dotwin Mar 04 '16 at 15:30
  • Probably a bug in Connector/J—you appear to be using v5.1.29, whereas the current GA release is v5.1.38. Are you able to try with the latest version? – eggyal Mar 09 '16 at 11:15
  • you may take a look [here](http://stackoverflow.com/a/21061305/2368696) – Adi Prasetyo Mar 10 '16 at 15:55

3 Answers3

1

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
Community
  • 1
  • 1
SkyWalker
  • 28,384
  • 14
  • 74
  • 132
0

The problem is that you are trying to select from a table which has different charsets on different columns.

You have to convert the columns that have different charsets in the query to the proper charset using CONVERT().

http://dev.mysql.com/doc/refman/5.7/en/charset-convert.html

In your case, you should modify your query to: 

SELECT CONVERT(name USING latin1), other_stuff_1 FROM test_names LIMIT 1 FOR UPDATE;
Andras Szell
  • 527
  • 2
  • 13
  • Thank you very much for your answer. Using `CONVERT` does not work, as it makes the `ResultSet` not updatable; I cannot update `other_stuff_1` any longer. The error message reads `com.mysql.jdbc.NotUpdatable: Result Set not updatable (references computed values or doesn't reference any columns or tables).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.` – dotwin Mar 04 '16 at 15:07
  • The `ResultSet` is created with `ResultSet.TYPE_FORWARD_ONLY` and `ResultSet.CONCUR_UPDATABLE`. The error appears due to converting. But this is definitely the right approach. I somehow need to tell Java that the PK in this DB is LATIN1. – dotwin Mar 04 '16 at 15:21
0

IMHO you can't use updatable resultset in this case when the encoding in the table is not uniform (for example the primary key is in latin1 but the other columns are in utf8).

As Andras pointed out, you can convert the encoding on the sql side but then you won't be able to update the resultset.

Why don't you simply update the table with executeUpdate(...)? You can narrow down the target line(s) with a simple select then iterate over the resulting primary key list and call executeUpdate.

For me that is working for mixed encoded columns. Just an example:

    conn.setAutoCommit(false);
    ResultSet rs = st.executeQuery("SELECT name other_stuff_1 FROM test_names");
    List<String> keys = new ArrayList<String>();
    while(rs.next()) {
        keys.add(rs.getString(1));
    }
    rs.close();
    for(String key : keys) {
        st.executeUpdate("update test_names set other_stuff_1='"+key.length()+"', other_stuff_2='" + key.toUpperCase() + "' where name='" + key + "'");
    }
    conn.commit();
riskop
  • 1,693
  • 1
  • 16
  • 34