auto_increment value increased from 20 to 32 when I inserted a new row.

Below is the definition of the table with the problem.

CREATE TABLE `series` (
`series_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL,
PRIMARY KEY (`series_id`)
) ENGINE=InnoDB

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.

This leap seems to happen on every table.

What is the cause of the leap in auto_increment?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: auto_increment value increased from 20 to 32 when I inserted a new row.

----- Original Message -----
> From: "crocket"
>
> 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org