auto increment skips

[oatesj77]

if i have a table with 12 (1-12) records and i delete the last two so there are only 10 (1-10) when i put a new record in it starts with the next original number "13" so the record would be 1-10, 13 rather than 1-11.

is there a way i can fix this.

here is the sql for my table;

CREATE TABLE `holga_data` (
`holgaID` int(11) NOT NULL auto_increment,
`holgaCat` varchar(225) collate latin1_general_ci NOT NULL default '',
`holgaTitle` tinytext collate latin1_general_ci NOT NULL,
`holgaLocation` tinytext collate latin1_general_ci,
`holgaCaption` text collate latin1_general_ci,
`holgaThumb` tinytext collate latin1_general_ci NOT NULL,
`holgaImage` tinytext collate latin1_general_ci NOT NULL,
PRIMARY KEY (`holgaID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=20 ;

[YvetteKuhns]

I am having a similar problem. It just started since the php upgrade.

[mixerson]

I have experienced this behavior as well and assumed that each table maintained some kind of "max auto_increment" value that never decreased, even when rows were deleted. However, I just looked it up in the MySQL docs and it appears that this behavior is dependent on the table type.If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused with an ISAM, or BDB table but not with a MyISAM or InnoDB table.If you need contiguous ID values and can't change your table type to ISAM or BDB, it looks like you'll have to write your own routine to find the first available ID value.

Coincidently, I had to do this for a client last week. They have a category table which contains only 50-100 items at one time. Each category has an ID, which is also used as an indexed column on another table which contains over 20,000 rows. I chose a column type of TINYINT for the category ID, since I know the number of categories will never approach 255. This makes the index on the large table smaller & faster, but I had to ditch the AUTO_INCREMENT property of the ID field, so repeated insertions and deletions wouldn't push the ID value beyond the range of TINYINT.

[YvetteKuhns]

if i have a table with 12 (1-12) records and i delete the last two so there are only 10 (1-10) when i put a new record in it starts with the next original number "13" so the record would be 1-10, 13 rather than 1-11.

If you delete the first two records, you will have 3 and up. If you try to insert data into 1 or 2, you will get a duplicate entry error. For your case, you would get the duplicate entry error for 11 and 12. Annoying, isn't it?

If you need contiguous ID values and can't change your table type to ISAM or BDB, it looks like you'll have to write your own routine to find the first available ID value.

Thanks for the tip!

Leona's travel site may have alot of properties, so maybe we can create a table to store the maximum id numbers and create a script to store the new maximum every time a new member or property is added. Then compare the maximum value to be sure it is not null (deleted). If it is null, we can compare the maximum value with max value minus 1 in a loop. If alot of values are deleted, then this may take a while, too. Hmm. Maybe autoincrement was not a good idea afterall.

[foofoo]

I'm pretty sure auto_increment was never really initially designed to work as a stack and more of a way to give a unique identifier to a row and the only way to certify that is by giving every new entry a value 1 larger than the last.

If you want a stack I'd recommend building an object that works like one and in the background maintain your stack with a database. Shouldn't be too hard.

YvetteKuhns: The way you suggested by having a table that has a "max_value" for each table where you pull from isn't a terrible idea. However, I think it would probably be a bit inefficient. My recommendation would be to just use mysql_num_rows() and do the math in php and insert your own unique identifiers into a tinyint or an int field. If you do your math right and you trust it you should never have to loop through and check for the newest max value.

This would be the way I'd write a stack object. The only assumption is that people aren't deleting from the middle of the stack. If you still need that functionality then let people delete from the middle and just create a cleanup script when they do that re-orders all your id's. Of course, this wouldn't be too bad inneficiency wise as the worst case is if someone deletes the first item and that only leaves you with a big-oh of n. No biggie.

[tbonekkt]

It just started since the php upgrade.A MySQL auto_increment shouldn't be affected by a php upgrade.

[YvetteKuhns]

A MySQL auto_increment shouldn't be affected by a php upgrade.

The auto_increment wasn't affected, but the database was corrupted, leaving missing records.

YvetteKuhns: The way you suggested by having a table that has a "max_value" for each table where you pull from isn't a terrible idea. However, I think it would probably be a bit inefficient.

Agreed and why it wasn't done.

The only assumption is that people aren't deleting from the middle of the stack. If you still need that functionality then let people delete from the middle and just create a cleanup script when they do that re-orders all your id's. Of course, this wouldn't be too bad inneficiency wise as the worst case is if someone deletes the first item and that only leaves you with a big-oh of n. No biggie.

The reorder of ids cannot be done because they are primary fields for several tables and that can cause problems. The image folder for each property is created and named using the property numbers. It would be confusing at best. I guess there will just be values that may be null.