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
© 2024, Oracle Corporation and/or its affiliates