We found a problem in auto_increment counter replication. Below is the details
Server 1:
mysql> show create table tbl_user_image\G
*************************** 1. row ***************************
Table: tbl_user_image
Create Table: CREATE TABLE tbl_user_image
(
id
bigint(20) unsigned NOT NULL AUTO_INCREMENT,
stub
char(1) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id
),
UNIQUE KEY stub
(stub
)
) ENGINE=InnoDB AUTO_INCREMENT=383345 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
Server 2:
mysql> show create table tbl_user_image\G
*************************** 1. row ***************************
Table: tbl_user_image
Create Table: CREATE TABLE tbl_user_image
(
id
bigint(20) unsigned NOT NULL AUTO_INCREMENT,
stub
char(1) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id
),
UNIQUE KEY stub
(stub
)
) ENGINE=InnoDB AUTO_INCREMENT=383188 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
Note the AUTO_INCREMENT in the create table statement. When a new record is inserted into Server 1, the AUTO_INCREMENT counter in Server 1 increases by 2 (it is a two-node set-up), but not replicated to Server 2. Is there any way to fix this problem?