Mariadb 10.5.x - Unknown system variable 'transaction_isolation'

[ludgart]

Hello,

I'm using the newest mariadb server version and I've found, that mariadb doesnt support the variable 'transaction_isolation' anymore. I saw proxysql also using the newest variable 'tx_isolation'. Is there a way to add the support for the variable 'transaction_isolation' to prevent issues with applications they doesnt support it yet?

Server environment: debian 10

Regards ludgart

[renecannao]

ProxySQL should recognize the backend and use the right variable: if this doesn't, it is a bug and we need to fix it. What version of proxysql are you using? Please also share the error log. Thanks

[ludgart]

ProxySQL is using the "correct" variable from the backend server. The thing is, the old variable 'transaction_isolation' doesnt work anymore on the newest mariadb server and I want to support it.

ProxySQL version. 2.0.12-percona-1.1

Regards ludgart

[mf-digital]

We can confirm the same behaviour. Using MariaDB 10.4.11 in a clustered mode and ProxySQL 2.0.14-70-g7b02b19.

Is there maybe some sort of workaround / rewrite rule?

[yakirgb]

Try to set mysql-server_version to 5.7.19 https://github.com/mariadb-corporation/mariadb-connector-j/blob/3bc66153b51aca188afc50ff35a0123f16c099ed/src/main/java/org/mariadb/jdbc/MariaDbConnection.java#L891

[ludgart]

@yakirgb Hello, I didnt tested it yet, but I watched into the code and the if statement will fail, because it is mariadb and not mysql. Is there a general other solution?

[peoples21]

I have same problem when I mix a MySQL/Oracle Connector with a MariaDB as backend. Same error for another variable: transaction_read_only.

To solve it, you can implement query rules, for example: INSERT INTO mysql_query_rules (rule_id,active,match_pattern,replace_pattern,apply) VALUES (100,1,'@@transaction_isolation','@@tx_isolation',1);

[qiuqiuqiubo]

I have always been puzzled，mysql-server_version Is for MySQL，So when I use mariadb, how to set it

[ludgart]

I have same problem when I mix a MySQL/Oracle Connector with a MariaDB as backend. Same error for another variable: transaction_read_only.

To solve it, you can implement query rules, for example: INSERT INTO mysql_query_rules (rule_id,active,match_pattern,replace_pattern,apply) VALUES (100,1,'@@transaction_isolation','@@tx_isolation',1);

I tried it with the rules without success.

[markuman]

Try to set mysql-server_version to 5.7.19 https://github.com/mariadb-corporation/mariadb-connector-j/blob/3bc66153b51aca188afc50ff35a0123f16c099ed/src/main/java/org/mariadb/jdbc/MariaDbConnection.java#L891

why on earth mariadb did this?
we switched mysql-server_version from 5.7.12 (our past aws aurora) to 10.4.13 (our mariadb version) and run into the same error.

message repeated 2 times: [ 2021-08-10 15:38:47 MySQL_Session.cpp:3985:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (20,some.mariadb.host,3306,50570): 1193, Unknown system variable 'transaction_isolation']

change it to mysql-server_version=5.7.19 solves this issue for us.

[net-expertise]

I can connect ok to MariaDB 10.5 through proxySQL (2.3.0) from a Mysql client. mysql-server_version='8.0.20' on proxysql

I do have a "transaction_isolation" error when using the Mysql Java connector. It works as expected when switching to MariaDB's

[thehunmonkgroup]

Just ran into this bug after upgrading from MariaDB 10.11.4 to 10.11.6 on Debian 12.

The MariaDB ODBC driver started throwing this error after the upgrade.

With a little query logging, I found this query as part of the driver setting up the connection:

SET session_track_system_variables='autocommit,transaction_isolation';

https://mariadb.com/kb/en/set-transaction/#isolation-level explains that the migration from tx_isolation to transaction_isolation happened in MariaDB 11.1.11

The ODBC driver was incorrectly identifying my server version as using the newer syntax.

In my case, I set mysql-server_version to 10.11.6-MariaDB, and afterwards the ODBC driver could correctly identify my server version through ProxySQL.