How to retain Auto Increment value on server restart InnoDB
We have a table schema as described below and we are setting auto increment value to 10.
CREATE TABLE `tasks` ( `tid` bigint(20) NOT NULL auto_increment, `jid` bigint(20) NOT NULL default 0, PRIMARY KEY (`tid`), ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
But value always starts from 1. As per the description provided in MySQL portal, "InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB re initializes the counter for each table for the first INSERT to the table, as described earlier."
So even if we set the auto increment to 10 using alter table command, once mariadb is restarted value reset to 1.
However I could not find any concrete solution as to how auto increment value can be retained.
It will be of great help if someone can share the workaround to avoid this issue, such that in between the server restart auto increment value can be retained.
Answer Answered by Ian Gilfillan in this comment.
From MariaDB 10.2.4, AUTO_INCREMENT is now persistent.