Mysql – What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction

archiveinnodbmariadb-10.1MySQLpartitioning

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

Best Answer

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.

Related Solutions

Mysql – Store equations/formula in thesql db table

You can try to leverage dynamic SQL.

If you need to get a calculated value for an id

DELIMITER $$
CREATE PROCEDURE get_value(IN _id INT)
BEGIN
  SET @sql = NULL;

  SELECT CONCAT('SELECT ', formula, ' value FROM table1 WHERE id = ', 1)
    INTO @sql
    FROM table1
   WHERE id = 1;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = NULL;
END$$
DELIMITER ;

Note: You can of course use OUT parameter instead of returning the resultset if you want to.

Sample usage:

CALL get_value(1);

Sample output:

|           VALUE |
|-----------------|
| 82.916129032258 |

Here is how a procedure might look like to get all values calculated by formulas

DELIMITER $$
CREATE PROCEDURE get_values()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(CONCAT(
    'SELECT id, ', formula, ' value FROM table1 WHERE id = ', id)
    ORDER BY id SEPARATOR ' UNION ALL ')
    INTO @sql
    FROM table1;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = NULL;
END$$
DELIMITER ;

Sample usage:

CALL get_value(1);

Sample output:

| ID |           VALUE |
|----|-----------------|
|  1 | 82.916129032258 |
|  2 |    0.0000109375 |

Here is SQLFiddle demo for both procedures

Mysql – Insert record only if a combination of column values doesn’t exist

MYSQL doesn't support such types of constraints. You should use stored procedure for inserting data. So you can do some checking and validation data.

Best Answer

Related Solutions

Mysql – Store equations/formula in thesql db table

Mysql – Insert record only if a combination of column values doesn’t exist

Related Question