Adding zerofill to existing table

mysql zerofill
David Jones picture David Jones · Jun 25, 2012 · Viewed 8.1k times · Source

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!

Answer

Kermit picture Kermit · Jun 26, 2012

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.

Related questions

leading zeros in mysql zerofill int field not showing when queried

I have a table with auto increment zerofill ID numbers. When I query the data the IDs lose their leading zeros (i.e. "000529" returns as "529"). Is there a way to preserve the leading zeros, or even generate them back in …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More