Foreign Key problem with PXDBC 5.5.29-23.7.1-387

[Laurent Minost]

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

[Laurent Minost (lolomin)]

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 :
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://bugs.launchpad.net/codership-mysql/+bug/1078346 affected the FK behavior ?

[Kari Lehtinen (kari-lehtinen)]

I updated our 3 node cluster yesterday to version Percona-XtraDB-Cluster-server-5.5.29-23.7.1.387.rhel6.x86_64.

Since then we have noticed some weird behaviour with tables using foreign keys. I just traced the root cause which seems to be that cascading doesent't work at all. So i made a little test:

mysql> create database fktest; use fktest;
Query OK, 1 row affected (0.00 sec)

Database changed
mysql> CREATE TABLE parent (id INT NOT NULL,
    -> PRIMARY KEY (id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE child (id INT, parent_id INT,
    -> INDEX par_ind (parent_id),
    -> FOREIGN KEY (parent_id) REFERENCES parent(id)
    -> ON DELETE CASCADE
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into parent values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into child values (1,1);
Query OK, 1 row affected (0.03 sec)

mysql> delete from parent;
Query OK, 1 row affected (0.00 sec)

mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 1 |
+------+-----------+
1 row in set (0.00 sec)

Why child is not removed event cascade on delete is used ?

Tried also with foreign_key_checks on/off

[Seppo Jaakola (seppo-jaakola)]

FK checks were skipped in parent table. This issue is indeed a side effect for the fix of lp:1078346
FK checking has been "normalized" in revision: http://bazaar.launchpad.net/~codership/codership-mysql/5.5-23/revision/3846

[Laurent Minost (lolomin)]

Hi Seppo,

Thanks for this fix.
Do you know when it will be available through the 5.5.29-23.7.2 release please as the impact of this problem is pretty important ?
I also guess that Percona needs to implement the fix to their branch for XtraDB ?

Regards,

Laurent

[Alex Yurchenko (ayurchen)]

fix released in 23.7.3

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-921