| Bug #56226 | Table map set to 0 after altering MyISAM table | ||
|---|---|---|---|
| Submitted: | 24 Aug 2010 17:36 | Modified: | 15 Nov 2010 14:32 |
| Reporter: | Mikiya Okuno | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S1 (Critical) |
| Version: | 5.1.50 | OS: | Any |
| Assigned to: | Libing Song | CPU Architecture: | Any |
[10 Sep 2010 9:07]
MySQL Verification Team
Due to this bug, binlog will include a wrong table-map. But the binlog itself is usable and it will not stop replication.
[10 Sep 2010 16:09]
MySQL Verification Team
There's a case which causes replication failure like below: === On the master === drop table if exists t1; drop table if exists t2; create table t1 (a int, b int) engine myisam; create table t2 like t1; delimiter // create trigger trg_t1ai after insert on t1 for each row begin insert into t2 (a) values (NEW.a); end;// delimiter ; insert into t1 (a) values(1); select * from t1; select * from t2; alter table t1 change b c int; alter table t2 change b c int; insert into t1 (a) values(1); select * from t1; select * from t2; === On the slave === select * from t1; select * from t2; You'll find the table contents between the master and the slave are different.
[12 Sep 2010 5:48]
MySQL Verification Team
testcase to crash the slave, or break with corrupted event. drop table if exists t1; drop table if exists t2; create table t1 (a int, b int) engine myisam; create table t2 (a varchar(255),b varchar(255)); delimiter // create trigger trg_t1ai after insert on t1 for each row begin insert into t2 (a) values (NEW.a); end;// delimiter ; insert into t1 (a) values(1); select * from t1; select * from t2; alter table t1 change b c int; alter table t2 change b c varchar(255); insert into t1 (a) values(1); select * from t1; select * from t2;
[12 Sep 2010 6:07]
MySQL Verification Team
5.1.49-debug crash with my above testcase: Assertion failed: m_curr_row_end <= m_rows_end, file g:\mysql-5.1.49-winbuild\mysql-advanced-gpl-debug-5.1.49-build\sql\log_event.h, line 3591 mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2083] mysqld-debug.exe!raise()[winsig.c:590] mysqld-debug.exe!abort()[abort.c:71] mysqld-debug.exe!_wassert()[assert.c:212] mysqld-debug.exe!Rows_log_event::unpack_current_row()[log_event.h:3591] mysqld-debug.exe!Rows_log_event::write_row()[log_event.cc:8529] mysqld-debug.exe!Write_rows_log_event::do_exec_row()[log_event.cc:8717] mysqld-debug.exe!Rows_log_event::do_apply_event()[log_event.cc:7495] mysqld-debug.exe!Log_event::apply_event()[log_event.h:1064] mysqld-debug.exe!apply_event_and_update_pos()[slave.cc:2154] mysqld-debug.exe!exec_relay_log_event()[slave.cc:2293] mysqld-debug.exe!handle_slave_sql()[slave.cc:3080] mysqld-debug.exe!pthread_start()[my_winthread.c:85] mysqld-debug.exe!_callthreadstart()[thread.c:293] mysqld-debug.exe!_threadstart()[thread.c:277] kernel32.dll!BaseThreadInitThunk() ntdll.dll!RtlInitializeExceptionChain() ntdll.dll!RtlInitializeExceptionChain() 5.1.49-release gave error; [ERROR] Slave SQL: Could not execute Write_rows event on table test.t1; Corrupted replication event was detected, Error_code: 1610; handler error HA_ERR_CORRUPT_EVENT; the event's master log anon-bin.000001, end_log_pos 5289, Error_code: 1610 [Warning] Slave: Corrupted replication event was detected Error_code: 1610
[16 Sep 2010 9:37]
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/118360 3489 Li-Bing.Song@sun.com 2010-09-16 Bug#56226 Table map set to 0 after altering MyISAM table A MyISAM table's table_map_id will always be reset to 0 after altered only its metadata. 0 is a valid value of table_map_id. But the problem will cause that more than one tables have the same table_map_id 0. If more than one tables which's table_map_id are 0 are updated in one statement, the update of these different tables will be write into only one rows event. It will cause slave server to crash. After this patch, table_map_id always be refreshed as a correct value after altered only its metadata. @ mysql-test/suite/rpl/r/rpl_alter.result Add test for BUG#56226 @ mysql-test/suite/rpl/t/rpl_alter.test Add test for BUG#56226 @ sql/sql_base.cc The invalid value of table_map_id is ~0UL. So it should be set to ~0UL but not 0 when a SHARE is initialized and we don't want to give a valid value. Because of this, we are able to know if table_map_id is invalid and should be set a new value when reopenning the table.
[20 Sep 2010 7:55]
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/118559 3489 Li-Bing.Song@sun.com 2010-09-20 Bug#56226 Table map set to 0 after altering MyISAM table A MyISAM table's table_map_id will always be reset to 0 after altered only its metadata. 0 is a valid value of table_map_id. But the problem will cause that more than one tables have the same table_map_id 0. If more than one tables which's table_map_id are 0 are updated in one statement, the update of these different tables will be write into only one rows event. It will cause slave server to crash. After this patch, table_map_id always be refreshed as a correct value after altered only its metadata. @ mysql-test/suite/rpl/r/rpl_alter.result Add test for BUG#56226 @ mysql-test/suite/rpl/t/rpl_alter.test Add test for BUG#56226
[26 Sep 2010 9:30]
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/119125 3519 Li-Bing.Song@sun.com 2010-09-26 Bug#56226 Table map set to 0 after altering MyISAM table After ALTER TABLE which changed only table's metadata, row-based binlog sometimes got corrupted since the tablemap was unexpectedly set to 0 for subsequent updates to the same table. ALTER TABLE which changed only table's metadata always reset table_map_id for the table share to 0. Despite the fact that 0 is a valid value for table_map_id, this step caused problems as it could have created situation in which we had more than one table share with table_map_id equal 0. If more than one table with table_map_id are 0 were updated in the same statement, updates to these different tables were written into the same rows event. This caused slave server to crash. This bug happens only on 5.1. It doesn't affect 5.5+. This patch solves this problem by ensuring that ALTER TABLE statements which change metadata only never reset table_map_id to 0. To do this it changes reopen_table() to correctly use refreshed table_map_id value instead of using the old one/ resetting it. @ mysql-test/suite/rpl/r/rpl_alter.result Add test for BUG#56226 @ mysql-test/suite/rpl/t/rpl_alter.test Add test for BUG#56226
[9 Oct 2010 6:25]
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/120417 3524 Li-Bing.Song@sun.com 2010-10-09 Bug#56226 Table map set to 0 after altering MyISAM table After ALTER TABLE which changed only table's metadata, row-based binlog sometimes got corrupted since the tablemap was unexpectedly set to 0 for subsequent updates to the same table. ALTER TABLE which changed only table's metadata always reset table_map_id for the table share to 0. Despite the fact that 0 is a valid value for table_map_id, this step caused problems as it could have created situation in which we had more than one table share with table_map_id equal 0. If more than one table with table_map_id are 0 were updated in the same statement, updates to these different tables were written into the same rows event. This caused slave server to crash. This bug happens only on 5.1. It doesn't affect 5.5+. This patch solves this problem by ensuring that ALTER TABLE statements which change metadata only never reset table_map_id to 0. To do this it changes reopen_table() to correctly use refreshed table_map_id value instead of using the old one/ resetting it. @ mysql-test/suite/rpl/r/rpl_alter.result Add test for BUG#56226 @ mysql-test/suite/rpl/t/rpl_alter.test Add test for BUG#56226
[11 Oct 2010 3:07]
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/120454 3526 Li-Bing.Song@sun.com 2010-10-11 Bug#56226 Table map set to 0 after altering MyISAM table After ALTER TABLE which changed only table's metadata, row-based binlog sometimes got corrupted since the tablemap was unexpectedly set to 0 for subsequent updates to the same table. ALTER TABLE which changed only table's metadata always reset table_map_id for the table share to 0. Despite the fact that 0 is a valid value for table_map_id, this step caused problems as it could have created situation in which we had more than one table share with table_map_id equal 0. If more than one table with table_map_id are 0 were updated in the same statement, updates to these different tables were written into the same rows event. This caused slave server to crash. This bug happens only on 5.1. It doesn't affect 5.5+. This patch solves this problem by ensuring that ALTER TABLE statements which change metadata only never reset table_map_id to 0. To do this it changes reopen_table() to correctly use refreshed table_map_id value instead of using the old one/ resetting it. @ mysql-test/suite/rpl/r/rpl_alter.result Add test for BUG#56226 @ mysql-test/suite/rpl/t/rpl_alter.test Add test for BUG#56226
[11 Oct 2010 3:47]
Libing Song
Pushed into mysql-5.1-bugteam Null merged into mysql-5.5-bugteam and mysql-trunk-merge, as the bug happened only on 5.1.
[13 Oct 2010 7:39]
Jon Stephens
Waiting on a merge to 5.1.
[29 Oct 2010 17:05]
Jon Stephens
Documented bugfix in the 5.1.53 changelog as follows:
An ALTER TABLE statement against a MyISAM table that altered a
column without setting its size caused the binary log to become
corrupted, leading to replication failure.
Closed.
[9 Nov 2010 19:44]
Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[13 Nov 2010 16:11]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:31]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[15 Nov 2010 14:32]
Jon Stephens
Set back to Closed state -- no additional chenglog entries required.
[18 Nov 2010 15:55]
Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)
[13 May 2011 15:56]
Jon Stephens
See also BUG#61073.
Description: After ALTER TABLE without column size change, binlog gets corrupted since the tablemap is unexpectedly set to 0 in subsequent updates. The problem can be avoided by flushing table after ALTER TABLE command. The problem happens on MyISAM table only. How to repeat: Enable binlog and perform the following statements: DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT ) ENGINE MYISAM; INSERT INTO t1 (id, c1) VALUES (NULL, 1); ALTER TABLE t1 CHANGE c1 c2 INT; -- <======== Only column name is changed. SET SESSION binlog_format = "ROW"; UPDATE t1 SET c2 = 2 WHERE id = 1; SHOW BINLOG EVENTS; Then, you'll see the output like below: | binlog.000001 | 711 | Query | 1 | 797 | use `test`; DROP TABLE IF EXISTS t1 | | binlog.000001 | 797 | Query | 1 | 950 | use `test`; CREATE TABLE t1 ( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT ) | | binlog.000001 | 950 | Query | 1 | 1018 | BEGIN | | binlog.000001 | 1018 | Table_map | 1 | 1060 | table_id: 17 (test.t1) | | binlog.000001 | 1060 | Write_rows | 1 | 1098 | table_id: 17 flags: STMT_END_F | | binlog.000001 | 1098 | Query | 1 | 1167 | COMMIT | | binlog.000001 | 1167 | Query | 1 | 1261 | use `test`; ALTER TABLE t1 CHANGE c1 c2 INT | | binlog.000001 | 1261 | Query | 1 | 1329 | BEGIN | | binlog.000001 | 1329 | Table_map | 1 | 1371 | table_id: 0 (test.t1) | | binlog.000001 | 1371 | Update_rows | 1 | 1419 | table_id: 0 flags: STMT_END_F | | binlog.000001 | 1419 | Query | 1 | 1488 | COMMIT Flushing table can avoid this issue: DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT ) ENGINE MYISAM; INSERT INTO t1 (id, c1) VALUES (NULL, 1); ALTER TABLE t1 CHANGE c1 c2 INT; FLUSH TABLES; -- <=========================== Flush the table here! SET SESSION binlog_format = "ROW"; UPDATE t1 SET c2 = 2 WHERE id = 1; SHOW BINLOG EVENTS; Suggested fix: n/a