The rewrite_rules table in the query_rewrite database provides persistent storage for the rules that the Rewriter plugin uses to decide whether to rewrite statements.
Users communicate with the plugin by modifying the set of rules stored in this table. The plugin communicates information to users by setting the table's message column.
The rules table is loaded into the plugin by the flush_rewrite_rules stored procedure. Unless that procedure has been called following the most recent table modification, the table contents do not necessarily correspond to the set of rules the plugin is using.
The rewrite_rules table has these columns:
id
The rule ID. This column is the table primary key. You can use the ID to uniquely identify any rule.
pattern
The template that indicates the pattern for statements that the rule matches. Use ? to represent parameter markers that match data values.
pattern_database
The database used to match unqualified table names in statements. 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_database and the table names are identical.
replacement
The template that indicates how to rewrite statements matching the pattern column value. Use ? to represent parameter markers that match data values. In rewritten statements, the plugin replaces ? parameter markers in replacement using data values matched by the corresponding markers in pattern.
enabled
Whether the rule is enabled. Load operations (performed by invoking the flush_rewrite_rules() stored procedure) load the rule from the table into the Rewriter in-memory cache only if this column is YES.
This column makes it possible to deactivate a rule without removing it: Set the column to a value other than YES and reload the table into the plugin.
message
The plugin uses this column for communicating with users. If no error occurs when the rules table is loaded into memory, the plugin sets the message column to NULL. A non-NULL value indicates an error and the column contents are the error message. Errors can occur under these circumstances:
Either the pattern or the replacement is an incorrect SQL statement that produces syntax errors.
The replacement contains more ? parameter markers than the pattern.
If a load error occurs, the plugin also sets the Rewriter_reload_error status variable to ON.
pattern_digest
This column is used for debugging and diagnostics. If the column exists when the rules table is loaded into memory, the plugin updates it with the pattern digest. This column may be useful if you are trying to determine why some statement fails to be rewritten.
normalized_pattern
This column is used for debugging and diagnostics. If the column exists when the rules table is loaded into memory, the plugin updates it with the normalized form of the pattern. This column may be useful if you are trying to determine why some statement fails to be rewritten.