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

Heikki Tuuri wrote:

>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 did see that in the InnoDB change log, but didn't think that I would 
need it to drop a whole self-contained database ;)

It does indeed solve my problem.

There's no mention of it in the MySQL manual in connection with DROP 
commands, though -- it's only mentioned in relation to LOAD DATA.  
Perhaps a comment in the manual about its usefulness for DROP 
TABLE/DATABASE would be in order too?

>
>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.
>
Excellent.  I'll certainly look forward to such an enhancement!

Many thanks,
- Steve

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




------------------------------------------------
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.