CODEWITHSUNDEEP
mysql
sridhard
sridhard

Reputation: 129

when creating partition it shows error

CREATE TABLE `cloud_ip_cost_details1` (

  `ipcost_details_id` INT(11) NOT NULL AUTO_INCREMENT,

  `server_entry_id` INT(11) DEFAULT NULL,

  `ip_address` BLOB,

  `project_name` VARCHAR(500) DEFAULT NULL,

  `project_id` INT(11) DEFAULT NULL,

  `group_id` INT(11) DEFAULT NULL,

  `division_id` INT(11) DEFAULT NULL,

  `cost` BLOB,

  `period` VARCHAR(50) DEFAULT NULL,

  `updated_by` VARCHAR(20) DEFAULT NULL,

  `temp_id` BIGINT(20) DEFAULT NULL,

  PRIMARY KEY (`ipcost_details_id`)

)

PARTITION BY LIST(period)(


    PARTITION Jan2017 VALUES IN ('Jan-2017')
);

Error Code : 1697

VALUES value for partition 'Jan2017' must have type INT

Upvotes: 0

Views: 4238

Answers (2)

Darshan Mehta
Darshan Mehta

Reputation: 30809

We need to make a couple of changes:

  • We need to use PARTITION BY LIST COLUMNS phrase instead of PARTITION BY COLUMNS
  • Partitioned column needs to be a part of the table keys (documentation here), so we need to add period to primary key.

Below should work:

CREATE TABLE `cloud_ip_cost_details1` (

  `ipcost_details_id` INT(11) NOT NULL AUTO_INCREMENT,
  `server_entry_id` INT(11) DEFAULT NULL,
  `ip_address` BLOB,
  `project_name` VARCHAR(500) DEFAULT NULL,
  `project_id` INT(11) DEFAULT NULL,
  `group_id` INT(11) DEFAULT NULL,
  `division_id` INT(11) DEFAULT NULL,
  `cost` BLOB,
  `period` VARCHAR(50) DEFAULT NULL,
  `updated_by` VARCHAR(20) DEFAULT NULL,
  `temp_id` BIGINT(20) DEFAULT NULL,

  PRIMARY KEY (`ipcost_details_id`, `period`)

)

PARTITION BY LIST COLUMNS(period)(
    PARTITION Jan2017 VALUES IN ('Jan-2017')
);

Here's the SQL Fiddle.

Upvotes: 1

Danilo Bustos
Danilo Bustos

Reputation: 1093

try like this:

PARTITION BY LIST COLUMNS(period) (
    PARTITION Jan2017 VALUES IN('Jan-2017')
);

or this:

 PARTITION BY RANGE (period)
    ( PARTITION Jan2017 VALUES LESS THAN (TO_DAYS('2012-07-01')) 
 );

documentation:https://dev.mysql.com/doc/refman/5.5/en/partitioning-columns-list.html

Upvotes: 0

Related Questions