MySql8.0碰到的坑

- 日理万妓 2021-12-19 03:09 276阅读 0赞

Mysql的版本为8.0.16，JDBC的jar包为8.0.16，c3p0版本为0.9.5.2

1.使用c3p0连接数据库的时候出现的问题，配置文件如下所示。

<c3p0-config>
      
      <default-config>
      	
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
        <property name="user">waggag</property>
        <property name="password">225514</property>
        
        
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">10</property>
        <property name="checkoutTimeout">3000</property>
      </default-config>
    
      <named-config name="oraclec3p0"> 
        
        <property name="driverClass">oracle.jdbc.OracleDriver</property>
        <property name="jdbcUrl">jdbc:oracle:thin:@//127.0.0.1:1521/orcl.168.118.1</property>
        <property name="user">waggag</property>
        <property name="password">225514</property>
        
        
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">8</property>
        <property name="checkoutTimeout">1000</property>
      </named-config>
    </c3p0-config>

2.demo实例

public class Demo1 {
        public static void main(String[] args) throws SQLException {
            //1.创建数据库连接池对象
            DataSource dataSource = new ComboPooledDataSource();
            //2.获取连接对象
            Connection connection = dataSource.getConnection();
            //3.检查是否获取
            System.out.println(connection);
        }
    }

3.运行发现一直报错

Exception in thread "main" java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
    	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
    	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
    	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
    	at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
    	at cn.waggag.jdbc.C3P0Demo1.main(C3P0Demo1.java:21)
    Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@19bb089b -- timeout at awaitAvailable()
    	at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
    	at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
    	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
    	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
    	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
    	... 2 more

4.经过很多的尝试解决发现和报错信息无关，真正的错误是时区错误。

原因：在使用mysql的jdbc驱动最新版（8.0+）时，遇到数据库和系统时区差异引起的问题。

Caused by: java.sql.SQLException: The server time zone value 'XXXXXXXXX' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.   You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

5.修改url为

jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC

6.成功的连接数据库

7.成功的解决出现的问题

解决方法：

（1）我是升级Mysql的版本遇到的问题，解决可以降版本，这个我们知道就行了，并不推荐；

（2）还有一种是在jdbc连接的url后面加上serverTimezone=UTC或GMT。