Suggestion from Mats.
IDEA
----
Now that we have a mixed binlogging mode, whose intent is that it should
toggle into row-based mode for all statements where statement-based
binlogging may be unsafe (e.g. ``UUID()``, UDFs, stored functions inserting
into more than one table with auto_increment), it makes sense to warn the
user (via a warning sent to the client) and the DBA (via a warning printed to
the error log) when he's using such statement in statement-based mode.
For example, if the master is using statement-based binary logging, you would see::
INSERT INTO t SELECT UUID();
warning: statement-based binary logging is not safe with this statement,
please consider using mixed or row-based binary logging
A similar warning would be printed to the error log (together with the
statement involved).
PROBLEMS
--------
- We should not fill the error log: write to the error log only once per
run of mysqld
- Should we issue one warning to the client per session or one per statement?
- We should not give false alarms: SELECT UUID() is safe, i.e. we should warn
only if the statement did write something to the binlog
EXTENSIONS
----------
To make it even more useful, we should extend the mixed mode to use row-based
in other statement-based-unsafe situations to be listed: multiple calls to
RAND(), use of SYSDATE(), etc, anything listed in the "replication features and
known problems" of the manual
REFERENCES
----------
See also WL#3303.
The following will be implemented:
1. Whenever an attempt is made to log an unsafe statement in statement
format:
a) A warning is pushed into the ``SHOW WARNINGS`` table. This is done
each time an unsafe statement is executed.
b) A warning message is logged to the error log. To prevent flooding
the error log, there is just one warning message logged for each
client session.
2. The warning message will contain the statement executed when logged to
the error log, but not in the ``SHOW WARNINGS`` table.
This is associated with WL#3303, which needs similar information.
Changes to class ``Query_tables_list``
--------------------------------------
Introduce one new member variable ``binlog_stmt_flags`` holding several flags
with information about the current statement being processed. This flag is set
according to what the scanner/parser decides about the statement.
Introduce a flag ``BINLOG_STMT_FLAG_UNSAFE`` to denote that the statement is
unsafe for logging. The flag is true if the statement is considered unsafe and
false if the statement is considered safe. Please note that this is a heuristic
decision.
Introduce member functions ``{is,set,clear}_stmt_unsafe()`` for manipulating the
unsafe flag.
This flag replaces the ``binlog_row_based_if_mixed`` flag is *all* code. In
order to set the row-based format for the statement, ``lock_tables()``
investigate the value of the flag and then sets the format based on the flags
value.
Changes to ``item_create`` module
---------------------------------
(I.e., files ``item_create.{h,cc}``)
If a statement is considered unsafe, set the unsafe flag. This replaces the
previous setting of ``binlog_row_based_if_mixed`` indicating that row-based
format should be used for the statement in mixed mode.
Changes to ``lock_tables()``
----------------------------
If an attempt is made to lock more than one table with an auto increment
column, set the unsafe flag. This replaces the previous setting of
``binlog_row_based_if_mixed`` indicating that row-based format should be
used for the statement in mixed mode.
Changes to ``sql_insert`` module
--------------------------------
If executing an ``INSERT DELAYED``, set the unsafe flag. This replaces the
previous setting of ``binlog_row_based_if_mixed`` indicating that row-based
format should be used for the statement in mixed mode.
Changes to the ``sql_view`` module
----------------------------------
If the ``SELECT`` statement of a view is unsafe, then so is the view. This
replaces the previous setting of ``binlog_row_based_if_mixed`` indicating that
row-based format should be used for the statement in mixed mode.
Changes to the ``sp_head`` module
---------------------------------
If a statement in the body of a stored routine is unsafe, then so is the
statement containing a call to the stored routine.
Changes to ``THD``
------------------
In order to generate a warning, the function ``binlog_query()`` will emit a
warning message if an unsafe statement is logged to the binary log under
statement mode.
You must be logged in to tag this worklog