Mysql – Recreate partition in thesql

MySQLpartitioning

I have a table having datetime column partitioned by RANGE

(DAYOFYEAR(datetime_col))

By this I want to drop oldest of 4th week partition data and recreate immediately for next year similar week inserts.

I.E. I use Mysql 5.6.19-67.0, the partitions are by week

(PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (71) ENGINE = InnoDB,
...
....
 PARTITION p49 VALUES LESS THAN (344) ENGINE = InnoDB,
 PARTITION p50 VALUES LESS THAN (351) ENGINE = InnoDB,
 PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
 PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Now I want to retain the structure by removing 4th week prior DATA ONLY . But I want to see the structure there WITHOUT data for that partition.

1st Option: alter table t1 truncate partition p4

2nd Option: Drop the partition and add partition to recreate it.

Issue in 1st Option: I'm afraid to issue this, as this doesn't drop the partition and recreate it. It goes for delete from partition where datetime_col < '2013-11-12 00:00:00'. Not sure how much CPU or undo buffers this will create in a 500G partition table.

Issue in 2nd Option: No issues, but it just won't work.

ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

Anyother recommendations are most welcome!!

Best Answer

Running TRUNCATE PARTITION works fine

DROP DATABASE IF EXISTS partest;
CREATE DATABASE partest;
USE partest
CREATE TABLE rolando (
  id int not null auto_increment,
  LogDate DATE,
  PRIMARY KEY (id,LogDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
(PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
 PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
 PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
ALTER TABLE rolando TRUNCATE PARTITION p4;

Here is the output

mysql> DROP DATABASE IF EXISTS partest;
Query OK, 1 row affected (1.55 sec)

mysql> CREATE DATABASE partest;
Query OK, 1 row affected (0.00 sec)

mysql> USE partest
Database changed
mysql> CREATE TABLE rolando (
    ->   id int not null auto_increment,
    ->   LogDate DATE,
    ->   PRIMARY KEY (id,LogDate)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
    -> (PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
    ->  PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
    ->  PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
    ->  PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
    ->  PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
    ->  PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
    ->  PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
    ->  PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
    ->  PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
    ->  PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
    ->  PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (2.60 sec)

mysql> ALTER TABLE rolando TRUNCATE PARTITION p4;
Query OK, 0 rows affected (0.25 sec)

mysql>

OK, fine. It works. However, doing this is logically unsafe. Why ? The partitioned table is based on DAYOFYEAR(datetime_col)). Since each partition contains a week for multiple years, running alter table t1 truncate partition p4; would wipe out week 4 from multiple years.

OK, let's try dropping and adding the partition

mysql> ALTER TABLE rolando DROP PARTITION p4;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE rolando ADD PARTITION
    -> (PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB);
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
mysql>

This makes sense since MAXVALUE already exists in the definition. This prevents you from adding (appending) a partition if MAXVALUE is present.

While you could play games with ALTER TABLE ... REORGANIZE PARTITION, the complexity isn't worth it and you won't achieve the result of wedging in a partition.

Rather that explanining further partition machinations, just do the following

Make sure an index exists on the date field
Perform the DELETE SQL already mentioned

An alternative would be to create a table with a weekly partition then drop the exact week.

Related Solutions

Mysql – Partition by year and sub-partition by month thesql

I had to do the same thing and solved slightly differently. Basically as far as I understand by reading the docs MySQL subpartitioning does not support partition types besides HASH and KEY.

In MySQL 5.6, it is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning. This is also known as composite partitioning.

This means that we can't determine in what subpartition a record will end up. It's up to MySQL. So I don't think it's wise giving such names to your subpartitions (i.e. january) because no way you're going to know if stuff created on that month will end up there. This because you can't subpartition by MONTH(nav_date) but only by HASH(MONTH(nav_date)) or KEY(MONTH(nav_date)).

So to solve the problem I decided to create a new month column in my table and then I added an index to it. Then I subpartioned by KEY(month) without caring about the subpartition names. This way in MySQL 5.6 I could select the main partition from the FROM clause and the subpartition by specifying the month in the WHERE clause.

Follows the full example:

CREATE TABLE `my_example` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `value` double DEFAULT NULL,
    `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
    `timestamp` datetime NOT NULL,
    `last_modified` datetime NOT NULL,
    `month` tinyint(1) NOT NULL,
    PRIMARY KEY (`id`,`timestamp`, `month`),
    KEY `in_is_deleted` (`is_deleted`),
    KEY `in_last_modified` (`last_modified`),
    KEY `in_timestamp` (`timestamp`),
    KEY `in_month` (`month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE (YEAR(`timestamp`))
SUBPARTITION BY KEY (`month`) 
SUBPARTITIONS 12 (
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015); -- will go through all twelve subpartitions
EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015) WHERE month = 9; -- will look only into one subpartition

MYSQL crashes on XAMPP while working with drupal

ok, i best solution which suited me well.

for being cautious in choosing the above solutions provided in my question above..

(cuz the solutions didn't guarantee 100% that my data would be intact), i made a backup for my drupal database folder just in case.

chose solution (2), which is adding the force_recovery..but no luck in fixing the problem..

so i went to the location of the corrupted file in "C:\xampp\mysql\data\bitnami_drupal7", and moved the corrupted file to another folder (NOTE: i did not delete the corrupted file).

then started mysql service, and viola! my drupal was back to life again.

and i was able to access phpmyadmin with sql queries.

(back then, mysql couldn't execute sql queries because it was in a state where its about to crash).

i went straight to the cache tables of drupal to clear them..

(back then, i wanted to clear drupals' cache since the error started, cuz ever since i knew that mysql crashes because of a table in drupal; but mysql couldn't have enough time to execute sql queries in phpmyadmin, again cuz of the the crash).

hence, after clearing all drupal cache at "bitnami_drupal7" with this code:

TRUNCATE TABLE cache;

TRUNCATE TABLE cache_block;

TRUNCATE TABLE cache_bootstrap;

TRUNCATE TABLE cache_field;

TRUNCATE TABLE cache_filter;

TRUNCATE TABLE cache_form;

TRUNCATE TABLE cache_image;

TRUNCATE TABLE cache_menu;

TRUNCATE TABLE cache_page;

TRUNCATE TABLE cache_path;

TRUNCATE TABLE cache_token;

TRUNCATE TABLE cache_update;

then, i replaced the corrupted file back again to try if it becomes recognized or not, and started mysql..and it worked!!

mysql never crashed again and complain about that file being corrupted anymore, and that saved me from deleting or affecting any data.

Best Answer

Related Solutions

Mysql – Partition by year and sub-partition by month thesql

MYSQL crashes on XAMPP while working with drupal

Related Question