MySQL Forums
Forum List  »  Partitioning

Unable to add parition using alter table
Posted by: Samad Ul Haq
Date: September 14, 2017 03:43AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Unable to add parition using alter table
1907
September 14, 2017 03:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.