All we need is an easy explanation of the problem, so here it is.
My tables have been created with InnoDB row compression (
ENGINE=InnoDB ROW_FORMAT=COMPRESSED). Not I am changing them to page compression. According to the official documentation of MariaDB, enabling page compression affect the newly created tables only.
Thus, I create a replica table and use
INSERT INTO SELECT.
I wonder if it is possible to
ALTER a table to change the compression type (from row to page)?
ALTER an ordinary table to use page compression as
ALTER TABLE t1 PAGE_COMPRESSED=1;
My question is about a safe way to remove
ROW_FORMAT=COMPRESSED and add
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I tried different methods and read many articles. The standard way to do so is
ALTER TABLE t1 KEY_BLOCK_SIZE=0 `PAGE_COMPRESSED`='ON';
However, this will leave
KEY_BLOCK_SIZE on all keys.
Some say this is just the
SHOW CREATE TABLE display, as
KEY_BLOCK_SIZE=8 standards for the largest block size for the keys, but the value is exactly that of the
COMPRESSED table. If the table was created with
KEY_BLOCK_SIZE=1, keys will have
KEY_BLOCK_SIZE=1 after uncompression.
Some, on the other hand, say this bug has been fixed in MySQL 8, but I confirm it exists in MariaDB 10.5.9.
ALTER is a clean way, but I favour
INSERT INTO SELECT for two reasons:
- ALTER writes the uncompressed table on disk while
INSERTwrites the compressed pages on the disk.
- We can create new table on a separate disk to reduce I/O.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂