From: Date: February 26 2004 4:57pm
Subject: Re: Bug in new FOREIGN KEY checks in MySQLInnoDB/4.0.18
List-Archive: http://lists.mysql.com/bugs/15263
Message-Id: <403E175F.1050805@uk.radan.com>
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Sinisa Milivojevic wrote:

>Steve Hay writes:
>  
>
>>Sinisa Milivojevic wrote:
>>I don't understand what you mean by "foreign constraints need be 
>>constrained to a single database".  Could you clarify please?
>>
>>    
>>
>
>I ment:
>
>foreign constraints need not be constrained to a single database.
>  
>
I didn't realise that.

It makes more sense now, but couldn't the new check be improved so that 
if the "other" table that is referencing the table that DROP DATABASE is 
in the process of deleting happens to be in the same database, then 
don't complain about it.  Suitable recursion would probably be involved, 
which would basically lead to the server figuring out what order to drop 
the tables in, rather than me having to explicitly do all the DROP 
TABLEs myself first.  That would be a big help to me.

What confused me the most is that it seems to be pot luck as to whether 
DROP DATABASE will drop the tables in an order that will succeed or 
not.  So, for example, this script runs without error ("mysql -u root < 
ok.sql"):

ok.sql
======
create database test;

use test

create table foo(
  fooid int not null primary key
) type=innodb;

create table bar(
  barid int not null primary key,
  fooid int not null,
  index (fooid),
  foreign key (fooid) references foo (fooid)
) type=innodb;

insert into foo values (1);
insert into bar values (1, 1);

drop database test;
=====

whereas this script produces the error that I saw before:

notok.sql
=========
create database test;

use test

create table foo(
  fooid int not null primary key
) type=innodb;

create table bar(
  barid int not null primary key,
  fooid int not null,
  index (fooid),
  foreign key (fooid) references foo (fooid)
) type=innodb;

create table baz(
  bazid int not null primary key,
  barid int not null,
  index (barid),
  foreign key (barid) references bar (barid)
) type=innodb;

insert into foo values (1);
insert into bar values (1, 1);
insert into baz values (1, 1);

drop database test;
=====

Presumably in the first example, "bar" happened to get dropped before 
"foo" so it all worked OK, but in the second example, "bar" and "baz" 
evidently don't both get dropped before "foo" so you get the error.

It would be great if the server could see that no foreign key 
constraints involve other databases, and figure out a sensible order in 
which to drop the tables.

Any chance of that happening?

Where was that bit in the manual about dropping tables one by one, btw?

- Steve



------------------------------------------------
Radan Computational Ltd.

The information contained in this message and any files transmitted with it are confidential and intended for the addressee(s) only.  If you have received this message in error or there are any problems, please notify the sender immediately.  The unauthorized use, disclosure, copying or alteration of this message is strictly forbidden.  Note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Radan Computational Ltd.  The recipient(s) of this message should check it and any attached files for viruses: Radan Computational will accept no liability for any damage caused by any virus transmitted by this email.