on duplicate key update引发mysql主从数据不一致【mysql bug】

on duplicate key update引发mysql主从数据不一致【mysql bug】

 

场景：表结构中有自增id为PK，有UNIQUE KEY；insert语句中不指明id

 

表结构

mysql> show create table user_task_stat\G
*************************** 1. row ***************************
       Table: user_task_stat
Create Table: CREATE TABLE `user_task_stat` (
  `id` int(11) NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL COMMENT '用户uid',
  `finish_task_count` int(10) unsigned NOT NULL COMMENT '完成父任务个数',
  `finish_sub_task_count` int(10) unsigned NOT NULL COMMENT '完成子任务个数',
  `award_weath` int(10) unsigned NOT NULL COMMENT '累计获得财富值',
  `award_experience` int(10) unsigned NOT NULL COMMENT '累计获得经验值',
  `award_goods` varchar(80) default NULL COMMENT '累计获得的物品编号，多个逗号分隔',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=781800 DEFAULT CHARSET=gbk COMMENT='用户任务统计表'

 

系统中对此表的更新只有一条sql：

insert ignore into user_task_stat(uid, finish_task_count, finish_sub_task_count, award_weath, award_experience, award_goo

    ds) values ($uid$, $uscore.finish_task_count$, $uscore.finish_sub_task_count$, $uscore.award_weath$, $uscore.award_experience$,

    "$uscore.award_goods$") on duplicate key update finish_task_count = finish_task_count + $uscore.finish_task_count$, finish_sub_t

    ask_count = finish_sub_task_count + $uscore.finish_sub_task_count$, award_weath = award_weath + $uscore.award_weath$, award_expe

    rience = award_experience + $uscore.award_experience$, award_goods = concat(award_goods,"$uscore.award_goods$")

 

附件中 on duplicate key update引发mysql主从数据不一致.doc    20111021-mysql-log.txt    on duplicate key update引发mysql主从数据不一致.doc