New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
primu2002 opened this Issue Aug 15, 2017 · 17 comments

Comments

Projects
None yet
5 participants
@primu2002

primu2002 commented Aug 15, 2017

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

This comment has been minimized.

Contributor

renecannao commented Aug 15, 2017

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

@Tusamarco

This comment has been minimized.

Tusamarco commented Aug 15, 2017

@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

This comment has been minimized.

primu2002 commented Aug 16, 2017

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

This comment has been minimized.

Contributor

renecannao commented Aug 16, 2017

@primu2002 : can I have proxysql2.tcp ?
Thanks

@primu2002

This comment has been minimized.

primu2002 commented Aug 16, 2017

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

@renecannao

This comment has been minimized.

Contributor

renecannao commented Aug 16, 2017

sure thing, rene dot cannao at gmail

@primu2002

This comment has been minimized.

primu2002 commented Aug 16, 2017

@renecannao done! Thanks.

@renecannao

This comment has been minimized.

Contributor

renecannao commented Aug 16, 2017

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

This comment has been minimized.

primu2002 commented Aug 16, 2017

@renecannao Stop stop, it works!!!

@renecannao

This comment has been minimized.

Contributor

renecannao commented Aug 16, 2017

:) How did you make it work?

@primu2002

This comment has been minimized.

primu2002 commented Aug 16, 2017

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

This comment has been minimized.

Tusamarco commented Aug 16, 2017

@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 comment has been minimized.

Contributor

renecannao commented Aug 16, 2017

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

This comment has been minimized.

primu2002 commented Aug 16, 2017

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

@mamen2022

This comment has been minimized.

mamen2022 commented Dec 28, 2017

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

This comment has been minimized.

jonasborn commented Mar 17, 2018

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

This comment has been minimized.

jonasborn commented Mar 17, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment