What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a...
Fantasy story; one type of magic grows in power with use, but the more powerful they are, they more they are drawn to travel to their source
An adverb for when you're not exaggerating
Is the Standard Deduction better than Itemized when both are the same amount?
Do I really need recursive chmod to restrict access to a folder?
Crossing US/Canada Border for less than 24 hours
What is the meaning of the simile “quick as silk”?
Is it fair for a professor to grade us on the possession of past papers?
What is the longest distance a player character can jump in one leap?
Can you use the Shield Master feat to shove someone before you make an attack by using a Readied action?
Do wooden building fires get hotter than 600°C?
How to find all the available tools in mac terminal?
How do pianists reach extremely loud dynamics?
Is it cost-effective to upgrade an old-ish Giant Escape R3 commuter bike with entry-level branded parts (wheels, drivetrain)?
Denied boarding although I have proper visa and documentation. To whom should I make a complaint?
Closed form of recurrent arithmetic series summation
How do I find out the mythology and history of my Fortress?
Is it ethical to give a final exam after the professor has quit before teaching the remaining chapters of the course?
How could we fake a moon landing now?
Is it common practice to audition new musicians one-on-one before rehearsing with the entire band?
What is homebrew?
How to compare two different files line by line in unix?
How come Sam didn't become Lord of Horn Hill?
Is safe to use va_start macro with this as parameter?
Why didn't Eitri join the fight?
What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Unexplained InnoDB timeoutsStore equations/formula in mysql db tableInefficient queries on partitioned tablesCalculate row size and max row size for a tablePartitioned Tables and Indexes - what are the downsides?Index not used on partitioned tableMySQL query taking too longNeed help in writing stored procedures in MYSQL?slow queries on indexed columns (large datasets)
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
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
mysql innodb partitioning archive mariadb-10.1
edited Mar 12 '18 at 22:18
Rick James
43.9k22360
asked Mar 12 '18 at 13:50
Avijit batabyalAvijit batabyal
62
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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
mysql innodb partitioning archive mariadb-10.1
edited Mar 12 '18 at 22:18
Rick James
43.9k22360
asked Mar 12 '18 at 13:50
Avijit batabyalAvijit batabyal
62
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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 fourUNIQUEkeys 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 theAUTO_INCREMENT.uniq1was 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
add a comment |
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
mysql innodb partitioning archive mariadb-10.1
edited Mar 12 '18 at 22:18
Rick James
43.9k22360
asked Mar 12 '18 at 13:50
Avijit batabyalAvijit batabyal
62
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
mysql innodb partitioning archive mariadb-10.1
mysql innodb partitioning archive mariadb-10.1
edited Mar 12 '18 at 22:18
Rick James
43.9k22360
asked Mar 12 '18 at 13:50
Avijit batabyalAvijit batabyal
62
edited Mar 12 '18 at 22:18
Rick James
43.9k22360
asked Mar 12 '18 at 13:50
Avijit batabyalAvijit batabyal
62
edited Mar 12 '18 at 22:18
Rick James
43.9k22360
edited Mar 12 '18 at 22:18
Rick James
43.9k22360
edited Mar 12 '18 at 22:18
Rick James
43.9k22360
43.9k22360
asked Mar 12 '18 at 13:50
Avijit batabyalAvijit batabyal
62
asked Mar 12 '18 at 13:50
Avijit batabyalAvijit batabyal
62
asked Mar 12 '18 at 13:50
Avijit batabyalAvijit batabyal
62
62
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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 fourUNIQUEkeys 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 theAUTO_INCREMENT.uniq1was 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
add a comment |
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 fourUNIQUEkeys 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 theAUTO_INCREMENT.uniq1was 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
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
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
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
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
add a comment |
1 Answer
1
active
oldest
votes
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
archiveat 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.
answered Mar 12 '18 at 22:26
Rick JamesRick James
43.9k22360
Yes , it can be done in our present scenario.
– Avijit batabyal
Mar 13 '18 at 9:01
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f199990%2fwhat-are-the-options-for-archiving-old-data-of-mariadb-tables-if-partitioning-ca%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
archiveat 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.
answered Mar 12 '18 at 22:26
Rick JamesRick James
43.9k22360
Yes , it can be done in our present scenario.
– Avijit batabyal
Mar 13 '18 at 9:01
add a comment |
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
archiveat 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.
answered Mar 12 '18 at 22:26
Rick JamesRick James
43.9k22360
Yes , it can be done in our present scenario.
– Avijit batabyal
Mar 13 '18 at 9:01
add a comment |
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
archiveat 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.
answered Mar 12 '18 at 22:26
Rick JamesRick James
43.9k22360
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
archiveat 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.
answered Mar 12 '18 at 22:26
Rick JamesRick James
43.9k22360
answered Mar 12 '18 at 22:26
Rick JamesRick James
43.9k22360
answered Mar 12 '18 at 22:26
Rick JamesRick James
43.9k22360
answered Mar 12 '18 at 22:26
Rick JamesRick James
43.9k22360
43.9k22360
Yes , it can be done in our present scenario.
– Avijit batabyal
Mar 13 '18 at 9:01
add a comment |
Yes , it can be done in our present scenario.
– Avijit batabyal
Mar 13 '18 at 9:01
Yes , it can be done in our present scenario.
– Avijit batabyal
Mar 13 '18 at 9:01
Yes , it can be done in our present scenario.
– Avijit batabyal
Mar 13 '18 at 9:01
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f199990%2fwhat-are-the-options-for-archiving-old-data-of-mariadb-tables-if-partitioning-ca%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
UNIQUEkeys 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.uniq1was 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