Getting duplicate primary key for auto increment column

Question

I have a MySQL Innodb table with 'id' as auto increment primary key column.

My Scripts are doing following operations:

1. insert ignore into tablename set x='a', y='b'
2. update ignore tablename set x='a', y='b' where id = 'smthing'
3. delete from tablename where id = 'smthing'

Now after running the script when I insert something manually (insert into tablename set x='ax', y='bx') I get the following error:

Duplicate Entry for Auto Increment Value. 

On check I see that Auto Increment is trying to put the highest possible value of int that MySql will support as new value even and it already exist in db.

The table has two index: id+x, id+y though idk if this is related to this issue.

• onedaywhen: Is your title a true statement? We used to get similar reports for MS Access but in reality it would turn out that in order to generate the duplicate values the system had silently dropped the primary key constraint. If it is true that you have duplicate columns within a column with a PK constraint still in place then that is a very worrying bug in mySQL.

Answer 1

I reached the max possible value of an int column. Problem resolved by altering table and setting big int than int.

The signed range of MIDINT is –8388608 to 8388607. The unsigned range is 0 to 16777215

Reference: http://help.scibit.com/mascon/masconMySQL_Field_Types.html

• ypercubeᵀᴹ: How is that in terms with your "Auto Increment is trying to put 765 as new value" ?
• dragosrsupercool: For example a took a simple value (765). The actual value was 8388607