Return to Question

added 3921 characters in body

Source Link

edited Feb 27, 2023 at 20:46

Here is my create table from the replica (created from master dump, so DDL is the same):

CREATE TABLE `bits` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Datetime` datetime NOT NULL,
  `B01` tinyint(1) NOT NULL,
  `B02` tinyint(1) NOT NULL,
  `B03` tinyint(1) NOT NULL,
  `B04` tinyint(1) NOT NULL,
  `B05` tinyint(1) NOT NULL,
  `B06` tinyint(1) NOT NULL,
  `B07` tinyint(1) NOT NULL,
  `B08` tinyint(1) NOT NULL,
  `B09` tinyint(1) NOT NULL,
  `B10` tinyint(1) NOT NULL,
  `B11` tinyint(1) NOT NULL,
  `B12` tinyint(1) NOT NULL,
  `B13` tinyint(1) NOT NULL,
  `B14` tinyint(1) NOT NULL,
  `B15` tinyint(1) NOT NULL,
  `B16` tinyint(1) NOT NULL,
  `B17` tinyint(1) NOT NULL,
  `B18` tinyint(1) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`),
  KEY `idx_dt` (`Datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=92553 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `words` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Datetime` datetime NOT NULL,
  `W01` int NOT NULL,
  `W02` int NOT NULL,
  `W03` int NOT NULL,
  `W04` int NOT NULL,
  `W05` int NOT NULL,
  `W06` int NOT NULL,
  `W07` int NOT NULL,
  `W08` int NOT NULL,
  `W09` int NOT NULL,
  `W10` int NOT NULL,
  `W11` int NOT NULL,
  `W12` int NOT NULL,
  `W13` int NOT NULL,
  `W14` int NOT NULL,
  `W15` int NOT NULL,
  `W16` int NOT NULL,
  `W17` int NOT NULL,
  `W18` int NOT NULL,
  `W19` int NOT NULL,
  `W20` int NOT NULL,
  `W21` int NOT NULL,
  `W22` int NOT NULL,
  `W23` int NOT NULL,
  `W24` int NOT NULL,
  `W25` int NOT NULL,
  `W26` int NOT NULL,
  `W27` int NOT NULL,
  `W28` int NOT NULL,
  `W29` int NOT NULL,
  `W30` int NOT NULL,
  `W31` int NOT NULL,
  `W32` int NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`),
  KEY `idx_dt` (`Datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=92553 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

There are single primary keys in the tables (bigint Id).

There are no INSERT operation sources on the replica, except replication from master.

Here is the log tail from my replica:

2023-02-14T09:48:00.884440Z 161 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error reconnecting to master '[email protected]:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '10.15.0.12:3306' (110), Error_code: MY-002003
2023-02-21T13:13:38.933590Z 161 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error reconnecting to master '[email protected]:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '10.15.0.12:3306' (110), Error_code: MY-002003
2023-02-27T01:05:20.054098Z 7 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log NBMS-DELL-bin.000009, end_log_pos 8885352; Could not execute Write_rows event on table nbmslogger.bits; Duplicate entry '91393' for key 'bits.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 8885352, Error_code: MY-001062
2023-02-27T11:58:07.334955Z 37 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log NBMS-DELL-bin.000009, end_log_pos 8885352; Could not execute Write_rows event on table nbmslogger.bits; Duplicate entry '91393' for key 'bits.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 8885352, Error_code: MY-001062

When I open that table in DBeaver I see that there are more records replicated from the master, but replication will probably try to insert them again for some reason.

Here is my create table from the replica (created from master dump, so DDL is the same):

CREATE TABLE `bits` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Datetime` datetime NOT NULL,
  `B01` tinyint(1) NOT NULL,
  `B02` tinyint(1) NOT NULL,
  `B03` tinyint(1) NOT NULL,
  `B04` tinyint(1) NOT NULL,
  `B05` tinyint(1) NOT NULL,
  `B06` tinyint(1) NOT NULL,
  `B07` tinyint(1) NOT NULL,
  `B08` tinyint(1) NOT NULL,
  `B09` tinyint(1) NOT NULL,
  `B10` tinyint(1) NOT NULL,
  `B11` tinyint(1) NOT NULL,
  `B12` tinyint(1) NOT NULL,
  `B13` tinyint(1) NOT NULL,
  `B14` tinyint(1) NOT NULL,
  `B15` tinyint(1) NOT NULL,
  `B16` tinyint(1) NOT NULL,
  `B17` tinyint(1) NOT NULL,
  `B18` tinyint(1) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`),
  KEY `idx_dt` (`Datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=92553 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `words` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Datetime` datetime NOT NULL,
  `W01` int NOT NULL,
  `W02` int NOT NULL,
  `W03` int NOT NULL,
  `W04` int NOT NULL,
  `W05` int NOT NULL,
  `W06` int NOT NULL,
  `W07` int NOT NULL,
  `W08` int NOT NULL,
  `W09` int NOT NULL,
  `W10` int NOT NULL,
  `W11` int NOT NULL,
  `W12` int NOT NULL,
  `W13` int NOT NULL,
  `W14` int NOT NULL,
  `W15` int NOT NULL,
  `W16` int NOT NULL,
  `W17` int NOT NULL,
  `W18` int NOT NULL,
  `W19` int NOT NULL,
  `W20` int NOT NULL,
  `W21` int NOT NULL,
  `W22` int NOT NULL,
  `W23` int NOT NULL,
  `W24` int NOT NULL,
  `W25` int NOT NULL,
  `W26` int NOT NULL,
  `W27` int NOT NULL,
  `W28` int NOT NULL,
  `W29` int NOT NULL,
  `W30` int NOT NULL,
  `W31` int NOT NULL,
  `W32` int NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`),
  KEY `idx_dt` (`Datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=92553 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

There are single primary keys in the tables (bigint Id).

There are no INSERT operation sources on the replica, except replication from master.

Here is the log tail from my replica:

2023-02-14T09:48:00.884440Z 161 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error reconnecting to master '[email protected]:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '10.15.0.12:3306' (110), Error_code: MY-002003
2023-02-21T13:13:38.933590Z 161 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error reconnecting to master '[email protected]:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '10.15.0.12:3306' (110), Error_code: MY-002003
2023-02-27T01:05:20.054098Z 7 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log NBMS-DELL-bin.000009, end_log_pos 8885352; Could not execute Write_rows event on table nbmslogger.bits; Duplicate entry '91393' for key 'bits.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 8885352, Error_code: MY-001062
2023-02-27T11:58:07.334955Z 37 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log NBMS-DELL-bin.000009, end_log_pos 8885352; Could not execute Write_rows event on table nbmslogger.bits; Duplicate entry '91393' for key 'bits.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 8885352, Error_code: MY-001062

When I open that table in DBeaver I see that there are more records replicated from the master, but replication will probably try to insert them again for some reason.

Source Link

asked Feb 27, 2023 at 12:21

Kamil

How to fix MySQL replication when duplicated key problem appears?

I have data logger software that writes data to master database. There are just 2 tables. If this database is locked or stopped for more than 1 minute I will loose some data that should be stored.

Master database is replicated to another machine via cellular connection which is not stable sometimes. Replica is used to see the data on Grafana. It can be stopped for a long time and nothing bad will happen.

I have experienced duplicated key problem second time and I'm looking for productive way of fixing it. Last time it took me some time to find a way to fix it and the master database was locked for a few minutes because I couldn't do it properly and made several attempts.

I think I can just lock master database for a moment, execute mysql dump, check current log position, unlock it and start rebuilding my slave database.

I don't know exactly how I should rebuild the slave from master dump file. Should I truncate or remove tables from the slave? What about users and permissions?

Could someone please explain step by step how should I do it?