Alfranio Correia wrote:
> Hi all,
>
> updates:
>
> Theres is a final patch, at least from my point of view :), available at
> http://lists.mysql.com/commits/74255
>
> BACKGROUND
> ----------
> Mixing transactional and non-transactional tables is not supported by the
> current code. In a nutshell, non-transactional operations are logged upon
> commit or rollback thus generating invalid sequence of operations.
> For instance, assume the following statements and sessions (connections):
>
> con0: CREATE TABLE test (a int);
> con0: INSERT INTO test VALUES(1);
>
> con1: START TRANSACTION;
> con2: START TRANSACTION;
> con1: UPDATE test SET a = 10;
> con2: DELETE FROM test;
> con2: COMMIT;
> con1: COMMIT;
>
> In RBR mode, the binary log produced would look like this:
>
> con0: CREATE TABLE test (a int);
> con0: INSERT INTO test VALUES(1);
>
> con2: START TRANSACTION;
> con2: DELETE FROM test WHERE a = 10;
> con2: COMMIT;
> con1: START TRANSACTION;
> con1: UPDATE test SET a = 10 WHERE a = 1;
> con1: COMMIT;
>
> Clearly, this would generate an error in the slave as the row (a = 10) to be
> deleted would not be found.
>
>
>
> SOLUTION
> --------
> To correctly handle the mix of transactional and non-transactional
> tables, we
> proceed as follows.
>
> In STMT mode, statements are logged to the binary log upon committing or
> rolling back a transaction thus preserving any semantic among the
> statements.
> Any statement that modifies a non-transactional table in the boundaries of a
> transaction is classified as unsafe. This includes the case where a single
> statement in auto-commit mode updates non-transactional and transactional
> tables.
>
> The term unsafe means that we cannot ensure that mixing transactional and
> non-transactional tables will be handled correctly and as such a warning
> message
> is printed out. Find below an example of what may go wrong:
>
> con0: CREATE TABLE test (a int);
> con0: INSERT INTO test VALUES(1);
>
> con1: START TRANSACTION;
> con2: START TRANSACTION;
> con1: UPDATE test SET a = 10 where a = 1;
> con2: DELETE FROM test where a = 10;
> con2: COMMIT;
> con1: COMMIT;
>
> In STMT mode, the binary log produced would look like this:
>
> con0: CREATE TABLE test (a int);
> con0: INSERT INTO test VALUES(1);
>
> con2: START TRANSACTION;
> con2: DELETE FROM test WHERE a = 10;
> con2: COMMIT;
> Clearly, the master and the slave would be out of sync.
>
> In RBR mode, changes to non-transactional tables are logged to the
> binary log
> upon committing or rolling back a statement. On the other hand,
> transactional
> changes are logged upon committing a transaction and ignored upon rolling a
> transaction back. This mode is completely safe and as such no warning
> message
> is printed out.
>
> The MIXED mode is equivalent to the STMT mode when there is no unsafe
> statement.
> Any unsafe statement, however, has its changes logged as in the RBR mode.
>
> To understand the details, let's use T to represent a statement that only
> changes transactional tables, N to represent a statement that only changes
> non-transactional tables, and M to represent a statement that changes
> both (i.e.
> multi-table update). Then consider the following cases:
>
> 1) B T...T C/R: all the statements are transactional.
>
> In STMT, ROW and MIXED modes, statements or changes are logged to the
> binary log
> upon committing a transaction and ignored upon rolling a transaction back.
>
> 2) B N...N C/R: all the statements are non-transactional.
>
> In STMT, the statements are logged to the binary log upon committing or
> rolling
> back a transaction and a warning message is printed out for each statement.
No warnings should be printed in this case, a transaction with only T or
N statements is safe to binlog.
> In ROW and MIXED mode, changes are logged to the binary log as soon as each
> statement is completed.
>
> 3) B N T... C/R: the beginning part is non-transactional, followed by
> transactional
> statements.
>
> Combination of 1 and 2.
I don't think this is a simple combination of case 1) and 2), Please
read my previous comments on this issue.
>
> 4) B T N... C/R: the beginning part is transactional, followed by
> non-transactional
> Combination of 1 and 2.
Here too.
>
> 5) M...: where M is either N T or T N.
>
> This case is handled as 3 or 4.
>
Please note that M is not a sequence of N T or T N, M is a **SINGLE**
statement that modifies both transactional and non-transactional tables.
But of cause, when logging in ROW format, it will become a sequence of N
T or T N rows, but beware that it is different in STATEMENT format, we
cannot split it into NT or TN.
>
>
> TEST CASE
> ---------
> The test case should check if transactions that mix transactional and
> non-transactional tables are correctly handled. Thus to check the behavior
> provided by the current code we divided the test as follows:
>
> 1 - CREATING TABLES through SELECT * FROM
> 2 - MIXING TRANSACTIONAL and NON-TRANSACTIONAL TABLES
> 1.1 - NN TT
> 1.2 - TT NN
> 1.3 - TT NN TT
> 3 - CONCURRENCY:
> 2.1 - NON-TRANSACT TABLES - SET AUTOCOMMIT = 0 | COMMIT
> 2.2 - NON-TRANSACT TABLES - SET AUTOCOMMIT = 1 | START - COMMIT
> 4 - UPDATING MULTIPLE TABLES
> 5 - PROCEDURES, FUNCTIONS AND TRIGGERS
>
>
>
> IMPLEMENTATION DETAILS
> ----------------------
>
> There are two caches: one for transactional changes (trx-cache) and
> another one
> for non-transactional changes (nontrx-cache). The trx-cache is flushed and
> truncated before committing or rolling back a transaction. The
> nontrx-cache is
> flushed and truncated before finishing a statement. For the 5 cases
> described
> above, these caches are used as follows:
I think the nontrx-cache is better be named stmt-cache, since it is
flushed after each statement.
>
> 1) B T...T C/R: all the statements are transactional.
>
> In STMT, ROW and MIXED modes, statements or changes are copied to the
> trx-cache
> and flushed to the binary log upon committing a transaction or just ignored
> upon rolling a transaction back.
>
> 2) B N...N C/R: all the statements are non-transactional.
>
> In STMT, the statements are copied to the trx-cache and flushed to the
> binary
I think we should flush the statements to binlog after each statement to
be compatible with the old behavior and the behavior of ROW or MIXED
mode you stated below.
> log upon committing or rolling back a transaction. In ROW and MIXED mode,
> changes are copied to the nontrx-cache and flushed to the binary log as
> soon as
> each statement is completed.
>
> 3) B N T... C/R: the beginning part is non-transactional, followed by
> transactional
> statements.
>
> Combination of 1 and 2.
See above comments.
>
> 4) B T N... C/R: the beginning part is transactional, followed by
> non-transactional
> statements.
>
> Combination of 1 and 2.
>
See above comments.
> 5) M...: where M is either N T or T N.
>
> This case is handled as 3 or 4.
>
See above comments.
>
>
>
> Mats Kindahl wrote:
> > Alfranio Correia wrote:
> >
> >> Mats Kindahl wrote:
> >>
> >>> Alfranio Correia wrote:
> >>>
> >>>
> >>>> Hi all,
> >>>>
> >>>> Find below a proposal on how to handle WL#2687.
> >>>> There is also a draft of a patch available in a related bug
> (BUG#40278).
> >>>>
> >>>>
> >>> There is also BUG#28976 to consider when implementing WL#2687.
> >>>
> >>>
> >> ok. Which bug should I reference when committing a patch?
> >> The fix will be same anyway.
> >>
> >
> > I suggest that you reference the worklog and then close the bugs manually.
> There
> > might be other bugs, that we do not know about right now, that are fixed by
> this
> > as well.
> >
> > [snip]
> >
> >
> >>>> PROPOSAL
> >>>> --------
> >>>> To correctly handle the mix of transactional and non-transactional
> >>>> tables, we
> >>>> propose what follows.
> >>>>
> >>>> In STMT and MIXED modes, statements are copied to the binary log
> upon
> >>>> commit or rollback thus preserving any semantic among the statements
> in a
> >>>> transaction.
> >>>>
> >>>>
> >>> You need to clarify this and point out that the non-transactional
> statements are
> >>> *not* written out of order but will appear in the same order in the
> transaction
> >>> as they are written.
> >>>
> >>>
> >> ok.
> >>
> >>
> >>> However, I am not sure why this need to apply to mixed mode. We can
> easily
> >>> switch to row-based, and write the T(s) part to the transaction cache
> and the
> >>> N(s) part directly to the binary log.
> >>>
> >>>
> >> So let's do it. I will change the implementation to do so.
> >>
> >>
> >>>
> >>>
> >>>> Unfortunately, we cannot ensure that mixing transactional and
> >>>> non-transactional tables will be handled correctly
> >>>>
> >>>>
> >>> ... in statement-based replication...
> >>>
> >>>
> >>>
> >> This is related to statement based if we switch to rows in mixed mode.
> >>
> >
> > Right.
> >
> >
> >>>> and as such we should
> >>>> properly document this and discourage such modes under this
> circumstance.
> >>>>
> >>>>
> >>> Clarify "this circumstance." Do you mean when using statement-based
> replication?
> >>>
> >>>
> >> ok. This is related to statement based if we switch to rows in mixed mode.
> >>
> >
> > OK. Agree.
> >
> > [snip]
> >
> >
> >>>> Note that we are not proposing to distinguish the case that
> >>>> non-transactional changes are processed before any transactional
> change, what could be safely
> >>>> handled with minor changes to our proposal.
> >>>>
> >>>>
> >>> I don't understand this sentence.
> >>>
> >>>
> >> I will rewrite this but I meant to say that we will not distinguish the
> >> cases presented below:
> >>
> >> start;
> >> N
> >> T
> >> commit;
> >>
> >> and
> >>
> >> start;
> >> T
> >> N
> >> commit;
> >>
> >> In other words, we will not implement any sort of optimization based on
> >> the position of the
> >> non-transactional changes.
> >>
> >
> > OK.
> >
> > [snip]
> >
> >
> >>>> TODO ------- WE NEED TO DISCUSS THIS CASE --------- DISCUSSION 1
> >>>> 1.1 - Should we distinguish between safe and unsafe cases?
> >>>>
> >>>>
> >>> Do you mean unsafe as in "statements containing, e.g.,
> non-deterministic
> >>> changes" or in any other sense. You were using "safe" in the preceding
> sentence,
> >>> so I am not sure which one you are referring to.
> >>>
> >>>
> >> I am using safe and unsafe here to denote the mix of non-transactional
> >> and transactional tables in STMT mode.
> >>
> >
> > OK.
> >
> >
> >>>> 1.2 - Should we either print out warning messages or throw an error
> for the
> >>>> unsafe cases?
> >>>>
> >>>>
> >>> Yes?
> >>>
> >>>
> >> If you are switching to rows in MIXED mode when mixing non-transactional
> >> and transactional tables, I think we should print warning messages in STMT
> >> mode.
> >>
> >
> > OK. Well... strictly speaking, they are not unsafe any more when switching to
> > row format.
> >
> > [snip]
> >
> >
> >>>> Thus to check the behavior provided by the current code we divided
> >>>> the test as follows:
> >>>>
> >>>> 1 - MIXING TRANSACTIONAL and NON-TRANSACTIONAL TABLES
> >>>> 1.1 - NN TT
> >>>> 1.2 - TT NN
> >>>> 1.3 - TT NN TT
> >>>> 2 - CONCURRENCY:
> >>>> 2.1 - NON-TRANSACT TABLES - SET AUTOCOMMIT = 0 | COMMIT
> >>>> 2.1 - NON-TRANSACT TABLES - SET AUTOCOMMIT = 1
> >>>> 2.2 - NON-TRANSACT TABLES - SET AUTOCOMMIT = 1 | START -
> COMMIT
> >>>> 3 - CREATING TABLES through SELECT * FROM
> >>>> 4 - UPDATING MULTIPLE TABLES
> >>>> 5 - PROCEDURES, FUNCTIONS AND TRIGGERS
> >>>> 6 - EVENTS
> >>>>
> >>>> TODO ------- WE NEED TO DISCUSS THIS CASE --------- DISCUSSION 2
> >>>> 2.1 Are the tests proposed for procedures, functions and triggers
> enough?
> >>>>
> >>>>
> >>> You need to ensure that you have different nesting levels for stored
> routines:
> >>> the behavior is different depending on the nesting level (not for RBR,
> but for SBR).
> >>>
> >>>
> >> How many levels?
> >> What I have is "statement --> proc --> proc". Three levels?
> >> Is this enough?
> >>
> >
> > Yes, I think that should be enough. AIUI, stored routines called from stored
> > routines are separate, but then it's all the same.
> >
> > [snip]
> >
> >
> >>>> TODO ------- WE NEED TO DISCUSS THIS CASE --------- DISCUSSION 3
> >>>> 3.1 Do we really need a cache for non-transactional events? IMHO, a
> cache to
> >>>> address the only the current worklog is overkilling.
> >>>>
> >>>>
> >>> I don't think so, and here is why:
> >>>
> >>> Suppose that we start a statement "s" that contain transactional ( T(s)
> ) and
> >>> non-transactional ( N(s) ) changes so that the non-transactional changes
> span
> >>> more than one event. If we flush the N(s) part immediately, the binary
> log will
> >>> look something like this:
> >>>
> >>> Table-map n1
> >>> Write-rows N(s) STMT_END_F
> >>> Table-map n1
> >>> Write-rows N(s) STMT_END_F
> >>> BEGIN;
> >>> Table-map t2
> >>> Write-rows T(s)
> >>> Write-rows T(s)
> >>> COMMIT;
> >>>
> >>> When we execute these statements on the master, any concurrent
> operations on n1
> >>> will be blocked, waiting for the statement to complete. However, on the
> slave,
> >>> concurrent operation on n1 will block until the first part has been
> executed,
> >>> then the lock will be released, and it will be allowed to execute. In
> that
> >>> sense, the changes to n1 will not be atomically executed, which can have
> all
> >>> sorts of consequences.
> >>>
> >>>
> >> Hummm ok !!! I thought this was not a problem as you are
> >> changing a non-transactional table.
> >>
> >> But I understood your point and agree with you.
> >> We need a cache. :)
> >>
> >
> > OK. Good. :)
> >
> > Best wishes,
> > Mats Kindahl
> >
>
>
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe: http://lists.mysql.com/replication?unsub=1
