[MDEV-27108] dropping/truncating a subpartition also drops/truncates MAIN partition Created: 2021-11-22  Updated: 2021-11-25  Resolved: 2021-11-23

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Partitioning
Affects Version/s: 10.2.30
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Anselmo Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

RH/CentOS Linux


Attachments: File MariaDB_bug.sql    

 Description   

Please check attached sql file for a full bug sample. I believe there are three distinct bugs highlighted in it. Everything is explained in comments inside sql file. I believe that all versions until last one are also affected.



 Comments   
Comment by Sergei Golubchik [ 2021-11-23 ]

# starts a comment, so the statement you've executed was essentially

alter table test truncate partition archived

and, as expected, it truncated the whole partition.

There is no way to truncate just one subpartition now. A partition is always subpartitioned BY HASH, that is you cannot control what subpartition a row will go into and the content of every subpartition is almost unpredictable. That is, in most cases it doesn't make much sense to truncate just one subpartition.

Comment by Anselmo [ 2021-11-25 ]

Sorry wasn't aware that for MariaDB # is also a comment.
About subpartitions, I think is possible to devise cases on which the content is predictable, like this:

create table if not exists test (id int auto_increment, something varchar(255), part int default dayofweek(utc_timestamp), primary key (id, part)) partition by hash (part) partitions 7;
insert into test (something, part) values ('sunday', 1), ('monday', 2), ('tuesday', 3), ('wednesday', 4), ('thursday', 5), ('friday', 6), ('saturday', 7);
select * from test;
select * from information_schema.partitions where table_name = 'test';

in such cases subpartitions could be useful for cyclic cleanup (truncate) of cache tables, for example.
There are already other people interested in subpartition maintenance, one case I found is here:

https://mariadb.com/kb/en/truncate-sub-partition/

(no answer)

thanks for attention, and sorry for mistake.

Generated at Thu Feb 08 09:50:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.