MySQL Bugs: #2799: Multi-table DELETE and Foreign Key (bad affected rows)

Bug #2799	Multi-table DELETE and Foreign Key (bad affected rows)
Submitted:	14 Feb 2004 16:18	Modified:	19 Feb 2004 11:35
Reporter:	Rinat Nasibullin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Windows (Win NT)
Assigned to:		CPU Architecture:	Any

Description:
Hello, developers!

1) Please, see my test.sql file (generated with phpMyAdmin 2.5.4). 
In last number string is error.
Affected rows should be 3.

2) I can't delete this test database `test`. MySQL write the message:
"#1217 - Cannot delete or update a parent row: a foreign key constraint fails"
Really MySQL deleting only one table `t3`. Why?
I am think MySQL should be drop the database without looking foreign keys.

Thanks.

test.sql
-------------------------
#CREATE DATABASE `test`;

CREATE TABLE `t1` (
`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
PRIMARY KEY ( `id` )
) TYPE = InnoDB AUTO_INCREMENT =2;# MySQL returned an empty result set (i.e. zero rows).

INSERT INTO `t1`
VALUES ( 1 ) ;# Affected rows:1

CREATE TABLE `t2` (
`id` int( 11 ) NOT NULL default '0',
UNIQUE KEY `id` ( `id` ) ,
CONSTRAINT `t1_id_fk` FOREIGN KEY ( `id` ) REFERENCES `t1` ( `id` )
) TYPE = InnoDB;# MySQL returned an empty result set (i.e. zero rows).

INSERT INTO `t2`
VALUES ( 1 ) ;# Affected rows:1

CREATE TABLE `t3` (
`id` int( 11 ) NOT NULL default '0',
KEY `id` ( `id` ) ,
CONSTRAINT `t2_id_fk` FOREIGN KEY ( `id` ) REFERENCES `t2` ( `id` )
) TYPE = InnoDB;# MySQL returned an empty result set (i.e. zero rows).

INSERT INTO `t3`
VALUES ( 1 ) ;# Affected rows:1

DELETE t3,t2,t1
FROM t1,t2,t3
WHERE t1.id =1 AND t2.id = t1.id AND t3.id = t2.id;# Affected rows:1
-------------------------

How to repeat:
-

Suggested fix:
-

I am sorry, the problem 2 is problem in phpMyAdmin.

I am sorry, the problem 2 is problem in phpMyAdmin, when it deleting tables one by one.

Hi!

The fact that MySQL wrongly reports 'affected rows' if those rows are deleted by ON DELETE CASCADE, is a known design deficiency, and will be addressed in a future version. That is why I am changing the status of this bug report to 'deferred'.

Starting from 4.0.18, you have to do

SET FOREIGN_KEY_CHECKS=0;
DROP DATABASE xyz;
SET FOREIGN_KEY_CHECKS=1;

if there are foreign keys defined in database xyz. This is to protect you from inadvertently breaking the foreign key constraints. I agree that if there are no rows in the child table(s), or all foreign key constraints refer the SAME database, then DROP DATABASE should succeed without a need to turn the foreign key checks off.

Regards,

Heikki

Dont't forget to pay attention to the last line in "test.sql" ! :)

Hi!

The multi-table DELETE deleted only 1 row in my test. Should it delete more?

Regards,

Heikki

heikki@hundin:~/mysql-4.0/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.19-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `t1` (
    -> `id` int( 11 ) NOT NULL AUTO_INCREMENT ,
    -> PRIMARY KEY ( `id` )
    -> ) TYPE = InnoDB AUTO_INCREMENT =2;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO `t1`
    -> VALUES ( 1 ) ;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE `t2` (
    -> `id` int( 11 ) NOT NULL default '0',
    -> UNIQUE KEY `id` ( `id` ) ,
    -> CONSTRAINT `t1_id_fk` FOREIGN KEY ( `id` ) REFERENCES `t1` ( `id` )
    -> ) TYPE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO `t2`
    -> VALUES ( 1 ) ;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE `t3` (
    -> `id` int( 11 ) NOT NULL default '0',
    -> KEY `id` ( `id` ) ,
    -> CONSTRAINT `t2_id_fk` FOREIGN KEY ( `id` ) REFERENCES `t2` ( `id` )
    -> ) TYPE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO `t3`
    -> VALUES ( 1 ) ;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> DELETE t3,t2,t1
    -> FROM t1,t2,t3
    -> WHERE t1.id =1 AND t2.id = t1.id AND t3.id = t2.id;# Affected rows:1
Query OK, 1 row affected (0.02 sec)

mysql> select * from t3;
Empty set (0.00 sec)

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

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

mysql>

There should be three affected rows, i.e. all tables after multi-table DELETE query should be empty. 
The multi-table DELETE query don't work true, when the tables have this relations (with foreign keys):
t3.id -> t2.id -> t1.id

If delete tables in order by t3, t2, t1, then all should be ok.
MySQL this understand? :)

SHOW INNODB STATUS:
--begin-----------------------------------
=====================================
040217 20:41:09 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 238, signal count 238
Mutex spin waits 75, rounds 1200, OS waits 15
RW-shared spins 440, OS waits 220; RW-excl spins 4, OS waits 3
------------------------
LATEST FOREIGN KEY ERROR
------------------------
040217 20:40:27 Transaction:
TRANSACTION 0 24246, ACTIVE 0 sec, OS thread id 404 updating or deleting, thread declared inside InnoDB 496
mysql tables in use 3, locked 3
7 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 18, query id 18357 localhost 127.0.0.1 root deleting from reference tables
DELETE t3,
t2,
t1 FROM t1,
t2,
t3 WHERE t1.id =1 AND t2.id = t1.id AND t3.id = t2.id
Foreign key constraint fails for table test/t3:
,
 CONSTRAINT `t2_id_fk` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)
Trying to delete or update in parent t... 
--end----------------------------------

Sinisa,

can you check if the behavior is right in that multi-table DELETE? At least, it seems to swallow the FOREIGN KEY error generated there, and does not roll back the full statement.

Regards,

Heikki

This bug happens only if foreign key constrants exist.

MySQL optimizer simply re-orders  tables in the order that suits best
the join procedure. 

This results in error that is not reported.

I also think that multi-table deletes / updates should not be used
with tables being related with foreign key constraints. 

I will write about this to our docs dept.

I have now fixed this so that error is reported and statement is
rolled back. 

I will soon post also a similar fix for multi-table updates.

patch is pushed.