.* in rewrite query does not cover special characters such as ä,ö,ü

[sschueller]

I'm not sure if this is a bug or just the way it works.

If I have a query which contains special characters such as an ä in a value the following rewrite rule will a hit but not perform a rewrite. In most regex cases ä would be covered by the ..

INSERT INTO 
mysql_query_rules (rule_id,active,match_pattern,replace_pattern,cache_ttl,apply) 
VALUES 
(10,1,'^insert delayed(.*)$', 'insert\1',NULL,1);

I changed the query to the following so it would work by avoiding the (.*)

INSERT INTO 
mysql_query_rules (rule_id,active,match_pattern,replace_pattern,cache_ttl,apply) 
VALUES 
(10,1,'^insert delayed ','insert ',NULL,1);

Thanks.

[renecannao]

This is not an expected behavior.
I will try to reproduce and check if it is a bug in ProxySQL, a bug in RE2 or a misconfiguration.
Thank you for reporting.

[renecannao]

Hi Stefan,

I am unable to reproduce the issue. Here is what I tried.

• add the rule:

Admin> INSERT INTO  mysql_query_rules (rule_id,active,match_pattern,replace_pattern,cache_ttl,apply)  VALUES  (10,1,'^insert delayed(.*)$', 'insert\1',NULL,1);
Query OK, 1 row affected (0.00 sec)

Admin> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT * FROM mysql_query_rules\G
*************************** 1. row ***************************
              rule_id: 10
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^insert delayed(.*)$
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: insert\1
destination_hostgroup: NULL
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
1 row in set (0.00 sec)

• connect a client and run queries:

$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE table646 (name VARCHAR(255)) engine=innodb;
Query OK, 0 rows affected (0.73 sec)

mysql> insert delayed table646 (name) VALUES ('ä,ö,ü');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM table646;
+----------+
| name     |
+----------+
| ä,ö,ü    |
+----------+
1 row in set (0.01 sec)

• verify in Admin if there were hits, and the queries executed:

Admin> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 10      | 1    |
+---------+------+
1 row in set (0.00 sec)

Admin> select * from stats_mysql_query_digest;
+-----------+--------------------+----------+--------------------+-----------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username | digest             | digest_text                       | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+-----------------------------------+------------+------------+------------+----------+----------+----------+
| 1         | test               | msandbox | 0xA8F5F3766305ECF1 | SELECT * FROM table646            | 1          | 1471475466 | 1471475466 | 12260    | 12260    | 12260    |
| 1         | test               | msandbox | 0x6E2950CDA12A4C92 | insert table646 (name) VALUES (?) | 1          | 1471475455 | 1471475455 | 47353    | 47353    | 47353    |
| 1         | test               | msandbox | 0x99531AEFF718C501 | show tables                       | 1          | 1471475395 | 1471475395 | 8548     | 8548     | 8548     |
| 1         | test               | msandbox | 0x02033E45904D3DF0 | show databases                    | 1          | 1471475395 | 1471475395 | 9879     | 9879     | 9879     |
| 1         | information_schema | msandbox | 0x620B328FE9D6D71A | SELECT DATABASE()                 | 1          | 1471475395 | 1471475395 | 48751    | 48751    | 48751    |
| 1         | information_schema | msandbox | 0x226CD90D52A2BA0B | select @@version_comment limit ?  | 2          | 1471475265 | 1471475378 | 0        | 0        | 0        |
+-----------+--------------------+----------+--------------------+-----------------------------------+------------+------------+------------+----------+----------+----------+
6 rows in set (0.00 sec)

In the example above, you can see that the query was correctly rewritten, row correctly inserted and retrieved.
Can you please help me reproduce your issue, giving few more information?
The queries were executed using mysql client or some script? In case a script was used, can you provide a snippet?
Did you have any message in the error log?
If you are hitting an edge case I would like to address it.
Thank you.

[sschueller]

Hi René,

I can reproduce it with the following php script. The file is ISO-8859-1 encoded. I run it via php-cli php script.php. PHP 5.3.3-7+squeeze29 with Suhosin-Patch (cli) (built: Feb 29 2016 18:51:17) on 3.2.0-4-amd64 #1 SMP Debian 3.2.68-1+deb7u3 x86_64 GNU/Linux

<?php
$host = 'localhost:3306';
$user = 'root';
$pass = 'test';
$db1 = 'test';

$db = mysql_connect($host, $user, $pass);

mysql_select_db($db1, $db);
// does not work
$sql = "insert delayed into table646 (name) VALUES ('geschäft');";
$res = mysql_query($sql, $db);
// works
$sql = "insert delayed into table646 (name) VALUES ('geschaeft');";
$res = mysql_query($sql, $db);

The rule I setup:

INSERT INTO mysql_query_rules (rule_id,active,match_pattern,replace_pattern,cache_ttl,apply) VALUES (10,1,'^insert delayed(.*)$', 'insert\1',NULL,1);

I get the following stats:

+---------+------+
| rule_id | hits |
+---------+------+
| 10      | 1    |
+---------+------+

+-----------+------------+----------+--------------------+-------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest             | digest_text                                     | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+-------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 1         | test       | root     | 0xED5D6D99B74CB0D9 | insert into table646 (name) VALUES (?);         | 1          | 1471511127 | 1471511127 | 1861     | 1861     | 1861     |
| 1         | test       | root     | 0x7904B6C94F4875C0 | insert delayed into table646 (name) VALUES (?); | 1          | 1471511127 | 1471511127 | 1036     | 1036     | 1036     |
+-----------+------------+----------+--------------------+-------------------------------------------------+------------+------------+------------+----------+----------+----------+

Let me know if you need a TCPDUMP.

Thanks

[renecannao]

Hi Stefan,
Thank you for the update. I will try to reproduce it.
One more thing: is there any relevant message in the error log?
Thanks.

[sschueller]

Hi René,

MySQL sends PHP back the following DELAYED option not supported for table 'table646'. Any other logs I should check?

Thanks again.

[renecannao]

Yes, I meant proxysql's error log, located (by default) in /usr/lib/proxysql/proxysql.log .
Maybe RE2(the regex engine) is reporting some error.
Thanks

[sschueller]

I have mysql-eventslog_filename defined which wrote a file proxysql.log.00000001. However it is empty. Is there a way to increase verbosity?
Thanks

[renecannao]

Eventslog tracks (all) traffic if enabled. But this isn't what I was looking for.
I was more interested in messages like re2/re2.cc:881: invalid rewrite pattern: ... in /usr/lib/proxysql/proxysql.log .
Thanks

[sschueller]

Ah sorry, I was not able to find that file on my system at that location but I found it at /var/lib/proxysql/proxysql.log. Here is the content:

2016-08-18 13:10:59 [INFO] Starting ProxySQL
2016-08-18 13:10:59 [INFO] Sucessfully started
2016-08-18 13:10:59 [INFO] Angel process started ProxySQL process 5006
2016-08-18 13:11:00 ProxySQL_Admin.cpp:2777:flush_mysql_variables___database_to_runtime(): [ERROR] Impossible to set not existing variable session_debug with value "(null)". Deleting
Standard ProxySQL Admin rev. 0.2.0902 -- ProxySQL_Admin.cpp -- Tue Aug  2 23:54:09 2016
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Tue Aug  2 23:54:09 2016
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Tue Aug  2 23:54:09 2016
HID: 1 , address: 127.0.0.1 , port: 3307 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0
2016-08-18 13:11:00 [INFO] New mysql_replication_hostgroups table
Standard Query Processor rev. 0.2.0902 -- Query_Processor.cpp -- Tue Aug  2 23:54:09 2016
In memory Standard Query Cache (SQC) rev. 0.2.0902 -- Query_Cache.cpp -- Tue Aug  2 23:54:09 2016
2016-08-18 13:11:00 [INFO] Starting new mysql log file //tmp/proxysql.log.00000001
Standard MySQL Monitor (StdMyMon) rev. 1.2.0723 -- MySQL_Monitor.cpp -- Tue Aug  2 23:54:09 2016
2016-08-18 13:11:23 MySQL_Session.cpp:1193:handler(): [WARNING] Error during query on (1,127.0.0.1,3307): 1616, DELAYED option not supported for table 'table646'