With the query rewrite framework in the latest MySQL (Optimizer/InnoDB/Replication) labs release, you get the opportunity to author plugins that can rewrite queries. You can choose whether to rewrite the queries before and/or after parsing. Today I am going to walk you through how to write a pre-parse query rewrite plugin.
When would you want to use a pre-parse query rewrite plugin? The greatest benefit compared to post-parse rewrites — which I cover in a separate post — is the efficiency, especially the lack of overhead for those queries that are actually rewritten. Typical cases where you may want to write a pre-parse plugin are:
- When you want to remove certain specific clauses from queries. For example, perhaps you want to remove all ENGINE clauses from DDL statements.
- When you want to replace all queries against table_foo with table_foo2.
- When you want to add a hint, for example a USE INDEX hint, to all queries against tbl3.
- Any general case where you might want to do a broad REGEXP style find and replace.
Example Plugin
There will be an example pre-parse plugin shipped with MySQL. It is not very useful, but you can take it as a starting point for developing your own plugins. What it does is rewrite all queries to lowercase. You would typically issue the following commands to install it and then to demonstrate its usage and effects:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> install plugin rewrite_example soname 'rewrite_example.so'; Query OK, 0 rows affected (0,15 sec) mysql> SELECT "HELLO World"; +-------------+ | hello world | +-------------+ | hello world | +-------------+ 1 row in set, 1 warning (0,01 sec) mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------+ | Note | 1105 | Query 'SELECT "HELLO World"' rewritten to 'select "hello world"' by plugin: rewrite_example. | | Note | 1105 | Query 'show warnings' rewritten to 'show warnings' by plugin: rewrite_example. | +-------+------+----------------------------------------------------------------------------------------------+ 2 rows in set, 1 warning (0,00 sec) |
As you can see, it really rewrites all queries, including the show warnings query that I just issued. I did that to show you that the server knows that the query was rewritten and lets you know by raising a note.
The Basics
The plugin is conceptually very simple: it’s a single C function. The input is a const char *, the output another const char *. If your plugin returns a new query string, that’s the new query and the server will forget about the old one. Only an SQL note remains as a testimony of the original query.
You really only need three files to create a plugin:
- Your source code file
- A plug.in file
- A CMakeLists.txt file
Most of what goes into writing general plugins is already covered well in the manual, and http://dev.mysql.com/doc/refman/5.7/en/writing-plugins.html is a good starting point. What is new is the specific pre-parse query rewrite plugin type. You need to declare your specific plugin of this new type as shown below:
|
1
2
3
4
5
|
static st_mysql_rewrite_pre_parse rewrite_example_descriptor= { MYSQL_REWRITE_PRE_PARSE_INTERFACE_VERSION, /* interface version */ rewrite_lower, /* rewrite raw query function */ free_rewritten_query, /* free allocated query */ }; |
This should be fairly straightforward. The first argument is the version number of the interface that the plugin is using, the second is the function doing the rewrite, and the third is called by plugin framework after the rewrite is complete in order to free up any resources you might have been using—for example, in the rewrite_example plugin’s rewrite_lower function we allocate a new char[] that we return a pointer to, so we need to clean that up afterwards in the free_rewritten_query function.
As always, THANK YOU for using MySQL. Happy rewriting!