Documentation Home
MySQL Internals Manual
Download this Manual
EPUB - 1.2Mb


MySQL Internals Manual  /  ...  /  Rules for Non-committing Statements, Except CREATE TEMPORARY TABLE...SELECT

19.4.3.3 Rules for Non-committing Statements, Except CREATE TEMPORARY TABLE...SELECT

The preliminary rules above, together with the principles for logging format, have been used to construct the following rules.

CALL statements are unrolled (see ???TODO: add section about unrolling???), so that each statement executed by the stored procedure is logged separately. (If a stored procedure A invokes a stored procedure B, then B is unrolled recursively). In the following, we assume that unrolling has already been done, and the word "statement" refers to a non-CALL top-level statement or a non-CALL sub-statement.

Let S be a logged statement that does not have an implicit commit, except CREATE TEMPORARY TABLE...SELECT (This includes all "pure DML": INSERT, UPDATE, DELETE, REPLACE, TRUNCATE, SELECT, DO, CALL, EXECUTE, LOAD DATA INFILE, and BINLOG. It also includes CREATE TEMPORARY TABLE without SELECT, and DROP TEMPORARY TABLE. CREATE TEMPORARY TABLE...SELECT is handled in the next subsection).

  • Before executing S, determine unsafeness:

(R-unsafe-nontransactional) If S either makes N-writes or reads from an N-table, and either S or a previous statement in the same transaction reads or writes to a T-table then S is marked unsafe.

(R-unsafe-self-logging) If either S or a previous statement in the same transaction reads or writes to a self-logging table, then S is marked unsafe.

  • When logging S, determine where to log it by applying the following rules in order:

(R-log-statement-format) If S is to be logged in statement format (that is, if one of the following holds: (1) @@session.binlog_format=STATEMENT; (2) @@session.binlog_format=MIXED and S is safe; (3) S is of DDL type, that is, CREATE TEMPORARY TABLE):

  1. If S produces an error and does not do any N-write, do not log.

  2. Otherwise, if either S or any previous statement in the same transaction reads or writes in any T-tables, log to TC.

  3. Otherwise, log to SC.

(R-log-row-format) If S is to be logged in row format (that is, if S is DML and one of the following holds: (1) @@session.binlog_format=ROW; (2) @@session.binlog_format=MIXED and S is unsafe):

  1. Do not log row events that write to temporary tables.

  2. Log row events that write to non-temporary N-tables to SC.

  3. Log row events that write to non-temporary T-tables to TC, except rows that are rolled back due to an error. (Note: if there is an error, rows written to a T-table are kept if there are subsequent rows written to an N-table.)

(R-flush-SC) At the end of S, write BEGIN + SC + COMMIT to the binlog and clear the SC.

  • At end of transaction:

(R-log-commit) At COMMIT or implicit commit, where all XA tables in the transaction succeed in the "prepare" phase:

  1. If the TC is non-empty, write BEGIN + TC + COMMIT to the binlog.

  2. If the TC is empty, do nothing.

(R-log-rollback) At ROLLBACK; or at COMMIT or implicit commit where some XA table fails in the "prepare" phase:

  1. If the TC contains any N-write, write BEGIN + TC + ROLLBACK to the binlog.

  2. If the TC does not contain any N-write, do nothing.

(R-log-rollback-to-savepoint) At ROLLBACK TO SAVEPOINT:

  1. If the TC contains any N-write after the savepoint, write ROLLBACK TO SAVEPOINT to the TC.

  2. Otherwise, clear the part of the TC that starts at the savepoint and extends to the end of the TC. (Bug#47327 breaks this rule)

(R-clear-TC) Clear the TC at the end of the transaction.


User Comments
Sign Up Login You must be logged in to post a comment.