2

I have the following code:

ALTER TABLE users MODIFY id int unsigned;

Which throws the following error:

ERROR 1025 (HY000) at line 114: Error on rename of './mydb/#sql-146dd_1d' to './mydb/users' (errno: 150)

I'm fairly certain this is because of a foreign key issue, but I'd like to find more details on the error.

I seem to remember that there was something I could enter into the mySQL command line that would display a list of the most recent errors for various types of issues (including foreign key problems) along with more detail on the exact cause (like the tables involved). Does anyone know what i am talking about?

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • @MikeBrant, no that's not what I'm looking for. The thing I'm talking about showed the last foreign key error that I had as well as the tables involved. – Abe Miessler May 06 '13 at 19:33
  • Sure you're not thinking of "show error" in Oracle SQL*Plus? – criticalfix May 06 '13 at 19:40
  • 1
    Search the error number using your favorite search engine? – Salman A May 06 '13 at 19:41
  • Here the answer says http://stackoverflow.com/questions/160233/what-does-mysql-error-1025-hy000-error-on-rename-of-foo-errorno-150-me : "you can use SHOW ENGINE INNODB STATUS and look for the LATEST FOREIGN KEY ERROR section which should contain details about what is wrong" isn't that what you are looking for? – hol May 06 '13 at 19:42
  • @AbeMiessler Sorry I thought you just wanted confirmation that it was a foreign key issue, which it is. I have added answer below that might be what you are looking for. – Mike Brant May 06 '13 at 19:42
  • 1
    @SalmanA, did you even read my question? Specifically this part: `...along with more detail on the exact cause (like the tables involved).` – Abe Miessler May 06 '13 at 20:48

2 Answers2

4

I think what you are looking for is the SHOW ENGINE INNODB STATUS command (I'm assuming the innodb engine) which provides the last foreign key error.

mysql> CREATE TABLE `T1`(`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB; 
mysql> CREATE TABLE `T2` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `refer_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `refer_id` (`refer_id`), CONSTRAINT `t2_id_to_t1_id` FOREIGN KEY (`refer_id`) REFERENCES `T1`.`id` ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'TEMP.T2' (errno: 150)

mysql>  SHOW ENGINE INNODB STATUS;
[...]
------------------------
LATEST FOREIGN KEY ERROR
------------------------
130506 15:50:41 Error in foreign key constraint of table TEMP/T2: FOREIGN KEY (`refer_id`) REFERENCES `T1`.`id` ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB: Cannot resolve table name close to: ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB
[...]

mysql> CREATE TABLE `T2` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `refer_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `refer_id` (`refer_id`), CONSTRAINT `t2_id_to_t1_id` FOREIGN KEY (`refer_id`) REFERENCES `T1`(`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

I've purposely inserted an error into table T2 creation statement to demonstrate the output on foreign key error (in this case schema resolution).

Charles
  • 90
  • 1
  • 6
0

You might be looking for SHOW WARNINGS.

MySQL Documentation

Mike Brant
  • 70,514
  • 10
  • 99
  • 103