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/bugs?unsub=gcdmb-bugs@m.gmane.org

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

SGVpa2tpIFR1dXJpIHdyb3RlOgoKPkhpIQo+Cj5BIGNvcnJlY3Rpb246IEZP UkVJR04gS0VZIGNv
bnN0cmFpbnRzIEFSRSBhbGxvd2VkIHRvIHJlZmVyZW5jZSB0YWJsZXMgaW4K PmFub3RoZXIgZGF0
YWJhc2UuCj4KPlRvIG1ha2Ugc3VyZSB0aGF0IGEgRFJPUCBEQVRBQkFTRSBj b21tYW5kIHdvcmtz
LCB5b3Ugc2hvdWxkIGVuY2xvc2UgaXQ6Cj4KPlNFVCBGT1JFSUdOX0tFWV9D SEVDS1M9MDsKPkRS
T1AgREFUQUJBU0UgLi4uCj5TRVQgRk9SRUlHTl9LRVlfQ0hFQ0tTPTE7Cj4K SSBkaWQgc2VlIHRo
YXQgaW4gdGhlIElubm9EQiBjaGFuZ2UgbG9nLCBidXQgZGlkbid0IHRoaW5r IHRoYXQgSSB3b3Vs
ZCAKbmVlZCBpdCB0byBkcm9wIGEgd2hvbGUgc2VsZi1jb250YWluZWQgZGF0 YWJhc2UgOykKCkl0
IGRvZXMgaW5kZWVkIHNvbHZlIG15IHByb2JsZW0uCgpUaGVyZSdzIG5vIG1l bnRpb24gb2YgaXQg
aW4gdGhlIE15U1FMIG1hbnVhbCBpbiBjb25uZWN0aW9uIHdpdGggRFJPUCAK Y29tbWFuZHMsIHRo
b3VnaCAtLSBpdCdzIG9ubHkgbWVudGlvbmVkIGluIHJlbGF0aW9uIHRvIExP QUQgREFUQS4gIApQ
ZXJoYXBzIGEgY29tbWVudCBpbiB0aGUgbWFudWFsIGFib3V0IGl0cyB1c2Vm dWxuZXNzIGZvciBE
Uk9QIApUQUJMRS9EQVRBQkFTRSB3b3VsZCBiZSBpbiBvcmRlciB0b28/Cgo+ Cj5JIGFncmVlIHRo
YXQgSW5ub0RCIHNob3VsZCBjaGVjayB0aGF0IGlmIHlvdSBhcmUgcGVyZm9y bWluZyBhIERST1Ag
REFUQUJBU0UKPmNvbW1hbmQsIGFuZCBhbGwgdGhlIEZPUkVJR04gS0VZcyBy ZWZlciB0byB0aGF0
IHNhbWUgZGF0YWJhc2UsIHRoZW4gRFJPUAo+REFUQUJBU0Ugc2hvdWxkIHN1 Y2NlZWQgZXZlbiB3
aXRob3V0IHR1cm5pbmcgZm9yZWlnbiBrZXkgY2hlY2tzIG9mZi4gSSBoYXZl Cj50byBjb25zaWRl
ciBhZGRpbmcgdGhhdCBvcHRpbWl6YXRpb24gdG8gNC4wLjE5Lgo+CkV4Y2Vs bGVudC4gIEknbGwg
Y2VydGFpbmx5IGxvb2sgZm9yd2FyZCB0byBzdWNoIGFuIGVuaGFuY2VtZW50 IQoKTWFueSB0aGFu
a3MsCi0gU3RldmUKCj4KPkJlc3QgcmVnYXJkcywKPgo+SGVpa2tpCj5Jbm5v YmFzZSBPeQo+aHR0
cDovL3d3dy5pbm5vZGIuY29tCj5Jbm5vREIgLSB0cmFuc2FjdGlvbnMsIHJv dyBsZXZlbCBsb2Nr
aW5nLCBhbmQgZm9yZWlnbiBrZXlzIGZvciBNeVNRTAo+SW5ub0RCIEhvdCBC YWNrdXAgLSBhIGhv
dCBiYWNrdXAgdG9vbCBmb3IgSW5ub0RCIHdoaWNoIGFsc28gYmFja3MgdXAg TXlJU0FNCj50YWJs
ZXMKPgo+T3JkZXIgTXlTUUwgc3VwcG9ydCBmcm9tIGh0dHA6Ly93d3cubXlz cWwuY29tL3N1cHBv
cnQvaW5kZXguaHRtbAo+Cj4KPi4uLi4uLi4uLi4uLi4uLi4uLi4uLgo+TGlz dDpNeVNRTCBCdWdz
qyBQcmV2aW91cyBNZXNzYWdlTmV4dCBNZXNzYWdlILsKPkZyb206U3RldmUg SGF5RGF0ZTpGZWJy
dWFyeSAyNiAyMDA0IDQ6NTdwbQo+U3ViamVjdDpSZTogQnVnIGluIG5ldyBG T1JFSUdOIEtFWSBj
aGVja3MgaW4gTXlTUUxJbm5vREIvNC4wLjE4Cj4KPlNpbmlzYSBNaWxpdm9q ZXZpYyB3cm90ZToK
Pgo+ICAKPgo+PlN0ZXZlIEhheSB3cml0ZXM6Cj4+Cj4+Cj4+ICAgIAo+Pgo+ Pj5TaW5pc2EgTWls
aXZvamV2aWMgd3JvdGU6Cj4+PkkgZG9uJ3QgdW5kZXJzdGFuZCB3aGF0IHlv dSBtZWFuIGJ5ICJm
b3JlaWduIGNvbnN0cmFpbnRzIG5lZWQgYmUKPj4+Y29uc3RyYWluZWQgdG8g YSBzaW5nbGUgZGF0
YWJhc2UiLiAgQ291bGQgeW91IGNsYXJpZnkgcGxlYXNlPwo+Pj4KPj4+Cj4+ Pgo+Pj4gICAgICAK
Pj4+Cj4+SSBtZW50Ogo+Pgo+PmZvcmVpZ24gY29uc3RyYWludHMgbmVlZCBu b3QgYmUgY29uc3Ry
YWluZWQgdG8gYSBzaW5nbGUgZGF0YWJhc2UuCj4+Cj4+Cj4+ICAgIAo+Pgo+ SSBkaWRuJ3QgcmVh
bGlzZSB0aGF0Lgo+Cj5JdCBtYWtlcyBtb3JlIHNlbnNlIG5vdywgYnV0IGNv dWxkbid0IHRoZSBu
ZXcgY2hlY2sgYmUgaW1wcm92ZWQgc28gdGhhdAo+aWYgdGhlICJvdGhlciIg dGFibGUgdGhhdCBp
cyByZWZlcmVuY2luZyB0aGUgdGFibGUgdGhhdCBEUk9QIERBVEFCQVNFIGlz Cj5pbiB0aGUgcHJv
Y2VzcyBvZiBkZWxldGluZyBoYXBwZW5zIHRvIGJlIGluIHRoZSBzYW1lIGRh dGFiYXNlLCB0aGVu
Cj5kb24ndCBjb21wbGFpbiBhYm91dCBpdC4gIFN1aXRhYmxlIHJlY3Vyc2lv biB3b3VsZCBwcm9i
YWJseSBiZSBpbnZvbHZlZCwKPndoaWNoIHdvdWxkIGJhc2ljYWxseSBsZWFk IHRvIHRoZSBzZXJ2
ZXIgZmlndXJpbmcgb3V0IHdoYXQgb3JkZXIgdG8gZHJvcAo+dGhlIHRhYmxl cyBpbiwgcmF0aGVy
IHRoYW4gbWUgaGF2aW5nIHRvIGV4cGxpY2l0bHkgZG8gYWxsIHRoZSBEUk9Q Cj5UQUJMRXMgbXlz
ZWxmIGZpcnN0LiAgVGhhdCB3b3VsZCBiZSBhIGJpZyBoZWxwIHRvIG1lLgo+ Cj5XaGF0IGNvbmZ1
c2VkIG1lIHRoZSBtb3N0IGlzIHRoYXQgaXQgc2VlbXMgdG8gYmUgcG90IGx1 Y2sgYXMgdG8gd2hl
dGhlcgo+RFJPUCBEQVRBQkFTRSB3aWxsIGRyb3AgdGhlIHRhYmxlcyBpbiBh biBvcmRlciB0aGF0
IHdpbGwgc3VjY2VlZCBvcgo+bm90LiAgU28sIGZvciBleGFtcGxlLCB0aGlz IHNjcmlwdCBydW5z
IHdpdGhvdXQgZXJyb3IgKCJteXNxbCAtdSByb290IDwKPm9rLnNxbCIpOgo+ Cj4KPiAgCj4KCgoK
Ci0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLQpSYWRhbiBD
b21wdXRhdGlvbmFsIEx0ZC4KClRoZSBpbmZvcm1hdGlvbiBjb250YWluZWQg aW4gdGhpcyBtZXNz
YWdlIGFuZCBhbnkgZmlsZXMgdHJhbnNtaXR0ZWQgd2l0aCBpdCBhcmUgY29u ZmlkZW50aWFsIGFu
ZCBpbnRlbmRlZCBmb3IgdGhlIGFkZHJlc3NlZShzKSBvbmx5LiAgSWYgeW91 IGhhdmUgcmVjZWl2
ZWQgdGhpcyBtZXNzYWdlIGluIGVycm9yIG9yIHRoZXJlIGFyZSBhbnkgcHJv YmxlbXMsIHBsZWFz
ZSBub3RpZnkgdGhlIHNlbmRlciBpbW1lZGlhdGVseS4gIFRoZSB1bmF1dGhv cml6ZWQgdXNlLCBk
aXNjbG9zdXJlLCBjb3B5aW5nIG9yIGFsdGVyYXRpb24gb2YgdGhpcyBtZXNz YWdlIGlzIHN0cmlj
dGx5IGZvcmJpZGRlbi4gIE5vdGUgdGhhdCBhbnkgdmlld3Mgb3Igb3Bpbmlv bnMgcHJlc2VudGVk
IGluIHRoaXMgZW1haWwgYXJlIHNvbGVseSB0aG9zZSBvZiB0aGUgYXV0aG9y IGFuZCBkbyBub3Qg
bmVjZXNzYXJpbHkgcmVwcmVzZW50IHRob3NlIG9mIFJhZGFuIENvbXB1dGF0 aW9uYWwgTHRkLiAg
VGhlIHJlY2lwaWVudChzKSBvZiB0aGlzIG1lc3NhZ2Ugc2hvdWxkIGNoZWNr IGl0IGFuZCBhbnkg
YXR0YWNoZWQgZmlsZXMgZm9yIHZpcnVzZXM6IFJhZGFuIENvbXB1dGF0aW9u YWwgd2lsbCBhY2Nl
cHQgbm8gbGlhYmlsaXR5IGZvciBhbnkgZGFtYWdlIGNhdXNlZCBieSBhbnkg dmlydXMgdHJhbnNt
aXR0ZWQgYnkgdGhpcyBlbWFpbC4K

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

Steve,

----- Original Message -----
From: "Steve Hay"
To: "Heikki Tuuri"
Cc:
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/bugs?unsub=gcdmb-bugs@m.gmane.org