[MDEV-15976] Partition by SYSTEM_TIME: rotation by time starts from a wrong partition Created: 2018-04-22  Updated: 2018-04-22  Resolved: 2018-04-22

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Partitioning, Versioned Tables
Affects Version/s: 10.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None


 Description   

As of the moment of writing the report, the documentation says:

one can rotate partitions by time:
<example>
This will initially write the history to p0, in a week it'll switch to p1, and then to p2.

That is, history should first be written to the first historical partition, and after that the interval applies. It makes sense, because the table can be created long before the history actually starts appearing, so it wouldn't make sense to waste first partitions.

Instead, it seems that the interval is counted from partition creation time. The test below demonstrates it.

  • create a partitioned table with a short interval, insert some (current) data;
  • wait for the first interval to pass;
  • delete some data to create historical rows;
    => see that they went to the second partition rather than first.

--source include/have_partition.inc
 
 
 
create or replace table t1 (i int)
 
with system versioning
 
partition by system_time interval 3 second (
 
  partition p0 history,
 
  partition p1 history,
 
  partition pc current
 
);
 
 
 
insert into t1 values (1),(2),(3);
 
--echo # Everything is in partition pc for now
 
select * from t1 partition (pc);
 
 
 
--echo # Wait for 3+ seconds
 
select sleep(4);
 
 
 
delete from t1 where i = 1;
 
--echo # The row should have gone to partition p0, but it doesn't:
 
select * from t1 partition (p0);
 
 
 
--echo # It went to partition p1 instead:
 
select * from t1 partition (p1);
 
 
 
 
 
# Cleanup
 
drop table t1;

10.3 c39f8a80c9fe7f

 
MariaDB [test]> --echo # The row should have gone to partition p0, but it doesn't:
 
MariaDB [test]> select * from t1 partition (p0);
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> --echo # It went to partition p1 instead:
 
MariaDB [test]> select * from t1 partition (p1);
 
+------+
 
| i    |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.00 sec)

If it is indeed meant to work this way, please adjust documentation.

 Comments   
Comment by Sergei Golubchik [ 2018-04-22 ]

This is intentional, the time starts counting from the table creation time.

Note that you can select from INFORMATION_SCHEMA.PARTITIONS to see the rotation time for each history partition.

Comment by Sergei Golubchik [ 2018-04-22 ]

I've clarified it in the manual

Generated at Thu Feb 08 08:25:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.