Unable to create partition InnoDB
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