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

Question

I am using play framework and I want to connect db, but I can't because I am getting following error:

play.api.Configuration$$anon$1: Configuration error[Cannot connect to database [default]]
Caused by: play.api.Configuration$$anon$1: Configuration error[Failed to initialize pool: Unknown system variable 'tx_isolation']
Caused by: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Unknown system variable 'tx_isolation' java.sql.SQLException: Unknown system variable 'tx_isolation

I tried to find tx_isolation, but it doesn't exist:

mysql> show variables like 'tx_isolation';
Empty set (0.00 sec)

So what is and how can I find tx_isolation?

---

Sorry. this is my error code. and I use mysql 8.0.11. so i find 'transaction_isolation'

play.db {
  config = "db"
  default = "default"
}
db {
//TODO : 작업필요
  default.driver = com.mysql.jdbc.Driver
  default.url = "jdbc:mysql://127.0.0.1:3306/testPlayDB"
  default.username = root
  default.password = "321A@654"
}

Error cause Default.url = "jdbc:mysql://127.0.0.1:3306/testPlayDB"

i use Scala, playframework and StackOverflow first time...

Thank you.

Solution 1

If you are using MYSQL8, try to show variables like 'transaction_isolation'. Mysql8 has renamed tx_isolation to transaction_isolation.

Solution 2

I had a same problem. I upgraded my MySql Connector and solved my issue. try to use version 8 , like this:

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.13</version>
</dependency>

Solution 3

You have a mismatch between client lib version and server version.

Here are the version numbers where things change:

mysql-connector-java-8.0.8 release notes

Important Change: Following the changes in MySQL Server 8.0.3, the system variables tx_isolation and tx_read_only have been replaced with transaction_isolation and transaction_read_only in the code of Connector/J. Users should update Connector/J to this latest release in order to connect to MySQL 8.0.3. They should also make the same adjustments to their own applications if they use the old variables in their codes. (Bug #26440544)

mysql server 5.7.20 release notes

Previously, the --transaction-isolation and --transaction-read-only server startup options corresponded to the tx_isolation and tx_read_only system variables. For better name correspondence between startup option and system variable names, transaction_isolation and transaction_read_only have been created as aliases for tx_isolation and tx_read_only. The tx_isolation and tx_read_only variables are now deprecated and will be removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation and transaction_read_only instead.

mysql server 8.0.3 release notes

The deprecated tx_isolation and tx_read_only system variables have been removed. Use transaction_isolation and transaction_read_only instead.

Comments

[taehoon]

Oh. thankyou. i use mysql 8.0.11 version. i find "transaction_isolation" how can i use transaction_isolation?..

[Kevinsss]

Try to use mysql-driver-8.* too

[kopos]

For clojurists, use this in the :dependencies map [mysql/mysql-connector-java "8.0.11"]

[Reza]

Still Work with JDK. 11

[Raptor0009]

I am using 8.0.22 version Mysql. I am using mysql 8.0.22 java connector jar, still getting this issue.