Movatterモバイル変換

• Adding Rewrite Rules

• How Statement Matching Works

• Rewriting Prepared Statements

• Rewriter Plugin Operational Information

• Rewriter Plugin Use of Character Sets

To add rules for theRewriter plugin, add rows to therewrite_rules table, then invoke theflush_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:

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: NULLnormalized_pattern: NULL

The rule specifies a pattern template indicating whichSELECT statements to match, and a replacement template indicating how to rewrite matching statements. However, adding the rule to therewrite_rules table is not sufficient to cause theRewriter plugin to use the rule. You must invokeflush_rewrite_rules() to load the table contents into the plugin in-memory cache:

mysql> CALL query_rewrite.flush_rewrite_rules();

When the plugin reads each rule from the rules table, it computes a normalized (statement digest) form from the pattern and a digest hash value, and uses them to update thenormalized_pattern andpattern_digest columns:

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: d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddaenormalized_pattern: select ?

For information about statement digesting, normalized statements, and digest hash values, seeSection 29.10, “Performance Schema Statement Digests and Sampling”.

If a rule cannot be loaded due to some error, callingflush_rewrite_rules() produces an error:

mysql> CALL query_rewrite.flush_rewrite_rules();ERROR 1644 (45000): Loading of some rule(s) failed.

When this occurs, the plugin writes an error message to themessage column of the rule row to communicate the problem. Check therewrite_rules table for rows with non-NULLmessage column values to see what problems exist.

Patterns use the same syntax as prepared statements (seeSection 15.5.1, “PREPARE Statement”). Within a pattern template,? characters act as parameter markers that match data values. The? characters should not be enclosed within quotation marks. Parameter markers can be used only where data values should appear, and they cannot be used for SQL keywords, identifiers, functions, and so on. The plugin parses a statement to identify the literal values (as defined inSection 11.1, “Literal Values”), so you can put a parameter marker in place of any literal value.

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 firstSELECT statement, but does for the second. The second statement illustrates that when theRewriter plugin rewrites a statement, it produces a warning message. To view the message, useSHOW WARNINGS:

mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Note   Code: 1105Message: Query 'SELECT 10' rewritten to 'SELECT 10 + 1' by a query rewrite plugin

A statement need not be rewritten to a statement of the same type. The following example loads a rule that rewritesDELETE statements toUPDATE statements:

INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)VALUES('DELETE FROM db1.t1 WHERE col = ?',       'UPDATE db1.t1 SET col = NULL WHERE col = ?');CALL query_rewrite.flush_rewrite_rules();

To enable or disable an existing rule, modify itsenabled column and reload the table into the plugin. To disable rule 1:

UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;CALL query_rewrite.flush_rewrite_rules();

This enables you to deactivate a rule without removing it from the table.

To re-enable rule 1:

UPDATE query_rewrite.rewrite_rules SET enabled = 'YES' WHERE id = 1;CALL query_rewrite.flush_rewrite_rules();

Therewrite_rules table contains apattern_database column thatRewriter uses for matching table names that are not qualified with a database name:

Suppose that a table namedappdb.users has a column namedid 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 whenappdb is the default database:

SELECT * FROM users WHERE appdb.id =id_value;SELECT * FROM users WHERE id =id_value;

Suppose also that theid column is renamed touser_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 theid column represents).

The change means that applications must refer touser_id rather thanid in theWHERE clause, but old applications that cannot be updated no longer work properly. TheRewriter plugin can solve this problem by matching and rewriting problematic statements. To match the statementSELECT * FROM appdb.users WHERE id =value and rewrite it asSELECT * FROM appdb.users WHERE user_id =value, you can insert a row representing a replacement rule into the rewrite rules table. If you also want to match thisSELECT using the unqualified table name, it is also necessary to add an explicit rule. Using? as a value placeholder, the twoINSERT statements needed look like this:

INSERT INTO query_rewrite.rewrite_rules    (pattern, replacement) VALUES(    'SELECT * FROM appdb.users WHERE id = ?',    'SELECT * FROM appdb.users WHERE user_id = ?'    );INSERT INTO query_rewrite.rewrite_rules    (pattern, replacement, pattern_database) VALUES(    'SELECT * FROM users WHERE id = ?',    'SELECT * FROM users WHERE user_id = ?',    'appdb'    );

After adding the two new rules, execute the following statement to cause them to take effect:

CALL query_rewrite.flush_rewrite_rules();

Rewriter uses the first rule to match statements that use the qualified table name, and the second to match statements that use the unqualified name. The second rule works only whenappdb is the default database.

TheRewriter plugin uses statement digests and digest hash values to match incoming statements against rewrite rules in stages. Themax_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 digest value.

The plugin matches each statement to the rewrite rules as follows:

If multiple rules match a statement, it is nondeterministic 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 themessage column of the rule row to communicate the problem, and sets theRewriter_reload_error status variable toON.

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 statement, aRewriter pattern must contain? in the same location. Suppose that a rewrite rule has this pattern:

SELECT ?, 3

The following table shows several preparedSELECT statements and whether the rule pattern matches them.

TheRewriter plugin makes information available about its operation by means of several status variables:

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, seeSection 7.6.4.3.4, “Rewriter Query Rewrite Plugin Status Variables”.

When you load the rules table by calling theflush_rewrite_rules() stored procedure, if an error occurs for some rule, theCALL statement produces an error, and the plugin sets theRewriter_reload_error status variable toON:

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 therewrite_rules table for rows with non-NULLmessage column values to see what problems exist.

When therewrite_rules table is loaded into theRewriter plugin, the plugin interprets statements using the current global value of thecharacter_set_client system variable. If the globalcharacter_set_client value is changed subsequently, the rules table must be reloaded.

A client must have a sessioncharacter_set_client value identical to what the global value was when the rules table was loaded or rule matching does not work for that client.