Hi we are using 8.0.36-28 Percona Server in a simple master-slave setup. Since the beginning on the week our replication broke two time. We are using row based replication.
When the replication break it break on an update for exemple update tablea set col1=’2’ where col2=’4’;
But on the replica the record with col2=’4’ does not exists.
We can see earlier in the binary log on the master another update on the same record that sets col2=’4’
If we check the binary logs on the slave this earlier transaction has not been executed.
How is this possible ?
Our slave is in super_read_only mode and the application cannot acces it directly.
We never had this problem and we got this setup for 5 years now. We had the problem twice since Monday and the application (Redcap) had a update on Monday morning.
We don’t have any filtering on the replication and we can see the missing update in the relay log on the slave.
Another info replica_preserve_commit_order is ON
Thanks if you can help
1 Like
It sounds like you may have data corruption or drift on the replica. The first thing I’d recommend is running pt-table-checksum from Percona Toolkit. This tool will scan your master and replica tables and report any inconsistencies.
Once you identify which tables differ, you can follow up with pt-table-sync to safely correct the mismatched data.
If you have not used these two tools before please take a look at this blog post. Lets be InSync!
1 Like
Hi I have tried on the problematic table and pt-table-checksum is unable to find a good index on the table to decide his chunksize.
But in the first place how is it possible to have a replica that does not execute an update ?
A MySQL replica can miss an UPDATE for several reasons — replication isn’t guaranteed to keep data identical, only to deliver events. Common causes include replication stopping on an error, schema differences, non-deterministic SQL, writes made directly on the replica, or tables without proper primary keys.
Do you have a primary key on the table in question? Can you share the create table statement on the table?
Hi I confirm this table has no primary key or unique index. That’s an open source system we don’t control. I know it is not recommended but I thought the worst thing that could happen is it could slow down replication when this table grows. I did not know that the lack of primary key could cause some statement to not transfer on the replica .
Here is the create table :
CREATE TABLE redcap_data (
project_id int NOT NULL DEFAULT ‘0’,
event_id int DEFAULT NULL,
record varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
field_name varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
value text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
instance smallint DEFAULT NULL,
KEY event_id_instance (event_id,instance),
KEY proj_record_field (project_id,record,field_name),
KEY project_field (project_id,field_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Maybe we could add an invisible auto increment column for a primary key ?
That would be a very good place to start.