springboot integrate hikariCP2.7.9, after config and access mysql, it throws "Default transaction isolation level detection failed (Proxy Warning - near ".": syntax error)."

Environment

HikariCP version: 2.7.9
JDK version     : 1.8.0_111
Database        : MySQL

Exception: HikariPool-1 - Exception during pool initialization. 2018-06-28 21:09:36.545 WARN 35084 --- [nio-8008-exec-2] c.z.h.p.PoolBase : HikariPool-1 - Default transaction isolation level detection failed (Proxy Warning - near ".": syntax error). java.sql.SQLException: Proxy Warning - near ".": syntax error at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.46.jar:5.1.46] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) ~[mysql-connector-java-5.1.46.jar:5.1.46] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) ~[mysql-connector-java-5.1.46.jar:5.1.46] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) ~[mysql-connector-java-5.1.46.jar:5.1.46] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) ~[mysql-connector-java-5.1.46.jar:5.1.46] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482) ~[mysql-connector-java-5.1.46.jar:5.1.46] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2440) ~[mysql-connector-java-5.1.46.jar:5.1.46] at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381) ~[mysql-connector-java-5.1.46.jar:5.1.46] at com.mysql.jdbc.ConnectionImpl.getTransactionIsolation(ConnectionImpl.java:3001) ~[mysql-connector-java-5.1.46.jar:5.1.46] at com.zaxxer.hikari.pool.PoolBase.checkDriverSupport(PoolBase.java:457) ~[HikariCP-2.7.9.jar:?] at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:412) ~[HikariCP-2.7.9.jar:?] at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:370) ~[HikariCP-2.7.9.jar:?] at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:194) ~[HikariCP-2.7.9.jar:?] at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:460) [HikariCP-2.7.9.jar:?] at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:534) [HikariCP-2.7.9.jar:?] at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:115) [HikariCP-2.7.9.jar:?] at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) [HikariCP-2.7.9.jar:?] at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151) [spring-jdbc-5.0.6.RELEASE.jar:5.0.6.RELEASE] at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115) [spring-jdbc-5.0.6.RELEASE.jar:5.0.6.RELEASE] at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78) [spring-jdbc-5.0.6.RELEASE.jar:5.0.6.RELEASE] at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82) [mybatis-spring-1.3.2.jar:1.3.2] at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68) [mybatis-spring-1.3.2.jar:1.3.2] at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:338) [mybatis-3.4.6.jar:3.4.6] at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:84) [mybatis-3.4.6.jar:3.4.6] at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62) [mybatis-3.4.6.jar:3.4.6] at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326) [mybatis-3.4.6.jar:3.4.6] at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) [mybatis-3.4.6.jar:3.4.6] at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) [mybatis-3.4.6.jar:3.4.6] at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) [mybatis-3.4.6.jar:3.4.6] at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) [mybatis-3.4.6.jar:3.4.6] at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) [mybatis-3.4.6.jar:3.4.6] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_101] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_101] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_101] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_101] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) [mybatis-spring-1.3.2.jar:1.3.2] at com.sun.proxy.$Proxy92.selectList(Unknown Source) [?:?] at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230) [mybatis-spring-1.3.2.jar:1.3.2] at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139) [mybatis-3.4.6.jar:3.4.6] at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76) [mybatis-3.4.6.jar:3.4.6] at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) [mybatis-3.4.6.jar:3.4.6]

my project use springboot 2.0 and the default integrate hikari 2.7.9.

config custom datasource:

@Bean(name = "xxOrderDSProperties")
@Qualifier("xxOrderDSProperties")
@ConfigurationProperties(prefix = "spring.datasource.datasource-xxorder")
public DataSourceProperties xxOrderDSProperties(){
    return new DataSourceProperties();
}

@Bean(name = "xxorderDS")
@ConfigurationProperties(prefix = "spring.datasource.datasource-xxorder")
public DataSource dataSourcexxOrder(){
    //return DruidDataSourceBuilder.create().build(); **//this could work**.
    return xxOrderDSProperties().initializeDataSourceBuilder().build(); //**this throws above exception**
}

config properties application.yaml: spring: datasource: datasource-xxorder: type: com.zaxxer.hikari.HikariDataSource name: datasource-xxorder url: jdbc:mysql://{$host}:{$port}/db?characterEncoding=UTF-8&characterSetResults=UTF-8&zeroDateTimeBehavior=convertToNull username: userxx password: pwdxx driver-class-name: com.mysql.jdbc.Driver

I debug the source code and found the code that throw above exception:

try { defaultTransactionIsolation = connection.getTransactionIsolation(); if (transactionIsolation == -1) { transactionIsolation = defaultTransactionIsolation; } } catch (SQLException e) { LOGGER.warn("{} - Default transaction isolation level detection failed ({}).", poolName, e.getMessage()); if (e.getSQLState() != null && !e.getSQLState().startsWith("08")) { throw e; }

I found the connection just has a property named "isolationLevel", rather than "transactionIsolation", and the getTransactionIsolation always get exception.

Then I compare it with druid. druid could skip this exception. and connect, execut sql operation success. code from druid: try { this.underlyingTransactionIsolation = conn.getTransactionIsolation(); } catch (SQLException e) { // compartible for alibaba corba if ("HY000".equals(e.getSQLState()) || "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException".equals(e.getClass().getName())) { // skip } else { throw e; } }

I want to know how to make hikariCP work？ Any suggestion? Thanks.

brettwooldridge / HikariCP

springboot integrate hikariCP2.7.9, after config and access mysql, it throws "Default transaction isolation level detection failed (Proxy Warning - near ".": syntax error)." #1191

Environment