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

[3 May 2005 13:29] Disha
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)"
[3 May 2005 14:05] MySQL Verification Team
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)
[30 Jun 2005 19:51] Kolbe Kegel
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).
[16 Aug 2005 15:22] andy hill
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
[17 Aug 2005 14:35] Heikki Tuuri
We may need to add a new error code to MySQL for this error.
[17 Aug 2005 14:35] Heikki Tuuri
We may need to add a new error code to MySQL for this error.
[28 Jul 2006 21:43] Heikki Tuuri
This is more like a feature request.
[25 Jul 2007 19:19] Peter Firth
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?
[25 Jul 2007 19:27] Peter Firth
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.
[22 Dec 2007 12:49] fenixshadow fenixshadow
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".
[16 Oct 2009 6:22] sanjaya sanjay
a

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

[25 Aug 2010 15:44] Christopher Schultz
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 :(
[17 Feb 2011 16:10] Ace Dimitrievski
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.