Solution to Resetting the specified InnoDB auto_increment Value

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.