MySQL autoincrement problem

tags:

mysql
php

views:

answers:

+1 Q:

MySQL autoincrement problem

I have a table with autoincrement field, I have for example 1,2,3 values, when I insert 4 and delete it the next will normally be 5. I delete 5 and after a long time , say 1 week when I insert new record the next is again 4.

Can somebody tell me why this happen , why when I insert a new record after a long time its getting the last inserted id of this table, and it is not taking into consideration the deleted records.

PS: This happens just after a long time,it is working ok when I am doing it in a short period of time

+2 A:

The last auto increment value for a table column is only stored in memory. It's not written to a table or something else. So if you restart your server (i.e. for a backup) the last increment value (5) is lost and mysql scans the table for the the last value which exists in the table (4).

shod 2010-07-29 08:42:22

Yes, you are right, I didn't believe it and tested on localhost, and it is like this , thanks, but is there a solution for this problem to keep deleted ids after restarting the server?

Centurion 2010-07-29 09:01:04

I think a combination of mysql trigger, stored procedure and a table for the last id could be a solution. So that you build your own ai functionality. But I newer done this with mysql before.

shod 2010-07-29 09:44:23

ok, for those who wil met this issue, this is the problem of just InnoDB tables, so my simplest solution was to make a new field deleted, but this way you should go through all application and change stuff, other solutions could be some of shod, thanks again problem solved.By the way there is no solution for making some adjustments to the field or table itself yet, as i read documentation.

Centurion 2010-07-29 09:51:52

ansaurus

tags:

views:

answers:

MySQL autoincrement problem

related questions