Re: need help from the list admin

On 4/1/2016 9:12 AM, Lentes, Bernd wrote:
> ----- On Mar 25, 2016, at 9:54 PM, shawn l.green [email protected] wrote:
>
>
>> "Unsafe" in that sense replies to the fact that certain commands can
>> have a different effect when processed from the Binary Log than they did
>> when they were executed originally on the system that wrote the Binary
>> Log. This would be true for both a point-in-time recovery situation and
>> for replication. The topic of unsafe commands is covered rather well on
>> these pages:
>> http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html
>> http://dev.mysql.com/doc/refman/5.6/en/replication-sbr-rbr.html
>>
>> This is particularly true for commands that may cross transactional
>> boundaries and change non-transactional tables.  The effect of those
>> commands are apparent immediately to any other user of the server. They
>> do not rely on the original transaction to complete with a COMMIT. The
>> workaround we employed was to keep the non-transactional table locked
>> (to keep others from altering it) until the transaction completes
>> (COMMIT or ROLLBACK). That way we do our best to make all changes
>> "permanent" at the same time.
>>
>
> Hi,
>
> oh my god. The more i read the more i'm getting confused. I totally underrated replication.
> But i will not give up ;-) And i appreciate your help, Shawn.
> What do you mean with the workaround ? Does MySQL this automatically or has it be done
> in the app code ?
>

It's inside the server. You don't need to do anything as a user.


>> You would be better served by first converting your MyISAM tables to
>> InnoDB to stop mixing storage engine behaviors (transactional and
>> non-transactional) within the scope of a single transaction. But if you
>> cannot convert them, using MIXED will be a good compromise.
>
> Is the mix of MyISAM and InnoDB a problem with Row-Based-Logging or
> with Statement-Based-Logging or with both ?
>
>

Both.


>> Look at this sequence and think what would happen without that "stronger
>> locking" you mentioned earlier.
>>
>> (connection 1)
>>    begin transaction
>>    INSERT myisam_table SELECT ... FROM InnoDB_table WHERE ...
>>           (connection 2)
>>           DELETE myisam_table WHERE ...  (this removes one of the rows that
>>           connection 1 just added)
>             (end of connection 2)
>> (connection 1)
>>    COMMIT
>>
>> When the slave sees this sequence, it will get the command from
>> Connection2 first (it completed first so it winds up in the Binary Log).
>> It removed 8 rows on the master but it would only see 7 on the slave.
>> Why? The 8th row has not been added to the MyISAM table on the slave
>> because the transaction that does it hasn't been recorded to the Binary
>> Log yet.
>>
>> That's why there is stronger locking comes into play. If we had not
>> blocked connection 2 until connection 1 completed things would be out of
>> temporally speaking. It's still possible for things to happen out of
>> sequence on the slave when mixing transactional and non-transactional
>> tables in the same transaction.
>>
>
> I don't understand the example:
> Does "begin transaction" and "COMMIT" have any influence on the insert ?
>  From what i understand a myisam table does not support transactions,
> so it should not care about "begin transaction" and "commit".
> So the insert should be done immediately. The select on the InnoDB also
> should not wait, because it's applied without "LOCK IN SHARE MODE".
> So x lines are added immediately. This is done on the master, written in the log
> and then replicated to the slave, which also adds x lines.
> Then connection 2 deletes 8 rows, one is from the previous insert.
> First on the master and then on the slave.
> I assume that the connections are established in the order they appear here
> (connection 2 is established after the insert in connection 1).
> So on both 8 rows are deleted.
>
>


You said, "This is done on the master, written in the log and then 
replicated to the slave, "

The INSERT would not appear in the Binary log until after session 1 
commits. Even if session 1 does a rollback, you would still see the 
entire transaction including the ROLLBACK. We have to do it that way to 
preserve the transaction isolation of the InnoDB data.

Yes, you read the shorthand correctly and in the correct temporal sequence.
   session1 did two commands.
   session2 issued one command.
   session1 did a commit.

It does not matter of the sessions were created in that order or not. 
Only the sequence in which the commands are executed matters.


>
>> This takes us to the next point you have...
>>> The doc says: "Due to concurrency issues, a slave can become
>>> inconsistent when a transaction contains updates to both transactional
>>> and nontransactional tables. MySQL tries to preserve causality among
>>> these statements by writing nontransactional statements to the
>>> transaction cache, which is flushed upon commit. However, problems arise
>>> when modifications done to nontransactional tables on behalf of a
>>> transaction become immediately visible to other connections because
>>> these changes may not be written immediately into the binary log.
>>> Beginning with MySQL 5.5.2, the binlog_direct_non_transactional_updates
>>> variable offers one possible workaround to this issue. By default, this
>>> variable is disabled. Enabling binlog_direct_non_transactional_updates
>>> causes updates to nontransactional tables to be written directly to the
>>> binary log, rather than to the transaction cache.
>>> binlog_direct_non_transactional_updates works only for statements that
>>> are replicated using the statement-based binary logging format; that is,
>>> it works only when the value of binlog_format is STATEMENT, or when
>>> binlog_format is MIXED and a given statement is being replicated using
>>> the statement-based format. This variable has no effect when the binary
>>> log format is ROW, or when binlog_format is set to MIXED and a given
>>> statement is replicated using the row-based format.
>>> Important:
>>> Before enabling this variable, you must make certain that there are no
>>> dependencies between transactional and nontransactional tables; an
>>> example of such a dependency would be the statement INSERT INTO
>>> myisam_table SELECT * FROM innodb_table. Otherwise, such statements are
>>> likely to cause the slave to diverge from the master."
>>> Does that mean that "Due to concurrency issues, a slave can become
>>> inconsistent when a transaction contains updates to both transactional
>>> and nontransactional tables" is also a problem for RBL ?
>>> "... you must make certain that there are no dependencies between
>>> transactional and nontransactional tables; an example of such a
>>> dependency would be the statement INSERT INTO myisam_table SELECT * FROM
>>> innodb_table. Otherwise, such statements are likely to cause the slave
>>> to diverge from the master."
>>> Are these statements problems when using RBL ?
>>>
>>> Thanks for any answer.
>>>
>>>
>>> Bernd
>>>
>>>
>>
>> Because the commands ("events" in the terminology used to describe the
>> Binary Log) are not recorded until their transaction completes it is
>> possible for other earlier-finishing transactions looking at
>> non-transactional tables to make changes that are out of sequence.
>>
>> It's possible for the two storage engines to co-exist but you need to be
>> careful or you will create the database equivalent to the "grandfather
>> paradox". This kind of self-protection needs to happen in your
>> application logic.
>>
>> Only using one type of storage engine per transaction is the other way
>> to avoid the problem.
>
> Independent from the binlog_format ?
> Does commit means "write now to the binlog" ?
>

Effectively, it does (for InnoDB-based transactions). InnoDB first 
writes the entire transaction to the Binary Log (it was sitting in the 
Binlog cache up until this point) then it pumps the necessary data into 
the REDO log (for disaster recovery). At that point the transaction is 
considered "committed".  In the case of a rollback, there is nothing to 
log in either location, no permanent changes were made to the data. 
However if the transaction that rolled back contained statements that 
changed MyISAM tables, then the entire transaction (all of the work it 
did) needs to be written into the Binary Log and REDO log just to have 
the very last command be "ROLLBACK".   What that will do is create the 
same sequence of data changes on the slave that happened on the master.



> Thanks.
>

You're welcome

>
> Bernd
>

-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql