MySQL 8.x DDL Rewriter and Query Rewriter Plugins: Implementation and Use Cases

MySQL 8 DDL Rewriter and Query RewriterRewriting a MySQL query for performance is an important process that every DBA should be aware of so they can fix the wrong queries on runtime without code changes on the application end. ProxySQL has great support for rewriting the queries, which Alkin Tezuysal already explored in his excellent blog ProxySQL Query Rewrite Use Case.

So far, MySQL community provides two built-in query rewrite plugins to perform this task. Recently they introduced the plugin “ddl_rewriter”.

  • query rewriter plugin : It has support for INSERT / UPDATE / DELETE / REPLACE statements from MySQL 8.0.12. 
  • ddl_rewritter plugin: It supports the CREATE TABLE statement. Introduced at MySQL 8.0.16.

In this blog, I am going to explain the complete process of the implementation and the testing of those plugins. The testing is based on MySQL 8.x features.

Query Rewriter Plugin

The plugin will help to modify the SQL statements which are received by the server before execution. Before MySQL 8.0.12, the plugin was only supported for SELECT. From MySQL 8.0.12 the plugin supports INSERT, UPDATE, DELETE, REPLACE as well.

Implementation

There are two SQL files to perform the install and uninstall operations. The files are located under the shared folder.

  • We can implement the rewriter plugin on runtime. 
  • When loading the SQL file “install_rewritter.sql” it will install the plugin “rewriter.so” and creates its own database, table, and function for the operations.

Install the plugin by loading the installer SQL file:

Plugin installation is completed, and you can verify that from the above logs.

Test Case

(Remove the LOWER function from UPDATE to avoid the FTS)

I have created a table “qrw8012” and made some records for testing purposes.

Requirement

The requirement is to update the column “name” from “sakthi” to “hercules7sakthi” WHERE id = 6. The UPDATE query from the application looks like this: