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:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[1 Jun 2005 18:09] Serdar S. Kacar
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.)
[1 Jun 2005 18:21] Serdar S. Kacar
Compressed version of the 3 files is above 200k file size limit. 
I can send them on request.
[2 Jun 2005 7:19] Vasily Kishkin
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 ?
[2 Jun 2005 10:01] Sergei Golubchik
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)
[2 Jun 2005 13:16] Serdar S. Kacar
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.
[3 Jun 2005 6:46] Aleksey Kishkin
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.
[3 Jun 2005 7:57] Serdar S. Kacar
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
[3 Jun 2005 12:32] Aleksey Kishkin
yes, I found the file you uploaded, thank you..
[21 Jun 2005 14:07] Serdar S. Kacar
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).
[14 Nov 2008 16:18] Serdar S. Kacar
outdated