HikariCp pool getting Unknown system variable 'tx_isolation' exception

[lusit]

Environment

HikariCP version: 3.2.0
JDK version     : 1.8.0_111
Database        : PostgreSQL|MySQL|...
Driver version  : x.x.x

^{⚠️ Please verify that your issue still occurs on the latest version of HikariCP before reporting.}

---

Have you searched the CLOSED issues already? How about checking stackoverflow?

I have an application where user can choose any type of database(relational) provide URL, username, password and create datasource connect to database, then run SELECT queries. To connect database I am using HikariCp for my connection pool(before I used DBCP2 as connection timeout didn't work I changed to HikariCp). Here is my configuration

HikariConfig config = new HikariConfig();       
config.setDriverClassName(StringUtils.trimToEmpty(driver));      
config.setJdbcUrl(StringUtils.trimToEmpty(url));
config.setUsername(username);
config.setPassword(password);       
config.setMaximumPoolSize(POOL_MAX_ACTIVE);
config.setMinimumIdle(POOL_INITIAL_SIZE);        
config.setConnectionTestQuery(validationQuery);      
config.setConnectionTimeout(TimeUnit.SECONDS.toMillis(jdbcTimeout));    
HikariDataSource dataSource = new HikariDataSource(config);
Connection connection = dataSource.getConnection();     

When I am trying to connect MYsql database I am getting the following error(even with the HikariCp latest version):

HikariPool-8 - Starting... 
2019-04-10  18:45:49  WARN com.zaxxer.hikari.pool.PoolBase:472 - ReqId:60a79ef60413424e96a4fa73010fc861 - HikariPool-8 - Default transaction isolation level detection failed ((conn=14) Unknown system variable 'tx_isolation'). 
2019-04-10  18:45:49 ERROR com.zaxxer.hikari.pool.HikariPool:574 - ReqId:60a79ef60413424e96a4fa73010fc861 - HikariPool-8 - Exception during pool initialization. 
java.sql.SQLException: (conn=14) Unknown system variable 'tx_isolation'
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:198)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)

What is wrong in the configuration? with DBCP2 it works fine

I am using mariadb-java-client, version: '2.2.1' driver to connect to Mysql database.

[nunnrlc]

I'm guessing you are working with Mysql version 8.

The version 5 setting "tx_isolation" has been renamed to "transaction_isolation". That change is matched by the official mysql Connector Java JDBC driver, version 8 release.

To fix the error, change your application to use the newest Connector Java driver instead of mariadb driver, when connecting to Mysql databases.

Perhaps DBCP wasn't changing the transaction setting, so you didn't encounter this issue before.

Please note that the Mysql and mariadb databases are quite different from each other now with versions 8 and 10 respectively (only version 5 of each can be treated as the same). For instance, Mysql 8 has fully transactional DDL, whilst mariadb 10 has point-in-time querying, and support for Oracle PL/SQL syntax.

Lastly, with Connector Java version 8 I have seen some changes to date handling, now enforcing time zone correction. If you are running an application, and database in different zones, you need to check that you don't get unexpected time offsets compared to the older JDBC drivers.

All the best.