Error 2014: Commands out of sync; you can't run this command now

[Jonah Benton]

Hi,

I am not the reporter of this issue:

https://github.com/sysown/proxysql/issues/1074

but am also seeing this in a test environment, with the latest release of ProxySql- 1.4.2-1. 

We have about a dozen applications in this environment that use MariaDB. When I only instruct the most critical application to use ProxySql rather than talk directly to MariaDB, all tests always pass. When I have the top 4 most critical applications use ProxySql, this problem appears in test runs fairly reliably, causing tests to fail. 

These applications have a fairly complex interaction model with the database; I would be hesitant to try to extract a test case from the work they're doing. There is nothing useful in the default ProxySql logs, though debug = true in configuration. The problem occurs both when all database users are set to use fast_forward = 0, as well as when fast_forward = 1. 

What's the best way to instruct ProxySql to provide more telemetry, and/or are there configuration knobs that are useful in diagnosing this problem?

Thanks-

Jonah

 

[René Cannaò]

Johan,

This issue is quite difficult to troubleshoot without a reproducible test case, because I am very confident that the problem is in the application.

Please refer to https://dev.mysql.com/doc/refman/5.7/en/commands-out-of-sync.html

All the errors 2xxx are generated in the client/library itself, therefore it is very difficult to troubleshoot the issue from proxysql.

To be more specific, even using tcpdump may not show any issue, because what is it likely to happen is that the proxy has sent some resultset to the client, and the client is trying to send another query without freeing the memory associated to the previous resultset.

In other words, outside the application everything could be ok, with no visibility on why the application is generating an error.

Let me highlight this: the application is not receiving an error, the application is generating the error (error 20

To support my argument, note that with fast_forward=1 proxysql doesn't perform any query processing, it just forward packets from one end to the other. In other words, once the connections are established, proxysql in fast_forward mode is completely transparent.

That said, I believe what you are saying, and that if not using ProxySQL you do not get these errors.
But unfortunately, without a reproducible test case, trying to troubleshoot this is like shooting in the dark.

Thinking out loud, it is possible that the client generates this error while ProxySQL is still sending data due to bug https://github.com/sysown/proxysql/issues/1128 , but I want to exclude this hypothesis for 2 reasons:

a) you are getting error also with fast_forward=1

b) you aren't making any reference to "[ERROR] RECEIVED AN UNKNOWN COMMAND: 28 "

Thanks

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jonah Benton]

Hi Rene,

I have an update on the occurrence of 

Error 2014: Commands out of sync; you can't run this command now

We are able to reliably reproduce this error with ProxySql (1.4.3) in between our PHP/PDO application and our database. It does not occur without ProxySql. 

We are also able to fix the issue, so that with ProxySql in the mix, the error does not occur.

The statement on which the error consistently appears is an insert, using prepared statement syntax:

   private function XXX($Id)

    {

        $query = "INSERT INTO api.table (id, xxxx, yyyy, zzz)

                        VALUES (?, 'xxx', 'yyy', 'zzz.');";

        $this->rwdb->execute($query, array($Id));

    }

Changing it to no longer use prepared statement syntax fixes the issue:

   private function XXX($Id)

    {

        $query = "INSERT INTO api.table (id, xxxx, yyyy, zzz)

                        VALUES (" . $Id . ", 'xxx', 'yyy', 'zzz.');";

        $this->rwdb->execute($query, array($Id));

    }

It looks related to the handling of cursors for prepared statements, an issue you mentioned in your earlier response.

Jonah

[René Cannaò]

Hi Jonah,

Glad you find that the issue is related to cursor.

This commit should fix the issue: https://github.com/sysown/proxysql/commit/62180d72216cde8a998c5010d2e7271f63253823

Thanks

[Jonah Benton]

Makes sense, thank you very much. Do you have near term plans to release 1.4.4 or is the roadmap still taking shape?

 

[Jonah Benton]

Hi Rene, 

We continued to have "commands out of sync" issues, but now have finally resolved it, and the resolution was different than described below. 

We found two causes. 

One cause was expected- our code was not closing result sets properly in all cases. We tracked those down, adding 

$rs->Close();

at appropriate points, and were able to resolve the majority of the occurrences. 

However, another case was not resolvable by closing the statement. We finally determined that removing the semicolon at the end of the statement described earlier, e.g. changing

private function XXX($Id)

    {

        $query = "INSERT INTO api.table (id, xxxx, yyyy, zzz)

                        VALUES (?, 'xxx', 'yyy', 'zzz.');";

        $this->rwdb->execute($query, array($Id));

    }

to

private function XXX($Id)

    {

        $query = "INSERT INTO api.table (id, xxxx, yyyy, zzz)

                        VALUES (?, 'xxx', 'yyy', 'zzz.')"; // removal here

        $this->rwdb->execute($query, array($Id));

    }

removes the "Command out of sync" condition on the subsequent use of the connection and allows the statement to complete execution successfully. 

We understand this to be related our backend's support for multiple statements. Below are our server's and ProxySql's extended attributes configuration:

Backend: a03f

    .... .... .... ...1 = Multiple statements: Set

    .... .... .... ..1. = Multiple results: Set

    .... .... .... .1.. = PS Multiple results: Set

    .... .... .... 1... = Plugin Auth: Set

    .... .... ...1 .... = Connect attrs: Set

    .... .... ..1. .... = Plugin Auth LENENC Client Data: Set

    .... .... .0.. .... = Client can handle expired passwords: Not set

    .... .... 0... .... = Session variable tracking: Not set

    .... ...0 .... .... = Deprecate EOF: Not set

    1010 000. .... .... = Unused: 0x50

ProxySql: 800f

    .... .... .... ...1 = Multiple statements: Set

    .... .... .... ..1. = Multiple results: Set

    .... .... .... .1.. = PS Multiple results: Set

    .... .... .... 1... = Plugin Auth: Set

    .... .... ...0 .... = Connect attrs: Set

    .... .... ..0. .... = Plugin Auth LENENC Client Data: Set

    .... .... .0.. .... = Client can handle expired passwords: Not set

    .... .... 0... .... = Session variable tracking: Not set

    .... ...0 .... .... = Deprecate EOF: Not set

    1000 000. .... .... = Unused: 0x50

I'm not sure there is anything actionable for ProxySql but wanted to pass it along.

Jonah

[René Cannaò]

Jonah,

Thank you very much for reporting your finding.

The first problem is expected: if the client sends commands in a connection without first freeing the result set, this will lead to a "Command out of sync" error.

The second issue, related to semicolon, is not expected.

Could you provide a small reproducible test case? I would like to debug this issue further.

Thanks

[Jonah Benton]

Hi Rene,

While working to distill our source tree into a small sharable example, I think we arrived at a more precise definition of the problem. 

We recently integrated request tracing into our application farm. One of the changes to this particular PHP application was to append the trace ids onto the SQL being issued to the database, using comment syntax. This statement:

       $query = "INSERT INTO api.table (id, xxxx, yyyy, zzz)

                        VALUES (?, 'xxx', 'yyy', 'zzz.');";

        $this->rwdb->execute($query, array($Id));

gets turned into

INSERT INTO api.table (id, xxxx, yyyy, zzz) 

VALUES (?, 'xxx', 'yyy', 'zzz.'); /*application:aaa,timestamp:999999,traceId:..., spanId:....*/

before being given to the mysqli library to issue to the database.

Without ProxySql, there is no impact on subsequent statements from statements that end with a semicolon and include a comment following the semicolon (as above).

With ProxySql, these statements- with a comment following the semicolon- cause the subsequent statement to be rejected at the client with the "Command out of sync" error.

With ProxySql, if the statement ends with a semicolon and does not have a comment (e.g. if we don't append the trace data), there is no impact to the subsequent statement.

With ProxySql, if the comment is prepended onto the statement, whether or not the statement ends in a semicolon, there is no impact on the subsequent statement.

This is with a custom build of ProxySql master branch from 11/5.

The implication is that the additional syntactic element following the end of the statement, perhaps in the context that the backend reports that it can support multi-statement commands (see the extended attributes notes earlier in this thread), seems to instruct ProxySql to tell the client that it should not issue a new command. 

Does that help?

Jonah

[René Cannaò]

Hi Johan,

Yes it helps!

I will try to debug this issue with the information provided.

Interesting to note is that semicolon is the separator in multi-statements, therefore your query is likely to be interpreted as a regular statement followed by an empty statement.

Thanks

--
Sent from my mobile device.
Email: rene....@gmail.com
Time zone: GMT+2