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 14:22	Modified:	26 May 2010 17:46
Reporter:	John Embretsen	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0-falcon-team (6.0.8a)	OS:	Any
Assigned to:	Christopher Powers	CPU Architecture:	Any
Tags:	F_ONLINE ALTER, pb2, pushbuild

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).