Re: BEGIN/COMMIT statements not written to the binary log : may it cause problems with replication ?
Hi! Heikki == Heikki Tuuri [EMAIL PROTECTED] writes: Heikki Guilhem, Heikki this is a known problem and mentioned in the Restrictions section of the Heikki InnoDB online manual. Heikki It is not the correct way to run SQL statements in the autocommit mode on Heikki the slave server, while the master has normal transaction processing on. Heikki I have asked Sasha to add COMMIT marks to the binlog, so that people would Heikki not see partial transactions on the slave server. I am sending a carbon copy Heikki of this message to Sasha. Heikki Best regards, We have this in our todo and it will be fixed in MySQL 4.0 or 4.1. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BEGIN/COMMIT statements not written to the binary log : may it cause problems with replication ?
Guilhem, this is a known problem and mentioned in the Restrictions section of the InnoDB online manual. It is not the correct way to run SQL statements in the autocommit mode on the slave server, while the master has normal transaction processing on. I have asked Sasha to add COMMIT marks to the binlog, so that people would not see partial transactions on the slave server. I am sending a carbon copy of this message to Sasha. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB .. Description: As I have no test case, only strong presomptions, I sent this to [EMAIL PROTECTED] but got no feedback. I use MySQL-Max 4.0.1 with InnoDB tables with binary logging on. I see that the BEGIN and COMMIT statements that wrap my queries are not written to the binary log. I perfectly understand this if I have one server. But assume I have a master server, and a slave server that replicates the master. Let's say I distribute reads. Let's say I issue the following queries on the master (I use BEGIN and COMMIT for consistency) :BEGIN; UPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12;# I don't work in a bank !!COMMIT; then onlyUPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12; is written to the master's binary log, and then propagates to the slave. Assume that, while it is propagating I issue a reading query on the slave :SELECT sum from accounts where num in(10,12); Then, to my mind, this select might be treated AFTER the first update being processed by the slave, and BEFORE the second update being processed by the slave.Then I'll get not-consistent results. This would not happen if BEGIN and COMMIT had been written to the master's binary log.Am I misunderstanding something ? If this problem really exists, is it to be fixed ?Thanks anyway ! How-To-Repeat: This is not repeatable, it depends on the milliseconds I suppose Fix: Synopsis: BEGIN/COMMIT statements not written to the binary log Submitter-Id: submitter ID Originator: BICHOT Organization: your organization MySQL support: [none] Severity: [non-critical | serious | critical ] Priority: [low | medium | high ] Category: [mysqld]Class: [ sw-bug | doc-bug | change-request | support ] Release: mysql-4.0.1Exectutable: [mysqld-nt] Environment: machine descriptionSystem:Win2000 Compiler: VC++ 6.0Architecture: i586Guilhem BICHOTIPSN/DIR/SG/SI 01 46 54 92 31mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BEGIN/COMMIT statements not written to the binary log : may it cause problems with replication ?
Guilhem, this is a known problem and mentioned in the Restrictions section of the InnoDB online manual. It is not the correct way to run SQL statements in the autocommit mode on the slave server, while the master has normal transaction processing on. I have asked Sasha to add COMMIT marks to the binlog, so that people would not see partial transactions on the slave server. I am sending a carbon copy of this message to Sasha. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB .. Description: As I have no test case, only strong presomptions, I sent this to [EMAIL PROTECTED] but got no feedback. I use MySQL-Max 4.0.1 with InnoDB tables with binary logging on. I see that the BEGIN and COMMIT statements that wrap my queries are not written to the binary log. I perfectly understand this if I have one server. But assume I have a master server, and a slave server that replicates the master. Let's say I distribute reads. Let's say I issue the following queries on the master (I use BEGIN and COMMIT for consistency) :BEGIN; UPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12;# I don't work in a bank !!COMMIT; then onlyUPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12; is written to the master's binary log, and then propagates to the slave. Assume that, while it is propagating I issue a reading query on the slave :SELECT sum from accounts where num in(10,12); Then, to my mind, this select might be treated AFTER the first update being processed by the slave, and BEFORE the second update being processed by the slave.Then I'll get not-consistent results. This would not happen if BEGIN and COMMIT had been written to the master's binary log.Am I misunderstanding something ? If this problem really exists, is it to be fixed ?Thanks anyway ! How-To-Repeat: This is not repeatable, it depends on the milliseconds I suppose Fix: Synopsis: BEGIN/COMMIT statements not written to the binary log Submitter-Id: submitter ID Originator: BICHOT Organization: your organization MySQL support: [none] Severity: [non-critical | serious | critical ] Priority: [low | medium | high ] Category: [mysqld]Class: [ sw-bug | doc-bug | change-request | support ] Release: mysql-4.0.1Exectutable: [mysqld-nt] Environment: machine descriptionSystem:Win2000 Compiler: VC++ 6.0Architecture: i586Guilhem BICHOTIPSN/DIR/SG/SI 01 46 54 92 31mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BEGIN/COMMIT statements not written to the binary log : may it cause problems with replication ?
Guilhem, this is a known problem and mentioned in the Restrictions section of the InnoDB online manual. It is not the correct way to run SQL statements in the autocommit mode on the slave server, while the master has normal transaction processing on. I have asked Sasha to add COMMIT marks to the binlog, so that people would not see partial transactions on the slave server. I am sending a carbon copy of this message to Sasha. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB .. Description: As I have no test case, only strong presomptions, I sent this to [EMAIL PROTECTED] but got no feedback. I use MySQL-Max 4.0.1 with InnoDB tables with binary logging on. I see that the BEGIN and COMMIT statements that wrap my queries are not written to the binary log. I perfectly understand this if I have one server. But assume I have a master server, and a slave server that replicates the master. Let's say I distribute reads. Let's say I issue the following queries on the master (I use BEGIN and COMMIT for consistency) :BEGIN; UPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12;# I don't work in a bank !!COMMIT; then onlyUPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12; is written to the master's binary log, and then propagates to the slave. Assume that, while it is propagating I issue a reading query on the slave :SELECT sum from accounts where num in(10,12); Then, to my mind, this select might be treated AFTER the first update being processed by the slave, and BEFORE the second update being processed by the slave.Then I'll get not-consistent results. This would not happen if BEGIN and COMMIT had been written to the master's binary log.Am I misunderstanding something ? If this problem really exists, is it to be fixed ?Thanks anyway ! How-To-Repeat: This is not repeatable, it depends on the milliseconds I suppose Fix: Synopsis: BEGIN/COMMIT statements not written to the binary log Submitter-Id: submitter ID Originator: BICHOT Organization: your organization MySQL support: [none] Severity: [non-critical | serious | critical ] Priority: [low | medium | high ] Category: [mysqld]Class: [ sw-bug | doc-bug | change-request | support ] Release: mysql-4.0.1Exectutable: [mysqld-nt] Environment: machine descriptionSystem:Win2000 Compiler: VC++ 6.0Architecture: i586Guilhem BICHOTIPSN/DIR/SG/SI 01 46 54 92 31mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BEGIN/COMMIT statements not written to the binary log : may it cause problems with replication ?
Hi! Heikki == Heikki Tuuri [EMAIL PROTECTED] writes: Heikki Guilhem, Heikki this is a known problem and mentioned in the Restrictions section of the Heikki InnoDB online manual. Heikki It is not the correct way to run SQL statements in the autocommit mode on Heikki the slave server, while the master has normal transaction processing on. Heikki I have asked Sasha to add COMMIT marks to the binlog, so that people would Heikki not see partial transactions on the slave server. I am sending a carbon copy Heikki of this message to Sasha. Heikki Best regards, We have this in our todo and it will be fixed in MySQL 4.0 or 4.1. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php