We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on.
We are using partitioning for this and the tables are innodb tables with 'innodb_files_per_table=ON'.
While doing this we are placing the partitions containing old data to a directory located in some other volume.
But a restriction in the form of 'every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is also a unique key.)' is a major road blocker in our activity. (https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html )
Here for partitioning we have to use a particular column which in most of the cases is not included in every unique key created on the table. We can not even change the key structure of the tables as it may impact the operation of applications.
So , we have to opt for other practices to archive the data. Any suggestions on this will be really helpful.
Sample scenario: Table:
CREATE TABLE `tbl` ( `col1` int(11) NOT NULL DEFAULT '0', `col2` int(11) NOT NULL AUTO_INCREMENT, `col3` int(11) NOT NULL DEFAULT '0', `col4` varchar(60) NOT NULL DEFAULT '', `col5` varchar(60) DEFAULT NULL, `changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY `uniq1` (`col2`), UNIQUE KEY ` uniq2` (`col1`), UNIQUE KEY ` uniq3` (`col4`,`col3`), UNIQUE KEY `uniq4` (`col5`,`col3`), KEY `key1` (`changed`), );
We are partitioning w.r.t col1.
alter table tbl partition by range(col1)( partition p0 values less than (10000000) data directory = '/partitions',partition p1 values less than (20000000) data directory = '/partitions',partition p2 values less than (30000000),partition p3 values less than (maxvalue));
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function