On 4/1/2016 9:12 AM, Lentes, Bernd wrote:
----- On Mar 25, 2016, at 9:54 PM, shawn l.green shawn.l.gr...@oracle.com 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
