Fixing a MySQL Error After Adding an Auto-Increment Index

I have a MySQL table and need to add auto-increment to the primary key. It’s easy to find a command like this:

alter table `table_name` 
change column `id` 
`id` int(10) unsigned auto_increment not null ;

However, the code that had always worked failed this time with the following error:

ERROR 1062: ALTER TABLE causes auto_increment resequencing, 
resulting in duplicate entry '1' for key 'PRIMARY'

The top answer in this Stack Overflow question basically says they don’t know why either; they only offer a workaround—abandon the original id and recreate it with auto_increment. But that’s useless: the primary keys in related databases would all be changed, so the whole thing goes to hell.

The second answer says:

This will happen if the table contains an existing record with an id of 0 (or negative). Updating all existing records to use positive values will auto_increment to be set on that column.

So just find the rows where id <= 0 and fix them.