binlog_format=ROW/STATEMENT/MIX 对记录不存在的对比
昨天一台(slave)不停的出现包含HA_ERR_KEY_NOT_FOUND的复制错误
- Include/my_base.sh (376行) #define HA_ERR_KEY_NOT_FOUND 120 /* Didn’t find key on read or update */
翻译的意思是查询或者更新操作找不到键值，实际进行测试的时候 ，where查询条件为索引和普通列值的时候，当主库binlog_format=row的时候，slave也会报错HA_ERR_KEY_NOT_FOUND
改slave对应的主库binlog_format=ROW
- 2. 测试重现
v031082.sqa.cm4 搞两个mysql实例 做主备
主库 /u01/mysql 3306 binlog_format=ROW
备库 /u01/mysql2 3309 binlog_format=STATEMENT
主库 create table t1(id int ,name varchar(100);
Insert into table t1 (name) value (‘mysql’);
Insert into table t1 (name) value (‘hbase’);
Insert into table t1 (name) value (‘oracle’);
主备库确认三条记录都在,备库清空t1表 .
备库 show slave status\G无告警
|
ROW和STATEMENT的类似操作对比 |
||
| MySQL | 主库 Binlog_format=row | 主库的binlog_format=MIX/statement; |
| v031082.sqa.cm4 3306 Master | delete from t1 where ;
delete from t1 where name=’hbase’; |
Delete from t1 where name=’mysql’ |
| v031082.sqa.cm4 3309 Slave | 备库此时show slave status ;会出现
Could not execute Update_rows event on table test.t1; Can’t find record in ‘t1′, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000001, end_log_pos 3499 |
备库没有错误信息 （目前线上大部分的MySQL 主库binlog_format=statement,如果TC迁移到MySQL，需要考虑好到底是采用MIX还是ROW） |
源码剖析
storage/innodb_plugin/handler/ha_innodb.cc 这里包含index_read(),index_first(),index_last()函数，这些函数都会判断记录是否存在
/**********************************************************************//**
Positions an index cursor to the index specified in the handle. Fetches the
row if any.
@return 0, HA_ERR_KEY_NOT_FOUND, or error number */
UNIV_INTERN
Int ha_innobase::index_read(
case DB_RECORD_NOT_FOUND:
error = HA_ERR_KEY_NOT_FOUND;
table->status = STATUS_NOT_FOUND;
break;
}
/********************************************************************//**
Positions a cursor on the first record in an index and reads the
corresponding row to buf.
@return 0, HA_ERR_END_OF_FILE, or error code */
UNIV_INTERN Int ha_innobase::index_first(
if (error == HA_ERR_KEY_NOT_FOUND) {
error = HA_ERR_END_OF_FILE;
}
)
/********************************************************************//**
Positions a cursor on the last record in an index and reads the
corresponding row to buf.
@return 0, HA_ERR_END_OF_FILE, or error code */
UNIV_INTERN int ha_innobase::index_last(
if (error == HA_ERR_KEY_NOT_FOUND) {
error = HA_ERR_END_OF_FILE;
}
)