2

I'm trying to add ZEROFILL to an auto-incremented primary ID field in a MySQL database. Here is the code (auto-generated by MySQL Workbench):

ALTER TABLE `database`.`table` CHANGE COLUMN `id` `id` INT(11) ZEROFILL NOT NULL AUTO_INCREMENT

This is the error I get:

Error Code: 1025. Error on rename of './database/#sql-2c8_cb' to './database/table' (errno: 150)

It appears that a temp table has been created and when the error occurs when the temp table is renamed with the original table name.

Any help would be great!

David Jones
  • 10,117
  • 28
  • 91
  • 139
  • Why add the zerofill attribute? MySQL will manage the ids for you automagically. – Mike Purcell Jun 25 '12 at 20:50
  • Do you have any foreign key constraints on this column? – Kermit Jun 25 '12 at 20:50
  • @njk: there are three other tables with foreign keys that reference this table. All are set with ON UPDATE CASCADE. – David Jones Jun 25 '12 at 20:52
  • If using InnoDB, check the status monitor (SHOW ENGINE INNODB STATUS) right after you execute the alter. It should tell you if it's because of the FK constraint. You may need to drop the constraints, alter the column and create them again. – Kermit Jun 25 '12 at 20:54
  • @MikePurcell: Each database entry has an associated data file in the file system. The name of each file is simply the field ID. I occasionally need to find a file manually and it's difficult to find things when files are not listed in numerical order. – David Jones Jun 25 '12 at 20:55
  • @njk: I'm not getting any output from SHOW ENGINE INNODB STATUS – David Jones Jun 25 '12 at 21:01
  • @danielfaraday: Interesting. Never seen zerofill used as an attribute on PK column, not sure it will even work. – Mike Purcell Jun 25 '12 at 21:01
  • @njk: Ah, nevermind. SHOW ENGINE INNODB STATUS gives plenty of output - I was just doing it wrong. It looks like it was indeed a foreign key problem. I don't understand why this is a problem, since I've set the foreign keys to update automatically. Oh well - it looks like I'll have to drop the constraints first. – David Jones Jun 25 '12 at 21:07

1 Answers1

0

If using InnoDB, check the status monitor (SHOW ENGINE INNODB STATUS) right after you execute the alter. It should tell you if it's because of the FK constraint. You may need to drop the constraints, alter the column and create them again.

Kermit
  • 33,827
  • 13
  • 85
  • 121