gtid 1060 同步异常

gtid 1060 同步异常

1、报警信息如下

[Gome Cloud]
[falcon] PROBLEM P1
Endpoint:192.168.1.11
Hostgroup:DBA-MYSQL
Metric:Slave_SQL_Running/isSlave=1,port=3306,readOnly=0,type=mysql
Strategy:all(#3) 0==0
Note:MySQL复制SQL线程存活告警
Application:
Time:2022-02-23 15:13:00

2、具体从库同步异常信息

root@localhost: 15:20 [3306][(none)]>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.10
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000482
          Read_Master_Log_Pos: 105203633
               Relay_Log_File: mysql-relay.001815
                Relay_Log_Pos: 84720712
        Relay_Master_Log_File: mysql-bin.000482
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: test
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1060
                   Last_Error: Error 'Duplicate column name 'userCode'' on query. Default database: 'testdb'. Query: 'ALTER TABLE `test_tbl` ADD COLUMN `userCode` varchar(258) NULL DEFAULT '' COMMENT '用户编号' AFTER `updateTime`'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 84720542
              Relay_Log_Space: 105204170
              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: 1060
               Last_SQL_Error: Error 'Duplicate column name 'userCode'' on query. Default database: 'testdb'. Query: 'ALTER TABLE `test_tbl` ADD COLUMN `userCode` varchar(258) NULL DEFAULT '' COMMENT '用户编号' AFTER `updateTime`'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10963306
                  Master_UUID: 896178f5-cb1b-11ea-949e-94292f719203
             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: 220223 15:10:17
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 276909df-1105-11e6-81d8-6c92bf177322:495803940-566695524,
4f9ba386-1103-11e6-81cc-6c92bf176998:2079168114-2079168115,
896178f5-cb1b-11ea-949e-94292f719203:1-102022
            Executed_Gtid_Set: 276909df-1105-11e6-81d8-6c92bf177322:495803940-566675454,
4f9ba386-1103-11e6-81cc-6c92bf176998:2079168114,
896178f5-cb1b-11ea-949e-94292f719203:1-102022,
89757714-cb1b-11ea-949e-94292f719173:1-518
                Auto_Position: 1
1 row in set (0.00 sec)

ERROR: 
No query specified

root@localhost: 15:20 [3306][(none)]>

3、查看表结构

root@localhost: 15:22 [7313][gomepush]>show create table test_tbl\G;
*************************** 1. row ***************************
       Table: push_template
Create Table: CREATE TABLE `push_template` (
  `infoId` int(32) NOT NULL AUTO_INCREMENT,
  `name` varchar(258) DEFAULT NULL,
  `templateDes` varchar(512) DEFAULT NULL,
  `chanel` varchar(32) DEFAULT NULL,
  `id` varchar(32) DEFAULT NULL,
  `title` varchar(258) CHARACTER SET utf8mb4 DEFAULT NULL,
  `content` varchar(1024) CHARACTER SET utf8mb4 DEFAULT NULL,
  `scheme` varchar(258) DEFAULT NULL,
  `showType` varchar(258) DEFAULT NULL,
  `pushImg` varchar(258) DEFAULT NULL,
  `centreImg` varchar(258) DEFAULT NULL,
  `expire` int(32) DEFAULT NULL,
  `userId` varchar(258) DEFAULT NULL,
  `state` varchar(32) DEFAULT NULL,
  `centreType` varchar(258) DEFAULT NULL,
  `createTime` datetime DEFAULT NULL,
  `updateTime` datetime DEFAULT NULL,
  `userCode` varchar(258) DEFAULT '' COMMENT '用户编号',
  PRIMARY KEY (`infoId`),
  UNIQUE KEY `id_unique` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

ERROR: 
No query specified

已经存在该字段。

4、从库信息如下

Retrieved_Gtid_Set: 276909df-1105-11e6-81d8-6c92bf177322:495803940-566707006,
4f9ba386-1103-11e6-81cc-6c92bf176998:2079168114-2079168115,
896178f5-cb1b-11ea-949e-94292f719203:1-102022
            Executed_Gtid_Set: 276909df-1105-11e6-81d8-6c92bf177322:495803940-566675454,
4f9ba386-1103-11e6-81cc-6c92bf176998:2079168114,
896178f5-cb1b-11ea-949e-94292f719203:1-102022,
89757714-cb1b-11ea-949e-94292f719173:1-518
                Auto_Position: 1

从这里看，由于环境不干净，导致信息难以确认。所以极力推荐，从主库解析binlog日志来确定gtid的信息。

5、在主库中解析从库报错信息的日志

# 在主库中解析binlog日志
/app/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v --start-position=84720542  /data/my3306/data/mysql-bin.000482 | more
DELIMITER /*!*/;
# at 84720542
#220223 15:10:16 server id 461597306  end_log_pos 84720590 CRC32 0x6166919b     GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '4f9ba386-1103-11e6-81cc-6c92bf176998:2079168115'/*!*/;
# at 84720590
#220223 15:10:16 server id 461597306  end_log_pos 84720792 CRC32 0xcc7102b3     Query   thread_id=992913     
   exec_time=1     error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1645600216/*!*/;
SET @@session.pseudo_thread_id=992913/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autoco
mmit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
ALTER TABLE `test_tbl` ADD COLUMN `userCode` varchar(258) NULL DEFAULT '' COMMENT '用户编号' AFTER `upda
teTime`
/*!*/;

重点关注： SET @@SESSION.GTID_NEXT= '4f9ba386-1103-11e6-81cc-6c92bf176998:2079168115'/*!*/;

6、解决办法

STOP SLAVE;
SET @@SESSION.GTID_NEXT= '4f9ba386-1103-11e6-81cc-6c92bf176998:2079168115';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;
START SLAVE;