Prepare statement for Hibernate fail without fast_forward=true

[m-radzikowski]

We have launched ProxySQL 1.3.5-0-g10bf524 to distribute traffic from several applications. One of them uses Hibernate ORM and, like most of the ORMs, uses prepared statements. On default ProxySQL config, with user created by:

insert into mysql_users (username, password, default_hostgroup, transaction_persistent) values ('tester', 'tester', 1, 1);

we got an error from Hibernate:

ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-103) No data supplied for parameters in prepared statement

I found issue #701, still open, in which it's said that should be supported from 1.3.0. Apparently they are not, or there is need for additional config.

I also found this topic: https://groups.google.com/forum/#!msg/proxysql/CQgZk82UYbs/SBhueqOQBQAJ in which there was a recommendation to set fast_forward=1 for this user. I tried this and in result queries are properly executed, but all statements go to master database, even simple selects.

My questions are:

• Are prepared statements supported in v1.3.5? If so, what is proper config to use them?
• Can user that uses prepared statements be routed to slave databases?
• What details can I provide you to further investigate this problem?

Executing all selects on master is unacceptable in our case, as this app makes much more reads than writes. Using ORM is also necessary. Just FYI, we had no such problems with MariaDB MaxScale.

[renecannao]

Hi Maciej,

Support for prepared statements was introduced 6 months ago in v1.3.0.
Initial implemented has experimental and it improved a lot version after version thanks to users' feedback.
In other words, v1.3.5 does support prepared statements, does not require fast_forward, and it does not require any additional configuration.

So I believe you had hit some not supported edge case.
To further investigate, I need:

• an extract from ProxySQL's error log
• (this is most important part) all the network traffic from the client with Hibernate to ProxySQL, and from ProxySQL to MySQL server.
  You can run this on the server where ProxySQL is running:

tcpdump -i any -s 0 port 6033 or port 3306 -w issue991.tcp

Then run your test, stop tcpdump, compress the file, and send it over either here or via email.

While mentioning MaxScale: MaxScale and ProxySQL use two different approaches to prepared statements.
Depending from the workload, MaxScale normally executes "3 x backends" more requests than ProxySQL. So for example, if you have 3 slaves, for many workload MaxScale will probably execute 9x more requests than ProxySQL. Or said differently, ProxySQL can reduce the latency due to network IO by 89% .
To achieve this performance boost, ProxySQL implementation is a lot complex, therefore it is possible that some edge cases are not supported yet.
Of course I want to support these too.

Thanks

[m-radzikowski]

Ok. Here is an error from ProxySQL log:

mysql_connection.cpp:496:stmt_execute_start(): [ERROR] mysql_stmt_bind_param() failed: Buffer type is not supported2017-04-14 09:52:11 MySQL_Session.cpp:2269:handler(): [WARNING] Error during query on (1,HOST,3306): 0,

As for tcpdump - I woudn't like to share all informations from it, and I will need to spend some time filtering data.

[renecannao]

ok, this seems a duplicate of #921 . That I haven't solved yet...