Bug #96026 | The HA instance causes FEDERATED engine operations to be performed multiple time | ||
---|---|---|---|
Submitted: | 28 Jun 2019 12:40 | Modified: | 1 Jul 2019 13:21 |
Reporter: | kfpanda kf | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
Version: | OS: | Any | |
Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[28 Jun 2019 12:40]
kfpanda kf
[28 Jun 2019 22:47]
MySQL Verification Team
Hi, Thanks for the report but I'm having some issues following what is going on. Can you please include more detailed info - what version of MySQL server you have on master and slave - how is the federated table defined on master and on slave - example DML that is executed twice From what I read the report it looks like you have a federated table defined on both master and slave to hit the same table on remote server. It is expected behavior to have the DML then executed twice?! There's now way we know on slave that it is "the same table". Normally your slave would either target a different server then master for federated or you would filter that table from replication. all best Bogdan
[29 Jun 2019 7:06]
kfpanda kf
-- MySQL version : 5.7.25 -- Recurring problems 1)Create table db1.t on instance A mysql> create table db1.t(id int); Query OK, 0 rows affected (0.01 sec) 2) Create federated engine test table t on HA instance B with its remote connection instance A table db.t create table t(id int)engine=FEDERATED CONNECTION='mysql://user:password@ip:port/db1/t'; 3) Query table t on instance B and return null mysql> select * from t; Empty set (0.00 sec) 4) Insert a row of data on instance B mysql> insert into t values(1); Query OK, 1 row affected (0.01 sec) 5) Querying table t on instance B found an extra row of data. mysql> select * from t; +------+ | id | +------+ | 1 | | 1 | +------+ 2 rows in set (0.00 sec) -- Repair advice Not logging binlog is probably a good solution to the federated engine.
[29 Jun 2019 7:06]
kfpanda kf
-- MySQL version : 5.7.25 -- Recurring problems 1)Create table db1.t on instance A mysql> create table db1.t(id int); Query OK, 0 rows affected (0.01 sec) 2) Create federated engine test table t on HA instance B with its remote connection instance A table db.t create table t(id int)engine=FEDERATED CONNECTION='mysql://user:password@ip:port/db1/t'; 3) Query table t on instance B and return null mysql> select * from t; Empty set (0.00 sec) 4) Insert a row of data on instance B mysql> insert into t values(1); Query OK, 1 row affected (0.01 sec) 5) Querying table t on instance B found an extra row of data. mysql> select * from t; +------+ | id | +------+ | 1 | | 1 | +------+ 2 rows in set (0.00 sec) -- Repair advice Not logging binlog is probably a good solution to the federated engine.
[29 Jun 2019 7:09]
kfpanda kf
Another solution: For federated engine tables, add a read-only property. Users can choose to define such tables as read-only to avoid problems.
[1 Jul 2019 13:21]
MySQL Verification Team
Hi, This is not a bug. As for the "no logging", you can setup replication filters for any table irrelevant to the storage engine, so I don't see a particular use to treat federated engine differently Thanks Bogdan