Commands out of sync; you can't run this command now when using ProxySQL

[TAnis222]

Hello,

We have a client application using mariadb-connector/c 3.1.16 to connect to a MariaDB server (10.6). We recently introduced proxySQL in the middle and since then we started to see the error "Commands out of sync; you can't run this command now" when executing prepared statements. The issue is observerd with proxySQL 2.3.2 and still happens after upgrading to 2.5.2.

This is a sample code to reproduce the issue.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include <mysql.h>
#include <mysqld_error.h>
#include <errmsg.h>
#include <mariadb_com.h>
typedef char bool;

MYSQL *conn_hdl;
MYSQL_STMT* stmt_handle;
char * myCnf = "path-to-my.cnf";
char * connecting_host = "entry-in-my.cnf";
char * user = "user";
char * user_password = "password";
char * effective_db = "database";

int checkError()
{
        int errNo = mysql_errno(conn_hdl);
        mysql_sqlstate(conn_hdl);
        if (errNo)
        {
                printf("Error %d:  %s\n",errNo ,mysql_error(conn_hdl));
        }

        return errNo;
}

int checkStmtError()
{
        int errNo = mysql_stmt_errno(stmt_handle);
        mysql_stmt_sqlstate(stmt_handle);
        if (errNo)
        {
                printf("Error %d:  %s\n",errNo ,mysql_stmt_error(stmt_handle));
        }

        return errNo;
}

int connect_to_database()
{
        mysql_thread_init();

        printf("mysql_init\n");
        conn_hdl = mysql_init(conn_hdl);
        if (checkError())  return 1;

        printf("mysql_options - MYSQL_READ_DEFAULT_FILE\n");
        mysql_options(conn_hdl, MYSQL_READ_DEFAULT_FILE, myCnf);
        if (checkError())  return 1;

        printf("mysql_options - MYSQL_READ_DEFAULT_GROUP\n");
        mysql_options(conn_hdl, MYSQL_READ_DEFAULT_GROUP, connecting_host );
        if (checkError())  return 1;

        printf("mysql_options - MYSQL_OPT_SSL_ENFORCE\n");
        bool ssl = 1;
        mysql_options(conn_hdl, MYSQL_OPT_SSL_ENFORCE, &ssl );
        if (checkError())  return 1;

        printf("mysql_real_connect\n");
        mysql_real_connect(conn_hdl, "", user, user_password, effective_db, 0, "", 0);
        if (checkError())  return 1;

        printf("mysql_autocommit\n");
        mysql_autocommit(conn_hdl, (my_bool)1 );
        if (checkError())  return 1;

        return 0;

}

int run_query(char * query)
{
        // Execute query
        printf("mysql_real_query\n");
        mysql_real_query(conn_hdl, query, strlen(query));
        if (checkError())  return 1;

        // Ignore results if any
        printf("mysql_store_result\n");
        MYSQL_RES * res_hdl = mysql_store_result(conn_hdl);
        if (checkError())  return 1;

        // Get number of impacted rows
        printf("mysql_affected_rows\n");
        mysql_affected_rows(conn_hdl);
        if (checkError())  return 1;

        // Free results
        if ( res_hdl )
        {
                printf("mysql_free_result\n");
                mysql_free_result( res_hdl );
                res_hdl = 0;
                if (checkError())  return 1;
        }

        return 0;
}

int run_prepared_query(char * query)
{
        printf("mysql_stmt_init\n");
        stmt_handle = mysql_stmt_init(conn_hdl);
        if (checkError())  return 1;
        if (!stmt_handle)
        {
                printf("failed to init stmt_handle\n");
                return 1;
        }

        printf("mysql_stmt_prepare\n");
        mysql_stmt_prepare( stmt_handle, query, strlen(query) );
        if (checkStmtError())  return 1;

        printf("mysql_stmt_attr_set CURSOR_TYPE_READ_ONLY\n");
        unsigned long type = (unsigned long) CURSOR_TYPE_READ_ONLY;
        mysql_stmt_attr_set(stmt_handle, STMT_ATTR_CURSOR_TYPE, (void*) &type);
        if (checkStmtError())  return 1;

        printf("mysql_stmt_attr_set STMT_ATTR_PREFETCH_ROWS\n");
        unsigned long prefetch_rows = 100;
        mysql_stmt_attr_set(stmt_handle, STMT_ATTR_PREFETCH_ROWS, (void*) &prefetch_rows);
        if (checkStmtError())  return 1;

        printf("mysql_stmt_execute\n");
        mysql_stmt_execute(stmt_handle);
        if (checkStmtError())  return 1;

        return 0;
}
int main()
{

        //Connect to the DB
        printf("*****  CONNECTING *****\n");
        int returnValue = connect_to_database();
        if (returnValue) return 1;

        //Create the table
        printf("\n\n*****  CREATING TABLE *****\n");
        run_query("CREATE TABLE test_simple (col1 INTEGER, col2 VARCHAR(100))");

        //select from the table
        printf("\n\n*****  SELECTING FROM TABLE *****\n");
        if(run_prepared_query("SELECT col1, col2 FROM test_simple ORDER BY col1"))
        {
                printf("mysql_stmt_free_result\n");
                mysql_stmt_free_result(stmt_handle);
                printf("mysql_stmt_close\n");
                mysql_stmt_close(stmt_handle);
        }

        // Disconnect
        printf("\n\n*****  DISCONNECTING *****\n");
        printf("mysql_close\n");
        mysql_close(conn_hdl);
        printf("mysql_thread_end\n");
        mysql_thread_end();

        return 0;
}

Here is the output I got after running the code

*****  CONNECTING *****
mysql_init
mysql_options - MYSQL_READ_DEFAULT_FILE
mysql_options - MYSQL_READ_DEFAULT_GROUP
mysql_options - MYSQL_OPT_SSL_ENFORCE
mysql_real_connect
mysql_autocommit

*****  CREATING TABLE *****
mysql_real_query
mysql_store_result
mysql_affected_rows

*****  SELECTING FROM TABLE *****
mysql_stmt_init
mysql_stmt_prepare
mysql_stmt_attr_set CURSOR_TYPE_READ_ONLY
mysql_stmt_attr_set STMT_ATTR_PREFETCH_ROWS
mysql_stmt_execute
Error 2014:  Commands out of sync; you can't run this command now
mysql_stmt_free_result
mysql_stmt_close

*****  DISCONNECTING *****
mysql_close
mysql_thread_end

Can you help with this issue please?

Thank you!

[renecannao]

Hi @TAnis222 . Your sample code assumes that a cursor exists. ProxySQL doesn't support cursors , and at protocol level it informs the client that no cursor exists. If the client tries to use a non existing cursor I would assume this is probably a client connector bug .

[renecannao]

Out of curiosity I tested your sample code (after disabling SSL to collect the traffic with tcpdump). ProxySQL sends to the client the same resultset it received from the server. The only difference I found is that during COM_STMT_EXECUTE the client explicitly sets "read-only cursor" .

I am pretty confident it is a client bug, and I tracked down where this happens. In latest mariadb client connector C , it is here , because status is MYSQL_STATUS_GET_RESULT .

[TAnis222]

Thanks @renecannao for your reply. So if the ProxySQL doesn't support cursors and that MariaDB connector tries to use a cursor anyway, does it mean it's not possible to use the MariaDB connector C with proxySQL ? How is a MariaDB client application supposed to do to select a set of lines from the DB? (line per line ?).

Thanks !

[renecannao]

does it mean it's not possible to use the MariaDB connector C with proxySQL

No. It means you shouldn't use cursors.

How is a MariaDB client application supposed to do to select a set of lines from the DB? (line per line ?)

I don't understand why a modern application would use cursors at all. Personally, I think cursors made sense decades ago, when hardware was very expensive and applications were running on systems with extremely limited resources: cursors allow to limit resource usage on application server at the expenses of using resources on the backend servers. Now application can retrieve data and consume it at the same time, without using cursors. My 2c, I may be wrong.