TokuDB Read Free Replication will not propagate reliably if table PK is missing
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
| 5.6 |
Fix Released
|
High
|
Vlad Lesin | ||
| 5.7 |
Fix Released
|
High
|
Vlad Lesin |
Bug Description
If TokuDB table is lacking PK, update, delete events will not propagate reliably and eventually will create a data inconsistency.
I use 5.6.27-76.0-log Percona Server (GPL), Release 76.0 1(Master) -> 2(Slaves)
Slaves: read-only, RBR, tokudb_
I drop id column with PK in world.City table.
on Master and Slaves:
☭ [world]> select * from City where Name = 'Amsterdam';
+------
| Name | CountryCode | District | Population | RollupDate |
+------
| Amsterdam | NLD | Noord-Holland | 731200 | 2015-02-10 |
Master:
update City set Population = Population + 10 where Name = 'Amsterdam';
After the update:
Master:
☭ [world]> select * from City where Name = 'Amsterdam';
+------
| Name | CountryCode | District | Population | RollupDate |
+------
| Amsterdam | NLD | Noord-Holland | 731210 | 2015-02-10 |
+------
Slave1:
☭ [world]> select * from City where Name = 'Amsterdam';
+------
| Name | CountryCode | District | Population | RollupDate |
+------
| Amsterdam | NLD | Noord-Holland | 731210 | 2015-02-10 |
+------
Slave2 extra row:
☭ [world]> select * from City where Name = 'Amsterdam';
+------
| Name | CountryCode | District | Population | RollupDate |
+------
| Amsterdam | NLD | Noord-Holland | 731200 | 2015-02-10 |
| Amsterdam | NLD | Noord-Holland | 731210 | 2015-02-10 |
+------
Now after I run delete on Master:
☭ [world]> delete from City where Name = 'Amsterdam';
Query OK, 1 row affected (0.00 sec)
on Master:
☭ [world]> select * from City where Name = 'Amsterdam';
Empty set (0.00 sec)
Slave1:
☭ [world]> select * from City where Name = 'Amsterdam';
Empty set (0.00 sec)
Slave2:
☭ [world]> select * from City where Name = 'Amsterdam';
+------
| Name | CountryCode | District | Population | RollupDate |
+------
| Amsterdam | NLD | Noord-Holland | 731200 | 2015-02-10 |
+------
As we see neither updates no deletes are reliable with RFR on the table lacking PK creating a data inconsistency.
This needs to be emphasised in the Documentation or fixed.
tags: | added: tokudb |
Przemek (pmalkowski) wrote : | #1 |
Perhaps this could be fixed on a slave by stopping RFR (and breaking replication) if a row event for a PK-less table is seen in the RFR configuration.
tags: | added: i65945 |
George Ormond Lorch III (gl-az) wrote : | #3 |
Another approach would be to limit the RFR functionality specifically to tables that have a PK. The decision to lookup/not lookup is done in sql/log_
+1
George Ormond Lorch III (gl-az) wrote : | #5 |
For reference, this commit to the old Tokutek MariaDB server https:/
George Ormond Lorch III (gl-az) wrote : | #7 |
When read-free-
explicit pk for replicated table there can be dublicated records in the table
on update operation.
Consider this update operation:
UPDATE t SET a = a + 10 WHERE b = 'b';
The master does rows lookup and updates the rows which values
correspond to the condition. The update events are written to binary log with
rows values from the master. As rows lookup is forbidden for slave
the new rows are added instead of updating corresponding rows.
Without the fix there will be several rows with b = 'b' in the table
on slave instead of one updated row.
The fix disables RFR for tables without explicit pk and do rows lookup
for update and delete binlog events and issues warning.
Shahriyar Rzayev (rzayev-sehriyar) wrote : | #9 |
Percona now uses JIRA for bug reports so this bug report is migrated to: https:/
Verified. Looks like the tokudb_ rpl_lookup_ rows disabled alone leads to inconsistency in a table without PK on a slave.
master [localhost] {msandbox} (world) > show create table City\G ******* ******* ****** 1. row ******* ******* ******* ******
*******
Table: City
Create Table: CREATE TABLE `City` (
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
KEY `CountryCode` (`CountryCode`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
master [localhost] {msandbox} (world) > update City set Population = Population + 10 where Name = 'Poznan';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
slave1 [localhost] {msandbox} (world) > show global variables like 'tokudb_rpl_%'; ------- ------- ------- -----+- ------+ ------- ------- ------- -----+- ------+ rpl_check_ readonly | ON | rpl_lookup_ rows | ON | rpl_lookup_ rows_delay | 0 | rpl_unique_ checks | ON | rpl_unique_ checks_ delay | 0 | ------- ------- ------- -----+- ------+
+------
| Variable_name | Value |
+------
| tokudb_
| tokudb_
| tokudb_
| tokudb_
| tokudb_
+------
5 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (world) > select * from City where Name = 'Poznan'; --+---- ------- --+---- ------- ----+-- ------- ---+ --+---- ------- --+---- ------- ----+-- ------- ---+ --+---- ------- --+---- ------- ----+-- ------- ---+
+------
| Name | CountryCode | District | Population |
+------
| Poznan | POL | Wielkopolskie | 576909 |
+------
1 row in set (0.00 sec)
slave2 [localhost] {msandbox} (world) > show global variables like 'tokudb_rpl_%'; ------- ------- ------- -----+- ------+ ------- ------- ------- -----+- ------+ rpl_check_ readonly | ON | rpl_lookup_ rows | OFF | rpl_lookup_ rows_delay | 0 | rpl_unique_ checks | ON | rpl_unique_ checks_ delay | 0 | ------- ------- ------- -----+- ------+
+------
| Variable_name | Value |
+------
| tokudb_
| tokudb_
| tokudb_
| tokudb_
| tokudb_
+------
5 rows in set (0.00 sec)
slave2 [localhost] {msandbox} (world) > select * from City where Name = 'Poznan'; --+---- ------- --+---- ------- ----+-- ------- ---+ --+---- ------- --+---- ------- ----+-- ------- ---+ --+---- ------- --+---- ------- ----+-- ------- ---+
+------
| Name | CountryCode | District | Population |
+------
| Poznan | POL | Wielkopolskie | 576899 |
| Poznan | POL | Wielkopolskie | 576909 |
+------
2 rows in set (0.00 sec)
slave2 [localhost] {msandbox} (world) > select @@version, @@version_ comment; ------- +------ ------- ------- ------- ------- ------- ------- ------+ ------- +------ ------- ------- ------- ------- ------- ------- ------+ ------- +------ ------- ------- ------- ------- ------- ------- ------+
+------
| @@version | @@version_comment |
+------
| 5.6.28-76.1 | Percona Server (GPL), Release 76.1, Revision 5759e76 |
+------
1 row in set (0.00 sec)