Rechercher dans le manuel MySQL
5.6.4.3 Rewriter Query Rewrite Plugin Reference
The following discussion serves as a reference to these
components associated with the Rewriter
query
rewrite plugin:
The
Rewriter
rules table in thequery_rewrite
databaseRewriter
procedures and functionsRewriter
system and status variables
5.6.4.3.1 Rewriter Query Rewrite Plugin Rules Table
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 inreplacement
using data values matched by the corresponding markers inpattern
.enabled
Whether the rule is enabled. Load operations (performed by invoking the
flush_rewrite_rules()
stored procedure) load the rule from the table into theRewriter
in-memory cache only if this column isYES
.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 toNULL
. 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 toON
.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.
Rewriter
plugin operation uses a stored
procedure that loads the rules table into its in-memory cache,
and a helper user-defined function (UDF). Under normal
operation, users invoke only the stored procedure. The UDF is
intended to be invoked by the stored procedure, not directly
by users.
flush_rewrite_rules()
This stored procedure uses the
load_rewrite_rules()
UDF to load the contents of therewrite_rules
table into theRewriter
in-memory cache.Calling
flush_rewrite_rules()
impliesCOMMIT
.Invoke this procedure after you modify the rules table to cause the plugin to update its cache from the new table contents. If any errors occur, the plugin sets the
message
column for the appropriate rule rows in the table and sets theRewriter_reload_error
status variable toON
.This UDF is a helper routine used by the
flush_rewrite_rules()
stored procedure.
The Rewriter
query rewrite plugin supports
the following system variables. These variables are available
only if the plugin is installed (see
Section 5.6.4.1, “Installing or Uninstalling the Rewriter Query Rewrite Plugin”).
-
Property Value System Variable rewriter_enabled
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value ON
Whether the
Rewriter
query rewrite plugin is enabled. -
Property Value System Variable rewriter_verbose
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer For internal use.
The Rewriter
query rewrite plugin supports
the following status variables. These variables are available
only if the plugin is installed (see
Section 5.6.4.1, “Installing or Uninstalling the Rewriter Query Rewrite Plugin”).
The number of rewrite plugin rewrite rules successfully loaded from the
rewrite_rules
table into memory for use by theRewriter
plugin.The number of times the
rewrite_rules
table has been loaded into the in-memory cache used by theRewriter
plugin.Rewriter_number_rewritten_queries
The number of queries rewritten by the
Rewriter
query rewrite plugin since it was loaded.Whether an error occurred the most recent time that the
rewrite_rules
table was loaded into the in-memory cache used by theRewriter
plugin. If the value isOFF
, no error occurred. If the value isON
, an error occurred; check themessage
column of therewriter_rules
table for error messages.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-rewriter-query-rewrite-plugin-reference.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.