低版本Mysql驱动包不能连接除3306之外端口bug的排查过程

如果项目中的mysql驱动使用是如下版本:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.9</version>
</dependency>

请升级至如下版本(5版本的最后一个版本):

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>

解决了:不能连接除了3306之外端口服务的bug。

bug排查过程总结:

项目背景:

1.项目中使用的c3p0;
2.mysql驱动版本 5.1.9

项目中报错信息:

2018-05-25 18:42:59,692  INFO AbstractPoolBackedDataSource:203 - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge46g9v15v8fgr1fzep76|391eb4d7, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge46g9v15v8fgr1fzep76|391eb4d7, idleConnectionTestPeriod -> 0, initialPoolSize -> 2, jdbcUrl -> jdbc:mysql://192.168.1.41:3307/uat_enginev4_mysql, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 4, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 2, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
    2018-05-25 18:43:30,059  WARN BasicResourcePool:214 - com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@4345974b -- 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: null,  message from server: "Host '192.168.16.168' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1104)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2137)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    at sun.reflect.GeneratedConstructorAccessor21.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1127)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1114)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1855)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)

jdbc报错信息:

java.sql.SQLException: Access denied for user 'root'@'192.168.16.168' (using password: YES)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919)
    at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3996)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1284)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2137)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)

排查过程:

1.使用客户端工具Navicat测试可以连通;
2.使用Druid 问题依然存在;
3.写了一个单元测试是ok的;(当时没有注意我的单元测试使用的版本是5.1.40,项目中使用的5.1.9,自坑了3个小时,汗颜！！！)[单元测试尽量写在当前使用的项目中,切记]
4.网上各种搜索,大部分说是mysql启动配置文件有问题,害的DBA同事各种验证各种改;
5.问题解决之前也在官方网站寻找相关,可惜没找到(没认真找)。像这么大的bug,官方应该有说明才是,比如修复列表之类的,可喜的是找到了。详见[https://bugs.mysql.com/bug.php?id=47494](作者Jens Nehlmeier在5.1.10中已修复)

重要链接:
(Mysql Bugs Home)https://bugs.mysql.com [说明:可在此页面提bug也可以检索别人提的bug或已解决的bug]