MySQL Forums
Forum List  »  Partitioning

Unable to create partition InnoDB
Posted by: Samad Ul Haq
Date: November 02, 2017 02:50AM

Hi Everyone,


Recently, i've tried to add partition by range, but it failed with following error;


mysql> ALTER TABLE log_bak PARTITION BY RANGE (UNIX_TIMESTAMP(RequestTime) ) (
-> PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2017-10-27 00:00:00') )
-> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> show errors;
+-------+------+-----------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------+
| Error | 1503 | A PRIMARY KEY must include all columns in the table's partitioning function |
+-------+------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table log_bak\G
*************************** 1. row ***************************
Table: log_bak
Create Table: CREATE TABLE `log_bak` (
`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`)
) ENGINE=InnoDB AUTO_INCREMENT=7897609 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

====================================================================
Before trying partition on this table i used another table test1
mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` bigint(20) NOT NULL DEFAULT '0',
`TransactionId` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`ApiName` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`InterfaceName` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`Request` varchar(5000) CHARACTER SET latin1 DEFAULT NULL,
`RequestTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Msisdn` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`ResourceURI` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`Authorization` varchar(100) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(RequestTime))
(PARTITION p0 VALUES LESS THAN (1512068400) ENGINE = InnoDB) */
1 row in set (0.00 sec)

This table doesn't have a primary key, whereas the above table has primary key.

Regards
Samad

Options: ReplyQuote


Subject
Views
Written By
Posted
Unable to create partition InnoDB
2889
November 02, 2017 02:50AM
1165
November 02, 2017 09:12AM
1049
November 02, 2017 12:57PM
1214
November 02, 2017 01:16PM
1100
November 02, 2017 01:55PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.