Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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.

share|improve this question
2  
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. –  onedaywhen Mar 1 '12 at 8:50

1 Answer 1

up vote 1 down vote accepted

I reached the max possible value of an int coloumn. 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

share|improve this answer
    
How is that in terms with your "Auto Increment is trying to put 765 as new value" ? –  ypercube Mar 1 '12 at 9:47
    
For example a took a simple value (765). The actual value was 8388607 –  dragosrsupercool Mar 3 '12 at 15:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.