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

Steve,

----- Original Message ----- 
From: "Steve Hay" <[email protected]>
To: "Heikki Tuuri" <[email protected]>
Cc: <[email protected]>
Sent: Friday, February 27, 2004 11:40 AM
Subject: 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?

yes, the manual is trailing behind. I have to update it.

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

Regards,

Heikki

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


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