RT,这是一个老bug: http://bugs.mysql.com/bug.php?id=56226
bug#56226的描述不太准确,事实上innodb同样存在问题。
低于5.1.53版本的MySQL在分别alter两个表时将table_map置为0,如果表上有触发器,这会导致在备库上的复制错误,例如如下的test case:
reset master;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
create table t1 (a int primary key ,b int)engine=innodb;
create table t2 like t1;
create table t3 select a from t1 where 1 = 0;
alter table t2 add index (b) ;
alter table t3 add primary key(a);
delimiter //
create trigger trg_t1 after insert on t1 for each row begin
insert into t2 (a ) values (NEW.a);insert into t3 values (NEW.a);
end;//
delimiter ;
insert into t1 values (1,2);
show binlog events;
| mysql-bin.000001 | 984 | Table_map | 112 | 1026 | table_id: 68 (test.t1) || mysql-bin.000001 | 1026 | Table_map | 112 | 1067 | table_id: 0 (test.t3) || mysql-bin.000001 | 1067 | Table_map | 112 | 1109 | table_id: 0 (test.t2) || mysql-bin.000001 | 1109 | Write_rows | 112 | 1147 | table_id: 68 || mysql-bin.000001 | 1147 | Write_rows | 112 | 1181 | table_id: 0 || mysql-bin.000001 | 1181 | Write_rows | 112 | 1215 | table_id: 0 flags: STMT_END_F || mysql-bin.000001 | 1215 | Xid | 112 | 1242 | COMMIT /* xid=152 */
可以看到在binlog里,被alter过的t2表和t3表的table_id都被置为0,而在备库上,rows_log_event只有通过table_id才能对应到相应的table_map,这在备库上导致混乱,复制中断。
2.patch很简单,只是将reopen_table()函数的如下一行去掉:
3085 tmp.s->table_map_id= table->s->table_map_id;
在mysql_alter_table里,当打开表时:
6655 if (!(table= open_n_lock_single_table(thd, table_list, TL_WRITE_ALLOW_READ))) (gdb) 6657 table->use_all_columns(); (gdb) p table->s->table_map_id $4 = 0
随后在完成DDL的大部分工作后,如果need_copy_table == ALTER_TABLE_METADATA_ONLY,也就是说只修改了meta data时,就需要重新打开表reopen_table
在reopen_table()里,首先调用open_unireg_entry来获取表的相关信息,然后将之前通过open_n_lock_single_table打开的表的相关信息复制给tmp:
(gdb) 3070 if (open_unireg_entry(thd, &tmp, &table_list, (gdb) 3078 tmp.tablenr= table->tablenr; (gdb) p tmp->s->table_map_id $5 = 20 (gdb) n 3079 tmp.used_fields= table->used_fields; (gdb) 3080 tmp.const_table= table->const_table; (gdb) 3081 tmp.null_row= table->null_row; (gdb) 3082 tmp.maybe_null= table->maybe_null; (gdb) 3083 tmp.status= table->status; (gdb) 3085 tmp.s->table_map_id= table->s->table_map_id; (gdb) 3088 tmp.in_use= thd; (gdb) p tmp->s->table_map_id $6 = 0
在函数open_unireg_entry中
open_unireg_entry
|--->get_table_share_with_create
|--->get_table_share
|--->assign_new_table_id(share);
tid= ++last_table_id;其中last_table_id为全局变量