After MySQL change to utf8mb4, Error Unknown character set index for field '224' received from server

Question

I've changed our server MySQL database to use utf8mb4 because we need to support emoticon. I have followed the instruction based on How to support full Unicode in MySQL databases article by Mathias Bynens. The only difference is I wasn't changing from utf8.

Here's the configuration:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Here is MySQL SHOW VARIABLES output:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

After I restarted MySQL & my application, the console printed the error log as below:

2014-08-05 17:33:49,022 [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2] WARN  com.mchange.v2.resourcepool.BasicResourcePool - com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@6ae03500 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Unknown character set index for field '224' received from server.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
    at com.mysql.jdbc.Connection.getCharsetNameForIndex(Connection.java:3556)
    at com.mysql.jdbc.Field.<init>(Field.java:192)
    at com.mysql.jdbc.MysqlIO.unpackField(MysqlIO.java:731)
    at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:387)
    at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2076)
    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1451)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1787)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3206)
    at com.mysql.jdbc.Statement.executeQuery(Statement.java:1232)
    at com.mysql.jdbc.Connection.loadServerVariables(Connection.java:4478)
    at com.mysql.jdbc.Connection.initializePropsFromServer(Connection.java:4062)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2789)
    at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Aug 5, 2014 5:33:49 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: null
Aug 5, 2014 5:33:49 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Connections could not be acquired from the underlying database!
Aug 5, 2014 5:33:49 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: null
Aug 5, 2014 5:33:49 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Connections could not be acquired from the underlying database!

Login to my CMS nor API calls don't work. It gave me the above error for which I can't google any solution.

What to do next, in order to fix the error?

Solution

I was there too, and I fixed it by simply upgrading the connector to "mysql-connector-java-5.1.30-bin.jar". Hope it would help.

Download latest connector from https://dev.mysql.com/downloads/connector/j/

Comments

[Dimag Kharab]

Thanks you saved my Life !

[Arjun Dev]

Thanks. You saved my life as well in 2020