MySQL Bugs: #10333: Incorrect error when deleting a foreign key with incorrect constraint name

Bug #10333	Incorrect error when deleting a foreign key with incorrect constraint name
Submitted:	3 May 2005 13:29	Modified:	23 Aug 2006 4:36
Reporter:	Disha	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.7	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When we try to delete a foreign key from a table and if we specify incorrect constraint name, the error displayed is incorrect.

How to repeat:
1. Start the MySQL client and connect to the database.
2. Set the delimiter to //.
3. Run the following commands to create and use a database.

    drop database if exists db1//
    create database db1//
    use db1//

4. Now using the following commands create two tables where the second table has a foreign key reference to the first table as follows:

    drop table if exists course//
    create table course (
      id int,
      courcename char(100) not null,
      duration int not null,
      primary key (id)
    ) engine=innodb//

    insert into course values(1,'system design',4), (2,'database system',6), 
    (3,'operating system',16)//

    drop table if exists students//
    create table students (
      id int not null, 
      name char(100) not null,
      course_id int,
      foreign key (course_id) references course(id) on delete set null
     ) engine=innodb//

     insert into students values(1,'l',1),	(2,'m',1),	(3,'n',2),(4,'o',3),(5,'p',3),(6,'q',1)//

5. Now try to delete the foreign key with incorrect constraint name as follows:

    alter table students drop foreign key id//

Expected Results: 
1. Appropriate error should be displayed as the constraint name supplied does not exist.

Actual Results: 
1. Incorrect error is displayed as:
"ERROR 1025 (HY000): Error on rename of '.\db1\students' to '.\db1\#sql2-1b0-57' (errno: 152)"

mysql> alter table students drop foreign key id//
ERROR 1025 (HY000): Error on rename of '.\db1\students' to '.\db1\#sql2-45c-1' (errno: 152)
mysql> select version();
    -> //
+------------------+
| version()        |
+------------------+
| 5.0.6-beta-debug |
+------------------+
1 row in set (0.00 sec)

This bug exists in 5.0.7 and is not platform specific. No change to the delimiter is required. It can be repeated by creating any InnoDB and attempting to drop a non-existing foreign key from that table.

The error code and/or message should be modified to more clearly represent the error that is occuring (attempt to drop a non-existent foreign key contraint).

Hi,

I keep getting the same error code

\withoutsp\#sql2-33c-55' (errno: 152)" 

Is there a patch available for this error yet?

Cheers

Andy

We may need to add a new error code to MySQL for this error.

We may need to add a new error code to MySQL for this error.

This is more like a feature request.

Two years later, version 5.0.41, I am unable to drop foreign keys with this same error.  Is there something new available about this problem or do I really need to drop and rebuild a production table in order to drop a foreign key?

Sorry, my bad.  Appears the drop foreign key statement is case sensitive regarding the fk name.  I was able to drop my fk when I capitalized the fk name.

Two years later, version 5.2.0-falcon-alpha-community-nt, I am unable to drop foreign keys with this same error. :)
And it cost me 1 hour to fixed it.

It is not a bug now, but a wrong message MySQL give us.

Your can run SHOW INNODB STATUS to know what really happen.

mysql> ALTER TABLE ruler.test2child DROP FOREIGN KEY test;
ERROR 1025 : Error on rename of './ruler/test2child' to './ruler/#sql2-298-8f' (errno: 152)
mysql> show innodb status; 
------------------------
LATEST FOREIGN KEY ERROR
------------------------
071222 20:48:26 Error in dropping of a foreign key constraint of table "ruler"."test2child",
in SQL command
ALTER TABLE ruler.test2child DROP FOREIGN KEY test
Cannot find a constraint with the given id "test".

a

Attachment: a.erwin (application/octet-stream, text), 155.25 KiB.

This would seem to be a trivial bug to fix. Any reason why it's being ignored?

I encountered this in 5.0.51a-24+lenny4 (Debian).

I haven't checked in 5.1... if it's fixed, there, does that mean that 5.0 will never get it? I understand that 5.0 has reached EOL, but lots of people are still running it :(

Use SHOW CREATE TABLE `table_name` to see the *actual name* of the foreign key. It looks like it might be mysql query browser problem when generating the query with wrong spelling of the foreign key name.