SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Wizard Rick's Avatar
    Join Date
    Oct 2002
    Location
    Lancashire, UK
    Posts
    3,847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Auto_Increment fields being given a stupidly high value

    I'm having a problem with auto_increment fields in a database, new entries are given a stupidly high value.

    I.E. The entries jump from 28 (entered on the old server) to 2147483647 on the new server

    As far as I can tell the new rows being sent to the database have the value 'null' for the user_id (auto_increment) coloum, and all I have really done to the table is delete a few rows and change the value of one of the rows user_id (auto_increment) coloum manually - none of this has ever caused me a problem before, so I am pretty much stumped on why this is happening.

    phpMyAdmin is telling me that the next value for the Autoindex will be 2,147,483,647 which seems a little odd to me considering the table has 26 rows.

    Sorry if I havn't been clear / posted all the info - but its late and am suffering from lack of sleep

    Thanks...
    ZOO
    Rick

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looks like mysql thinks -1 was the latest user_id, which could explain 0 (or 2147483647) being the next one?

    Tried this?
    http://www.mysql.com/doc/en/CHECK_TABLE.html
    http://www.mysql.com/doc/en/REPAIR_TABLE.html

  3. #3
    SitePoint Wizard Rick's Avatar
    Join Date
    Oct 2002
    Location
    Lancashire, UK
    Posts
    3,847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well as far as I can remeber there was a row with the user_id -1 (added by the forums to handle annon posting) but I deleted it after the move.

    If that is the cause of the problem what's the best way to fix it - last time I used Repair_table I corrupted most of the rows and Nicky won't be too happy with me if I corrupt half of her forum users profile and user data

    Cheers
    ZOO
    Rick

  4. #4
    Linux Junkie signlink's Avatar
    Join Date
    Feb 2003
    Location
    Annapolis
    Posts
    195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    // can you just trtanslate them by hand -

    <?php
    $db = new DataBase;
    $db->Connect();
    // use a class to connect here
    $i = 2147183whatever;
    while (p=0;p<27;p++) {
    $id = $p + $i;
    $sql = "
    UPDATE yourdb.yourtable SET id = $p where id = $id
    ";
    mysql_query($sql);
    }
    ?>
    // Something like that?

  5. #5
    SitePoint Wizard Rick's Avatar
    Join Date
    Oct 2002
    Location
    Lancashire, UK
    Posts
    3,847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but the problem isn't updating them - there are only one or two entires causing trouble, the problem is that new rows are being enterd with stupidly high values - I need a way to tell MySQL to enter new rows with a sensible value...

    ...if that makes any sense

    Cheers
    Rick
    Rick


Bookmarks

Posting Permissions

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