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
Triage: Triaged: D2 (Serious)

[26 Sep 2008 14:22] John Embretsen
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
[26 Sep 2008 16:37] Philip Stoev
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.
[1 Oct 2008 22:31] Kevin Lewis
Chris, This seems to be associated with Online Add Index.  Let me know if it is something else.
[6 Oct 2008 16:14] Christopher Powers
Possibly related: Bug#39132, Bug#28178
[22 Feb 2009 23:16] Christopher Powers
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
[22 Feb 2009 23:18] Christopher Powers
Philip Stoev reports that neither errno: 121 nor any other suspicious errors have occurred for the last 25 runs of falcon_online_alter.
[17 Jul 2009 8:27] John Embretsen
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.
[23 Jul 2009 9:11] John Embretsen
Opened Bug#46299 for similar issue with ALTER TABLE DROP KEY (as mentioned in previous comment).