Auto_Increment fields being given a stupidly high value

[Rick]

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

[jofa]

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

[Rick]

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

[signlink]

// 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?

[Rick]

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