| Bug #115437 | Query rewrite plugin not working | ||
|---|---|---|---|
| Submitted: | 26 Jun 2024 18:17 | Modified: | 29 Apr 19:24 |
| Reporter: | Mathava Selvan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 Jun 2024 18:38]
MySQL Verification Team
Hi MR. Selvan, According to this documentation: https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin-usage.html this is how it is expected to work. Not a bug.
[27 Jun 2024 3:58]
Mathava Selvan
Agreed. But as per understanding when we calling the query_rewrite.flush_rewrite_rules() , pattern_digest & pattern_digest values should be loaded into pattern_digest. If there is anomalies , that should be captured in table and error message. But in my case, not able to find root cause which one causing the blocking.
[28 Jun 2024 12:50]
MySQL Verification Team
HI,
This is just to inform you that I managed to get correct results .......
Here is the last SELECT:
select * from query_rewrite.rewrite_rules \G
*************************** 1. row ***************************
id: 7
pattern: SELECT ?
pattern_database: test
replacement: SELECT ? limit 1
enabled: YES
message: NULL
pattern_digest: d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae
normalized_pattern: select ?
1 row in set (0.00 sec)
This is not a bug.
However, the problem is in the configuration file.
Please, send us your my.cnf file, so that we can locate the correct option ......
[28 Jun 2024 13:23]
MySQL Verification Team
Hi Mr. Selvan, In order that this plugin works, you have to remove: autocommit= OFF from your my.cnf file ...... And everything will work just fine ....... I am putting the comment in your previous bug report too ......
[28 Jun 2024 14:34]
MySQL Verification Team
Hi , This is now a verified documentation bug. Simply, when Rewrite Plugin is used, autocommit must NOT be set to OFF !!!!! This has to be documented properly in 8.0 and all higher and maintained versions.
[1 Jul 2024 12:06]
Mathava Selvan
Thanks , I don't have entry for autocommit in my cnf file. After disabling read_only mode pattern_digest got updated, even though query rewrite is still hot happening.
localhost:[(none)]> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES ('SELECT ?', 'SELECT ? + 1');
Query OK, 1 row affected (0.00 sec)
localhost:[(none)]> call query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.00 sec)
localhost:[(none)]> select * from query_rewrite.rewrite_rules;
+----+----------+------------------+--------------+---------+---------+----------------+--------------------+
| id | pattern | pattern_database | replacement | enabled | message | pattern_digest | normalized_pattern |
+----+----------+------------------+--------------+---------+---------+----------------+--------------------+
| 1 | SELECT ? | NULL | SELECT ? + 1 | YES | NULL | NULL | NULL |
+----+----------+------------------+--------------+---------+---------+----------------+--------------------+
1 row in set (0.00 sec)
localhost:[(none)]> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
localhost:[(none)]> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)
localhost:[(none)]> call query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.00 sec)
localhost:[(none)]> select * from query_rewrite.rewrite_rules;
+----+----------+------------------+--------------+---------+---------+------------------------------------------------------------------+--------------------+
| id | pattern | pattern_database | replacement | enabled | message | pattern_digest | normalized_pattern |
+----+----------+------------------+--------------+---------+---------+------------------------------------------------------------------+--------------------+
| 1 | SELECT ? | NULL | SELECT ? + 1 | YES | NULL | d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae | select ? |
+----+----------+------------------+--------------+---------+---------+------------------------------------------------------------------+--------------------+
1 row in set (0.00 sec)
localhost:[(none)]> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
[29 Apr 19:24]
Jon Stephens
Documented fix as follows in the MySQL 8.0.43, 8.4.6, and 9.4.0 changelogs:
The query rewrite plugin did not work properly when the server
was run with autocommit=OFF.
Closed.
Description: localhost:[(none)]> show global variables like 'rewriter_enabled'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | rewriter_enabled | ON | +------------------+-------+ 1 row in set (0.00 sec) localhost:[(none)]> delete from query_rewrite.rewrite_rules; Query OK, 1 row affected (0.00 sec) localhost:[(none)]> INSERT INTO query_rewrite.rewrite_rules (pattern_database,pattern, replacement) -> VALUES('testdba','SELECT * FROM test', 'SELECT * from test limit 1'); Query OK, 1 row affected (0.00 sec) localhost:[(none)]> CALL query_rewrite.flush_rewrite_rules(); Query OK, 1 row affected (0.01 sec) localhost:[(none)]> select * from query_rewrite.rewrite_rules \G *************************** 1. row *************************** id: 10 pattern: SELECT * FROM test pattern_database: testdba replacement: SELECT * from test limit 1 enabled: YES message: NULL pattern_digest: NULL normalized_pattern: NULL 1 row in set (0.00 sec) Not showing any error while running the query_rewrite.flush_rewrite_rules() , but the pattern_digest & normalized_pattern values are not getting update , query rewrite is not happening. No error been captured in error log and query_rewrite.rewrite_rules table. Please guide to identify the issue and make it functional. How to repeat: localhost:[(none)]> show global variables like 'rewriter_enabled'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | rewriter_enabled | ON | +------------------+-------+ 1 row in set (0.00 sec) localhost:[(none)]> delete from query_rewrite.rewrite_rules; Query OK, 1 row affected (0.00 sec) localhost:[(none)]> INSERT INTO query_rewrite.rewrite_rules (pattern_database,pattern, replacement) -> VALUES('testdba','SELECT * FROM test', 'SELECT * from test limit 1'); Query OK, 1 row affected (0.00 sec) localhost:[(none)]> CALL query_rewrite.flush_rewrite_rules(); Query OK, 1 row affected (0.01 sec) localhost:[(none)]> select * from query_rewrite.rewrite_rules \G *************************** 1. row *************************** id: 10 pattern: SELECT * FROM test pattern_database: testdba replacement: SELECT * from test limit 1 enabled: YES message: NULL pattern_digest: NULL normalized_pattern: NULL 1 row in set (0.00 sec)