Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Nottingham, UK

    Question Unanswered: auto-increment primary key jumped by over 2 million!


    I'm wondering why the auto-increment primary key values in a mysql database table have jumped from 949 to 2147483648. That's a jump of over 2 million! Has anyone ever seen this happen?

    I had to change the datatype from int(9) to bigint(20) just so the database could make new records in the table. Before I changed the datatype it was truncating the new value and then trying to generate an identically truncated value for the next primary key, thus causing a database error and drawing my attention. This has wreaked a good deal of havoc in my database, as the values are also a foreign key in another table, whose datatype I also realized had to be changed.

    Thanks in advance for any light you may be able to shed onto this mysterious occurrence.

    Mac Powerbook 1GHz

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    probably what happened is that somebody/something inserted a negative value into the key

    if it were me, i would have fixed the data error instead of altering the datatype

    plus, there's no guarantee that the same underlying problem won't screw up your BIGINT values, and then you're cooked | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts