Solution to Resetting the specified InnoDB auto_increment Value

Sometimes the newly created table needs to specify the initial value for the auto-increment column, but sometimes the initial value explicitly specified is reset. Here is a small experiment to illustrate this problem:

MySQL version: 5.1.42 OS: redhat5.3

No nonsense. Create a table first:

Code
Create table 'sbtest1 '(
'Id' int (10) unsigned not null AUTO_INCREMENT,
'K' int (10) unsigned not null default '0 ',
'C' char (120) not null default '',
'Pad 'Char (60) not null default '',
Primary key ('id '),
KEY 'K' ('k ')
) ENGINE = InnoDB AUTO_INCREMENT = 20000000 default charset = latin1;

You can see that I have specified auto_increment = 20000000.

After the table is created, if data is inserted immediately after the table is created, this phenomenon will not occur:

Code
Mysql> insert into sbtest1 (k, pad) values (123, 'What are you doing ');
Query OK, 1 row affected (0.00 sec)

Mysql> select * from sbtest1;
+ ---------- + ----- + --- + -------------------- +
| Id | k | c | pad |
+ ---------- + ----- + --- + -------------------- +
| 20000000 | 123 | what are you doing |
+ ---------- + ----- + --- + -------------------- +
1 row in set (0.00 sec)

So when? Well, exactly what kind of operation will happen after resetting? Test:

Code
Create table 'sbtest1 '(
'Id' int (10) unsigned not null AUTO_INCREMENT,
'K' int (10) unsigned not null default '0 ',
'C' char (120) not null default '',
'Pad 'Char (60) not null default '',
Primary key ('id '),
KEY 'K' ('k ')
ENGINE = InnoDB AUTO_INCREMENT = 20000000 default charset = latin1

Mysql> quit
Bye
[Root @ test_2 ~] # Service mysqld restart
Mysql> show create table sbtest1 \ G
* *************************** 1. row ***************************
Table: sbtest1
Create Table: create table 'sbtest1 '(
'Id' int (10) unsigned not null AUTO_INCREMENT,
'K' int (10) unsigned not null default '0 ',
'C' char (120) not null default '',
'Pad 'Char (60) not null default '',
Primary key ('id '),
KEY 'K' ('k ')
) ENGINE = InnoDB default charset = latin1
1 row in set (0.00 sec)

The value of auto_increment is reset!

Through the above small experiments, we can use the manual to understand why this phenomenon occurs:

If auto_increment is specified for an innodb table, innodb maintains an auto_increment counter for it in data dictionary. Note: This counter is only stored in memory and not written on disk.
After innodb is restarted, how does one initialize the counter (described in this table in the experiment )?
After the database is restarted, InnoDB performs the following operations on the table (sbtest1) that has specified auto_increment:

Select max (id) FROM sbtest1 for update;
Then, the value + 1 obtained by this statement is assigned to the field of the specified auto_increment and the counter of the table in memory. If the table is empty, the value will be 1.

Solution:
1. After the table is rebuilt, do not restart before inserting data (to put it bluntly, ensure that the data in the memory will not be released)
2. Insert a dirty data record