|
Columns index prefixes are not supported by key partitioning. However, there is no warning, and a misleading error message:
CREATE OR REPLACE TABLE t1 (
|
a VARCHAR(10),
|
b VARCHAR(10),
|
c VARCHAR(10),
|
PRIMARY KEY (a(5), b, c(5))
|
) PARTITION BY KEY() PARTITIONS 2;
|
a(5) and c(5) are silently ignored, so the effect is the same as the following, but no warnings are given:
CREATE OR REPLACE TABLE t1 (
|
a VARCHAR(10),
|
b VARCHAR(10),
|
c VARCHAR(10),
|
PRIMARY KEY (b)
|
) PARTITION BY KEY() PARTITIONS 2;
|
If all specified columns in the primary key have index prefixes, a misleading error message is returned:
CREATE OR REPLACE TABLE t1 (
|
a VARCHAR(10),
|
b VARCHAR(10),
|
c VARCHAR(10),
|
PRIMARY KEY (a(5), b(5), c(5))
|
) PARTITION BY KEY() PARTITIONS 2;
|
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
|
This issue was fixed in MySQL 8.0.21:
CREATE TABLE t1 (
|
a VARCHAR(10),
|
b VARCHAR(10),
|
c VARCHAR(10),
|
PRIMARY KEY (b)
|
) PARTITION BY KEY() PARTITIONS 2;
|
|
SHOW WARNINGS;
|
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Warning | 1681 | Column 'test.t1.a' having prefix key part 'a(10)' is ignored by the partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause is deprecated and will be removed in a future release. |
|
| Warning | 1681 | Column 'test.t1.c' having prefix key part 'c(2)' is ignored by the partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause is deprecated and will be removed in a future release. |
|
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
and
CREATE TABLE t1 (
|
a VARCHAR(10),
|
b VARCHAR(10),
|
c VARCHAR(10),
|
PRIMARY KEY (a(5), b(5), c(5))
|
) PARTITION BY KEY() PARTITIONS 2;
|
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
|
|