----- 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 ? > 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 ? > 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. > 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" ? Thanks. Bernd Helmholtz Zentrum Muenchen Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH) Ingolstaedter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir'in Baerbel Brumme-Bothe Geschaeftsfuehrer: Prof. Dr. Guenther Wess, Dr. Alfons Enhsen, Renate Schlusen (komm.) Registergericht: Amtsgericht Muenchen HRB 6466 USt-IdNr: DE 129521671 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
