Hi everyone,
I am unable to add parition using alter table command, please find below;
alter table log ADD PARTITION BY RANGE (UNIX_TIMESTAMP((RequestTime)(
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-12 00:00:00')),
PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-13 00:00:00')),
PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-14 00:00:00')),
PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-15 00:00:00')),
PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-17 00:00:00')),
PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-18 00:00:00')),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
Here is show create table output
mysql> show create table apim_log_request\G
*************************** 1. row ***************************
Table: log
Create Table: CREATE TABLE `log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`TransactionId` varchar(50) DEFAULT NULL,
`ApiName` varchar(50) DEFAULT NULL,
`InterfaceName` varchar(50) DEFAULT NULL,
`Request` varchar(5000) DEFAULT NULL,
`RequestTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Msisdn` varchar(50) DEFAULT NULL,
`ResourceURI` varchar(100) DEFAULT NULL,
`Authorization` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `apim_req_index` (`TransactionId`,`Msisdn`) USING BTREE,
KEY `apim_req_iFace_rsp` (`InterfaceName`,`RequestTime`) USING BTREE
) ENGINE=ndbcluster AUTO_INCREMENT=820631 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY ()
(PARTITION p0 ENGINE = ndbcluster,
PARTITION p1 ENGINE = ndbcluster,
PARTITION p2 ENGINE = ndbcluster) */
1 row in set (0.01 sec)
In case i am creating another table with partition its also failing :( .....
CREATE TABLE log (
id bigint(20) NOT NULL AUTO_INCREMENT,
TransactionId varchar(50) DEFAULT NULL,
ApiName varchar(50) DEFAULT NULL,
InterfaceName varchar(50) DEFAULT NULL,
Request varchar(5000) DEFAULT NULL,
RequestTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
Msisdn varchar(50) DEFAULT NULL,
ResourceURI varchar(100) DEFAULT NULL,
Authorization varchar(100) DEFAULT NULL,
PRIMARY KEY (id),
KEY apim_req_index (TransactionId,Msisdn) USING BTREE,
KEY apim_req_iFace_rsp (InterfaceName,RequestTime) USING BTREE
) ENGINE=ndbcluster AUTO_INCREMENT=820631 DEFAULT CHARSET=latin1
PARTITION BY RANGE (UNIX_TIMESTAMP((RequestTime)(
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-12 00:00:00')),
PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-13 00:00:00')),
PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-14 00:00:00')),
PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-15 00:00:00')),
PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-17 00:00:00')),
PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-18 00:00:00')),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-12 00:00:00')),
' at line 16
I was trying to follow this MySQL documentation
https://dev.mysql.com/doc/refman/5.5/en/partitioning-range.html.
Any help in this regard is appreciable.
Regards
Samad