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:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S1 (Critical)
Version:5.1.50 OS:Any
Assigned to: Libing Song CPU Architecture:Any

[24 Aug 2010 17:36] Mikiya Okuno
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
[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.