I can connect to my proxysql instance via terminal but not via jdbc.

[primu2002]

Hi guys and girls. I am testing a ProxySQL installation talking to 3 galera nodes.

I can successfully connect to the proxy by: mysql -P6033 -uusr -ppwd -h192.168.xx.xxx DbName

If, instead, I try to connect via jdbc with a small java application, it crashes when I try to connect, i.e., at: conn = DriverManager.getConnection("jdbc:mariadb://192.168.xx.xxx:6033/DbName", "usr", "pwd");

Removing the DbName from the connection string, does not help. In the ProxySQL log I have: MySQL_Session.cpp:2282:handler(): [ERROR] Unexpected packet from client, disconnecting the client

If I analyze the traffic on the port, the following looks strange to me: G....set autocommit=1, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES') a....SELECT @@max_allowed_packet,@@system_time_zone,@@time_zone,@@sql_mode,@@auto_increment_increment 1 FIN-WAIT-1 192.168.xx.xxx:46366 > same_ip_as_left:6033 1 FIN-WAIT-2 192.168.xx.xxx:46366 > same_ip_as_left:6033 G....set autocommit=1, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES') 1 RESET 192.168.xx.xxx:46366 > same_ip_as_left:6033

Can anybody help? Thanks a lot!

[renecannao]

Hi. Can you please share the traffic collected with tcpdump? Thanks

[Tusamarco]

@primu2002 what version of Jconnector (MySQL) are you using? I had debug some issue in the past about connecting to Proxysql with Java but that happens with the admin interface and I had created a patched Jdbc version for that. But the client side should work fine ... at least I am connecting all the time

[primu2002]

Hi @Tusamarco I have been trying with mysql-connector-java-5.1.43-bin.jar and with mariadb-java-client-2.1.0.jar. @renecannao Starting tcpick 0.2.1 at 2017-08-15 17:15 CEST Timeout for connections is 600 tcpick: reading from proxy2.tcp 1 SYN-SENT 192.168.xx.xxx:46366 > 192.168.xx.xxx:6033 1 SYN-RECEIVED 192.168.xx.xxx:46366 > 192.168.xx.xxx:6033 1 ESTABLISHED 192.168.xx.xxx:46366 > 192.168.xx.xxx:6033 J... 5.5.30..K..m0GVW3:g.*.!...............xIAH&%'4oT6Q.mysql_native_password. S..........@!.......................myinfo....y....q.....70.~n..mysql_native_password. ........... G....set autocommit=1, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES') a....SELECT @@max_allowed_packet,@@system_time_zone,@@time_zone,@@sql_mode,@@auto_increment_increment 1 FIN-WAIT-1 192.168.xx.xxx:46366 > 192.168.xx.xxx:6033 1 FIN-WAIT-2 192.168.xx.xxx:46366 > 192.168.xx.xxx6033 G....set autocommit=1, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES') 1 RESET 192.168.xx.xxx:46366 > 192.168.xx.xxx:6033 tcpick: done reading from proxy2.tcp

[renecannao]

@primu2002 : can I have proxysql2.tcp ? Thanks

[primu2002]

@renecannao Yes, can I send it to you somehow privately? Thanks,

[renecannao]

sure thing, rene dot cannao at gmail

[primu2002]

@renecannao done! Thanks.

[renecannao]

Received, thanks.

Indeed there is something odd with the jdbc driver, as it sends a SET command immediately followed by a SELECT statement without waiting any reply after the first command (SET). Therefore ProxySQL complains about "Unexpected packet from client", as the client is sending data without waiting a reply.

Will check flags to understand why JDBC does that.

[primu2002]

@renecannao Stop stop, it works!!!

[renecannao]

:) How did you make it work?

[primu2002]

Sorry guys, my bad. First of all, I was using the mariadb driver. Which was making this mess with the packets. Then why it worked for this guy? http://blog.yannickjaquier.com/mysql/proxysql-high-availability-replication.html Mystery!

Then, once I switched to the mysql connector, I didn't change the jdbc string. So obviously while parsing the connection url, the connector would just return null because he could not find the "mysql://" token.

Thanks a lot to @renecannao and @Tusamarco! If there is a possibility to give plus points, just let me know how 👍

[Tusamarco]

@primu2002 happy it works ... @renecannao btw I still have to use the patched jdbc version for the admin ... but will ping u in pvt with a test case

[renecannao]

This issue turned to be really interesting.

@primu2002 : if you can give a try to mariadb-java-client , can you disable usePipelineAuth ? Reference: https://mariadb.com/kb/en/mariadb/mariadb-connector-j-200-release-notes/

It seems that starting from mariadb-connector-j 2.0.0 , usePipelineAuth is used by default. This allows to send requests immediately after connection, without waiting for a reply: exactly what is happening in your case. I couldn't find any reference to a similar feature in mysql-connector-j , neither I can't find anything in the protocol documentation. This feature seems specific to mariadb-connector-j . Some references: https://jira.mariadb.org/browse/CONJ-366 https://jira.mariadb.org/browse/CONJ-477 (feature usePipelineAuth doesn't work with Aurora too)

Should ProxySQL support usePipelineAuth ? Not sure. ProxySQL internally uses a state machine, and if a client has sent a query it must wait a reply before sending a new query. Normally, the client library should prevent the application from sending multiple commands without waiting a reply, returning a CR_COMMANDS_OUT_OF_SYNC error , but in this case is the library itself that "breaks the rule".

[primu2002]

BINGO!!! @renecannao Setting "usePipelineAuth" to "false" solved the issue with the mariadb connector.

[mamen2022]

Hi @Tusamarco, I have exactly the problem you referenced in your comment:

I had debug some issue in the past about connecting to Proxysql with Java but that happens with the admin interface and I had created a patched Jdbc version for that.

I want to use the Jdbc connector to get the Proxysql stats from the admin interface (6032), but i cant connect. It works perfectly in client interface (6033). How did you solved this? I am using the mysql-connector-java-5.1.37.jar and Proxysql is monitoring a XtraDb Cluster.

Do you know what is the problem @renecannao ?

[jonasborn]

Is there some progress on this topic? I‘m still facing this issue on the admin interface and have no idea how to work around (without switching from Java)

[jonasborn]

Seems only to be working with DrizzleJDBC (https://github.com/krummas/DrizzleJDBC/) but not with the newest versions of MariaDB JDBC oder j/Connector

[woopstar]

We see this issue with the newest MariaDB connector.