Duplicate entry '0' for key 1 in mysql

Question

I had a table field with a data type as 'smallint' (primary key), and that was auto_increment . It worked fine.

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

I am using the InnoDB engine.

What exactly can I do to solve this problem?

Answer 1

This is because, probably, the table counter was reset to zero, so the next element is added with 0, the existing key!
You can try using

 ALTER TABLE your_table AUTO_INCREMENT=32677