Bug #36859 The optimize api does not handle the HA_ERR_LOCK_WAIT_TIMEOUT error.
Submitted: 21 May 2008 20:08 Modified: 10 Jun 2012 20:31
Reporter: Kathy Steinbrink Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.1.24-rc OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any

[21 May 2008 20:08] Kathy Steinbrink
The optimize does not cleanly handle the HA_ERR_LOCK_WAIT_TIMEOUT (146) error being returned by a storage engine. Here are the results I'm seeing:

| Table | Op       | Msg_type | Msg_text                                     
| i.t1  | optimize | error    | Unknown - internal error 146 during operation

The Innodb storage engine uses an alter table to complete this function, and the following error is more properly reported:
 | i.t1  | optimize | error    | Lock wait timeout exceeded; try restarting t
 ansaction |                                                                 
 | i.t1  | optimize | status   | Operation failed                            
 2 rows in set, 1 warning (50.77 sec)  

It seems the MySQL server should be handling the 146 error from the optimize api and report a similar 'lock wait timeout' error instead of the generic internal error.  


How to repeat:
To recreate the error, force the optimize() method in a storage engine to return a HA_ERR_LOCK_WAIT_TIMEOUT (146) error.
[22 May 2008 3:48] Valeriy Kravchuk
Thank you for a problem report. Please, give some more details. What storage engine should I use for the table and how exactly to provoke that error 146?
[28 May 2008 19:18] Kathy Steinbrink
To recreate the problem using the MyISAM storage engine,set a debug breakpoint in the optimize() method of ha_myisam.cc.   Change the returned value of the 'error' variable to HA_ERR_LOCK_WAIT_TIMEOUT (146).  This will effect the error handling problem. 
Although a 146 is not a natural error for the MyISAM engine, it is a likely error for my new storage engine which supports concurrent transactions. 

A 146 is a natural error for innodb, which also supports transactions. However, innodb redirects an optimize operation to an alter table operation.  The MySQL alter table processing correctly handles the 146 error.  The MySQL optimize table processing does not.
[2 Oct 2008 20:15] MySQL Verification Team
Verified on 5.1.30 (Windows server) following latest how to repeat from reporter:

mysql 5.1 >optimize table country;
| Table         | Op       | Msg_type | Msg_text                                      |
| world.country | optimize | error    | Unknown - internal error 146 during operation |
1 row in set (2 min 4.03 sec)

mysql 5.1 >
[10 Jun 2012 20:31] Jon Olav Hauglid
Closing bug.