mysql - "Commands out of sync" error when putting multiple statements into con:query

[hengfengli]

Hi there,

I am using the prepared statement in mysql. But when I put multiple statements into con:query, I got errors:

con:query(string.format("SET @p1 = %d; SET @p2 = %d; SET @p3 = %d; ", w_id, d_id, c_id))
con:query("EXECUTE select_customer_info"..table_num.." USING @p1, @p2, @p3;")

This throws: 
FATAL: mysql_drv_query() returned error 2014 (Commands out of sync; you can't run this command now) for query 'EXECUTE select_customer_info1 USING @p1, @p2, @p3;'
FATAL: `thread_run' function failed: ./tpcc_run.lua:436: SQL error, errno = 2014, state = 'HY000': Commands out of sync; you can't run this command now

con:query(string.format("SET @p1 = %d; SET @p2 = %d; SET @p3 = %d; EXECUTE select_customer_info"..table_num.." USING @p1, @p2, @p3;", w_id, d_id, c_id))

This throws: 
Segmentation fault (core dumped)

When splitting into multiple calls, it works fine:

con:query(string.format("SET @p1 = %d;", w_id))
con:query(string.format("SET @p2 = %d;", d_id))
con:query(string.format("SET @p3 = %d;", c_id))
con:query([[EXECUTE select_customer_info]]..table_num..[[ USING @p1, @p2, @p3;]])

Does anyone know why it doesn't work when putting multiple statements into con:query? It works fine with PostgreSQL driver.

[dveeden]

For MySQL sending multiple statements with the C driver usually requires one to set the CLIENT_MULTI_STATEMENTS flag, which then gets set in the client flags when connecting to the server. See also https://dev.mysql.com/doc/c-api/8.0/en/c-api-multiple-queries.html and https://dev.mysql.com/doc/dev/mysql-server/latest/group__group__cs__capabilities__flags.html for details

Could you check a packet trace (e.g. with tcpdump or dumpcap). Please don't use a sensitive password and/or reset the password afterwards and disable SSL/TLS to allow the dump to be analyzed easily with wireshark.