#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    11
    Rep Power
    0
    I'm having a strange problem. I have a database with an auto_increment field. I "seeded" the count after a few hundred, so the numbers go up until a few hundred, then skip up to 750000. The numbers have climbed to around 764000, but every so often a number will be assigned out of order. For example, a record id of 759292 was given even though the highest number was over 764000!

    Any idea why this is happening? It's quite disconcerting when things do not perform as expected, especially when there is no apparent cause!

  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,640
    Rep Power
    4476
    If you have your table set up correctly, this shouldn't matter. The auto_increment column should be used for a unique id for each record, nothing else. You shouldn't depend on it for find the last record inserted (the highest id) because that's not always true. You should have a date or timestamp column for this. How exactly is this causing trouble, or does it just bother you?

    ---John Holmes...

    ------------------
    *************************************************************
    * The manual can probably answer 90% of your questions...
    *
    * PHP Manual. www.php.net/manual
    * MySQL Manual: www.mysql.com/documentation/mysql/bychapter
    *************************************************************
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    11
    Rep Power
    0
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by SepodatiCreations:
    You shouldn't depend on it for find the last record inserted (the highest id) because that's not always true. You should have a date or timestamp column for this. How exactly is this causing trouble, or does it just bother you?
    [/B][/quote]

    Actually I am using it to generate invoice numbers so it is quite a bother. It's disconcerting when an invoice number appears out of sequence. I guess I will need to find another solution to generation invoice numbers, such as having another table with the explicit purpose of generating invoice numbers.

    According to the manual, I thought that auto_increment was failsafe.

    Hm...

    Thank you for your response.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    11
    Rep Power
    0
    You know, I just realized that this is not an explanation for what happened since records from this table are never deleted!!

    This makes the problem even more disconcerting since it seems to me that records are being overwritten!

    Do you have any possible explanation for this...? I am at a dead end.


  8. #5
  9. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,640
    Rep Power
    4476
    So you're saying that you'll have a row with id number = 123456 at one time, then for some reason the id is updated to another number? Are you doing inserts or updates?

    All I can say is that this shouldn't happen with regular inserts and auto_increment columns. There must be something in your code that changes the numbers, maybe? What version of MySQL are you using? If it's an older version, maybe there is a bug?

    ---John Holmes...

Similar Threads

  1. Replies: 1
    Last Post: February 6th, 2004, 05:53 AM
  2. Inserting from form to multiple tables
    By barth in forum MySQL Help
    Replies: 0
    Last Post: January 25th, 2004, 09:27 AM
  3. put variable in text field
    By alexmasters in forum PHP Development
    Replies: 3
    Last Post: January 14th, 2004, 07:32 PM
  4. return value from 3 fields into 1 field
    By denali in forum MS SQL Development
    Replies: 1
    Last Post: December 19th, 2003, 07:44 AM
  5. Validating a field with multiple criteria...
    By brotherloco in forum Java Help
    Replies: 0
    Last Post: October 29th, 2003, 07:40 AM

IMN logo majestic logo threadwatch logo seochat tools logo