Description:
Query rewrite plugin fails to parse pattern including reserved works.
I try to use a table name that uses a reserved keyword when upgrading from 5.7 to 8 and tried to setup rewriter query rewrite plugin I get a parsing failure that I can't workaround.
I tested quoting the incoming reserved word through replication, in this case the table is called `lead` but I can't make the plugin to properly parse and convert the work either to quote using backticks or change the name to a new table called leads
I'm not even able to test this variable https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin-reference.html#sysva... as the parser is failing to read the query from binary log.
How to repeat:
mysql> \s
--------------
mysql Ver 8.0.34 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 25
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.31 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 20 min 39 sec
Threads: 2 Questions: 83 Slow queries: 0 Opens: 458 Flush tables: 4 Open tables: 45 Queries per second avg: 0.066
--------------
mysql> show create table `lead`\G
*************************** 1. row ***************************
Table: lead
Create Table: CREATE TABLE `lead` (
`LeadID` int(11) NOT NULL AUTO_INCREMENT,
`LeadActionedDateTime` datetime DEFAULT NULL,
PRIMARY KEY (`LeadID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Source is 5.7, the query that fails is following:
mysql> select * from performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 1064
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log db-bin.000191, end_log_pos 340029760; Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lead SET LeadActionedDateTime = '2023-09-07 09:32:26' WHERE leadid = '222' at line 1' on query. Default database: 'test'. Query: 'UPDATE lead SET LeadActionedDateTime = '2023-09-07 09:32:26' WHERE leadid = '2222''
LAST_ERROR_TIMESTAMP: 2023-09-18 14:47:20.978248
LAST_APPLIED_TRANSACTION:
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION: ANONYMOUS
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2023-09-18 14:47:20.978157
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
Rules tested in rewriter table plugin:
mysql> update query_rewrite.rewrite_rules set replacement='UPDATE `lead`' where id=1;
Query OK, 1 row affected (0,00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> CALL query_rewrite.flush_rewrite_rules();
ERROR 1644 (45000): Loading of some rule(s) failed.
mysql> select * from query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
id: 1
pattern: UPDATE `lead`
pattern_database: NULL
replacement: UPDATE `lead`
enabled: YES
message: Parse error in pattern: >>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1<<
pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0,00 sec)
mysql> update query_rewrite.rewrite_rules set pattern='UPDATE lead' where id=1;
Query OK, 1 row affected (0,00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> CALL query_rewrite.flush_rewrite_rules();
ERROR 1644 (45000): Loading of some rule(s) failed.
mysql> select * from query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
id: 1
pattern: UPDATE lead
pattern_database: NULL
replacement: UPDATE `lead`
enabled: YES
message: Parse error in pattern: >>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lead' at line 1<<
pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0,00 sec)
mysql> update query_rewrite.rewrite_rules set pattern='UPDATE \`lead\`' where id=1;
Query OK, 1 row affected (0,00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> CALL query_rewrite.flush_rewrite_rules();
ERROR 1644 (45000): Loading of some rule(s) failed.
mysql> select * from query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
id: 1
pattern: UPDATE `lead`
pattern_database: NULL
replacement: UPDATE `lead`
enabled: YES
message: Parse error in pattern: >>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1<<
pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0,00 sec)
I'm not even able to test this variable https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin-reference.html#sysva... as the parser is failing to read the query from binary log.
Suggested fix:
Make sure query rewriter can parse reserved words as plain text and verify the rules afterwards.
This is affecting an upgrade process through replication.
May be related to this bug: https://bugs.mysql.com/bug.php?id=112067
