MySQL's ERROR 1025 explained

MySQL issues a cryptic error message, “Error on rename,” when you try to alter a table in such a way that it would break a foreign key constraint:

create table test1(a int not null primary key)engine=innodb;
create table test2(a int not null, foreign key(a) references test1 (a)) engine=innodb;
alter table test2 modify a smallint not null;       
ERROR 1025 (HY000): Error on rename of './test/#sql-2fa8_1' to './test/test2' (errno: 150)

This happens because ALTER TABLE really works by making a copy of the table, then renaming to move the old table out of the way and move the new table into its place. It is certainly one of the less meaningful error messages I’ve seen in MySQL.

There’s slightly more information in the output of SHOW ENGINE INNODB STATUS, if you are looking there (of course, if you’re looking there you’re probably already clued in to what’s going on). And innotop can parse that information for you:

In case you didn’t understand why the foreign key constraint was failing, the error message innotop parses out is much clearer. It’s because the foreign key columns in the parent and child table have to have the same data type. I was trying to change the child’s column to an incompatible type.

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.