To enable or disable the plugin, enable or disable the
variable. By default, the
Rewriter plugin is
enabled when you install it (see
Section 220.127.116.11, “Installing or uninstalling the Rewriter Query Rewrite Plugin”).
To set the initial plugin state explicitly, you can set the
variable at server startup. For example, to enable the plugin in
an option file, use these lines:
It is also possible to enable or disable the plugin at runtime:
mosql> SET GLOBAL rewriter_enabled = ON; mysql> SET GLOBAL rewriter_enabled = OFF;
Assuming that the
Rewriter plugin is enabled,
it examines and possibly modifies each
SELECT statement received by the
server. The plugin determines whether to rewrite statements
based on its in-memory cache of rewriting rules, which are
loaded from the
rewrite_rules table in the
To add rules for the
Rewriter plugin, add
rows to the
rewrite_rules table, then invoke
flush_rewrite_rules() stored procedure to
load the rules from the table into the plugin. The following
example creates a simple rule to match statements that select a
single literal value:
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) -> VALUES('SELECT ?', 'SELECT ? + 1');
The resulting table contents look like this:
mysql> SELECT * FROM query_rewrite.rewrite_rules\G *************************** 1. row *************************** id: 1 pattern: SELECT ? pattern_database: NULL replacement: SELECT ? + 1 enabled: YES message: NULL pattern_digest: NULL normalized_pattern: NULL
The rule specifies a pattern template indicating which
SELECT statements to match, and a
replacement template indicating how to rewrite matching
statements. However, adding the rule to the
rewrite_rules table is not sufficient to
Rewriter plugin to use the rule.
You must invoke
flush_rewrite_rules() to load
the table contents into the plugin in-memory cache:
mysql> CALL query_rewrite.flush_rewrite_rules();
If your rewrite rules seem not to be working properly, make
sure that you have reloaded the rules table by calling
When the plugin reads each rule from the rules table, it
computes a normalized form (digest) from the pattern and a
digest hash value, and updates the
mysql> SELECT * FROM query_rewrite.rewrite_rules\G *************************** 1. row *************************** id: 1 pattern: SELECT ? pattern_database: NULL replacement: SELECT ? + 1 enabled: YES message: NULL pattern_digest: 46b876e64cd5c41009d91c754921f1d4 normalized_pattern: select ?
For information about statement digesting and normalized statements, see Section 25.9, “Performance Schema Statement Digests”.
Patterns use the same syntax as prepared statements (see
Section 13.5.1, “PREPARE Syntax”). Within a pattern template,
? characters act as parameter markers that
match data values. Parameter markers can be used only where data
values should appear, not for SQL keywords, identifiers, and so
? characters should not be
enclosed within quotation marks.
Like the pattern, the replacement can contain
? characters. For a statement that matches a
pattern template, the plugin rewrites it, replacing
? parameter markers in the replacement using
data values matched by the corresponding markers in the pattern.
The result is a complete statement string. The plugin asks the
server to parse it, and returns the result to the server as the
representation of the rewritten statement.
After adding and loading the rule, check whether rewriting occurs according to whether statements match the rule pattern:
mysql> SELECT PI(); +----------+ | PI() | +----------+ | 3.141593 | +----------+ 1 row in set (0.01 sec) mysql> SELECT 10; +--------+ | 10 + 1 | +--------+ | 11 | +--------+ 1 row in set, 1 warning (0.00 sec)
No rewriting occurs for the first
SELECT statement, but does for
the second. The second statement illustrates that when the
Rewriter plugin rewrites a statement, it
produces a warning message. To view the message, use
mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1105 Message: Query 'SELECT 10' rewritten to 'SELECT 10 + 1' by a query rewrite plugin
To enable or disable an existing rule, modify its
enabled column and reload the table into the
plugin. To disable rule 1:
mysql> UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1; mysql> CALL query_rewrite.flush_rewrite_rules();
This enables you to deactivate a rule without removing it from the table.
To re-enable rule 1:
mysql> UPDATE query_rewrite.rewrite_rules SET enabled = 'YES' WHERE id = 1; mysql> CALL query_rewrite.flush_rewrite_rules();
rewrite_rules table contains a
pattern_database column that
Rewriter uses for matching table names that
are not qualified with a database name:
Qualified table names in statements match qualified names in the pattern if corresponding database and table names are identical.
Unqualified table names in statements match unqualified names in the pattern only if the default database is the same as
pattern_databaseand the table names are identical.
Suppose that a table named
appdb.users has a
id and that applications are
expected to select rows from the table using a query of one of
these forms, where the second can be used only if
appdb is the default database:
SELECT * FROM users WHERE appdb.id = id_value; SELECT * FROM users WHERE id = id_value;
Suppose also that the
id column is renamed to
user_id (perhaps the table must be modified
to add another type of ID and it is necessary to indicate more
specifically what type of ID the
The change means that applications must refer to
user_id rather than
WHERE clause. But if there are old
applications that cannot be written to change the
SELECT queries they generate, they will no
longer work properly. The
Rewriter plugin can
solve this problem. To match and rewrite statements whether or
not they qualify the table name, add the following two rules and
reload the rules table:
mysql> INSERT INTO query_rewrite.rewrite_rules -> (pattern, replacement) VALUES( -> 'SELECT * FROM appdb.users WHERE id = ?', -> 'SELECT * FROM appdb.users WHERE user_id = ?' -> ); mysql> INSERT INTO query_rewrite.rewrite_rules -> (pattern, replacement, pattern_database) VALUES( -> 'SELECT * FROM users WHERE id = ?', -> 'SELECT * FROM users WHERE user_id = ?', -> 'appdb' -> ); mysql> CALL query_rewrite.flush_rewrite_rules();
Rewriter uses the first rule to match
statements that use the qualified table name. It uses the second
to match statements that used the unqualified name, but only if
the default database is
appdb (the value in
Rewriter plugin uses statement digests to
match incoming statements against rewrite rules in stages. The
max_digest_length system variable determines
the size of the buffer used for computing statement digests.
Larger values enable computation of digests that distinguish
longer statements. Smaller values use less memory but increase
the likelihood of longer statements colliding with the same
The plugin matches each statement to the rewrite rules as follows:
Compute the statement digest hash value and compare it to the rule digest hash values. This is subject to false positives, but serves as a quick rejection test.
If the statement digest hash value matches any pattern digest hash values, match the normalized form of the statement to the normalized form of the matching rule patterns.
If the normalized statement matches a rule, compare the literal values in the statement and the pattern. A
?in the pattern matches any literal value in the statement. If the statement prepares a
?in the pattern also matches
?in the statement. Otherwise, corresponding literals must be the same.
If multiple rules match a statement, it is indeterminate which one the plugin uses to rewrite the statement.
If a pattern contains more markers than the replacement, the
plugin discards excess data values. If a pattern contains fewer
markers than the replacement, it is an error. The plugin notices
this when the rules table is loaded, writes an error message to
message column of the rule row to
communicate the problem, and sets the
Prepared statements are rewritten at parse time (that is, when they are prepared), not when they are executed later.
Prepared statements differ from nonprepared statements in that
they may contain
? characters as parameter
markers. To match a
? in a prepared
Rewriter pattern must contain
? in the same location. Suppose that a
rewrite rule has this pattern:
SELECT ?, 3
The following table shows several prepared
SELECT statements and whether the
rule pattern matches them.
|Prepared Statement||Whether Pattern Matches Statement|
Rewriter plugin makes information
available about its operation by means of several status
mysql> SHOW GLOBAL STATUS LIKE 'Rewriter%'; +-----------------------------------+-------+ | Variable_name | Value | +-----------------------------------+-------+ | Rewriter_number_loaded_rules | 1 | | Rewriter_number_reloads | 5 | | Rewriter_number_rewritten_queries | 1 | | Rewriter_reload_error | ON | +-----------------------------------+-------+
For descriptions of these variables, see Section 18.104.22.168.4, “Rewriter Query Rewrite Plugin Status Variables”.
When you load the rules table by calling the
flush_rewrite_rules() stored procedure, if an
error occurs for some rule, the
statement produces an error, and the plugin sets the
Rewriter_reload_error status variable to
mysql> CALL query_rewrite.flush_rewrite_rules(); ERROR 1644 (45000): Loading of some rule(s) failed. mysql> SHOW GLOBAL STATUS LIKE 'Rewriter_reload_error'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Rewriter_reload_error | ON | +-----------------------+-------+
In this case, check the
message column of
rewrite_rules table rows for
NULL values to see what the problem was.
rewrite_rules table is loaded into
Rewriter plugin, the plugin interprets
statements using the current global value of the
variable. If the global
character_set_client value is
changed subsequently, the rules table must be reloaded.
A client must have a session
identical to what the global value was when the rules table was
loaded or rule matching will not work for that client.