Bug in new FOREIGN KEY checks in MySQLInnoDB/4.0.18

Hi,

I've just upgraded from MySQL 4.0.16 to 4.0.18 and now find that some
commands that used to work without error (dropping and re-creating a
database) no longer work.

The error is:

error: 'Cannot delete or update a parent row: a foreign key
constraint fails'

which presumably relates to the first item in the InnoDB version history
(http://www.innodb.com/ibman.php#InnoDB.history). (All the tables in
the database concerned are InnoDB tables.)

It appears that the foreign key constraint is being checked even when
dropping the database, which I assume is not intended.

I tried a simple case of one table X referencing another table Y, and
"DROP DATABASE" works fine (even with data in it, so foreign key
constraints are not being checked here, which is as I would expect). I
tried another case with two tables X and Y referencing one table Z, but
still no error. Finally, I reproduced my problem by having table X
referencing table Y and table Y referencing table Z.

The following DOS command-lines result in the error above:

=====
C:\>C:\mysql\bin\mysqladmin -u root -f drop test
Database "test" dropped

C:\>C:\mysql\bin\mysqladmin -u root create test

C:\>C:\mysql\bin\mysql -u root test -e "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"

C:\>C:\mysql\bin\mysql -u root test -e "insert into foo values (1);
insert into bar values (1, 1); insert into baz values (1, 1)"

C:\>C:\mysql\bin\mysqladmin -u root -f drop test
C:\mysql\bin\mysqladmin: DROP DATABASE test failed;
error: 'Cannot delete or update a parent row: a foreign key constraint
fails'
=====

I'm running Windows XP SP1.

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


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

Steve Hay writes:
> Hi,
>
> I've just upgraded from MySQL 4.0.16 to 4.0.18 and now find that some
> commands that used to work without error (dropping and re-creating a
> database) no longer work.
>
> The error is:
>
> error: 'Cannot delete or update a parent row: a foreign key
> constraint fails'
>
> which presumably relates to the first item in the InnoDB version history
> (http://www.innodb.com/ibman.php#InnoDB.history). (All the tables in
> the database concerned are InnoDB tables.)
>
> It appears that the foreign key constraint is being checked even when
> dropping the database, which I assume is not intended.
>
> I tried a simple case of one table X referencing another table Y, and
> "DROP DATABASE" works fine (even with data in it, so foreign key
> constraints are not being checked here, which is as I would expect). I
> tried another case with two tables X and Y referencing one table Z, but
> still no error. Finally, I reproduced my problem by having table X
> referencing table Y and table Y referencing table Z.
>
> The following DOS command-lines result in the error above:
>
> I'm running Windows XP SP1.
>
> Regards,
> - Steve
>

Hi!

Yes, the above is now the expected behaviour and it is documented in
our Changelog.

All you have to do is drop tables in the right order.

--

Sincerely,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Full time Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus

Meet the MySQL at User Conference ! (April 14-16, 2004)
http://www.mysql.com/uc2004/


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

Sinisa Milivojevic wrote:

>Steve Hay writes:
>
>
>>Hi,
>>
>>I've just upgraded from MySQL 4.0.16 to 4.0.18 and now find that some
>>commands that used to work without error (dropping and re-creating a
>>database) no longer work.
>>
>>The error is:
>>
>> error: 'Cannot delete or update a parent row: a foreign key
>>constraint fails'
>>
>>which presumably relates to the first item in the InnoDB version history
>>(http://www.innodb.com/ibman.php#InnoDB.history). (All the tables in
>>the database concerned are InnoDB tables.)
>>
>>It appears that the foreign key constraint is being checked even when
>>dropping the database, which I assume is not intended.
>>
>>I tried a simple case of one table X referencing another table Y, and
>>"DROP DATABASE" works fine (even with data in it, so foreign key
>>constraints are not being checked here, which is as I would expect). I
>>tried another case with two tables X and Y referencing one table Z, but
>>still no error. Finally, I reproduced my problem by having table X
>>referencing table Y and table Y referencing table Z.
>>
>>The following DOS command-lines result in the error above:
>>
>>I'm running Windows XP SP1.
>>
>>Regards,
>>- Steve
>>
>>
>>
>
>Hi!
>
>Yes, the above is now the expected behaviour and it is documented in
>our Changelog.
>
>All you have to do is drop tables in the right order.
>
>
I understand that if I was issuing DROP TABLE statements then I would
need to put them in the right order, but I'm doing a DROP DATABASE.

Surely foreign key constraints should not be checked when the entire
database is being dropped.

If they /are/ supposed to be checked then the other cases that I
mentioned above (where DROP DATABASE worked fine) are not working properly!

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


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

Steve Hay writes:
> Sinisa Milivojevic wrote:
> I understand that if I was issuing DROP TABLE statements then I would
> need to put them in the right order, but I'm doing a DROP DATABASE.
>
> Surely foreign key constraints should not be checked when the entire
> database is being dropped.
>
> If they /are/ supposed to be checked then the other cases that I
> mentioned above (where DROP DATABASE worked fine) are not working properly!
>
> - Steve
>

Just as our manual says, drop tables one by one, as foreign
constraints need be constrained to a single database.

--

Sincerely,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Full time Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus

Meet the MySQL at User Conference ! (April 14-16, 2004)
http://www.mysql.com/uc2004/


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

Sinisa Milivojevic wrote:

>Steve Hay writes:
>
>
>>Sinisa Milivojevic wrote:
>>I understand that if I was issuing DROP TABLE statements then I would
>>need to put them in the right order, but I'm doing a DROP DATABASE.
>>
>>Surely foreign key constraints should not be checked when the entire
>>database is being dropped.
>>
>>If they /are/ supposed to be checked then the other cases that I
>>mentioned above (where DROP DATABASE worked fine) are not working properly!
>>
>>- Steve
>>
>>
>>
>
>Just as our manual says, drop tables one by one, as foreign
>constraints need be constrained to a single database.
>
>
I don't understand what you mean by "foreign constraints need be
constrained to a single database". Could you clarify please?

I just want to drop a database, and can't understand why MySQL/InnoDB
would now be checking the foreign key constraints within it. Why would
it care which tables refer to which others if they're all being dropped
anyway?

I've just searched through the manual ("manual.html" that comes with
MySQL 4.0.18) and I couldn't see any mention at all of the need to drop
tables one by one before dropping the database. Which section of the
manual is that described in?

Having to drop all the tables individually before dropping the database
would be a real PITA for me since the database in question has some 600+
tables.

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


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

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.

--

Sincerely,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Full time Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus

Meet the MySQL at User Conference ! (April 14-16, 2004)
http://www.mysql.com/uc2004/


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

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.


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