CODEWITHSUNDEEP
mysqlsqlpartitioningcreate-table
Kasinath Kottukkal
Kasinath Kottukkal

Reputation: 2589

Create table with partition syntax error : MySQL

I have an error in creating MySQL table with partitioning.

    CREATE TABLE employees (
    context VARCHAR(25),
    id INT
)
PARTITION BY LIST(context) (
    PARTITION pNorth VALUES IN('ads','asd')
);

It gives:

ERROR 1064 (42000): VALUES value must be of same type as partition function near ''asd') )' at line 6

Please help me out.

Upvotes: 2

Views: 3573

Answers (4)

Rick James
Rick James

Reputation: 142208

You will probably find there is no performance advantage of BY LIST over a suitable INDEX.

More Partition discussion.

Upvotes: 0

Kasinath Kottukkal
Kasinath Kottukkal

Reputation: 2589

As @Bill Karwin pointed it out.. "In MySQL 5.1, you can do LIST partitioning only with integers. In MySQL 5.5, they added the capability to do LIST COLUMNS with some other data types."

Upvotes: 1

Bohemian
Bohemian

Reputation: 424963

The documentation for LIST partitions does not mention COLUMNS as a keyword.
Try removing COLUMNS from your query:

CREATE TABLE ...
)
PARTITION BY LIST (context) (
  PARTITION pNorth VALUES IN('ads','asd')
)

Upvotes: 0

Tom Mac
Tom Mac

Reputation: 9853

I think the problem here is that you are trying to partition on a VARCHAR data type column. For a LIST partition you must reference an INTEGER type column. From the docs:

List partitioning in MySQL is similar to range partitioning in many ways. As in partitioning by RANGE, each partition must be explicitly defined. The chief difference between the two types of partitioning is that, in list partitioning, each partition is defined and selected based on the membership of a column value in one of a set of value lists, rather than in one of a set of contiguous ranges of values. This is done by using PARTITION BY LIST(expr) where expr is a column value or an expression based on a column value and returning an integer value, and then defining each partition by means of a VALUES IN (value_list), where value_list is a comma-separated list of integers.

Misleading error though for sure!

EDIT

What you should take a look at is partitioning by LIST COLUMNS as opposed to partitioning via LIST. Hence:

CREATE TABLE employees (
    context VARCHAR(25),
    id INT
)
PARTITION BY LIST COLUMNS(context) (
    PARTITION pNorth VALUES IN('ads','asd')
);

Upvotes: 3

Related Questions