Using digest, match_digest, replace_pattern causes crash

[utdrmac]

Attempting simple demonstrations of ProxySQL's ability to rewrite queries results in repeatable crash.

ProxySQL 1.4.10. Test query: SELECT k FROM sysbench.sbtest1 LIMIT 1

proxysql> SELECT digest, digest_text FROM stats_mysql_query_digest;
+--------------------+----------------------------------------+
| digest             | digest_text                            |
+--------------------+----------------------------------------+
| 0xD30E0F01DED402B1 | SELECT k FROM sysbench.sbtest1 LIMIT ? |
...

proxysql> INSERT INTO mysql_query_rules (rule_id, active, digest, replace_pattern, destination_hostgroup, apply) VALUES (5, 1, '0xD30E0F01DED402B1', "SELECT CONCAT('TACOS-', k) FROM sysbench.sbtest1 LIMIT 1", 20, 1);

Expected result:

mysql> SELECT k FROM sysbench.sbtest1 LIMIT 1;
+---------------------+
| CONCAT('TACOS-', k) |
+---------------------+
| TACOS-91            |
+---------------------+
1 row in set (0.03 sec)

Actual result:

mysql3> SELECT k FROM sysbench.sbtest1 LIMIT 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

ProxySQL Error log snippet:

Error: signal 11:
proxysql(_Z13crash_handleri+0x25)[0x4cba23]
/lib64/libc.so.6(+0x35270)[0x7f6b7e9d4270]
proxysql(_ZN15Query_Processor19process_mysql_queryEP13MySQL_SessionPvjP10Query_Info+0x1f6a)[0x5387ec]
proxysql(_ZN13MySQL_Session7handlerEv+0xae1)[0x51bad7]
proxysql(_ZN12MySQL_Thread20process_all_sessionsEv+0x637)[0x50b14b]
proxysql(_ZN12MySQL_Thread3runEv+0x2cde)[0x50a460]
proxysql(_Z24mysql_worker_thread_funcPv+0xb9)[0x4c637c]
/lib64/libpthread.so.0(+0x7e25)[0x7f6b8027ee25]
/lib64/libc.so.6(clone+0x6d)[0x7f6b7ea9734d]
2018-08-16 15:01:33 main.cpp:910:ProxySQL_daemonize_phase3(): [ERROR] ProxySQL crashed. Restarting!
2018-08-16 15:01:33 main.cpp:882:ProxySQL_daemonize_phase3(): [INFO] Angel process started ProxySQL process 14766
Standard ProxySQL Cluster rev. 0.1.0702_DEBUG -- ProxySQL_Cluster.cpp -- Tue Aug  7 13:08:51 2018
Standard ProxySQL Statistics rev. 1.4.1027_DEBUG -- ProxySQL_Statistics.cpp -- Tue Aug  7 13:08:51 2018
Standard ProxySQL HTTP Server Handler rev. 1.4.1031_DEBUG -- ProxySQL_HTTP_Server.cpp -- Tue Aug  7 13:08:51 2018
Admin initialized in 0.014763 secs.

After crash restart, attempt to use digest hex matching with error_msg (this works):

proxysql> UPDATE mysql_query_rules SET replace_pattern=NULL, error_msg="Haha" WHERE rule_id=5;
proxysql> LOAD.. SAVE..

mysql3> SELECT k FROM sysbench.sbtest1 LIMIT 1;
ERROR 1148 (42000): Haha

Attempting replace_pattern with match_digest:

proxysql> UPDATE mysql_query_rules SET replace_pattern="SELECT CONCAT('TACOS-', k) FROM sysbench.sbtest1 LIMIT 1", error_msg=NULL, digest=NULL, match_digest="^SELECT k FROM sysbench\.sbtest1" WHERE rule_id=5;

proxysql> SELECT * from mysql_query_rules WHERE rule_id=5;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------------------+---------------+----------------------+--------------+---------+----------------------------------------------------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest                     | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern                                          | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------------------+---------------+----------------------+--------------+---------+----------------------------------------------------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| 5       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT k FROM sysbench\.sbtest1 | NULL          | 0                    | CASELESS     | NULL    | SELECT CONCAT('TACOS-', k) FROM sysbench.sbtest1 LIMIT 1 | 20                    | NULL      | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------------------+---------------+----------------------+--------------+---------+----------------------------------------------------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
1 row in set (0.00 sec)

Result (crash):

mysql3> SELECT k FROM sysbench.sbtest1 LIMIT 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

proxysql_cores.tar.gz

[renecannao]

https://github.com/sysown/proxysql/issues/1386#issuecomment-367134720

[utdrmac]

Why can't replace_pattern work against digest? Why not just a simple replace?

If $queryDigest == $digest then $query = $replace_pattern; else if regexMatch($query, $match_pattern) then regexReplace($query, $replace_pattern);

[Tusamarco]

@utdrmac this is a duplicate of https://github.com/sysown/proxysql/issues/1556 and of #1386 .

[Tusamarco]

See renecannao commented on Jun 19 The new definition for mysql_query_rules.replace_pattern is:

replace_pattern VARCHAR CHECK(match_pattern IS NOT NULL)

are u using an old version?

[utdrmac]

@Tusamarco Not using old version. Using 1.4.10.

[utdrmac]

I was using 1.4.9 and upgraded to 1.4.10. Does not appear that the schema for mysql_query_rules table was updated. My schema still shows this:

replace_pattern VARCHAR,

[renecannao]

The new schema definition is in 2.0.0 , not 1.4 series. But the bottom line is the same: replace_pattern requires match_pattern .