Proxysql: Prepare Statements in mysqli for php fail

[tachu]

When ever I go over the proxy the prepare statements fail

$mysqli = new mysqli("127.0.0.1", "user", "pass", "mydb");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!($stmt = $mysqli->prepare("SELECT t0.id AS id1, t0.product AS product2, t0.name AS name3, t0.environment AS environment4, t0.whitelist_users AS whitelist_users5, t0.blacklist_users AS blacklist_users6, t0.percentage AS percentage7, t0.start_date AS start_date8, t0.end_date AS end_date9, t0.min_version AS min_version10, t0.max_version AS max_version11, t0.install_date AS install_date12 FROM FeatureToggles t0 WHERE t0.product = ? AND t0.name = ? AND t0.environment = ? LIMIT 1"))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

Prepare failed: (1045) Command not supported[root@cfcg-fl1-1 ~]#

[renecannao]

Prepared statements are currently not supported.

If you really need prepared statement, this feature can be added but the first implementation will disable a lot of other features, like multiplexing, query re-execution, transparent failover .
Thoughts?

[tachu]

Thanks Rene, a lot of ORMs use prepared statements so perhaps it could be a flag feature so that people can enable but know they will loose capabilities by using them.

Thanks

t

[renecannao]

You are perfectly right.
For your use, the absence of support for prepared statement, is it a big blocker ?

[tachu]

yeah since we use an ORM and it uses prepared statements

On Dec 8, 2015, at 4:56 PM, René Cannaò [email protected] wrote:

You are perfectly right.
For your use, the absence of support for prepared statement, is it a bit blocker ?

—
Reply to this email directly or view it on GitHub https://github.com/sysown/proxysql/issues/444#issuecomment-163073006.

[aismail]

Which ORM?

Andrei Ismail

_Software Engineer_
http://ismail.strikingly.com

[rays]

We're using a PHP code base and use prepared statements. We'd really like to see this feature

[mtokioka]

I use Ecto as an ORM for Elixir which use prepared statements for all queries.

[renecannao]

As said in a previous comment, a first implementation of prepared statements won't support a lot of features available for the text protocol.
If you can let me know which features of ProxySQL are important for you (connection pool, load balancing, automatic failover, query cache, query rewrite, mirroring, etc), I can try to prioritize these features when implementing prepared statements.
Although, there is no defined milestone for prepared statements.

[mtokioka]

Manual switching connection is most important for me.
When I devide a table from a database to new one without maintenance time, (for performance issue)

• temporary use ProxySQL for accessing only to the table
• disable access to the table on a current database
• enable access to the table on a new database

With MySQL router or another proxy, I should restart such process to switch connection destination. This cause not a few failer to application.

Using ProxySQL, this technique is also useful, when alter table to huge and heavy load table(alter offline, and then switch connection)

[ManjotS]

This issue also affects sysbench:

FATAL: mysql_stmt_prepare() failed
FATAL: MySQL error: 1045 "Command not supported"
FATAL: thread#0: failed to prepare statements for test

[renecannao]

For the time being, until prepared statements are supported (work in progress), please use sysbench with --db-ps-mode=disable

[FabienO]

Are these supported yet or any plans to bring it in anytime soon?

[renecannao]

Support for prepared statements is the main development focus right now.
Results are not stable yet, but promising.

[jkklee]

HI Rene，do you have a plan when will take this feature into Proxysql? I use php whih the laravel Framework, it use prepare statement to avoid the SQL Injection.

Tow question about this:

• why can I use prepare in mysql-cli but can't use it with the php PDO?
• As far as I konw, problem in using prepare statement with "database proxy" is that prepare be routed node A but execute be routed to node B.
  According to the results of my test，within one client and one connect, queries with same digest will be routed to the specific one(and only this one) node. Is right?
  If so, and if we set mysql_query_rules that prepare,set,execute statement go to a specific hostgroup(with one or more nodes), is there any else problems with prepare? statement?

@renecannao thanks!

[renecannao]

@jkklee

MySQL supports two type of prepared statements:

• using API
• using SQL
  Further details here

SQL support currently not supported either, because PREPARE doesn't disable multiplexing, so it is possible that ProxySQL sends PREPARE in a connection, and EXECUTE into another.
SQL support will be fixed in v1.2.3 , see #684 .

API support is planned in ProxySQL 1.3 .
ProxySQL 1.3 is not GA yet, but I would really appreciate if you can test it and give me some feedback.

Thanks

[jkklee]

@renecannao

OK，I'm very glad to keep focus on this project.
And which load-balance arithmetic(betwen more than one nodes in an HG) that ProxySQL use? In general,we know such as round-robin,src IP hash etc...

[renecannao]

The only load-balancing algorithm currently used is weighted random

[julrich]

Was there some progress made, here? I'm seeing lots of Commits on different branches related to Prepared Statements, is there any version we could test? Is there something that needs to be done to activate support (e.g. feature flag)?

We'd really need those Prepared Statements, too. Would be willing to test, if there is something to test right now :). We're currently running the Docker Image on latest.

[renecannao]

Hi @julrich , I had forgot to update this issue.
1.3.x supports prepared statements out of the box.
Version v1.3.1 is the latest version.
If you find any issue, please let me know.
Thanks

[julrich]

After reading this thread I'd realised my error. I was running on 1.2.x, because of a very outdated Docker Hub Repository by Percona: https://hub.docker.com/r/perconalab/proxysql/

This should be my problem :) Will search for a better Docker Image now.

[renecannao]

AFAIK, there is no Docker image with 1.3 out there.
Yet I strongly recommend 1.3.1 (released yesterday) that fixes several bugs related to prepared statements.

[julrich]

That's no problem, I've forked the base-repo for the Docker Image by percona anyway. Just updated the version that's installed, and it works perfectly fine! :) Sorry for the noise!

[renecannao]

This issue needed to be closed long time ago.