| Bug #112067 | Failed to upgrade the query rewrite plugin installed in 5.7 to 8.0 | ||
|---|---|---|---|
| Submitted: | 15 Aug 2023 4:00 | Modified: | 16 Aug 2023 10:42 |
| Reporter: | Lehman Michael | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Dictionary | Severity: | S3 (Non-critical) |
| Version: | 8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 8.0, Query Rewrite, Upgrade mysql 8 | ||
[16 Aug 2023 10:42]
MySQL Verification Team
Hello Michael, Thank you for the report and feedback. Verified as described. Thanks, Umesh
[16 Aug 2023 10:43]
MySQL Verification Team
5.7.43->8.0.34 upgrade
Attachment: 112067.log (application/octet-stream, text), 15.21 KiB.
[27 Sep 2023 19:22]
FRANCISCO BORDENAVE
I've tried 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. 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)
Description: I attempted to install and use the query rewrite plugin on version 5.7.40. There's no problem with the usage process. When I am ready to upgrade to 8.0.32 and try the new features of 8.0,startup log informs me of upgrade errors,and cannot be started(add rewriter_enabled=off to my.cnf is also useless)as fallows: ----- 2023-08-14T17:43:52.428926+08:00 1 [Note] [MY-011088] [Server] Data dictionary initializing version '80023'. 2023-08-14T17:43:58.050741+08:00 1 [Note] [MY-010337] [Server] Created Data Dictionary for upgrade 2023-08-14T17:43:58.109279+08:00 0 [Warning] [MY-010918] [Repl] 'rpl_semi_sync_master' is deprecated and will be removed in a future release. Please use rpl_semi_sync_source instead. 2023-08-14T17:43:58.109433+08:00 0 [Note] [MY-011130] [Repl] Semi-sync replication initialized for transactions. 2023-08-14T17:43:58.109455+08:00 0 [Note] [MY-011142] [Repl] Semi-sync replication enabled on the master. 2023-08-14T17:43:58.109580+08:00 0 [Warning] [MY-010918] [Repl] 'rpl_semi_sync_slave' is deprecated and will be removed in a future release. Please use rpl_semi_sync_replica instead. 2023-08-14T17:43:58.109601+08:00 0 [Note] [MY-011166] [Repl] Starting ack receiver thread. 2023-08-14T17:44:00.352324+08:00 2 [ERROR] [MY-013235] [Server] Error in parsing Routine 'query_rewrite'.'flush_rewrite_rules' during upgrade. 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 'QUERY CACHE; IF NOT message_text IS NULL THEN SIGNAL SQLSTATE '45000' SET ' at line 6 2023-08-14T17:44:01.192241+08:00 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables. 2023-08-14T17:44:01.192419+08:00 0 [ERROR] [MY-010119] [Server] Aborting ------ When I searched for the definition of this stored procedure in 5.7, I found that I used the RESET QUERY CACHE command,as follows: ----- CREATE PROCEDURE query_rewrite.flush_rewrite_rules() BEGIN DECLARE message_text VARCHAR(100); COMMIT; SELECT load_rewrite_rules() INTO message_text; RESET QUERY CACHE; IF NOT message_text IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = message_text; END IF; END // ------ However,the query cache is removed in MySQL 8.0. The command to reset query cache was also removed from the query rewrite script in 8.0. as follows: CREATE PROCEDURE query_rewrite.flush_rewrite_rules() BEGIN DECLARE message_text VARCHAR(100); COMMIT; SELECT load_rewrite_rules() INTO message_text; IF NOT message_text IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = message_text; END IF; END // This is an uncommon upgrade risk. It is recommended to fix such bugs and rewrite unsupported types How to repeat: As described Suggested fix: Remove unsupported commands in the query rewrite plugin