Re: need help from the list admin
Am 01.04.2016 um 21:09 schrieb Lentes, Bernd: - Am 1. Apr 2016 um 17:45 schrieb shawn l.green shawn.l.gr...@oracle.com: 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. So the INSERT take care about the transaction (begin transaction ... COMMIT) although it's a MyISAM table ? Because i read MyISAM does not care about it: http://stackoverflow.com/questions/8036005/myisam-engine-transaction-support and hence you should not mix innodb and non-transactional tables, a MyISAM table is not and will never be part of a transaction signature.asc Description: OpenPGP digital signature
Re: need help from the list admin
Sorry for pm ! - Am 1. Apr 2016 um 17:45 schrieb shawn l.green shawn.l.gr...@oracle.com: >>> 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. Aah ! In the beginning i thought it's just a problem for RBL. >>> 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 >> 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. So the INSERT take care about the transaction (begin transaction ... COMMIT) although it's a MyISAM table ? Because i read MyISAM does not care about it: http://stackoverflow.com/questions/8036005/myisam-engine-transaction-support >> 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). And when in that temporal sequence is the data written to the tablespace ? > 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. So when transaction is rollbacked, the inserted data in the MyISAM table remains ? Thaks again. Bernd P.S. i tried several times to rename the subject into something like "Replication - was "need help from the list admin"", but this mail is always bounced back because it is recognized as spam !?! I just renamed the subject ! 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
Re: need help from the list admin
- Am 1. Apr 2016 um 17:52 schrieb shawn l.green shawn.l.gr...@oracle.com: >> What is true ? when the transaction started or when the first read is >> performed ? > Until you need to establish a snapshot of the data, then you don't need > a snapshot position. > > The transaction physically begins (rows begin to be protected against > changes by other transactions) with the first read. OK. But only when the first read is in a transaction and isolation level is REPEATABLE READ the query is "snapshotted" for further queries, so seeing the same result ? When several SELECT are issued not inside a transaction they always get the current data ? > Consider the alternative: If we started protecting data with the START > TRANSACTION command we would need to protect every row in every table in > every database. That is simply not efficient. YES. > We protect the pages that contain the rows that are physically required > by the individual transaction. This is a much smaller locking footprint > and is much easier to manage. 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
Re: need help from the list admin
On 4/1/2016 10:08 AM, Lentes, Bernd wrote: - On Apr 1, 2016, at 3:12 PM, Bernd Lentes bernd.len...@helmholtz-muenchen.de wrote: Btw: i read about isolation levels. REPEATABLE READ is the default for InnoDB. http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_repeatable_read says: "...so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.". http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_consistent_read says: "With the repeatable read isolation level, the snapshot is based on the time when the first read operation is performed". What is true ? when the transaction started or when the first read is performed ? Until you need to establish a snapshot of the data, then you don't need a snapshot position. The transaction physically begins (rows begin to be protected against changes by other transactions) with the first read. Consider the alternative: If we started protecting data with the START TRANSACTION command we would need to protect every row in every table in every database. That is simply not efficient. We protect the pages that contain the rows that are physically required by the individual transaction. This is a much smaller locking footprint and is much easier to manage. 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
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 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
Re: need help from the list admin
- On Apr 1, 2016, at 3:12 PM, Bernd Lentes bernd.len...@helmholtz-muenchen.de wrote: Btw: i read about isolation levels. REPEATABLE READ is the default for InnoDB. http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_repeatable_read says: "...so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.". http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_consistent_read says: "With the repeatable read isolation level, the snapshot is based on the time when the first read operation is performed". What is true ? when the transaction started or when the first read is performed ? 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
RE: need help from the list admin
- 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
