Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
Description
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).
|
Attachments
Issue Links
- is part of
-
MDEV-28906 MySQL 8.0 desired compatibility
-
- Open
-