Having a little trouble with PHP/MySQL duplicate auto-increment values

[georgiek50]

I wrote some code to store some values in a table to dynamically display ads on the right column of my site. This code allows you to organize their order, remove, update, etc... In order to do this, everytime there is a change made to the layout, I first delete all the records for the current page then I re-insert everything.

The problem is, that on rare occassions this fails because the auto-increment value of the primary key eg. 127, is already in the table taken by another record. I should mention that the id field (primary key, auto-increment) is never manually inserted, I always pass a blank value for auto-increment to take care of it.

Is this some kind of MySQL bug, or could it have something to do with my DELETE before inserting again?

[jatar_k]

why do you delete first why not just update the row?

[georgiek50]

I delete first because this code also gives the option to get rid of ads, it's a bit complicated (and not the best written code I must say) but I figured out my stupidity...After 3 years of C++ programming you would think that I would make sure not to assign id as a signed tiny int since I would be deleting/updating all the time...sometimes I can be so careless!

Thanks for your reply

[coopster]

What kind of table is it? ISAM, MyISAM, BDB, InnoDb...? And what version of MySQL?

My guess is that you are successful whenever you are deleting all the rows in your table, otherwise it should fail...

If you delete the row containing the maximum value for an

AUTO_INCREMENT

column, the value will be reused...If you delete all rows in the table with

DELETE FROM tbl_name

(without a

WHERE

) in

AUTOCOMMIT

mode, the sequence starts over...

Resource: CREATE TABLE [mysql.com]

>>Is this some kind of MySQL bug...

No. MySQL will set it to the next sequence value. Typically this is

value+1

, where

value

is the largest value for the column currently in the table.

Have you considered changing the column from type

TINYINT

to

INT

? ......and I would also make it

SIGNED

.

[georgiek50]

Yes, that's exactly what was needed...I caught myself after I hit the submit button...number 127, the highest value of an unsigned tiny-int. Thanks.