0

MySql keeps the AUTO_INCREMENT value in memory and whenever mysql gets restarted it gets lost, So mysql reads the maximum value from table and set the auto_increment to the next available id. If i have a table with ID: 1,2,3,4,5,6,7 and i delete ID 6,7 from the table. So now i have ID 1,2,3,4,5.
Now if i insert another entry into this table it will set the next ID:
8 (if insert without server restart)
6 (If insert after mysql restart). My question is, what is a good way to persist the auto_increment value so even after restarting mysql, i get next ID as 8.

Manoj-kr
  • 776
  • 5
  • 18
  • @RiggsFolly what are you sure it does not? Save the `auto_increment` in memory? – Mjh Jul 14 '17 at 12:43
  • I will ask again. This does not happen in MYISAM or INNODB databases SO, what type of database are you using???? – RiggsFolly Jul 14 '17 at 12:53
  • @RiggsFolly - for MariaDB auto_increment persists for InnoDB, for Percona - I've no clue and for MySQL 5.5 (haven't tested above) it performs recalc like per OP's post. MyISAM seems to keep this value saved with table metadata, while InnoDB does not. OP hasn't wrote anything so far, but he probably wouldn't ask this question had he not have this problem occur, right? – Mjh Jul 14 '17 at 12:56
  • @RiggsFolly I'm seeing the same as Mjh on mariabd innodb.(apologies for the me too) – P.Salmon Jul 14 '17 at 13:03
  • Well I never noticed that before. Then I have not used V5.5 in some time, and I dont think I ever used INNODB in 5.5. So fair enough Apologise all round. – RiggsFolly Jul 14 '17 at 13:08
  • Another good reason to keep your MySQL updated – RiggsFolly Jul 14 '17 at 13:08
  • Sorry guys, my system got into some trouble so could not respond quickly. @RiggsFolly, i faced this issue with mysql 5.6 with innodb, even in mysql 5.7 issue exists. i guess that is enough updated version ;) – Manoj-kr Jul 14 '17 at 13:13
  • Hi Money, which version of 5.7 I tested it on 5.7.10 and it did not happen – RiggsFolly Jul 14 '17 at 13:16
  • @RiggsFolly: 5.7.12 – Manoj-kr Jul 14 '17 at 13:19
  • Yea it does it on 5.7.18 as well. – RiggsFolly Jul 14 '17 at 14:25
  • `In MySQL 5.7 and earlier, the auto-increment counter is stored only in main memory, not on disk. To initialize an auto-increment counter after a server restart, InnoDB would execute the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column. SELECT MAX(ai_col) FROM table_name FOR UPDATE;`. See [MySQL 8.0 :: 15.8.1.5 AUTO_INCREMENT Handling in InnoDB :: InnoDB AUTO_INCREMENT Counter Initialization](https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization). – wchiquito Jul 14 '17 at 20:30
  • From MariaDB 10.2.4: Persistent AUTO_INCREMENT for InnoDB/XtraDB. See [AUTO_INCREMENT :: InnoDB/XtraDB](https://mariadb.com/kb/en/mariadb/auto_increment/#innodbxtradb) and [MariaDB 10.2.4 Release Notes :: Persistent AUTO_INCREMENT for InnoDB (MDEV-6076)](https://mariadb.com/kb/en/mariadb/mariadb-1024-release-notes/). – wchiquito Jul 14 '17 at 21:00

0 Answers0