连接MySQL时,字符无法识别
这个是之前遇到的问题,当时在网上看到MySQL的字符集有选项为utf-8mb4,据说是utf-8的升级版,于是乎就有了下面的故事。
主要异常:java.sql.SQLException: Unknown character set index for field ‘224’ received from server.
异常
昨天还可以的项目今天就搞不定了。程序向我抛了一堆异常:
二月 27, 2017 7:39:59 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
二月 27, 2017 7:39:59 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
二月 27, 2017 7:39:59 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 30000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1pmfv249mzc9i32rpv5yt|114e4582, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1pmfv249mzc9i32rpv5yt|114e4582, idleConnectionTestPeriod -> 30, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost:3306/productstore, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 30, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 200, maxStatementsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
二月 27, 2017 7:40:19 下午 com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run
警告: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@3f3d8d82 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
二月 27, 2017 7:40:19 下午 com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run
警告: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@3f3d8d82 -- APPARENT DEADLOCK!!! Complete Status:
Managed Threads: 3
Active Threads: 3
Active Tasks:
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@3bfdef54 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1c6b9291 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@5a637c2a (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
Pending Tasks:
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@396cd7c2
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@18c91f02
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@3c12c904
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@754b446b
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@207908f6
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@6a3b5f21
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@5fe2a603
Pool thread stack traces:
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,main]
java.lang.Thread.sleep(Native Method)
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2,5,main]
java.lang.Thread.sleep(Native Method)
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,main]
java.lang.Thread.sleep(Native Method)
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
二月 27, 2017 7:40:28 下午 com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask run
警告: com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@3bfdef54 -- 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)
很多异常直接懵逼,提示有无法识别的字符,但是当时没有想到是修改字符集导致的,一直以为程序中文件的编码或者是我的存储的字符有特殊的非法字符,后来检查了很久觉得自己很没有问题,于是就去网上搜索了,找到了相同的问题,utf-8mb4。就是因为这个原因。
解决
因为MySQL数据库以utf8mb4解析客户端发送的请求,导致建立连接的时候得到 java.sql.SQLException: Unknown character set index for field ‘224’ received from server.
从而导致连接创建不成功。
后来我就把MySQL的字符集改了回去,就没有问题了,所以说不要瞎改字符集,不要作死。