This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

How to retain Auto Increment value on server restart InnoDB

Hello,

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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.