[SOLVED] auto_increment issue after record delete

[phpdragon]

MySQL ver 4.1.22, using phpmyadmin 2.10.0.2

 

I have a table with auto_increment loginID, I had 33 records which store (loginID,userID,ipAddress,loginTime,logoutTime), which were created while testing, I used phpmyadmin selected records 1-30 and use the delete tool, leaving records 31-33, table listed as follows

 

31

32

33

 

I have now added some more records 34-37 by using the pages that generate them but now the auto_increment is messed up. It still seems to assign the correct value but the order in which it is listing now is weird eg

 

loginID(auto_increment)

37

36

35

34

31

32

33

 

Can someone help me workout what is going on with this please, very much appreciated.

[kickstart]

Hi

 

The order records are listed is largely random unless you specify an order.

 

Best not to rely on any default order.

 

All the best

 

Keith

[phpdragon]

this is the order phpmyAdmin displays them now, usually they are listed in order of the rows.

 

I had another issue earlier today with the users table, whch was a copied table with 6 existing users already in it, to which I deleted the last 4 making the next entry autoID still 7. This happened and this was fine but the table also started to display weird in phpmyAdmin with the rows out of order, I had not noticed auto_increment feilds behave like this before in phpmyAdmin, when I called the userID 1 in a script and then assigned it to a session it would assign the session the value of 7 for the ID, this happend for record 2 which I also left behind, and this also got assigned the value 0f 7 in the session, but all other newly created users got assigned their correct ID as session ID using exactly the same script. For some reason I had to change userID in the script to usersID and the problem stopped, very strange.

 

So you beleive I have nothing to worry about with the data integrity then and the table should continue to insert and delete as normal?

 

cheers for you help

 

[revraz]

I think you have some bad code or your design of the table is wrong.

[kickstart]

this is the order phpmyAdmin displays them now, usually they are listed in order of the rows.

 

Yes, but it could display them in any order at all. The order is not defined, and relying on a default order is likely to cause problems.

 

So you beleive I have nothing to worry about with the data integrity then and the table should continue to insert and delete as normal?

 

Nope. Shouldn't be anything to worry about.

 

All the best

 

Keith