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:

From a database perspective, all my rows are updated with lower case only. So, here the LOWER function is not required. Also, using the LOWER function on the WHERE clause column will hide the index for that particular column. In our case, the query will scan the entire table ( FTS ).

With LOWER Function

It goes to a full table scan (FTS).

Without LOWER Function

In this case, the query is using the available index.

Note: I have converted the UPDATE to SELECT for analysis purposes. 

From the above example, I have the index for the column “name”. But, still, it is not usable with the LOWER function. If I remove the LOWER function, the index is usable. Let’s see how we can fix this using the query rewrite plugin.

In the first step, I need to update the query rules in the table “rewrite_rules”. Below are the key points to be followed when updating the query rules.

  • We have to use the query digest output to configure the query rules.
  • We need to always call the function “flush_rewrite_rules” once the rules table is modified.
  • If you wrongly configured the query rules, you will get the error message “ERROR 1644 (45000): Loading of some rule(s) failed.” during the flush function call.
  • We can check the warning message to know if the query rules are applied or not.

I configured the query rules, so now executing the query.

Yes, it works well. We can confirm this by checking the warning message and the general log output.

To uninstall the plugin you have to load the SQL file “uninstall_rewriter.sql”. It will drop the database, function, and uninstall the plugin as well.

DDL Rewriter Plugin

MySQL community team introduced the ddl_rewriter plugin in MySQL 8.0.16. The plugin can be used to modify the CREATE TABLE statements received by the server. The plugin will remove the below clauses from the CREATE TABLE statement.

  • ENCRYPTION
  • DATA DIRECTORY
  • INDEX DIRECTORY

Implementation

We can configure the plugin by using the INSTALL PLUGIN command.

Once ddl_rewriter is installed, you can use the –ddl-rewriter option for subsequent server startups to control ddl_rewriter plugin activation. For example, to deactivate the feature:

Test Case

(Migrate table structure from source to destination without ENCRYPTION, DATA DIRECTORY, and INDEX DIRECTORY )

Requirement

I have two MySQL environments, which are called “source” and “destination”. At my source environment, all my tables are configured with encryption and some of the tables have different DATA DIRECTORY and INDEX DIRECTORY. 

The requirement is I need to migrate the table “ddl_rwtest” from source to destination. The table has encryption and different DATA DIRECTORY and INDEX DIRECTORY as well. I don’t need the encryption and separate data and index directory at my destination.

At the source, the table structure looks like this:

Process

In the first step, I enabled the ddl_rewriter plugin as shown in the implementation section. Now, I am just going to load the structure using the same SQL command.

From the above logs, the ddl_rewriter plugin has removed those encryption and data/index directories from my SQL command. You can verify the warning message to confirm this.

This plugin will really help with huge data structure migrations using logical backups.

Conclusion

It seems the MySQL community team is proactively working on Query rewrite plugin development as we have a new DDL rewriter plugin from MySQL 8.0.16. Right now the plugin supports only CREATE TABLE statements, and I am looking forward to more features and support of other DDL statements as well.

Share this post

Leave a Reply