duplicate entry '0' for key 1 in mysql

Question

I had a table field with datatype as 'smallint' (primary key) and it was auto_increment. It was working fine.

After a long time, I got the error duplicate entry '32676' for key 1. So I updated that field from smallint(6) to int(11). Now I got the error duplicate entry '0' for key 1.

I am using InnoDB engine.

What specifically can I do to resolve this?

Answer 1

That's because probably table counter was reset to zero, so next item is added with 0, an existing key!!
You could try to use

ALTER TABLE your_table AUTO_INCREMENT=32677