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.
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)