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

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

    Hi,

    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.

    -G
    ----
    system:
    Mac Powerbook 1GHz

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @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
  •