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.
