MySQL Bugs: #11021: "Dublicate Entry '#####' for key 1" when it should not occur

Bug #11021	"Dublicate Entry '#####' for key 1" when it should not occur
Submitted:	1 Jun 2005 18:09	Modified:	14 Nov 2008 16:18
Reporter:	Serdar S. Kacar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	3.23 to current	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Occurance of this error is extremely rare. But it is annoying.

Table is a fixed-row-length MyISAM table with an integer, auto-incremented primary key. Like;

CREATE TABLE `main` (
  `ID` int(11) NOT NULL auto_increment,
  `START` datetime NOT NULL default '0000-00-00 00:00:00',
  `DURATION` int(11) NOT NULL default '0',
  `ATINYINT` tinyint(4) NOT NULL default '0',
  `UPDATE_TIME` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ABIGINT` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `IDX_MAIN_BY_START` (`START`)
) ENGINE=MyISAM DEFAULT CHARSET=latin5;

Insert queries issued does not involve the auto-incremented primary key field(ID.)

Symptoms:
Say this error reads "Duplicate entry '92258' for key 1".
When a "select * from main" query issued, maximum ID is observed as 92257.
When a "select max(ID) from main" query issued, return value is again 92257.
Trying to insert a row from another connection returns the same error.
myisamchk fix the problem.

How to repeat:
Since its seldom and has no known pattern of occurance, only thing I can do is attaching .FRM, .MYD and .MYI files in this state.

Those files belong to MySQL Server version 4.1.11.

Suggested fix:
If an auto-incremented field which has not explicitly set to value in the insert query cause such an error, MySQL Database Engine may correct itself immediately without myisamchk (and hence user intervention.)

Compressed version of the 3 files is above 200k file size limit. 
I can send them on request.

I could't reproduce this bug. Probably you need to send me your data files. Could you please send that data to my e-mail address ?

Even better would be to upload the tarball (or zip archive) to
ftp://ftp.mysql.com/pub/mysql/upload/

(be sure bug number is clear from the filename, e.g. bug-11021.zip)

It is now at ftp://ftp.mysql.com/pub/mysql/upload/bug-11021.rar

After pondering on the situation, I guess it is something to do with inproper shutdown in the mid of an insert operation. That explains its seldomness. 
And the situation might be "by design." 
Then, may be, setting "on start, auto-check tables after inproper shutdown" parameter -if there is one- is enough.

hmm. cannot download it (for verifying).  Did you check if index files is OK?
it can be done by command

check table your_table_name;

from mysql console client utility.

No, index files are not OK. Errors retuned are
- Found key at page 2095104 that points to record outside datafile, and
- Currupt.

Files are also available on request - bug-11021.rar , 1.28 MB. Send request mail to the following address (clear spaces):  sskacar _ d@hotmail.com

yes, I found the file you uploaded, thank you..

Found that MySQL has already a solution to the problem. Simply,
- Locate your My.ini file
- Locate [mysqld] section
- Insert the following (you can change BACKUP,FORCE options):
# Automatically check and repair not properly closed MyISAM tables.
myisam_recover=BACKUP,FORCE

I wish that was the default configuration. Rarity of the problem makes it easily overlooked.

It is better make this submission's severity S4 (Feature Request).

outdated