MySQL Bugs: #39670: Error on rename (errno: 121) from ALTER TABLE ADD KEY in concurrent test

Bug #39670	Error on rename (errno: 121) from ALTER TABLE ADD KEY in concurrent test
Submitted:	26 Sep 2008 16:22	Modified:	23 Feb 0:16
Reporter:	John H. Embretsen
Status:	Patch queued
Category:	Server: Falcon	Severity:	S3 (Non-critical)
Version:	6.0-falcon-team (6.0.8a)	OS:	Any
Assigned to:	Christopher Powers	Target Version:	6.0-beta
Tags:	F_ONLINE ALTER, pushbuild, pb2
Triage:	Triaged: D2 (Serious)

Description:
The output from the "randomly" generated falcon_online_alter test in "Pushbuild 2"
contains some weird error messages related to statements of the kind 

ALTER TABLE x ADD KEY y

The error messages appear e.g. when there are roughly 10 concurrent threads running a few
thousand queries each.

Example:
# 09:47:46 Query: ALTER TABLE `BB` ADD KEY m ( `time_key` , `datetime_key` ) , ADD KEY b
( `pk` , `time_nokey` ) failed: 1025 Error on rename of './test/BB' to
'./test/#sql2-36bc-a' (errno: 121)

MySQL error code 121 is "Duplicate key on write or update".

Similar bugs:
Bug#35913 - Failed ALTER table leaves orphaned temporary files (Bug category Partition)
Bug#38041 - Bizarre errors when ALTER ADD/DROP KEY on Falcon tables (Supposed to be fixed
already)

How to repeat:
Branch or checkout the mysql-test-extra-6.0 bazaar tree and run:

$ cd mysql-test/gentest
$ runall.pl \ 
  --basedir=/path/to/mysql-6.0-falcon-team \ 
  --engine=Falcon \ 
  --grammar=conf/falcon_online_alter.yy \ 
  --threads=10 \ 
  --queries=10000

The error may not apply to the current ALTER, but may come from some previous ALTER in the
same connection. See bug #39132 which may explain why ALTER KEY would get a "duplicate
key" error.

Chris, This seems to be associated with Online Add Index.  Let me know if it is something
else.

Possibly related: Bug#39132, Bug#28178

The index mapping between Falcon and the server was resolved in Bug#42099. See the bug
report for a detailed explanation.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63274

2957 Christopher Powers	2009-01-14
      Bug #42099 Falcon: Online ALTER add/drop primary key
      
      - Implemented online alter add/drop primary key
      - Improved index mapping between server and Falon

Philip Stoev reports that neither errno: 121 nor any other suspicious errors have occurred
for the last 25 runs of falcon_online_alter.

In the last comment to this bug, Chris wrote that no suspicious errors were seen in the
falcon_online_alter test for a while a few months ago.

Looking at current test logs, I would count the following error as suspicious:

ALTER  TABLE `cc` DROP KEY q failed: 7 Error on rename of '.\test\#sql-10c0_14.frm' to
'.\test\cc.frm' (Errcode: 13)

(OS error code 13 is "Permission denied")

Seen in logs from falcon_online_alter test on Windows in PB2 for mysql-6.0-falcon-team
branch in July 2009.

I'll consider opening a new bug for this error message.

Opened Bug#46299 for similar issue with ALTER TABLE DROP KEY (as mentioned in previous
comment).