1

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.

Query:

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

  • You could add col1 to your primary key, and add a unique key replacing the original primary one. What is the primary key of tbl ? – Gerard H. Pille Mar 12 '18 at 14:59
  • It is extremely rare to have four UNIQUE keys on a table. Please justify them, since 3 of them are in the way of partitioning. – Rick James Mar 12 '18 at 22:19
  • Also unusual (but not necessarily "wrong") -- the PK is something other than the AUTO_INCREMENT. uniq1 was promoted to be the PK (by InnoDB's rules for PKs), hence the wording of the error message. – Rick James Mar 12 '18 at 22:28
0

Here's a thought on archiving, with transactional integrity.

  • Have another table; let's call it archive. It needs few, if any indexes.
  • Move 1000 rows from your table to archive at a time, transactionally.
  • After you have moved "enough". do whatever you want to Archive archive.

See this for suggestions on efficiently DELETEing rows from a table. The rest is

INSERT INTO `archive` SELECT ... FROM main ...;

where the ... come from the chunking. That can probably be done with autocommit=on and not need any BEGIN...COMMIT.

  • Yes , it can be done in our present scenario. – Avijit batabyal Mar 13 '18 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.