记录使用mysql-connector-java低版本和最新版连接MYSQL的问题，及解决方法

mysql-connector-java低版本：mysql-connector-java-5.1.7.jar

mysql-connector-java高版本：mysql-connector-java-8.0.15.jar

做的C3P0的实验（没使用配置文件），

使用mysql-connector-java-5.1.7.jar包低版本下关键代码：

		dataSource.setDriverClass("com.mysql.jdbc.Driver");
		dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
		dataSource.setUser("root");
		dataSource.setPassword("root");

运行报错如下：

		5月 12, 2019 4:54:41 下午 com.mchange.v2.log.MLog <clinit>
		信息: MLog clients using java 1.4+ standard logging.
		5月 12, 2019 4:54:42 下午 com.mchange.v2.c3p0.C3P0Registry banner
		信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
		5月 12, 2019 4:54:42 下午 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 -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge758a21biky4t1w62z4d|57a3af25, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge758a21biky4t1w62z4d|57a3af25, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost/bank, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 30, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 200, maxStatementsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {password=******, user=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]

		5月 12, 2019 4:55:02 下午 com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run
		警告: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@7b63b516 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
		5月 12, 2019 4:55:02 下午 com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run
		警告: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@7b63b516 -- APPARENT DEADLOCK!!! Complete Status: 
		Managed Threads: 3
		Active Threads: 3
		Active Tasks: 
		com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@5671ce9 		(com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
		com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@2a209acd (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
		com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@54b6b17e (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
		Pending Tasks: 
		com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@22412447
		com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1ffcf4b2
		com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@3124f372
		com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@50f88969
		com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@7bb0ec2d
		com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@311ce8d
		com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@15b812fe
		Pool thread stack traces:
		Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,main]
		java.base@11.0.1/java.lang.Thread.sleep(Native Method)
		app//com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
		app//com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
		Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,main]
		java.base@11.0.1/java.lang.Thread.sleep(Native Method)
		app//com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
		app//com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
		Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2,5,main]
		java.base@11.0.1/java.lang.Thread.sleep(Native Method)
		app//com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
		app//com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
		5月 12, 2019 4:55:13 下午 com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask run
		警告: com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@54b6b17e -- 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 system variable 'tx_isolation'
		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:3515)
		at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
		at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
		at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
		at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
		at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
		at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
		at com.mysql.jdbc.ConnectionImpl.getTransactionIsolation(ConnectionImpl.java:3131)
		at com.mchange.v2.c3p0.impl.NewPooledConnection.<init>(NewPooledConnection.java:107)
		at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:198)
		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)
		java.sql.SQLException: Connections could not be acquired from the underlying database!
		at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
		at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)
		at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
		at com.itlwj.c3p0.C3P0Test.testC3P0(C3P0Test.java:48)
		at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
		at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
		at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
		at java.base/java.lang.reflect.Method.invoke(Method.java:566)
		at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
		at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
		at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
		at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
		at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
		at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
		at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
		at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
		at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
		at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
		at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
		at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
		at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
		at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:89)
		at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)
		at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:541)
		at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:763)
		at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:463)
		at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:209)
		Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
		at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)
		at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
		at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
		at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
		... 25 more

以上报错关键就两行：

		java.sql.SQLException: Unknown system variable 'tx_isolation'

		java.sql.SQLException: Connections could not be acquired from the underlying database!

分析错误结果：

	所使用的mysql-connector-java-5.1.7.jar版本低导致与MYSQL版本及JAR版本不兼容。

使用mysql-connector-java-8.0.15.jar包高版本下初始关键代码：

		dataSource.setDriverClass("com.mysql.jdbc.Driver");
		dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
		dataSource.setUser("root");
		dataSource.setPassword("root");

这时，出现了和低版本一样的错误。

修改初始关键代码后的代码：

		dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
		dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?serverTimezone=GMT");
		dataSource.setUser("root");
		dataSource.setPassword("root");

仔细观察，有两处更改了。结果编译运行顺利通过。

		dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
		dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?serverTimezone=GMT");

---

---

总结：

		每个版本对应有自己的语法规则和写法，所以有的错误，如果你很难解决，不如去看看对应版本的语法规则。

转载于:https://my.oschina.net/u/4121693/blog/3048609