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

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

i have mysql 5.5.37 running on development machine. utilize innodb tables. faced next problem - autoincrement resets after server restart.

found autoinc_lock_mode, set 0, did not helped.

show variables command shows value 0 autoinc_lock_mode.

what do:

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

then restart server... , .... (drum roll)

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

what wrong? :(

// upd

i have utilize myisam table. replies/comments.

in innodb, autoincrement value not part of table's metadata , reset on each server restart.

innodb uses next algorithm initialize auto-increment counter table t contains auto_increment column named ai_col: after server startup, first insert table t, innodb executes equivalent of statement:

select max(ai_col) t update

; innodb increments value retrieved statement , assigns column , auto-increment counter table. default, value incremented one. default can overridden auto_increment_increment configuration setting.

if table empty, innodb uses value 1. default can overridden auto_increment_offset configuration setting.

during server executable lifetime, it's stored in special construction not persisted between server restarts:

if specify auto_increment column innodb table, table handle in innodb info dictionary contains special counter called auto-increment counter used in assigning new values column. counter stored in main memory, not on disk.

the variable you're setting defines way construction accessed concurrently different sessions, not lifetime.

if want persist auto-increment value between server restarts, should save in table not subject transaction command (like myisam or archive) on each write , reset table on server restart.

mysql innodb auto-increment