AnsweredAssumed Answered

Need to help about partition MySQL

Question asked by phong_van on Dec 18, 2018
Latest reply on Jan 7, 2019 by afaust

Hi everyone,

Recently, my data on Alfresco has grown (I'm using. alfresco 5 with solr 6 and MySQL 5.6 - using CMIS with java).I've plan to improve system in 2 phase:

  • The first: partition for alf_node and alf_node_properties
  • The second for others big data (I found some table: alf_activity_feed, alf_activity_post, alf_child_assoc, alf_content_data, alf_content_url, alf_node_aspects, alf_node_assoc, alf_transaction have very large data).

 

In the first phase, i've try partition alf_node by range as below:

 

CREATE TABLE `alf_node` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`version` bigint(20) NOT NULL,
`store_id` bigint(20) NOT NULL,
`uuid` varchar(36) NOT NULL,
`transaction_id` bigint(20) NOT NULL,
`type_qname_id` bigint(20) NOT NULL,
`locale_id` bigint(20) NOT NULL,
`acl_id` bigint(20) DEFAULT NULL,
`audit_creator` varchar(255) DEFAULT NULL,
`audit_created` varchar(30) DEFAULT NULL,
`audit_modifier` varchar(255) DEFAULT NULL,
`audit_modified` varchar(30) DEFAULT NULL,
`audit_accessed` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `store_id` (`store_id`,`uuid`),
KEY `idx_alf_node_txn_type` (`transaction_id`,`type_qname_id`),
KEY `fk_alf_node_acl` (`acl_id`),
KEY `fk_alf_node_store` (`store_id`),
KEY `idx_alf_node_tqn` (`type_qname_id`,`store_id`,`id`),
KEY `fk_alf_node_loc` (`locale_id`),
KEY `idx_alf_node_mdq` (`store_id`,`type_qname_id`),
KEY `idx_alf_node_cor` (`audit_creator`,`store_id`,`type_qname_id`),
KEY `idx_alf_node_crd` (`audit_created`,`store_id`,`type_qname_id`),
KEY `idx_alf_node_mor` (`audit_modifier`,`store_id`,`type_qname_id`),
KEY `idx_alf_node_mod` (`audit_modified`,`store_id`,`type_qname_id`)
)

 

PARTITION BY RANGE ( YEAR(audit_created) ) (

PARTITION empty VALUES LESS THAN ( 0 ),
PARTITION p2018 VALUES LESS THAN ( YEAR('2019-01-01 00:00:00') ),
PARTITION p2019 VALUES LESS THAN ( YEAR('2020-01-01 00:00:00') ),
PARTITION other VALUES LESS THAN maxvalue

)

 

But when i try to execute statement, the system show error: 1486. Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

 

I've try to check data on audit_created I've found that some row this colum (audit_created) is null, others contain Time zone in data (ex: 2018-10-01T11:55:56.312Z). 

 

 

So I have to question:

1- why some row have audit_created is null

2- Could you give some advise about how to partition alf_node on audit_created?

 

Many thanks!

Outcomes