Implementing and Testing MySQL 8.x Query and DDL Rewriter Plugins

MySQL query rewriting is a crucial technique for DBAs to improve performance by fixing inefficient queries at runtime without changing application code. MySQL 8.0 provides two built‑in plugins: the query rewriter (available since 8.0.12) and the DDL rewriter (available since 8.0.16).

1. Query Rewriter Plugin

1.1 Environment preparation

Two SQL files, install_rewriter.sql and uninstall_rewriter.sql, are placed in the shared directory. The install file creates a database query_rewrite, a table rewrite_rules, and loads the plugin rewriter.so:

mysql> show global variables like 'lc_messages_dir';
+-----------------+----------------------------+
| Variable_name   | Value                      |
+-----------------+----------------------------+
| lc_messages_dir | /usr/share/percona-server/ |
+-----------------+----------------------------+
1 row in set (0.01 sec)

[root@hercules7sakthi3 ~]# cd /usr/share/mysql-8.0/
[root@hercules7sakthi3 mysql-8.0]# ls -lrth | grep -i rewriter
-rw-r--r--. 1 root root 1.3K Mar 26 14:16 uninstall_rewriter.sql
-rw-r--r--. 1 root root 2.2K Mar 26 14:16 install_rewriter.sql

Running the install script creates the plugin and its supporting objects:

[root@hercules7sakthi3 mysql-8.0]# mysql -vv < install_rewriter.sql | grep -i 'create\|install\|drop'
CREATE DATABASE IF NOT EXISTS query_rewrite
CREATE TABLE IF NOT EXISTS query_rewrite.rewrite_rules (
INSTALL PLUGIN rewriter SONAME 'rewriter.so'
CREATE FUNCTION load_rewrite_rules RETURNS STRING
CREATE PROCEDURE query_rewrite.flush_rewrite_rules()

mysql> show schemas like 'query_rewrite';
+--------------------------+
| Database (query_rewrite) |
+--------------------------+
| query_rewrite            |
+--------------------------+
1 row in set (0.00 sec)

mysql> show tables from query_rewrite;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules           |
+-------------------------+
1 row in set (0.05 sec)

mysql> show create table query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
       Table: rewrite_rules
Create Table: CREATE TABLE `rewrite_rules` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pattern` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `pattern_database` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `replacement` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `enabled` enum('YES','NO') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'YES',
  `message` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `pattern_digest` varchar(64) DEFAULT NULL,
  `normalized_pattern` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select plugin_name,plugin_status, plugin_version from information_schema.plugins where plugin_name='Rewriter';
+-------------+---------------+----------------+
| plugin_name | plugin_status | plugin_version |
+-------------+---------------+----------------+
| Rewriter    | ACTIVE        | 0.2            |
+-------------+---------------+----------------+
1 row in set (0.00 sec)

1.2 Test case

A sample table qrw8012 is created with a few rows. An UPDATE statement that uses LOWER(name) forces a full table scan because the index on name cannot be used:

mysql> show create table qrw8012\G
*************************** 1. row ***************************
       Table: qrw8012
Create Table: CREATE TABLE `qrw8012` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> explain select * from qrw8012 where LOWER(name)='sakthi'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: qrw8012
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

To avoid the full scan, a rewrite rule is added that removes the LOWER call:

mysql> insert into rewrite_rules (id,pattern_database,pattern,replacement) values (1,'percona','update qrw8012 set name = ? where LOWER(name) = ?','update qrw8012 set name = ? where name = ?');
Query OK, 1 row affected (0.01 sec)

mysql> call query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.03 sec)

mysql> select id,pattern_database,pattern,replacement from rewrite_rules\G
*************************** 1. row ***************************
              id: 1
pattern_database: percona
         pattern: update qrw8012 set name = ? where LOWER(name) = ?
     replacement: update qrw8012 set name = ? where name = ?
1 row in set (0.00 sec)

After flushing, the original UPDATE is automatically rewritten, as shown by the warning message and the general log:

mysql> update qrw8012 set name='hercules7sakthi' where LOWER(name)='sakthi';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'update qrw8012 set name='hercules7sakthi' where LOWER(name)='sakthi'' rewritten to 'update qrw8012 set name = 'hercules7sakthi' where name = 'sakthi'' by a query rewrite plugin
1 row in set (0.00 sec)

Output from general log :
2020-06-22T11:20:36.952153Z   22 Query update qrw8012 set name = 'hercules7sakthi' where name = 'sakthi'

Uninstalling the plugin simply runs uninstall_rewriter.sql, which drops the database, function, and plugin.

[root@hercules7sakthi3 mysql]# cat /usr/share/mysql-8.0/uninstall_rewriter.sql
... 
DROP DATABASE IF EXISTS query_rewrite;
DROP FUNCTION load_rewrite_rules;
UNINSTALL PLUGIN rewriter;

2. DDL Rewriter Plugin

The ddl_rewriter plugin, introduced in MySQL 8.0.16, rewrites CREATE TABLE statements by stripping the clauses ENCRYPTION, DATA DIRECTORY, and INDEX DIRECTORY. It is installed with:

mysql> install plugin ddl_rewriter soname 'ddl_rewriter.so';
Query OK, 0 rows affected (0.04 sec)

mysql> select plugin_name,plugin_status, plugin_version from information_schema.plugins where plugin_name like '%ddl%';
+--------------+---------------+----------------+
| plugin_name  | plugin_status | plugin_version |
+--------------+---------------+----------------+
| ddl_rewriter | ACTIVE        | 1.0            |
+--------------+---------------+----------------+
1 row in set (0.01 sec)

When the server is started with --ddl-rewriter=ON, the plugin becomes active; setting it to OFF disables rewriting.

[mysqld]
ddl-rewriter = OFF

2.2 Test case

A source table ddl_rwtest is defined with encryption and explicit data/index directories:

create table ddl_rwtest
(id int primary key, name varchar(16),dob date,msg text)
ENCRYPTION='Y'
DATA DIRECTORY = '/mysql/data'
INDEX DIRECTORY = '/mysql/index';

After installing the plugin and executing the CREATE statement, MySQL logs a warning indicating the rewrite, and the resulting table definition no longer contains the stripped clauses:

mysql> create table ddl_rwtest
    (id int primary key, name varchar(16),dob date,msg text)
    ENCRYPTION='Y'
    DATA DIRECTORY = '/mysql/data'
    INDEX DIRECTORY = '/mysql/index';
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'create table ddl_rwtest
(id int primary key, name varchar(16),dob date,msg text)
ENCRYPTION='Y'
DATA DIRECTORY = '/mysql/data'
INDEX DIRECTORY = '/mysql/index'' rewritten to 'create table ddl_rwtest
(id int primary key, name varchar(16),dob date,msg text) ' by a query rewrite plugin
1 row in set (0.00 sec)

mysql> show create table ddl_rwtest\G
*************************** 1. row ***************************
       Table: ddl_rwtest
Create Table: CREATE TABLE `ddl_rwtest` (
  `id` int NOT NULL,
  `name` varchar(16) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `msg` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

This demonstrates how the DDL rewriter can simplify large‑scale logical backups by removing environment‑specific clauses.

Conclusion

The MySQL community continues to enhance the query rewrite framework, and the newly added DDL rewriter (currently limited to CREATE TABLE) paves the way for broader DDL support in future releases.