A wired bug about prepared-statement affect the production

[seanlook]

Let me explain from the first start.

PHP pdo has the attribute ATTR_EMULATE_PREPARES to use emulating prepares or use original prepares. Emulating prepares provide prepare/execute-like statement but it's done in php code, and original prepares(MySQL supports it) send prepare/execute command to servers separately.

In my production enviroments ATTR_EMULATE_PREPARES is set to FALSE, thus true binary prepared statement is taken. But the following quries have wired results: (just change the limit X)

No.7: select * from `d_ec_ads`.`t_ads_crowd` where `f_corp_id` = ? order by `f_id` desc limit 7 offset 0
No.8: select * from `d_ec_ads`.`t_ads_crowd` where `f_corp_id` = ? order by `f_id` desc limit 8 offset 0
No.9: select * from `d_ec_ads`.`t_ads_crowd` where `f_corp_id` = ? order by `f_id` desc limit 9 offset 0
No.10: select * from `d_ec_ads`.`t_ads_crowd` where `f_corp_id` = ? order by `f_id` desc limit 10 offset 0

No.7 and 9 return the right results, but No.8 and 10 return nothing.

• When I change proxysql address to servers address and connect database directly, everything is fine.
• When I change ATTR_EMULATE_PREPARES to TRUE, everything is fine. (because it just send the whole queries not prepared statements)

limit 8 and limit 10 is special, ANY other limit X number works fine.

Thanks god, error logs have something out:

2017-08-04 11:50:19 MySQL_Session.cpp:2200:handler(): [ERROR] RECEIVED AN UNKNOWN COMMAND: 28 -- PLEASE REPORT A BUG
2017-08-04 11:51:54 MySQL_Session.cpp:2200:handler(): [ERROR] RECEIVED AN UNKNOWN COMMAND: 28 -- PLEASE REPORT A BUG

Also I captured the tcp packets to find out what query commands the laravel has sent: (captured in apps side)

tcpdump -x -n -q -tttt -w proxysql_prepare3.cap -s 0  host 100.98.153.187 and port 6033

proxysql_prepare3_packets.zip

Both limit 8 and limit 10 have RST packets and it's reproducible. I think proxysql receive a unknown comand to itself but it ok to mysql servers actually. But what makes 8 or 10 so special, charset?

proxysql version: proxysql-1.3.6-1-dbg-centos67.x86_64.rpm

[renecannao]

Hi Sean, This is related to #892 . Honestly, I can't find enough documentation about COM_STMT_FETCH .

Can you collect some traffic of the same requests sent to mysql directly? I want to understand what sort of reply is provided by mysqld.

Thanks

[seanlook]

Traffics sent to mysqld directly is attached: (limit 7, limit 8, limit 9) proxysql_prepare4.zip

I don't COM_STMT_FETCH is related to this. Sample queries No.7 & No.9 return ok, but No.8 cause unknown command, the only difference is limit X.

After I restart the proxysql, the problem is gone. But other nodes that have run for one week still log UNKNOWN COMMANDS.

[renecannao]

I finally found some time to study further the protocol. Using binary protocol, when executing COM_STMT_EXECUTE the client can ask the server to open a cursor. When a cursor is opened:

• the server sends data to client only when client executes COM_STMT_FETCH
• the above also means that the server holds the resultset
• the client can request the server to destroy the resultset calling COM_STMT_RESET either explicitly via mysql_stmt_reset() or implicitly via mysql_stmt_free_result() .

Reference: https://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-attr-set.html https://github.com/mysql/mysql-server/blob/8.0/include/mysql_com.h#L699-L712

The problem

• ProxySQL doesn't support cursor client side
• ProxySQL does support cursor server side
• whatever status flag returned from the server is forwarded to client This leads to an interesting scenario:
• client ask for a cursor
• ProxySQL executes the statement to the server, and obtains a cursor
• ProxySQL informs the client that there is a cursor
• client sends COM_STMT_FETCH , but ProxySQL doesn't know how to handle it

The solution

What is interesting about the use of cursors in prepared statements is that client can ask for a cursor, but it is up to the server to decide if a cursor is created or not: https://github.com/MariaDB/mariadb-connector-c/blob/master/libmariadb/mariadb_stmt.c#L1838-L1866

That said, the solution will probably be simple:

• no matter what the client asks, and no matter what server replied to ProxySQL, ProxySQL will always reply to the client that there is no cursor

Ping on #892 and #961

[seanlook]

Glad to hear that.

Buy if ProxySQL tell the client that there is no cursor, how will the client fetch the resultset that last prepared statement has executed?

[seanlook]

ProxySQL wouldn't cache backend table definition(metadata), right? After I add a new column, this issue appears again. Then I delete the column,everything goes well. Column type is normal int.

If I add column first and then restart proxysql, this bug is gone. Any temporary advice? Restart proxysql in production will interrupt the normal connections, this is horrible

[renecannao]

1074 seems related to this

[renecannao]

This is fixed in 62180d72216cde8a998c5010d2e7271f63253823