proxysql stored procedure calls

[kisienya]

Hello,

I appreciate this tool.

I have configured proxysql to mirror queries coming from backend application. I have a catch all rule that does the mirroring.
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply) VALUES (10,1,'.*',0,1,1);

When I run simple update queries from proxysql, mirroring works, i.e. data on both MySQL servers gets updated. For example
mysql -u root '-ppassword' -h 127.0.0.1 -P6033 -e "UPDATE database.table SET lastname='Doe' WHERE personid=1"

The application is able to contact MySQL server via sqlproxy without the above rule. Immediately the rule is inserted, the application looses connection.

The application heavily uses stored procedures to communicate with MySQL database.

Any help on this?

Much appreciated

[kisienya]

I am getting the following errors from /var/lib/proxysql.log

proxysql: MySQL_Session.cpp:1781: int MySQL_Session::handler(): Assertion `0' failed.
Error: signal 6:
proxysql(_Z13crash_handleri+0x1a)[0x4384da]
/lib64/libc.so.6(+0x347e0)[0x7f4798f417e0]
/lib64/libc.so.6(gsignal+0x35)[0x7f4798f41765]
/lib64/libc.so.6(abort+0x16a)[0x7f4798f4336a]
/lib64/libc.so.6(+0x2cf97)[0x7f4798f39f97]
/lib64/libc.so.6(+0x2d042)[0x7f4798f3a042]
proxysql(_ZN13MySQL_Session7handlerEv+0xb6a)[0x46468a]
proxysql(_ZN13MySQL_Session92handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY___create_mirror_sessionEv+0x1d5)[0x466705]
proxysql(_ZN13MySQL_Session7handlerEv+0x1cc0)[0x4657e0]
proxysql(_ZN12MySQL_Thread20process_all_sessionsEv+0x283)[0x453823]
proxysql(_ZN12MySQL_Thread3runEv+0x1edc)[0x45c17c]
proxysql(_Z24mysql_worker_thread_funcPv+0x54)[0x437124]
/lib64/libpthread.so.0(+0x75ca)[0x7f479a8725ca]
/lib64/libc.so.6(clone+0x6d)[0x7f47990100ed]
2016-11-23 10:56:05 main.cpp:642:ProxySQL_daemonize_phase3(): [ERROR] ProxySQL crashed. Restarting!
2016-11-23 10:56:05 [INFO] Angel process is waiting 1 seconds before starting a new ProxySQL process
2016-11-23 10:56:06 [INFO] Angel process started ProxySQL process 214
2016-11-23 10:56:06 ProxySQL_Admin.cpp:2873:flush_mysql_variables___database_to_runtime(): [ERROR] Impossible to set not existing variable session_debug with value "(null)". Deleting
2016-11-23 10:56:06 ProxySQL_Admin.cpp:2873:flush_mysql_variables___database_to_runtime(): [ERROR] Impossible to set not existing variable ping_interval_server with value "120000". Deleting
Standard ProxySQL Admin rev. 0.2.0902 -- ProxySQL_Admin.cpp -- Mon Nov 14 16:07:04 2016
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Mon Nov 14 16:07:04 2016
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Mon Nov 14 16:07:04 2016
HID: 0 , address: 172.18.0.3 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 1 , address: 172.18.0.4 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
2016-11-23 10:56:06 [INFO] New mysql_replication_hostgroups table
Standard Query Processor rev. 0.2.0902 -- Query_Processor.cpp -- Mon Nov 14 16:07:04 2016
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Mon Nov 14 16:07:04 2016
Standard MySQL Monitor (StdMyMon) rev. 1.2.0723 -- MySQL_Monitor.cpp -- Mon Nov 14 16:07:04 2016

[renecannao]

Hi.
Just to confirm I understand correctly: when stored procedure calls are mirrored it crashes, while for queries without stored procedure calls it doesn't crash.

I need to double check, but I think the handling of multiple resultsets (the result of a stored procedure) is not handled by the monitor module.
Can you please avoid that calls to stored procedures are not mirrored for now?
Something like this should work:

INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply)
VALUES (9,1,'^CALL',0,1);
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply)
VALUES (10,1,'.*',0,1,1);

There are plans to rewrite/improve the mirroring feature for better performance, therefore at this stage I won't be fixing this bug and get it fixed when the mirroring feature will be rewritten.

[kisienya]

Many thanks,

Actually I created a stored procedure and by passed the application. That worked. My initial guess is that there are issues with mirroring authentication information from the app.
I have put the first query and it works well for my needs.
So I have
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply)
VALUES (9,1,'^CALL',0,1);