proxysql return "General error: 2057" when the table column changed

[jkklee]

My environment:

• proxysql 1.3.4-0-gd158707
• mysql 5.6.24-72.2-log Percona Server
• php 5.6
• laravel(php framework) 5.1.45

Everything goes well before today.
Today morning, I add a column to a table, then my app show errors like that:

SQLSTATE[HY000]: General error: 2057 A stored procedure returning result sets of different size was called. This is not supported by libmysql (SQL: select * from `user` where `uid` = 229 limit 1)

I tried some effort to analyse this error(include analyse the network traffic), but nothing can explain this error. At last I tried restart my MySQL, Proxysql, and php-fpm. I found only when I restart the Proxysql, this error disappeared.

Another point confuse me is the error message "A stored procedure returning result sets...", there is no stored procedure in my app.

In the other hand, I simulate the same scenario(use proxysql and modify the table's column) with mysql-cli and prepare statment, it's ok;
also the pure php script(no framework) using PDO and prepare statment, it's ok too.

So, what do you think may cause this error? If you need some additional information, I will show you.

[renecannao]

Hi @jkklee .

I add a column to a table

This triggered the error.
The bug is that this is not supported in ProxySQL and prepared statements.
Was ProxySQL's error log reporting some errors?

About mysql-cli and prepared statements: mysql-cli doesn't support real prepared statements.
mysql-cli supports only TEXT protocol, not BINARY protocol.

[jkklee]

About mysql-cli and prepared statements: mysql-cli doesn't support real prepared statements.
mysql-cli supports only TEXT protocol, not BINARY protocol.

Yes , I remember we discussed about this before.
I check the proxysql.log, there is no output when I test this error.

And if I did nothing except drop the column which I add to the table, things goes well again

[renecannao]

Indeed we had this discussion before :)

Thank you for checking in the error log. If there is nothing in the error log it means that the proxy is not aware that the structure has changed.
This is absolutely a bug, I know what causes it, but I am still not sure how to fix it.
I will look into it.

Thank you.

[jkklee]

The error message I saw in the client SQLSTATE[HY000]: General error: 2057 A stored procedure is proxysql return?

I capture tow "pure" tcpdump files (ok.txt and err.txt) just now, If you want to see,I will send you.

[renecannao]

If the dump collects traffic from the client to proxysql and from proxysql to backend: yes please! Thanks

[jkklee]

Yes, it collects traffic from the client to proxysql and from proxysql to backend.
this is my test environment, and proxysql and backend in the same server and only one backend

mysql> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | 127.0.0.1     | 3307 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.16.200.24 | 3307 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
# 127.0.0.1 and 172.16.200.24 are the same one server

HG 0 is default hostgroup, HG 1 is the select hostgroup.

I send you a email attach the tcpdump files.

Thank's for your work.

[jkklee]

@renecannao Did you receive my email to rene.cannao@gmail.com?
And can you please summarize what happened during the proxysql received app's prepare statment and proxysql send execute statment to the backend?

[jkklee]

@renecannao I think the key of this question is select * ...(although select * is not a good habit), and message 2017-03-31 10:16:11 MySQL_Session.cpp:2225:handler(): [WARNING] Error during query on (0,192.168.1.21,3307): 0, is logged to the proxysql.log

Another strange thing is:
Aftre I add or drop one column, the first time I run the test php script

#it's out put is 
[ljk@demo ljk]# php test_prepare.php 
array(0) {
}
## hunged here...
^C

## and proxysql.log show 
2017-03-31 10:41:50 MySQL_Session.cpp:2225:handler(): [WARNING] Error during query on (0,192.168.1.21,3307): 0,

but when I run the script second time after I add or drop one column

[ljk@demo ljk]# php test_prepare.php 
段错误
## segment fault

## and there is nothing logged to the proxysql.log

I think this error should be consider as high priority , and I hope this can make some help

attachment is my test php script
test_prepare.zip

[renecannao]

Several prepared statements bug were fixed since this issue was opened (12 months ago).
Closing