Bug #42566 Failed to read auto-increment value from storage engine

• Submitted: 3 Feb 2009 12:18
• Modified: 5 Feb 2009 6:36
• Reporter: white phoenix
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.1.30
• OS: FreeBSD
• CPU Architecture: Any
• Tags: autoinc, innodb

[3 Feb 2009 12:18] white phoenix

Description:
I've converted existing MyISAM table to InnoDB and got error "Failed to read auto-increment value from storage engine". However, after query "OPTIMIZE TABLE `xE_users` ", autoinc has written and INSERT is working normally.

How to repeat:
CREATE TABLE IF NOT EXISTS `xE_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `groupid` int(11) DEFAULT '0',
  `username` varchar(100) NOT NULL DEFAULT '',
  `password` varchar(32) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `active` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `groupid` (`groupid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Users and passwords' AUTO_INCREMENT=1922 ;

// there are 1902 rows in xE_users

ALTER TABLE `xE_users`  ENGINE = InnoDB ;

 INSERT INTO `devproject`.`xE_users` (
`id` ,
`groupid` ,
`username` ,
`password` ,
`email` ,
`active`
)
VALUES (
NULL , '0', '', '', '', '0'
) 

#1467 - Failed to read auto-increment value from storage engine

[3 Feb 2009 19:33] Sveta Smirnova

Thank you for the report.

I can not repeat described behavior.

Did you try to ALTER after OPTIMIZE TABLE? Did you run CHECK TABLE? Was MyISAM table created in MySQL version before 5.1.30?

[4 Feb 2009 13:41] white phoenix

Just execute this dump 
http://quicky-tpl.net/files/mysqlbug42566.sql.txt

mysql> INSERT INTO `bug42566`.`xE_users` (`id`, `groupid`, `username`, `password`, `email`, `active` ) VALUES (NULL , '0', '', '', '', '0');
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.30-log |
+------------+
1 row in set (0.01 sec)

[4 Feb 2009 13:51] white phoenix

(!) This error caused by first row with id = -1.

[5 Feb 2009 6:36] Sveta Smirnova

Thank you for the feedback.

This is duplicate of bug #36411 which does not exist in version 5.1.31. Please wait when 5.1.31 is released.