Percona XtraDB Cluster - HA scalable solution for MySQL

Foreign Key problem with PXDBC 5.5.29-23.7.1-387

Reported by Laurent Minost on 2013-02-06
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Critical
Seppo Jaakola
Percona XtraDB Cluster
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

Laurent Minost (lolomin) wrote :

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) wrote :

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

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
Seppo Jaakola (seppo-jaakola) wrote :

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

Changed in codership-mysql:
status: In Progress → Fix Committed
Laurent Minost (lolomin) wrote :

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

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
Alex Yurchenko (ayurchen) wrote :

fix released in 23.7.3

Changed in codership-mysql:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers