mariadb (mysql) sub partition error (total sub partition count exceeds 64)

enter image description here

Hello

I want to configure a partition (monthly)/subpartition (day by day) as the query above. If the total number of subpartitions exceeds 64, '(errno: 168 "Unknown (generic) error from engine")' The table is not created due to an error. (Creating less than 64 is successed).

I know that the maximum number of partitions (including subpartitions) that can be created is 8,192, is there anything I missed?

@Rick James Thank you for your reply. I am currently making a web service that accumulates more than 100 million logs in a year. Logs should be provided to users on a web page in the form of a pageable table.

The user should be able to view log data in the desired date period on the web page. For that reason, we tried to implement daily partitions with subpartitioning. Subpartitioning was considered for efficiency of search and alignment.

This is a new employee assignment for the company. There is a requirement that the log table should be partitioned monthly. It was determined that the monthly partitioning would result in up to 8 million rows per partition, which would reduce the pageation performance. So I considered day-to-day subpartitioning.

Below is the log table.

create table detection_log ( id bigint auto_increment, detected_time datetime default '1970-01-01' not null, malware_title varchar(255) null, malware_category varchar(30) null, user_name varchar(30) null, department_path varchar(255) null, PRIMARY KEY (detected_time, id), INDEX detection_log_id_uindex (id), INDEX detection_log_malware_title_index (malware_title), INDEX detection_log_malware_category_index (malware_category), INDEX detection_log_user_name_index (user_name), INDEX detection_log_department_path_index (departmenenter code heret_path) );

https://stackoverflow.com/questions/65729377/mariadb-mysql-sub-partition-error-total-sub-partition-count-exceeds-64 January 15, 2021 at 09:45AM