MySQL - A UNIQUE INDEX must include all columns in the table's partitioning function

Question

I have the following table setup (column names simplified for the example):

CREATE TABLE data_2016
( `a` INTEGER , 
  `b` INTEGER,
  `c` VARCHAR(255),
  `d` BIGINT,
  `e` VARCHAR(255) NOT NULL,
  `f` INTEGER ,
  `g` BIGINT ,
  `h` BIGINT ,
  `i` SERIAL,
PRIMARY KEY (`d`,`i`),
UNIQUE KEY(`b`, `c`, `d`, `e`, `f`,`g`,`h`,`i`),
INDEX `idx1` (`b`,`c`)
)
PARTITION BY RANGE (`d`) (
PARTITION p1 VALUES LESS THAN (...)
...
PARTITION px VALUES LESS THAN (MAXVALUE)
)

But I am getting the exception A UNIQUE INDEX must include all columns in the table's partitioning function

I read through the documentation, and from what I can tell, I do have the correct setup. The partitioned column d is included in both the PRIMARY KEY and the UNIQUE KEY definition. What am I doing wrong here?

I think the unique index in this case refers to the primary key, not the unique key. — Gordon Linoff
– Gordon Linoff, Commented Feb 3, 2017 at 17:11
@GordonLinoff what about the i column? I'm not partitioning on it. Is it saying I need to include it in the partition? — aarbor
– aarbor, Commented Feb 3, 2017 at 19:18
What do you hope to gain by PARTITIONing? It rarely provides any performance benefit. (I can't guess at the answer because of the obfuscation.) — Rick James
– Rick James, Commented Feb 4, 2017 at 1:36

Rick James · Accepted Answer · 2017-02-05 00:29:07Z

1

Change SERIAL to INT UNSIGNED AUTO_INCREMENT (or whatever is equivalent). The manual says:

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

Which means that there is an implicit

UNIQUE(i)

Which does not include d, the partition key.

CHARACTER SET latin1 is needed to avoid another error about index length. (8 columns is usually "too long" for an index.)

(And see my comments.)

edited Feb 5, 2017 at 0:29

answered Feb 5, 2017 at 0:22

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aarbor Over a year ago

Yes, this was it! Thank you!

Collectives™ on Stack Overflow

MySQL - A UNIQUE INDEX must include all columns in the table's partitioning function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related