Bug #31757 Federated tables break replication
Submitted: 22 Oct 2007 15:42 Modified: 26 Oct 2007 17:01
Reporter: Giuseppe Maxia Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S2 (Serious)
Version:5.0.50, 5.1.23,6.0.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: federated, replication
Triage: Triaged: D2 (Serious)

[22 Oct 2007 15:42] Giuseppe Maxia
Description:
Using federated tables in a replicated system can create duplicates or break replication.

It happens in three ways: (the first two apply to 5.0.x, the last one is 5.1.x specific)

1) Table without unique constraint, created with the old CONNECTION syntax.
   When replicated, the insert is repeated for each slave. The original table
   will get one record from the master plus one record from each slave.
   This problem occurs even when the original table is on a remote server.
   if the federated table is replicated, the remote table receives duplicates.

2) Table with unique constraint, created with the old CONNECTION syntax.
   Replication stops after an insertion. Each slave inserts the same record 
   to the federated table, and a duplicated key error is raised.

3) table created with the new CONNECTION syntax (CREATE SERVER).
   Replication breaks when the table is created.

NOTE: After Bug#18287 and Bug#25679 were fixed, it is now possible to create Federated tables on the same server of the linked table.

How to repeat:
#1 
# on a system with 1 master + 2 slaves 

drop table if exists t1;
create table t1 (id int);
create table t1f (id int) engine=federated 
  connection=mysql://msandbox:msandbox@127.0.0.1:16123/test/t1';

insert into t1f values (1);
select * from t1;
+----+
| id |
+----+
| 1  | 
| 1  | 
| 1  | 
+----+

#2
# on a system with 1 master + 2 slaves 

drop table if exists t1;
create table t1 (id int not null primary key);
create table t1f (id int not null primary key) engine=federated 
  connection=mysql://msandbox:msandbox@127.0.0.1:16123/test/t1';

insert into t1f values (1);

# check the status on the slaves
Error 'Can't write; duplicate key in table 't1f'' on query. 
Default database: 'test'. Query: 'insert into t1f values (1)'

#3 (requires 5.1.22 or newer)
# on a system with 1 master + 2 slaves 

drop table if exists t1, t1f;
create server s1 FOREIGN DATA WRAPPER mysql 
  options ( host '127.0.0.1',
            database 'test', 
            port 16123, 
            user 'msandbox', 
            password 'msandbox');
create table t1 (id int);
create table t1f (id int) engine = federated connection= 's1/t1';

# check the status on the slaves:

Error 'Can't create/write to file 'server name: 's1' doesn't exist!' 
  (Errcode: -1263794884)' on query. 
  Default database: 'test'. 
  Query: 'create table t1f (id int) engine = federated connection= 's1/t1''

Suggested fix:
All cases can be avoided by disabling the binary log before creating a federated table.
At the very minimum, a fix for this problem should include a warning about this risk in the manual.

The third case is more serious and requires more work.u