MySQL Bugs: #23366: strange error when dropping schema, Foreign Key points into another schema

Bug #23366	strange error when dropping schema, Foreign Key points into another schema
Submitted:	17 Oct 2006 11:57	Modified:	16 Nov 2006 15:38
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	5.0	OS:
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Heavily modified rest derived from NIST tests: schema8, schema9

CREATE USER mysqltest@localhost identified by 'PWD';
DROP TABLE IF EXISTS test.t2;
DROP SCHEMA IF EXISTS mysqltest;
CREATE SCHEMA mysqltest ;
grant ALL ON mysqltest.* to mysqltest@localhost;
grant ALL ON test.* to mysqltest@localhost;
# Establish connection mysqltest (user = mysqltest)
CREATE TABLE t1
(f1   CHAR(3) NOT NULL,
UNIQUE  (f1)) ENGINE = InnoDB;
CREATE TABLE test.t2
(f1   CHAR(3),
f2     CHAR(3),
FOREIGN KEY (f1) REFERENCES mysqltest.t1(f1)) ENGINE = InnoDB;
# Switch to connection default (user = root)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest@localhost;
DROP USER mysqltest@localhost;
DROP SCHEMA mysqltest;
-------->
mysqltest: At line 43: query 'DROP SCHEMA mysqltest' failed:
           1217: Cannot delete or update a parent row: 
                 a foreign key constraint fails

Some observations/experiments:
The bad effect disappears if
- MyISAM is used instead of InnoDB                or
- the user root is used instead of the user mysqltest or
- all tables are created within the schema mysqltest
Therefore I guess it is a problem around evaluation of
permissions when more than one schema is involved.

My environment:
 - PC Intel Pentium M (x86-32Bit) with Linux(SuSE 10.1)
 - MySQL compiled from source
     mysql-5.0 last ChangeSet@1.2280, 2006-10-13
     BUILD/compile-pentium-debug-max

Priority and severity are set to 1, because we have here a regression.
The NIST tests were successful history.

How to repeat:
Please use the attached files.
cd <top directory of release>/mysql-test
cp n2.test t
cp n2.result r  # This file contains expected results
./mysql-test-run --skip-ndb n2

testscript

Attachment: n2.test (application/octet-stream, text), 1.33 KiB.

Expected result

Attachment: n2.result (application/octet-stream, text), 700 bytes.

The current MySQL 5.1 last ChangeSet@1.2311, 2006-10-13
show the same wrong behaviour.

Matthias,

CREATE TABLE test.t2
(f1   CHAR(3),
f2     CHAR(3),
FOREIGN KEY (f1) REFERENCES mysqltest.t1(f1)) ENGINE = InnoDB;

why should anyone be able to drop mysqltest.t1? Then the foreign key reference is broken. Dropping mysqltest.t1 should only be possible if SET FOREIGN_KEY_CHECKS=0.

Regards,

Heikki

Heikki,
you are right that a "successful" DROP SCHEMA will cause a broken 
foreign key reference. Excellent, I am a fan of strict modes
preventing inconsistent structures.
But I have some problems with the current behaviour/MySQL manual:
1. I am relatively but not 100 % sure that this behaviour was introduced
   after the converted NIST tests (~ April 2005) were ready for use.
   If this is true, than we have an incompatible change of behaviour
   which should be documented.
   If this was probably introduced after GA (autumn 2005) than it
   must be documented.
2. The manual is not 100 % clear about the effect of
   SET FOREIGN_KEY_CHECKS=0.
   The impression I get from the manual is that such a setting
   switches the FK constraint checks during INSERT/UPDATE/DELETE off.
   I did not found anything about DDL.
   If the current behaviour (server denies DROP SCHEMA) is
   intended and not wrong, than we have a bug within the manual.
   It must be mentioned within the server variables+foreign key+
   drop schema chapter that the FOREIGN_KEY_CHECKS setting affects
   for instance DROP SCHEMA and probably also DROP TABLE.
3. Even if we assume that the current behaviour is correct
   (my vote) the error message
      1217: Cannot delete or update a parent row: 
                   a foreign key constraint fails
   is at least very questionable.
   I fear most customers will not have the idea that they
   get this server response because the server denies to
   damage the foreign key reference.

Do you secure the "internal" referential integrity within
InnoDB schemas/tables by using your foreign key feature ?
If yes, very clever.

Regards,

Matthias

The docs should mention that FOREIGN_KEY_CHECKS=0 also affects DDL statements. If FOREIGN_KEY_CHECKS=0 then InnoDB allows one to drop the referenced table.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I've added the following paragraph to
http://dev.mysql.com/doc/refman/5.0/en/set-option.html:

Setting FOREIGN_KEY_CHECKS to 0 also affects data definition
statements: DROP DATABASE drops a database even if it contains tables
that have foreign keys that are referred to by tables outside the
database, and DROP TABLE drops tables that have foreign keys that are
referred to by other tables.