【MySQL主从复制异常处理】LAST_ERROR_NUMBER:1292 Error 'Truncated incorrect DOUBLE value: 'TB40001343''
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.172.160.1
Master_User: repl
Master_Port: 27415
Connect_Retry: 60
Master_Log_File: master-bin.013694
Read_Master_Log_Pos: 232284895
Relay_Log_File: relay-bin.022457
Relay_Log_Pos: 125229798
Relay_Master_Log_File: master-bin.007730
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: 1292
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 2 failed executing transaction '6780264b-a74d-11ec-97c2-b8cef6724950:55483680' at master log master-bin.007730, end_log_pos 125232274. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 125229583
Relay_Log_Space: 3135160809013
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: 1292
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 2 failed executing transaction '6780264b-a74d-11ec-97c2-b8cef6724950:55483680' at master log master-bin.007730, end_log_pos 125232274. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1601
Master_UUID: 6780264b-a74d-11ec-97c2-b8cef6724950
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: 220512 18:04:36
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 6780264b-a74d-11ec-97c2-b8cef6724950:3476080-97878682
Executed_Gtid_Set: 6780264b-a74d-11ec-97c2-b8cef6724950:1-55483679:55483681-55483682
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select * from performance_schema.replication_applier_status_by_worker\G;
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: 6780264b-a74d-11ec-97c2-b8cef6724950:55483679
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME:
WORKER_ID: 2
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: 6780264b-a74d-11ec-97c2-b8cef6724950:55483680
LAST_ERROR_NUMBER: 1292
LAST_ERROR_MESSAGE: Worker 2 failed executing transaction '6780264b-a74d-11ec-97c2-b8cef6724950:55483680' at master log master-bin.007730, end_log_pos 125232274; Error 'Truncated incorrect DOUBLE value: 'TB40001343'' on query. Default database: 'ods_garden'. Query: 'insert into is_dw.t05_tmp(
CARD_NO,
hosp_id,
mysqlbinlog --no-defaults --database=ods_garden --base64-output=decode-rows -vv relay-bin.022457 > relay-bin.022457.txt
#220420 5:26:20 server id 1601 end_log_pos 125230297 CRC32 0x92e3209b GTID last_committed=130 sequence_number=132 rbr_only=no
SET @@SESSION.GTID_NEXT= '6780264b-a74d-11ec-97c2-b8cef6724950:55483680'/*!*/;
# at 125230512
#220420 5:25:37 server id 1601 end_log_pos 125230389 CRC32 0x050f6ab8 Query thread_id=964961 exec_time=18 error_code=0
SET TIMESTAMP=1650403537/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
BEGIN
/*!*/;
# at 125230604
#220420 5:25:37 server id 1601 end_log_pos 125230554 CRC32 0x222327e3 Query thread_id=964961 exec_time=18 error_code=0
SET TIMESTAMP=1650403537/*!*/;
delete from is_dw.t05_tmp
where hosp_id = 'whtj'
/*!*/;
# at 125230769
#220420 5:25:55 server id 1601 end_log_pos 125232274 CRC32 0x280cff7d Query thread_id=964961 exec_time=25 error_code=0
SET TIMESTAMP=1650403555/*!*/;
insert into is_dw.t05_tmp(
CARD_NO,
hosp_id,
NAME,
BIRTHDAY,
SEX_CODE,
IDENNO,
BLOOD_CODE,
PROF_CODE,
WORK_HOME,
HOME,
OPER_DATE)
SELECT
CARD_NO,
hosp_id,
NAME,
BIRTHDAY,
SEX_CODE,
#220420 5:26:20 server id 1601 end_log_pos 125232305 CRC32 0x33860f42 Xid = 3998123735
COMMIT/*!*/;
# at 125232520
#220420 5:26:20 server id 1601 end_log_pos 125232370 CRC32 0xce53cbdf GTID last_committed=130 sequence_number=133 rbr_only=no
SET @@SESSION.GTID_NEXT= '6780264b-a74d-11ec-97c2-b8cef6724950:55483681'/*!*/;
# at 125232585
#220420 5:26:16 server id 1601 end_log_pos 125232475 CRC32 0x618c0bdd Query thread_id=964882 exec_time=0 error_code=0
SET TIMESTAMP=1650403576/*!*/;
BEGIN
#mysql> stop slave;
mysql> set GTID_NEXT='6780264b-a74d-11ec-97c2-b8cef6724950:55483681';
mysql> begin;
mysql> commit;
mysql> set session GTID_NEXT='AUTOMATIC';
mysql> start slave sql_thread;
