MySQL: Table partially archived

My goal was to create a table which is partitioned and partially archived (compressed and read-only mode).
I tried to compress only one partition and change the permissions. No success.

It was partially achieved by using combination with engine called merge.

CREATE TABLE IF NOT EXISTS `test_arch` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt` TEXT ,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM   ;

CREATE TABLE IF NOT EXISTS `test_current` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt` TEXT ,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM /* some partitions */  ;

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt` TEXT ,
  UNIQUE KEY `id_idx` (`id`)
) ENGINE=MRG_MyISAM   INSERT_METHOD=FIRST UNION=(`test_current`,`test_arch`)

-- you have to fulfill table with some data in order to use ./myisampack

insert into test_arch (`id`, `txt`) VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, '');
update test set `txt`='Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam';

Now we create read-only compressed table (test_arch);

./myisampack  /db_storage/data/test/test_arch
./myisamchk -rq /db_storage/data/test/test_arch

Next, flush table through SQL:

FLUSH TABLE test_arch;

Now, your table is in read-only mode as well as compressed.

However, it is not safe for insert DML which has explicit literal for unique key, e.g.:
“INSERT INTO … (`id`) VALUES(123) … ON DUPLICATE KEY UPDATE … “;
or
“INSERT INTO … (`id`) VALUES(123)”
Only table indicated in option INSERT_METHOD=FIRST is used to check whether the record exists or not.

I am going to use it on production in some more complex way.

Additional advantages (besides these obvious) that I see is:
* permission limitation (you can make delete w/o success. You can even drop the table but data);

  1. No comments yet.

  1. No trackbacks yet.