Bug #84853 Weird error message "ERROR 1025 (HY000): Error on rename of '...' to '...' (errn

• Submitted: 7 Feb 2017 5:25
• Reporter: Csongor Halmai
• Status: Open
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 5.5.51
• OS: Linux
• CPU Architecture: Any
• Tags: drop colum foreign key error message

[7 Feb 2017 5:25] Csongor Halmai

Description:
When I want to drop a column that contains foreign keys then I get a weird error message. 

Example:

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS countries; 

CREATE TABLE countries (
   id INT NOT NULL AUTO_INCREMENT,
   country_name VARCHAR(200),
   PRIMARY KEY (id)
);

CREATE TABLE users (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(200),
	country_id INT,
	PRIMARY KEY (id)
);

ALTER TABLE users ADD CONSTRAINT dummy FOREIGN KEY (country_id) REFERENCES countries (id);
ALTER TABLE users DROP COLUMN country_id;

Then I get:

ERROR 1025 (HY000): Error on rename of './blah/#sql-3941_618' to './blah/users' (errno: 150)

If I drop the FK constraint manually then I can drop the column nicely:

ALTER TABLE users DROP FOREIGN KEY dummy;
ALTER TABLE users DROP COLUMN country_id;

How to repeat:
Execute the commands from the Description field. 

Suggested fix:
Instead of this error message, I want the FK constraint to be dropped automatically. Or, at least a reasonable error message would be nice.

[7 Feb 2017 21:01] MySQL Verification Team

looks better in current versions.  I suppose in 5.5, SHOW ENGINE INNODB STATUS will show the foreign key error detail.

mysql> ALTER TABLE users DROP COLUMN country_id;
ERROR 1553 (HY000): Cannot drop index 'dummy': needed in a foreign key constraint
mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 8.0.1-dmr-debug |
+-----------------+
1 row in set (0.00 sec)

[8 Oct 2019 18:35] Federico Razzoli

I think the bug is fixed. In 8.0 I get:
ERROR 1828 (HY000): Cannot drop column 'country_id': needed in a foreign key constraint 'dummy'