List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:September 20 2011 6:12am
Subject:Re: auto_increment value increased from 20 to 32 when I inserted a new row.
View as plain text  
----- Original Message -----
> From: "crocket" <crockabiscuit@stripped>
> 
> I had 19 rows in series table. And when I tried inserting the 20th
> row, the auto_increment value suddenly increased from 20 to 32, and
> the new row has 20 as series_id.

The first thing that comes to mind, is transactions that insert, but then rollback - the
autoincrement counter gets increased immediately so that other transactions don't risk
doubles, but it never gets decreased because that would be relatively complicated and the
overhead is mostly useless anyway. Don't assume autoincrements never have gaps; don't even
assume they're always assigned in sequence - especially if you're using transactions.
Optimize table will also reset the autoincrement counter and reuse the gaps, iirc.

Autoincrements are really just a convenience for automatic primary key generation, nothing
more is implied or guaranteed.



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Thread
auto_increment value increased from 20 to 32 when I inserted a new row.crocket20 Sep
  • Re: auto_increment value increased from 20 to 32 when I inserted a new row.Johan De Meersman20 Sep