二进制文件set insert_id 引起的主键冲突
1,此处sql 没有set insert_id
BEGIN
/*!*/;
# at 25544023
#160518 14:13:00 server id 103 end_log_pos 628971482 Query thread_id=76787 exec_time=0 error_code=0
SET TIMESTAMP=1463551980/*!*/;
insert into corp_change(corp_id,change_type,change_content_old,change_content_new,change_reason,change_date)
select NAME_CONST('l_corp_id',100018011498),change_type,change_content_old,change_content_new,change_reason,change_date from corp_change_link where corp_id= NAME_CONST('i',10067710)
/*!*/;
# at 25544389
#160518 14:13:00 server id 103 end_log_pos 628971559 Query thread_id=76787 exec_time=0 error_code=0
SET TIMESTAMP=1463551980/*!*/;
COMMIT
2 此处sql 有set insert_id
BEGIN
/*!*/;
# at 25544843
#160518 14:13:00 server id 103 end_log_pos 628971964 Intvar
SET INSERT_ID=33775179/*!*/;
# at 25544871
#160518 14:13:00 server id 103 end_log_pos 628972441 Query thread_id=76787 exec_time=0 error_code=0
SET TIMESTAMP=1463551980/*!*/;
insert into corp_director(personalID,corp_id,name,business,card_name,card_no,degrees,profession,telephone,mobi,contact_fax,start_time,end_time,base_assess,cause)
select personalID, NAME_CONST('l_corp_id',100018011498),name,business,card_name,card_no,degrees,profession,telephone,mobi,contact_fax,start_time,end_time,base_assess,cause
from corp_director_link where corp_id= NAME_CONST('i',10067710)
/*!*/;
# at 25545348
#160518 14:13:00 server id 103 end_log_pos 628972468 Xid = 303755555
COMMIT/*!*/;
1处的sql 已经把我2 里面的insert_id :33775179 用掉了
导致 复制的时候回出现 主键冲突的错误
如下
Error 'Duplicate entry '33775179' for key 'PRIMARY'' on query. Default database: 'zxlh'. Query: 'insert into corp_director(personalID,corp_id,name,business,card_name,card_no,degrees,profession,telephone,mobi,contact_fax,start_time,end_time,base_assess,cause)
select personalID, NAME_CONST('l_corp_id',100018011498),name,business,card_name,card_no,degrees,profession,telephone,mobi,contact_fax,start_time,end_time,base_assess,cause
from corp_director_link where corp_id= NAME_CONST('i',10067710)'
同时 1 那个sql 实际上 在 主库里面 根本就没有插入进去,怎么还是会同步到备库呢,
我现在每次都是 出现问题了 我人工去删除 1的数据 才能把备库 同步起来
所以 我觉得的是 set insert_id 引起问题
,哪位大牛 解答下 这个问题?