Java connection using MariaDB 2.4.3 JDBC driver fails to load server variables

[ujmoser]

This is a reocuring bug showing the same behaviour as reported in issue #2009.

We are using MariaDB Connector/J 2.4.3 and ProxySQL 2.0.8 on top of a MariaDB Galera Cluster 10.2.23 on Ubuntu 18.04.3 LTS (GNU Linux 4.15.0-76-generic_x86_64)

When connecting to port 6033 the connector reports

could not load system variables
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
Query is: SHOW VARIABLES WHERE Variable_name in ('max_allowed_packet','system_time_zone','time_zone','auto_increment_increment')

[renecannao]

Please provide a reproducible test case

[ujmoser]

Only thing we can provide is the java connect string we use to open the connnections.
here it is:
jdbc:mariadb://192.168.23.234:6033/c4?fastConnect=true&tcpNoDelay=true&autoReconnect=true&sessionVariables=net_write_timeout=7200

[ujmoser]

We will try to create a small snippet using that connect string and test whether the error occurs.

[ujmoser]

Here we have a sample and included is a stacktrace.
MDB_ConJ_TestCase.java_src.txt
MDB_ConJ_TestCase.java.stacktrace.txt

[ujmoser]

Hi,
we found out that removing the part 'sessionVariables=net_write_timeout=7200' from the connection string solves the problem. Looking into the documentation for net_write_timeout shows that for our application it would make sense to have this setting, since resultsets might be quite large and therefore can be split up into several transfers and as a result might run into the default 60 secs.

[alpes214]

Verified.

Issue is reproduced with the following connection string:

"jdbc:mariadb://localhost:6033/test?fastConnect=true&tcpNoDelay=true&autoReconnect=true&sessionVariables=net_write_timeout=7200"

The error returned by application:

Exception java.sql.SQLNonTransientConnectionException: could not load system variables

proxysql error log

2020-02-26 09:36:30 MySQL_Session.cpp:2536:handler_again___status_SETTING_GENERIC_VARIABLE(): [WARNING] Error while setting SQL_MODE to "concat(@@sql_mode,',STRICT_TRANS_TABLES'),net_write_timeout" on 127.0.0.1:3306 hg 0 :  1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
2020-02-26 09:36:30 MySQL_Session.cpp:134:kill_query_thread(): [WARNING] KILL CONNECTION 108 on 127.0.0.1:3306
2020-02-26 09:36:30 MySQL_Session.cpp:2536:handler_again___status_SETTING_GENERIC_VARIABLE(): [WARNING] Error while setting SQL_MODE to "concat(@@sql_mode,',STRICT_TRANS_TABLES'),net_write_timeout" on 127.0.0.1:3306 hg 0 :  1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
2020-02-26 09:36:30 MySQL_Session.cpp:134:kill_query_thread(): [WARNING] KILL CONNECTION 111 on 127.0.0.1:3306
2020-02-26 09:36:30 MySQL_Thread.cpp:4583:process_all_sessions(): [WARNING] Closing unhealthy client connection 127.0.0.1:35806

Application connects to proxysql successfully when 'sessionVariables=net_write_timeout=7200'is removed from connection string.

[alpes214]

@ujmoser Please try a modified connection string:

jdbc:mariadb://192.168.23.234:6033/c4?fastConnect=true&tcpNoDelay=true&autoReconnect=true&sessionVariables=@@session.net_write_timeout=7200

[ujmoser]

@Val214 Thank for your support. The modified connection string works.