About MySQL ERROR (1025)

When I was change the table, the following error was returned.

ERROR 1025 (HY000): Error on rename of './test/#sql-2fa8_1' to './test/test2' (errno: 150)

This case may be occur if foreign key constraint  is on the table.

Try to test for this case.

Parent table is PARENT_TBL and child table is CHILD_TBL.

CHILD_TBL is references an id of PARENT_TBL.

1. Create tables, PARENT_TBL and CHILD_TBL . CREATE TABLE `PARENT_TBL` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; CREATE TABLE `CHILD_TBL` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, `cvalue` varchar(20) NOT NULL, PRIMARY KEY (`id`), KEY `fk_parent_tbl_id` (`pid`), CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ; 2. Data input ( PARENT_TBL ) insert into PARENT_TBL (value) values ('test') ; insert into PARENT_TBL (value) values ('test') ; insert into PARENT_TBL (value) values ('test') ; insert into PARENT_TBL (value) values ('test') ; insert into PARENT_TBL (value) values ('test') ; insert into PARENT_TBL (value) values ('test') ; +----+-------+ | id | value | +----+-------+ | 1 | test | | 2 | test | | 3 | test | | 4 | test | | 5 | test | | 6 | test | +----+-------+ 3. Data input ( CHILD_TBL ) INSERT INTO CHILD_TBL (pid, cvalue) values (1, 'child_test') ; INSERT INTO CHILD_TBL (pid, cvalue) values (1, 'child_test') ; INSERT INTO CHILD_TBL (pid, cvalue) values (1, 'child_test') ; +----+-----+-------------+ | id | pid | cvalue | +----+-----+-------------+ | 2 | 1 | child_test | | 3 | 1 | child_test | | 4 | 1 | child_test | +----+-----+-------------+

If you specify a value that is not in PARENT_TBL by pid error occurs.

INSERT INTO CHILD_TBL (pid, cvalue) values (7, 'child_test') ;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`CHILD_TBL`, CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

And then let's perform an alter to change the id column type. 

mysql> alter table PARENT_TBL change id id bigint NOT NULL AUTO_INCREMENT ; ERROR 1025 (HY000): Error on rename of './test/#sql-72f5_330c7' to './test/PARENT_TBL' (errno: 150)

The error occurs.

In this case, drop fk constraint on the child table before altering the parent table. (temporarily)

1. Drop FK constraint on CHILD_TBL alter table CHILD_TBL drop foreign key fk_parent_tbl_id ; Query OK, 3 rows affected (0.19 sec) Records: 3 Duplicates: 0 Warnings: 0 show create table CHILD_TBL \G *************************** 1. row *************************** Table: CHILD_TBL Create Table: CREATE TABLE `CHILD_TBL` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, `cvalue` varchar(20) NOT NULL, PRIMARY KEY (`id`), KEY `fk_parent_tbl_id` (`pid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 2. Change the column in PARENT_TBL mysql> alter table PARENT_TBL change id id bigint NOT NULL AUTO_INCREMENT ; Query OK, 6 rows affected (0.12 sec) Records: 6 Duplicates: 0 Warnings: 0 3. Add columns referenced in CHILD_TBL alter table CHILD_TBL change pid pid bigint NOT NULL ; Query OK, 3 rows affected (0.29 sec) Records: 3 Duplicates: 0 Warnings: 0 4. If you do not modify the column, an error occurs when you add back the CONSTRAINT. ERROR 1005 (HY000): Can't create table 'test.#sql-72f5_330c7' (errno: 150). 5. Add original FK constraint to CHILD_TBL mysql> alter table CHILD_TBL ADD CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ; Query OK, 3 rows affected (0.12 sec) Records: 3 Duplicates: 0 Warnings: 0

When alter command finished, you should check the schema is changed to bigint.

CREATE TABLE `PARENT_TBL` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `value` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 CREATE TABLE `CHILD_TBL` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` bigint(20) NOT NULL, `cvalue` varchar(20) NOT NULL, PRIMARY KEY (`id`), KEY `fk_parent_tbl_id` (`pid`), CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

If you want to find more errors, see the following command:

mysql> show engine indoor status \G ... ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 150825 9:56:45 Error in foreign key constraint of table test/#sql-72f5_330c7: FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign key definition. ...