How to make InnoDB table not reset autoincrement on server restart?

Question

I have MySQL 5.5.37 running on my development machine. I use innodb tables. Faced next problem - autoincrement resets after server restart.

Found autoinc_lock_mode, set to 0, but did not helped.

SHOW VARIABLES command shows value 0 for autoinc_lock_mode.

What I do:

select max(id) from tablex; // 11, autoincrement is 12
insert into tablex values ('foo');
select max(id) from tablex; // 12, autoincrement is 13
delete from tablex where id > 11; // autoincrement is 13

Then I restart server... and .... (drum roll)

show create table tablex; // autoincrement 12 instead of 13

WHAT I DO WRONG? :(

// UPD

I have to use MyISAM table. Thanks you all for replies/comments.

Answer 1

In InnoDB, the autoincrement value is not part of the table's metadata and is reset on each server restart.

InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:

SELECT MAX(ai_col) FROM t FOR UPDATE

; InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by one. This default can be overridden by the auto_increment_increment configuration setting.

If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.

During the server executable lifetime, it's stored in a special structure but not persisted between server restarts:

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

The variable you're setting defines the way this structure is accessed concurrently by different sessions, not its lifetime.

If you want to persist an auto-increment value between the server restarts, you should save it in a table not subject to transaction control (like MyISAM or Archive) on each write and reset it from the table on server restart.