MySQL主从异常处理Errno: 1292-REPLICATE_WILD_IGNORE_TABLE的使用
- 一、Slave错误信息
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.172.64.161
Master_User: dbsync
Master_Port: 27415
Connect_Retry: 60
Master_Log_File: mysql_bin.014571
Read_Master_Log_Pos: 22657121
Relay_Log_File: relay-bin.043708
Relay_Log_Pos: 137275030
Relay_Master_Log_File: mysql_bin.014569
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: Error 'Truncated incorrect INTEGER value: '2023-01-01'' on query. Default database: 'MDM'. Query: 'insert into CALENDAR(
PERIOD_ID,
PERIOD_TYPE_ID,
PERIOD_NAME,
CALENDAR_DATE ,
CALENDAR_YEAR,
HALF_YEAR,
CALENDAR_QUARTER,
CALENDAR_MONTH,
YEAR_WEEK,
YEAR_DAY,
MONTH_DAY,
WEEK,
PUBLIC_HOLIDAY,
WORKING_DAY,
WEEKEND,
MONTH_END,
QUARTER_END,
YEAR_END,
YOY,
MOM,
HALF_YEAR_END,
CREATE_DATE,
UPDATE_DATE
)(
select
convert(DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'),'%Y%m%d'),UNSIGNED) as PERIOD_ID,
1 as PERIOD_TYPE_ID,
CONCAT(DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'),'%Y'),'年',DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedis
Skip_Counter: 0
Exec_Master_Log_Pos: 137274817
Relay_Log_Space: 2171129201
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: Error 'Truncated incorrect INTEGER value: '2023-01-01'' on query. Default database: 'MDM'. Query: 'insert into CALENDAR(
PERIOD_ID,
PERIOD_TYPE_ID,
PERIOD_NAME,
CALENDAR_DATE ,
CALENDAR_YEAR,
HALF_YEAR,
CALENDAR_QUARTER,
CALENDAR_MONTH,
YEAR_WEEK,
YEAR_DAY,
MONTH_DAY,
WEEK,
PUBLIC_HOLIDAY,
WORKING_DAY,
WEEKEND,
MONTH_END,
QUARTER_END,
YEAR_END,
YOY,
MOM,
HALF_YEAR_END,
CREATE_DATE,
UPDATE_DATE
)(
select
convert(DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'),'%Y%m%d'),UNSIGNED) as PERIOD_ID,
1 as PERIOD_TYPE_ID,
CONCAT(DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'),'%Y'),'年',DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedis
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: ef92423d-488b-11ec-a1ae-525493542058
Master_Info_File: /u01/mysql/data/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: 220727 16:34:55
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
- 二、查询表结构
mysql> show create table CALENDAR\G; *************************** 1. row *************************** Table: CALENDAR Create Table: CREATE TABLE `CALENDAR` ( `PERIOD_ID` int(11) NOT NULL, `PERIOD_TYPE_ID` int(11) DEFAULT NULL, `PERIOD_NAME` varchar(64) NOT NULL, `CALENDAR_DATE` date NOT NULL, `CALENDAR_YEAR` int(11) DEFAULT NULL, `HALF_YEAR` int(11) DEFAULT NULL, `CALENDAR_QUARTER` int(11) DEFAULT NULL, `CALENDAR_MONTH` int(11) DEFAULT NULL, `YEAR_WEEK` int(11) DEFAULT NULL, `QUARTER_WEEK` int(11) DEFAULT NULL, `MONTH_WEEK` int(11) DEFAULT NULL, `YEAR_DAY` int(11) DEFAULT NULL, `QUARTER_DAY` int(11) DEFAULT NULL, `MONTH_DAY` int(11) DEFAULT NULL, `WEEK` int(11) DEFAULT NULL, `QUARTER` int(11) DEFAULT NULL, `SUMMARY_DAYS` int(11) DEFAULT NULL, `SUMMARY_WORK_DAYS` int(11) DEFAULT NULL, `PUBLIC_HOLIDAY` varchar(1) DEFAULT NULL, `NATIONAL_HOLIDAY` varchar(1) DEFAULT NULL , `COMPANY_HOLIDAY` varchar(1) DEFAULT NULL, `WORKING_DAY` varchar(8) DEFAULT NULL, `WEEKEND` varchar(8) DEFAULT NULL, `MONTH_END` varchar(8) DEFAULT NULL, `QUARTER_END` varchar(8) DEFAULT NULL, `YEAR_END` varchar(8) DEFAULT NULL, `HALF_YEAR_END` varchar(8) DEFAULT NULL, `DESCRIPTION` varchar(512) DEFAULT NULL, `PERIOD_START_DATE` date DEFAULT NULL, `PERIOD_END_DATE` date DEFAULT NULL, `YOY` int(11) DEFAULT NULL, `MOM` int(11) DEFAULT NULL, `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `UPDATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `LOAD_TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `CALENDAR_DATE_21` date DEFAULT NULL, PRIMARY KEY (`PERIOD_ID`) USING BTREE, KEY `IDX_CALENDAR_DATE` (`CALENDAR_DATE`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC 1 row in set (0.00 sec) ERROR: No query specified mysql>
- 三、读取relay log日志，确定执行错误的SQL
/home/mysql/base/bin/mysqlbinlog --no-defaults --database=MDM --base64-output=decode-rows -vv --start-position=137275030 relay-bin.043708 > /data/relay-bin.043708.txt
# at 137275190
#220727 16:34:55 server id 1 end_log_pos 137279129 CRC32 0xcf5a44c9 Query thread_id=5622166 exec_time=0 error_code=0
use `MDM`/*!*/;
SET TIMESTAMP=1658910895/*!*/;
insert into CALENDAR(^M
PERIOD_ID,^M
PERIOD_TYPE_ID,^M
PERIOD_NAME,^M
CALENDAR_DATE ,^M
CALENDAR_YEAR,^M
HALF_YEAR,^M
CALENDAR_QUARTER,^M
WORKING_DAY,^M
WEEKEND,^M
MONTH_END,^M
YOY,^M
MOM,^M
HALF_YEAR_END,^M
CREATE_DATE,^M
)(^M
select ^M
convert(DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'),'%Y%m%d'),UNSIGNED) as PERIOD_ID,^M
1 as PERIOD_TYPE_ID,^M NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci') as CALENDAR_DATE,^M
YEAR( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci')) as CALENDAR_YEAR,^M
(case when DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'),'%m')<='06' then 1 else 2 end) as HALF_YEAR,^M
QUARTER( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci')) as CALENDAR_QUARTER,^M
MONTH( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci')) as CALENDAR_MONTH,^M
convert(DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci') ,'%u'),UNSIGNED) as YEAR_WEEK,^M
convert(DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci') ,'%j'),UNSIGNED) as YEAR_DAY,^M
DAY( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci')) as MONTH_DAY,^M
WEEK( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci') ) as WEEK,^M
NAME_CONST('is_holiday',_utf8'Y' COLLATE 'utf8_general_ci') as PUBLIC_HOLIDAY,^M
^M
(case NAME_CONST('is_holiday',_utf8'Y' COLLATE 'utf8_general_ci') when 'Y' then 'N' when 'N' then 'Y' end) as WORKING_DAY,^M
NAME_CONST('is_weekend',_utf8'N' COLLATE 'utf8_general_ci') as WEEKEND,^M
^M
(case when MONTH( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'))<MONTH(date_sub( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'),interval -1 day)) then 'Y' else 'N' end) as MONTH_END,^M (case when YEAR( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'))*100+QUARTER( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci')) <YEAR(date_sub( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'),interval -1 day))*100+QUARTER( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci')) then 'Y' else 'N' end) as QUARTER_END,^M (case when YEAR( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'))<YEAR(date_sub( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'),interval -1 day)) then 'Y' else 'N' end) as YEAR_END,^M ^M
CONVERT(date_sub( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'),interval 12 MONTH),UNSIGNED),^M
CONVERT(date_sub( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci'),interval 1 DAY),UNSIGNED),^M
(case when DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci') ,'%m%d') ='0630' or DATE_FORMAT( NAME_CONST('date_now',_latin1'2024-01-01' COLLATE 'latin1_swedish_ci') ,'%m%d') ='1231' then 'Y' else 'N' end) as HALF_YEAR_END,^M NOW() as CREATE_DATE,^M
NOW() as UPDATE_DATE^M
^M
)
/*!*/;
执行记录共计2559条
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
insert into CALENDAR(
[mysql@p0-tkhibdp-bdp-jtdsj01 data]$ cat relay-bin.043708.txt |grep "insert into CALENDAR"|wc -l
2559
- 四、该表无新增数据，处理过程，采用REPLICATE_WILD_IGNORE_TABLE恢复同步，择机mysqldump在从库导入该表数据
mysql> show variables like '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE =('MDM.CALENDAR');
Query OK, 0 rows affected (0.00 sec)
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.172.64.161 Master_User: dbsync Master_Port: 27415 Connect_Retry: 60 Master_Log_File: mysql_bin.014572 Read_Master_Log_Pos: 191330675 Relay_Log_File: relay-bin.043708 Relay_Log_Pos: 176749349 Relay_Master_Log_File: mysql_bin.014569 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: MDM.CALENDAR Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 176749136 Relay_Log_Space: 3414083380 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: 5121 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: ef92423d-488b-11ec-a1ae-525493542058 Master_Info_File: /u01/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: update Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
