CODEWITHSUNDEEP
spring-boothikaricpspring-mybatismulti-database
michael
michael

Reputation: 1

springboot2.0 integrate hikariCP2.7.9, throws "Default transaction isolation level detection failed (Proxy Warning - near ".": syntax error)."

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 with springboot correctly when config with mybatis to access mysql？ Any suggestion?

Thanks.

Upvotes: 0

Views: 2519

Answers (1)

michael
michael

Reputation: 1

Found the reason. We use atlas as db middleware, and it could not support the command "SELECT @@session.tx_isolation" when hikari getTransactionIsolation used. and it will throw exception "ERROR 1105 (HY000): Proxy Warning - near ".": syntax error"

however, druid skip this syntax exception. and could exec the following sql success.

solutions:

1.if hikari could skip this exception like druid, it will be solved. 2.if atlas could support the command "SELECT @@session.tx_isolation", it will work. 3.just change to druid.

Upvotes: 0

Related Questions