MySQL Bugs: #96026: The HA instance causes FEDERATED engine operations to be performed multiple time

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:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
The HA instance causes FEDERATED engine operations to be performed multiple times.

For example: one master and one slave. If you insert one row of data on the master, you will query for two.

The reason is that this statement inserts a row of data in the master, and the row data is also inserted in the slave, causing the remote table to actually insert two data.

How to repeat:
The HA instance causes FEDERATED engine operations to be performed multiple times.

For example: one master and one slave. If you insert one row of data on the master, you will query for two.

The reason is that this statement inserts a row of data in the master, and the row data is also inserted in the slave, causing the remote table to actually insert two data.

Suggested fix:
For the FEDERATED engine table, DML statements should be avoided to record binlog.

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

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

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

Another solution:

For federated engine tables, add a read-only property. Users can choose to define such tables as read-only to avoid problems.

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