MySQL Bugs: #43924: Federated respect of InnoDB integrity constraint

Bug #43924	Federated respect of InnoDB integrity constraint
Submitted:	27 Mar 2009 21:05	Modified:	1 Apr 2009 12:45
Reporter:	Paul Lemay	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S3 (Non-critical)
Version:	5.0.68	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Federated constraint

Description:
Hello there,

I would like to know if the federated engine report Innodb constraint properly?

How to repeat:
I have the following setup on node 1:

use bluedbg;
drop table test;
create table test (
  stest varchar(15) not null,
  primary key (sTest)
) type = InnoDB ;

******* node 2 setup
use bluedbg;
drop table test;
create table test (
  sTest varchar(15) not null,
  primary key (sTest)
) type =FEDERATED DEFAULT CHARSET=latin1 
    CONNECTION='mysql://Federated:Federated@169.254.1.20:3306/bluedbg/Test';

and the following table:
use bluedb;
create table test2 (
    sTest varchar(15) not null, 
    sTest2 varchar(15) not null, 
    primary key (sTest, sTest2), 
    constraint test2_C1 
    foreign key (sTest) references bluedbg.test (sTest) 
    on delete restrict 
    on update cascade) 
type = InnoDB ;

on node 1, execute the following command:

insert into bluedbg.test set stest = "11111"; # works ok

on node 2, execute the following command:

insert into bluedb.test2 set stest = "11111", stest2="12345";

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bluedb/test2`, CONSTRAINT `test2_C1` FOREIGN KEY (`sTest`) REFERENCES `bluedbg`.`test` (`sTest`) ON UPDATE CASCADE)

Is this suppose to be supported?

Thank you for the report.

Yes, it is not supported. But is interesting how you managed to create table test2? Could you please provide steps how you did it?

Hello Sveta,

Ok, you are right there are some steps missing. To create Test2, as you might imagine, I had to execute the following command:

SET FOREIGN_KEY_CHECKS=0;

I then stopped the database itself and restarted it before doing the test below. How sould I proceed to make it work?

Thank you for the feedback.

Unfortunately there is no way to have it working. Please wait when http://forge.mysql.com/worklog/task.php?id=148 is done.

All right Thanks!