Foreign Key problem with PXDBC 5.5.29-23.7.1-387
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL patches by Codership |
Fix Released
|
Critical
|
Seppo Jaakola | ||
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC |
Fix Released
|
Critical
|
Unassigned |
Bug Description
Hi,
We discovered a problem with the foreign key constraint check behavior within Percona XtraDB Cluster latest Debian release 5.5.29-23.7.1-387, FK checks are not properly done when RESTRICT action is used (which is default action used when ON DELETE or ON UPDATE subclauses of the FOREIGN KEY clause are not specified) and DELETE is done on a referenced col. This problem makes FK unusable for this usage as you can see in the exemple below :
mysql> create table alpha(col1 int primary key) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> create table beta(col1 int primary key) engine =innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table beta add foreign key(col1) references alpha(col1);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into alpha values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into alpha values (2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into beta values(3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`beta`, CONSTRAINT `beta_ibfk_1` FOREIGN KEY (`col1`) REFERENCES `alpha` (`col1`)) <- behavior is OK here
mysql> insert into beta values(1);
Query OK, 1 row affected (0.00 sec)
mysql> delete from alpha where col1 = 1;
Query OK, 1 row affected (0.00 sec) <- behavior is not OK here, it should not allow to DELETE this row in alpha table as it is a reference for beta table col1.
Regards,
Laurent
Changed in codership-mysql: | |
status: | New → Confirmed |
assignee: | nobody → Seppo Jaakola (seppo-jaakola) |
importance: | Undecided → Critical |
milestone: | none → 5.5.29-23.7.2 |
Changed in codership-mysql: | |
status: | Confirmed → In Progress |
Changed in percona-xtradb-cluster: | |
status: | New → In Progress |
Changed in percona-xtradb-cluster: | |
milestone: | none → 5.5.29-23.7.2 |
status: | In Progress → Fix Committed |
importance: | Undecided → Critical |
Changed in percona-xtradb-cluster: | |
status: | Fix Committed → Fix Released |
Updated informations :
Tried to test if problem is present in Debian PXDBC 5.5.28-23.7-369 and it is not because expected behavior happened : /bugs.launchpad .net/codership- mysql/+ bug/1078346 affected the FK behavior ?
mysql> delete from alpha where col1 = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testlolo`.`beta`, CONSTRAINT `beta_ibfk_1` FOREIGN KEY (`col1`) REFERENCES `alpha` (`col1`))
-> So problem has been introduced with PXDBC 5.5.29-23.7.1-387, maybe some modifications related to this bug entry https:/