Bug #22499 Unable to rename 'packed' table, Unable to 'myisampack' table with live databas.
Submitted: 19 Sep 2006 22:33 Modified: 14 Jun 2013 0:36
Reporter: J Jorgenson Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S4 (Feature request)
Version:5.0.18 OS:Any
Assigned to: CPU Architecture:Any
Tags: ALTER TABLE, myisampack
Triage: D5 (Feature request)

[19 Sep 2006 22:33] J Jorgenson
Description:
Unable to 'ALTER TABLE compressed_table RENAME new_table_name'
ERROR 1036 (HY000): Table 'compressed_table' is read only

Using RENAME as work-around to compressing (myisampack) tables while database is 'live'.  

Need a means to 'guarantee' no table access during external compression, when external-locking-disabled. The system 'cost' for external-locking is prohibitive to leave enabled.  Production databases need to be able to have externally myisamchk/myisampack tables reliably.  

Is there nobody else who needs manipulate(repair/pack) *some* tables while database is 'live'?   While still have the performance gain of --external-locking-disabled?

How to repeat:
Create a MyISAM table... myisampack it

CREATE TABLE x (_id INT) ENGINE=MyISAM;
SET @var:=0; SELECT (@var:=@var+1) FROM information_schema.TABLES; 
FLUSH TABLE x;
myisampack x;
ALTER TABLE x RENAME y;

I've tried each of the following scenerios to 'force' reliable external table manipulation with --external-locking-disabled, with no success:

LOCK TABLE x WRITE;
FLUSH TABLE x;
myisamchk x;
myisampack x;
myisamchk -raq x;
UNLOCK TABLES;
-- Table is corrupted.

FLUSH TABLE x;
myisamchk x;
myisampack x;
myisamchk -raq x;
FLUSH TABLE x;
-- Table is NOT-corrupted.

Suggested fix:
Allow rename of packed/compressed table.
Allow 'external-locking' to be temporarily turned ON thru some mechanisim.
Allow 'lock table write' followed by myisampack to not produce a corrupt table.
Allow means to use external tools on *specific* table(s) without having to shutdown database.