FEDERATED表数据的update会引发SLAVE的1032错误
master 上创建FEDERATED表:
CREATE TABLE `test_bill_hdr` (
`Bill_Hdr_Id` char(32) NOT NULL COMMENT '单据抬头ID',
`Bill_Hdr_Id_Old` varchar(20) DEFAULT NULL COMMENT 'LMIS单据汇总ID',
`Operator_Id` char(32) NOT NULL COMMENT '运营方ID',
`Con_Id` char(32) NOT NULL COMMENT '委托方ID',
`Ldc_Id` char(32) DEFAULT NULL COMMENT '物流中心ID',
`Businessbill_No` varchar(100) NOT NULL COMMENT '业务单据编号',
`Ssa_Id` char(32) NOT NULL COMMENT '单位ID',
`Ssa_Id_2nd` char(32) DEFAULT NULL COMMENT '二级单位ID',
`Superbill_Hdr_Id` char(32) DEFAULT NULL COMMENT '上级单据抬头ID',
`Supplementalbill_Hdr_Id` char(32) DEFAULT NULL COMMENT '补单抬头ID',
`Oper_Status` varchar(2) NOT NULL DEFAULT '00' COMMENT '作业状态',
`Wave_No` varchar(10) DEFAULT NULL COMMENT '波次号',
`Dispatching_Type` varchar(1) NOT NULL DEFAULT '1' COMMENT '调度类型',
`Dispatcher` varchar(100) DEFAULT NULL COMMENT '调度员',
`Takegoods_Mode` varchar(2) NOT NULL DEFAULT '0' COMMENT '提货方式',
`Takegoods_Mode_Converted` varchar(2) NOT NULL DEFAULT '0' COMMENT '提货方式转换',
`Outstorehouse_Priority` varchar(2) NOT NULL DEFAULT '0' COMMENT '出库优先级',
`Goods_Type` varchar(2) NOT NULL COMMENT '商品类型',
`Bill_Type` varchar(2) DEFAULT NULL COMMENT '单据类型',
`Sale_Type` varchar(2) NOT NULL COMMENT '销售类型',
`Delivery_Type` varchar(100) NOT NULL DEFAULT '2' COMMENT '配送类型',
`OutbAddress_Id` char(32) DEFAULT NULL COMMENT '出库地址ID',
`Address_Id` char(32) DEFAULT NULL COMMENT '送货地址ID',
`Purchasereturn_Mode` varchar(1) DEFAULT NULL COMMENT '购退方式',
`Businessman` varchar(100) DEFAULT NULL COMMENT '业务员',
`Operator` char(32) DEFAULT NULL COMMENT '操作员',
`Buyer` varchar(100) DEFAULT NULL COMMENT '采购员',
`Is_Provide_Gift` varchar(1) NOT NULL DEFAULT 'N' COMMENT '是否发放赠品',
`Gift_Provision_Mode` varchar(2) DEFAULT NULL COMMENT '赠品发放方式',
`Consignment_Payment` varchar(20) DEFAULT NULL COMMENT '托运付款',
`Stagingarea_Id_F` varchar(20) DEFAULT NULL COMMENT '起始月台ID',
`Stagingarea_Id_T` varchar(20) DEFAULT NULL COMMENT '终止月台ID',
`Is_Stockout` varchar(1) NOT NULL DEFAULT 'N' COMMENT '是否缺货',
`Invoice_Time` datetime NOT NULL COMMENT '开票时间',
`Settleup_Type` varchar(20) DEFAULT '无' COMMENT '结算类型',
`Settleup_Time` datetime DEFAULT NULL COMMENT '结算时间',
`Is_Wholebill_Reversion` varchar(1) NOT NULL COMMENT '是否整单冲红',
`Download_Side` varchar(1) DEFAULT NULL COMMENT '单据下传方',
`Is_Drawbackpricebill` varchar(1) NOT NULL COMMENT '是否退补价单',
`Taxreceipt_Type` varchar(4) DEFAULT NULL COMMENT '税票类型',
`Taxreceipt_Name` varchar(100) DEFAULT NULL COMMENT '税票名称',
`Is_Print_Ps_Cont` varchar(1) NOT NULL DEFAULT 'N' COMMENT '是否打印购销合同',
`Is_Reversion_Audited` varchar(1) NOT NULL DEFAULT 'N' COMMENT '是否冲红审核',
`Is_Uploaded` varchar(1) NOT NULL DEFAULT 'N' COMMENT '是否上传',
`Is_Coll_Completed` varchar(1) NOT NULL DEFAULT 'N' COMMENT '是否集货完成',
`Inchk_Completed_Time` datetime DEFAULT NULL COMMENT '内复核完成时间',
`Recheckbill_Print_Sign` varchar(1) NOT NULL DEFAULT 'N' COMMENT '复核单打印标识',
`Recheckbill_Print_Time` datetime DEFAULT NULL COMMENT '复核单打印时间',
`Reportbill_Print_Sign` varchar(1) NOT NULL DEFAULT 'N' COMMENT '报告单打印标识',
`Reportbill_Clerk` varchar(100) DEFAULT NULL COMMENT '报单员',
`Outchk_Completed_Time` datetime DEFAULT NULL COMMENT '外复核完成时间',
`Is_Stagingarea_Cleared` varchar(1) NOT NULL DEFAULT 'N' COMMENT '是否清空月台',
`Print_Sign` varchar(1) NOT NULL DEFAULT 'N' COMMENT '打印标识',
`Print_Times` int(10) NOT NULL DEFAULT '0' COMMENT '打印次数',
`Created_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
`Issue_Time` datetime DEFAULT NULL COMMENT '下发时间',
`Delivery_Time` datetime DEFAULT NULL COMMENT '送货日期(出库配送时间)',
`Outbound_Mode` varchar(2) DEFAULT NULL COMMENT '出库方式',
`Kilometers` decimal(14,2) DEFAULT NULL COMMENT '公里数',
`Order_Status` varchar(2) NOT NULL DEFAULT '0' COMMENT '订单状态',
`Invoice_Staff` char(32) DEFAULT NULL COMMENT '开票员',
`Remarks` varchar(100) DEFAULT NULL COMMENT '备注',
`Aging` decimal(14,3) DEFAULT NULL COMMENT '时效',
`Is_Receipt` char(2) NOT NULL DEFAULT 'N' COMMENT '是否回单',
`Is_ScenePrint` char(2) NOT NULL DEFAULT 'N' COMMENT '是否现场打印',
`Outstore_Time` varchar(30) DEFAULT NULL COMMENT '出库时间'
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='销售开票单汇总' CONNECTION='mysql://jlp:jlp@10.3.3.4:3306/jlp/test_bill_hdr'
更改一条记录:
UPDATE `test_bill_hdr` SET `Bill_Hdr_Id_Old`='CKD888888888' WHERE (`Bill_Hdr_Id`='0000e4dcccf9e71180c5a0369f77891a') ;
slave上产生异常
(ttt@10.3.3.3:3306)[TMP]show slave status \G ;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.3.87.30
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000059
Read_Master_Log_Pos: 207252002
Relay_Log_File: relay_bin.000034
Relay_Log_Pos: 197426432
Relay_Master_Log_File: mysql_bin.000059
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table TMP.test_bill_hdr; Can't find record in 'test_bill_hdr', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql_bin.000059, end_log_pos 197427766
Skip_Counter: 0
Exec_Master_Log_Pos: 197426269
Relay_Log_Space: 207252709
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table TMP.test_bill_hdr; Can't find record in 'test_bill_hdr', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql_bin.000059, end_log_pos 197427766
Replicate_Ignore_Server_Ids:
Master_Server_Id: 30
Master_UUID: 8a1a4138-c51e-11e7-9d92-14187752957a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180117 14:16:10
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
原因:test_bill_hdr在slave找不到记录。FEDERATED表在本地是没数据记录了,只是记录字典信息。
在slave上设置参数跳过异常event
stop slave;
set global sql_slave_skip_counter =1 ;
start slave ;
slave恢复正常。