| Bug #115259 | query_rewrite not working in 8.0.34 | ||
|---|---|---|---|
| Submitted: | 10 Jun 2024 6:20 | Modified: | 10 Jun 2024 9:48 |
| Reporter: | Mathava Selvan | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | rewrite query plugin | ||
[10 Jun 2024 9:48]
MySQL Verification Team
Hello Mathava Selvan, Thank you for the report and feedback. I quickly tried the example provided in the manual and confirm that it works as expected - https://dev.mysql.com/doc/refman/8.4/en/rewriter-query-rewrite-plugin-installation.html If you are able to reproduce the issue in current GA 8.0.37 then please provide the steps which manifest the issue. Thank you. -- 8.0.32 bin/mysql -uroot -S/tmp/mysql.sock < ./share/install_rewriter.sql bin/mysql -uroot -S/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.32 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | rewriter_enabled | ON | +------------------+-------+ 1 row in set (0.00 sec) mysql> SELECT * FROM query_rewrite.rewrite_rules\G Empty set (0.01 sec) mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) -> VALUES('SELECT ?', 'SELECT ? + 1'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM query_rewrite.rewrite_rules\G *************************** 1. row *************************** id: 1 pattern: SELECT ? pattern_database: NULL replacement: SELECT ? + 1 enabled: YES message: NULL pattern_digest: NULL normalized_pattern: NULL 1 row in set (0.00 sec) mysql> CALL query_rewrite.flush_rewrite_rules(); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM query_rewrite.rewrite_rules\G *************************** 1. row *************************** id: 1 pattern: SELECT ? pattern_database: NULL replacement: SELECT ? + 1 enabled: YES message: NULL pattern_digest: d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae normalized_pattern: select ? 1 row in set (0.00 sec) - 8.0.37 ( current GA, please check in your case). bin/mysql -uroot -S/tmp/mysql.sock < ./share/install_rewriter.sql bin/mysql -uroot -S/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.37 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. 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> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | rewriter_enabled | ON | +------------------+-------+ 1 row in set (0.01 sec) mysql> SELECT * FROM query_rewrite.rewrite_rules\G Empty set (0.01 sec) mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) -> VALUES('SELECT ?', 'SELECT ? + 1'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM query_rewrite.rewrite_rules\G *************************** 1. row *************************** id: 1 pattern: SELECT ? pattern_database: NULL replacement: SELECT ? + 1 enabled: YES message: NULL pattern_digest: NULL normalized_pattern: NULL 1 row in set (0.00 sec) mysql> CALL query_rewrite.flush_rewrite_rules(); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM query_rewrite.rewrite_rules\G *************************** 1. row *************************** id: 1 pattern: SELECT ? pattern_database: NULL replacement: SELECT ? + 1 enabled: YES message: NULL pattern_digest: d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae normalized_pattern: select ? 1 row in set (0.00 sec) regards, Umesh
[28 Jun 2024 13:25]
MySQL Verification Team
Hi Mr. Selvan, In order that this plugin works, you have to remove: autocommit= OFF from your my.cnf file ...... Or to put it to ON. And everything will work just fine ....... Hence, my colleague is correct ...... this is not a bug. Just a misconfiguration.
Description: Rewrite query plugin is not working in 8.0.34 , pattern_digest and normalized_pattern fields are not getting update after running "query_rewrite.flush_rewrite_rules" proc. No error message been found . But the same approach works fine in 8.0.32. Please guide us on this. How to repeat: Rewrite query plugin is not working in 8.0.34 , pattern_digest and normalized_pattern fields are not getting update after running "query_rewrite.flush_rewrite_rules" proc. No error message been found . CALL query_rewrite.flush_rewrite_rules(); [(none)]> select * from query_rewrite.rewrite_rules \G *************************** 1. row *************************** id: 1 pattern: select * from mathav limit ? 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) But the same approach works fine in 8.0.32. Please guide us on this.