WL#3339: Issue warnings when statement-based replication may fail
Affects: Server-5.1
—
Status: Complete
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.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.