Re: Bug in new FOREIGN KEY checks in MySQLInnoDB/4.0.18!

Hi!

A correction: FOREIGN KEY constraints ARE allowed to reference tables in
another database.

To make sure that a DROP DATABASE command works, you should enclose it:

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

I agree that InnoDB should check that if you are performing a DROP DATABASE
command, and all the FOREIGN KEYs refer to that same database, then DROP
DATABASE should succeed even without turning foreign key checks off. I have
to consider adding that optimization to 4.0.19.

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables

Order MySQL support from http://www.mysql.com/support/index.html


.....................
List:MySQL Bugs« Previous MessageNext Message »
From:Steve HayDate:February 26 2004 4:57pm
Subject:Re: Bug in new FOREIGN KEY checks in MySQLInnoDB/4.0.18

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


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]