Description:
Current MySQL Connector/J versions (tested with 8.0.33, 8.1.0 and 8.2.0) can not connect to MariaDB (tested with MariaDB 11.0.2) anymore.
This is the error message during connecting:
java.sql.SQLException: Unknown system variable 'transaction_isolation'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:815)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:438)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:189)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
How to repeat:
1. Install MariaDB 11 (tested with 11.0.2)
2. Download MySQL Connector/J (tested with 8.0.33, 8.1.0 and 8.2.0)
3. Compile and run the following test program
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
public class ConnectorJMariaDB {
public static void main(String[] args) {
Statement stmt = null;
Connection conn = null;
PreparedStatement pStmt = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql",
"root", "password");
stmt = conn.createStatement();
stmt.execute("SELECT 1");
stmt.close();
stmt = null;
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
// ignore
}
stmt = null;
}
if (pStmt != null) {
try {
pStmt.close();
} catch (SQLException sqlEx) {
// ignore
}
pStmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
// ignore
}
conn = null;
}
}
}
}
Suggested fix:
First question is, if MySQL Connector/J should be compatible with MariaDB server.
According to ConnectionImpl.java in Connector/J the server variable transaction_isolation is fetched during connecting. This variable was named tx_isolation in MySQL Server <8.0.3 and <5.7.20. MariaDB still uses the name tx_isolation.
A correct check for MariaDB can be found in Connection.java from MariaDB Java client.
This excerpt is from MySQL Connector/J (8.0.33)
@Override
public int getTransactionIsolation() throws SQLException {
synchronized (getConnectionMutex()) {
if (!this.useLocalSessionState.getValue()) {
String s = this.session.queryServerVariable(
versionMeetsMinimum(8, 0, 3) || versionMeetsMinimum(5, 7, 20) && !versionMeetsMinimum(8, 0, 0) ? "@@session.transaction_isolation"
: "@@session.tx_isolation");
...
}
This excerpt is from MariaDB Connection (8.2.0)
@Override
public int getTransactionIsolation() throws SQLException {
if (conf.useLocalSessionState() && client.getContext().getTransactionIsolationLevel() != null) {
return client.getContext().getTransactionIsolationLevel();
}
String sql = "SELECT @@session.tx_isolation";
if (!client.getContext().getVersion().isMariaDBServer()) {
if ((client.getContext().getVersion().getMajorVersion() >= 8
&& client.getContext().getVersion().versionGreaterOrEqual(8, 0, 3))
|| (client.getContext().getVersion().getMajorVersion() < 8
&& client.getContext().getVersion().versionGreaterOrEqual(5, 7, 20))) {
sql = "SELECT @@session.transaction_isolation";
}
}
Description: Current MySQL Connector/J versions (tested with 8.0.33, 8.1.0 and 8.2.0) can not connect to MariaDB (tested with MariaDB 11.0.2) anymore. This is the error message during connecting: java.sql.SQLException: Unknown system variable 'transaction_isolation' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:815) at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:438) at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241) at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:189) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) How to repeat: 1. Install MariaDB 11 (tested with 11.0.2) 2. Download MySQL Connector/J (tested with 8.0.33, 8.1.0 and 8.2.0) 3. Compile and run the following test program import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; public class ConnectorJMariaDB { public static void main(String[] args) { Statement stmt = null; Connection conn = null; PreparedStatement pStmt = null; try { conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "password"); stmt = conn.createStatement(); stmt.execute("SELECT 1"); stmt.close(); stmt = null; } catch (SQLException ex) { ex.printStackTrace(); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { // ignore } stmt = null; } if (pStmt != null) { try { pStmt.close(); } catch (SQLException sqlEx) { // ignore } pStmt = null; } if (conn != null) { try { conn.close(); } catch (SQLException ex) { // ignore } conn = null; } } } } Suggested fix: First question is, if MySQL Connector/J should be compatible with MariaDB server. According to ConnectionImpl.java in Connector/J the server variable transaction_isolation is fetched during connecting. This variable was named tx_isolation in MySQL Server <8.0.3 and <5.7.20. MariaDB still uses the name tx_isolation. A correct check for MariaDB can be found in Connection.java from MariaDB Java client. This excerpt is from MySQL Connector/J (8.0.33) @Override public int getTransactionIsolation() throws SQLException { synchronized (getConnectionMutex()) { if (!this.useLocalSessionState.getValue()) { String s = this.session.queryServerVariable( versionMeetsMinimum(8, 0, 3) || versionMeetsMinimum(5, 7, 20) && !versionMeetsMinimum(8, 0, 0) ? "@@session.transaction_isolation" : "@@session.tx_isolation"); ... } This excerpt is from MariaDB Connection (8.2.0) @Override public int getTransactionIsolation() throws SQLException { if (conf.useLocalSessionState() && client.getContext().getTransactionIsolationLevel() != null) { return client.getContext().getTransactionIsolationLevel(); } String sql = "SELECT @@session.tx_isolation"; if (!client.getContext().getVersion().isMariaDBServer()) { if ((client.getContext().getVersion().getMajorVersion() >= 8 && client.getContext().getVersion().versionGreaterOrEqual(8, 0, 3)) || (client.getContext().getVersion().getMajorVersion() < 8 && client.getContext().getVersion().versionGreaterOrEqual(5, 7, 20))) { sql = "SELECT @@session.transaction_isolation"; } }