| Bug #45855 | row events in binlog after switch from binlog_fmt=mix to stmt with open tmp tbl | ||
|---|---|---|---|
| Submitted: | 30 Jun 2009 14:11 | Modified: | 8 Mar 2010 19:53 |
| Reporter: | Sven Sandberg | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.1+ | OS: | Any |
| Assigned to: | Daogang Qu | CPU Architecture: | Any |
| Tags: | binlog_format, mixed, temporary table | ||
[3 Jul 2009 20:29]
MySQL Verification Team
c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.37-Win X64-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.1 >use test
Database changed
mysql 5.1 >CREATE TABLE t1 (a VARCHAR(100));
Query OK, 0 rows affected (0.29 sec)
mysql 5.1 >CREATE TEMPORARY TABLE temp (a VARCHAR(100));
Query OK, 0 rows affected (0.06 sec)
mysql 5.1 >
mysql 5.1 >INSERT INTO temp VALUES (UUID());
Query OK, 1 row affected, 1 warning (0.08 sec)
mysql 5.1 >INSERT INTO t1 VALUES ('blah');
Query OK, 1 row affected (0.12 sec)
mysql 5.1 >
mysql 5.1 >SET SESSION binlog_format = STATEMENT;
Query OK, 0 rows affected (0.00 sec)
mysql 5.1 >
mysql 5.1 >INSERT INTO t1 SELECT * FROM temp;
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql 5.1 >
mysql 5.1 ># Last statement was logged in row format despite binlog_format = STATEMENT
mysql 5.1 >SHOW BINLOG EVENTS;
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| server51.000001 | 4 | Format_desc | 51 | 106 | Server ver: 5.1.37-Win X64-log, Binlog ver: 4 |
| server51.000001 | 106 | Query | 51 | 189 | create database test |
| server51.000001 | 189 | Query | 51 | 284 | use `test`; CREATE TABLE t1 (a VARCHAR(100)) |
| server51.000001 | 284 | Query | 51 | 391 | use `test`; CREATE TEMPORARY TABLE temp (a VARCHAR(100)) |
| server51.000001 | 391 | Query | 51 | 459 | BEGIN |
| server51.000001 | 459 | Query | 51 | 554 | use `test`; INSERT INTO temp VALUES (UUID()) |
| server51.000001 | 554 | Query | 51 | 623 | COMMIT |
| server51.000001 | 623 | Query | 51 | 691 | BEGIN |
| server51.000001 | 691 | Query | 51 | 784 | use `test`; INSERT INTO t1 VALUES ('blah') |
| server51.000001 | 784 | Xid | 51 | 811 | COMMIT /* xid=12 */ |
| server51.000001 | 811 | Query | 51 | 879 | BEGIN |
| server51.000001 | 879 | Query | 51 | 975 | use `test`; INSERT INTO t1 SELECT * FROM temp |
| server51.000001 | 975 | Xid | 51 | 1002 | COMMIT /* xid=14 */ |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
13 rows in set (0.00 sec)
mysql 5.1 >
[19 Jan 2010 3:47]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/97313 3316 Dao-Gang.Qu@sun.com 2010-01-19 Bug #45855 row events in binlog after switch from binlog_fmt=mix to stmt with open tmp tbl If binlog_format=MIXED, there are open temporary tables, an unsafe statement is executed, and the user issues 'SET @@session.binlog_format = STATEMENT', then subsequent DML statements will be written in row format despite binlog_format=STATEMENT. Because the binlog format can't be reset to statement based by 'reset_current_stmt_binlog_row_based' function. To fix the problem, generate ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR also and forbid switching from MIXED to STATEMENT when there are open temp tables and we are logging in row based format. @ mysql-test/suite/rpl/r/rpl_binlog_format_switch_in_tmp_table.result Test result for Bug#45855. @ mysql-test/suite/rpl/t/rpl_binlog_format_switch_in_tmp_table.test Added the test file to verify if the program will generate ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR error and forbid switching from MIXED to STATEMENT when there are open temp tables and we are logging in row based format.
[20 Jan 2010 6:21]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/97479 3164 Dao-Gang.Qu@sun.com 2010-01-20 Bug #45855 row events in binlog after switch from binlog_fmt=mix to stmt with open tmp tbl Bug #45856 can't switch from binlog_format=row to mix with open tmp tbl If binlog_format=MIXED, there are open temporary tables, an unsafe statement is executed, and the user issues 'SET @@session.binlog_format = STATEMENT', then subsequent DML statements will be written in row format despite binlog_format=STATEMENT. Because the binlog format can't be reset to statement based by 'reset_current_stmt_binlog_row_based' function. If binlog_format=ROW, there are open temporary tables, and an unsafe statement is executed, then the statement 'SET @@session.binlog_format = MIXED' generates the error: "Cannot switch out of the row-based binary log format when the session has open temporary tables" However, it is safe to switch to MIXED mode because events in row format are allowed. To fix the above two problems, generate ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR and forbid switching from MIXED or ROW to STATEMENT when there are open temp tables and we are logging in row format. There is no error in any other case. @ mysql-test/suite/rpl/r/rpl_binlog_format_switch_in_tmp_table.result Test result for bug#45855 and bug#45856. @ mysql-test/suite/rpl/t/rpl_binlog_format_switch_in_tmp_table.test Added test file to verify if the program will generate ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR error and forbid switching from MIXED or ROW to STATEMENT when there are open temp tables and we are logging in row format. There is no error in any other case.
[26 Jan 2010 9:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/98160 3164 Dao-Gang.Qu@sun.com 2010-01-26 Bug #45855 row events in binlog after switch from binlog_fmt=mix to stmt with open tmp tbl Bug #45856 can't switch from binlog_format=row to mix with open tmp tbl If binlog_format=MIXED, there are open temporary tables, an unsafe statement is executed, and the user issues 'SET @@session.binlog_format = STATEMENT', then subsequent DML statements will be written in row format despite binlog_format=STATEMENT. Because the binlog format can't be reset to statement based by 'reset_current_stmt_binlog_row_based' function. If binlog_format=ROW, there are open temporary tables, and an unsafe statement is executed, then the statement 'SET @@session.binlog_format = MIXED' generates the error: "Cannot switch out of the row-based binary log format when the session has open temporary tables" However, it is safe to switch to MIXED mode because events in row format are allowed. To fix the above two problems, generate ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR and forbid switching from MIXED or ROW to STATEMENT when there are open temp tables and we are logging in row format. There is no error in any other case. @ mysql-test/suite/binlog/r/binlog_format_switch_in_tmp_table.result Test result for bug#45855 and bug#45856. @ mysql-test/suite/binlog/t/binlog_format_switch_in_tmp_table.test Added test file to verify if the program will generate ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR error and forbid switching from MIXED or ROW to STATEMENT when there are open temp tables and we are logging in row format. There is no error in any other case.
[27 Jan 2010 2:33]
Daogang Qu
Pushed into mysql-5.1-rpl+2.
[13 Feb 2010 8:37]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100213083436-9pesg4h55w1mekxc) (version source revid:luis.soares@sun.com-20100209123439-1wao1hywtw0o6cej) (merge vers: 6.0.14-alpha) (pib:16)
[13 Feb 2010 8:39]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100213083327-cee4ao3jpg33eggv) (version source revid:luis.soares@sun.com-20100209115836-3m4f1celquf37g4q) (pib:16)
[13 Feb 2010 9:39]
Jon Stephens
Documented together with BUG#45856 in the 6.0.14 changelog as follows: There were two related issues concerning handling of unsafe statements and setting of the binary logging format when there were open temporary tables on the master, and the existing replication format was row-based or mixed: 1. When using binlog_format=ROW, and an unsafe statement was executed while there were open temporary tables on the master, the statement SET @@session.binlog_format = MIXED failed with the error -Cannot switch out of the row-based binary log format when the session has open temporary tables-. 2. When using binlog_format=MIXED, and an unsafe statement was executed while there were open temporary tables on the master, the statement SET @@session.binlog_format = STATEMENT caused any subsequent DML statements to be written to the binary log using the row-based format instead of the statement-based format. Set Needs Merge status, waiting for 5.1 and other merges.
[6 Mar 2010 10:57]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100213160132-nx1vlocxuta76txh) (merge vers: 5.5.99-m3) (pib:16)
[8 Mar 2010 19:53]
Jon Stephens
Also documented in the 5.5.3 changelog. Closed.
Description: If binlog_format=MIXED, there are open temporary tables, an unsafe statement is executed, and the user issues 'SET @@session.binlog_format = STATEMENT', then subsequent DML statements will be written in row format despite binlog_format=STATEMENT. How to repeat: source include/have_binlog_format_mixed.inc; CREATE TABLE t1 (a VARCHAR(100)); CREATE TEMPORARY TABLE temp (a VARCHAR(100)); INSERT INTO temp VALUES (UUID()); INSERT INTO t1 VALUES ('blah'); SET SESSION binlog_format = STATEMENT; INSERT INTO t1 SELECT * FROM temp; # Last statement was logged in row format despite binlog_format = STATEMENT SHOW BINLOG EVENTS; Suggested fix: Generate ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR also when switching from MIXED to STATEMENT format.