CODEWITHSUNDEEP
mysql
davisca
davisca

Reputation: 1184

Error while partitioning. [Err] 1654 - Partition column values of incorrect type

What's wrong with this MySQL query?
Result is [Err] 1654 - Partition column values of incorrect type

DROP TABLE IF EXISTS part;
CREATE TABLE `part` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cnt` varchar(255) DEFAULT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`, `created`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY RANGE COLUMNS (created)(
    PARTITION p_2015_01 VALUES LESS THAN ('2015-01-30') ENGINE=InnoDB,
    PARTITION p_2015_02 VALUES LESS THAN ('2015-02-30') ENGINE=InnoDB,
    PARTITION p_2015_03 VALUES LESS THAN ('2015-03-30') ENGINE=InnoDB,
    PARTITION p_catchall VALUES LESS THAN (MAXVALUE) ENGINE=InnoDB
);

If it matters, the version is 5.5

Upvotes: 1

Views: 2810

Answers (3)

migalho
migalho

Reputation: 1

An example of a variant that may lead to a similar error [#HY000 Partition column values of incorrect type]:

create table if not exists tbl ( col varchar(10)) partition by list columns (col)( partition blablablabla values in ('blablablabla') );

If length('blablablabla') > to the size specified in the column col (varchar(10)) we will get this error. In my case, increasing the max length of the characters of that specific column solve the issue.

Upvotes: 0

birdierising
birdierising

Reputation: 1

If you're using the online-schema-change tool from the Percona toolkit to reorganize partitions, you can also get this error for using single quotes around an alter containing single quotes. Silly, but I just ran into it.

root@host:~# pt-online-schema-change --execute --no-drop-old-table --progress='time,1' --recursion-method=none  --alter='REORGANIZE PARTITION pMAXVALUE INTO ( PARTITION p20161206 VALUES LESS THAN ('2016-12-06'),PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE))' D=somedb,t=sometable

vs

root@host:~# pt-online-schema-change --execute --no-drop-old-table --progress='time,1' --recursion-method=none  --alter="REORGANIZE PARTITION pMAXVALUE INTO ( PARTITION p20161206 VALUES LESS THAN ('2016-12-06'),PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE))" D=somedb,t=sometable

Upvotes: 0

davisca
davisca

Reputation: 1184

It took quite a long time to see the obvious:

The date '2015-02-30' does not exist.

Presumably it is converted to NULL or something, therefore the message about 'incorrect type'.

Hopefully it helps somebody someday.

Upvotes: 2

Related Questions