Bug #57725 Memory Storage Engine not to keep the auto_increment value of table option

• Submitted: 26 Oct 2010 4:20
• Modified: 11 Nov 2010 0:13
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server: Memory storage engine
• Severity: S2 (Serious)
• Version: 5.1.51
• OS: Any
• CPU Architecture: Any
• Tags: auto_increment Memory

[26 Oct 2010 4:20] [ name withheld ]

Description:
When I create a table with AUTO_INCREMENT=1000 (just >1) and type=MEMORY options, the auto_increment field value was 1000 after I inserted a row into the table.

I stoped the MySQL daemon and restarted it. The table row count was 0. I inserted a row again. The auto_increment field value was 1, not the option value 1000.

How to repeat:
/* create a MEMORY type table */
CREATE TABLE `test` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(50) NOT NULL DEFAULT '0',
	`reg_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
)
ENGINE=MEMORY
AUTO_INCREMENT=1000;

/* insert a row */
INSERT INTO `test` (`title`) VALUES ('dfg');
/* the id field value is 1000 */

/* Now, stop the MySQL daemon and restart it. */

/* insert a row again */
INSERT INTO `test` (`title`) VALUES ('dfg');
/* the id field value is 1 */
/* and the "SHOW CREATE TABLE test" result was miss AUTO_INCREMENT option */

[11 Nov 2010 0:13] Sveta Smirnova

Thank you for the report.

This is not a bug. After server restart MEMORY table is truncated thus auto_increment reset.