MySQL Bugs: #54636: Optimize Table Hangs with Error 137

Bug #54636	Optimize Table Hangs with Error 137
Submitted:	19 Jun 2010 18:13	Modified:	23 Aug 2010 19:13
Reporter:	James McKelvey	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.1.36-log	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Running OPTIMIZE TABLE hangs with 100% CPU and no I/O.

Mysqld error log shows multiple copies of:

100619 14:49:59 [Warning] Warning: Enabling keys got errno 137 on msl_gdsdb_v5_0.#sql-6a13_5, retrying

Killing query several times eventually shows "killed" in process list, but it does not die.

Attempt to stop mysql never finishes.

Forced to kill -9 MySQL.

Table in question contains about 184M rows.

How to repeat:
Don't know. We were upgrading an old database, and the optimize was one of the final steps.

Will try entire upgrade again soon.

Please, run CHECK TABLE ... EXTENDED and send the results. Why do you think this is related to any bug in MySQL code, by the way?

For that run, MySQL ran out of memory. But the 137 error persists with a new copy of the DB, even on another host. Monitoring shows that there is no swapping.

We try to add indexes to a table:

(msl_gdsdb_v5_0)> ALTER TABLE SseEvr
     ->     ADD INDEX ertIndex(ertExact),
     ->     ADD INDEX scetIndex(scetExact),
     ->     ADD INDEX sclkIndex(sclkExact);
Query OK, 54333205 rows affected, 2 warnings (20 min 30.18 sec)
Records: 54333205  Duplicates: 0  Warnings: 0

While that is running I get this in the mysql log:

100623 18:48:10 [Warning] Warning: Enabling keys got errno 137 on 
msl_gdsdb_v5_0.#sql-627d_1, retrying

and I see this in the processlist:

|  1 | mckelvey | mushroom.jpl.nasa.gov:60375 | msl_gdsdb_v5_0 | Query 
  |  195 | Repair with keycache | ALTER TABLE SseEvr
     ADD INDEX ertIndex(ertExact),
     ADD INDEX scetIndex(scetExact),
     ADD IND |

In other words, the 137 error might be causing the repair by keycache. The 
repair actually finishes.

Then I can see the warnings from the alter table:

(msl_gdsdb_v5_0)> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Error | 1034 | myisam_sort_buffer_size is too small      |
| Error | 1034 | Number of rows changed from 0 to 54333205 |
+-------+------+-------------------------------------------+
2 rows in set (0.00 sec)

"Check table extended" on SseEvr gives a clear bill of health.

myisam_sort_buffer_size is 16G.

So, do you still see any bug in MySQL in this case?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Setting myisam_sort_buffer_size much larger fixes the problem.

So, can we conclude that this problem was a result of misconfiguration, not a bug in MySQL code?

If you want to consider it a misconfiguration and close, that's OK with me.

Thank you for the feedback.

Closing as "Not a Bug" because last comment.