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
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Columns index prefixes are not supported by key partitioning. However, there is no warning, and a misleading error message:
{code} 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; {code} a(5) and c(5) are silently ignored, so the effect is the same as the following, but no warnings are given: {code} CREATE OR REPLACE TABLE t1 ( a VARCHAR(10), b VARCHAR(10), c VARCHAR(10), PRIMARY KEY (b) ) PARTITION BY KEY() PARTITIONS 2; {code} If all specified columns in the primary key have index prefixes, a misleading error message is returned: {code} 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 {code} |
Columns index prefixes are not supported by key partitioning. However, there is no warning, and a misleading error message:
{code} 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; {code} a(5) and c(5) are silently ignored, so the effect is the same as the following, but no warnings are given: {code} CREATE OR REPLACE TABLE t1 ( a VARCHAR(10), b VARCHAR(10), c VARCHAR(10), PRIMARY KEY (b) ) PARTITION BY KEY() PARTITIONS 2; {code} If all specified columns in the primary key have index prefixes, a misleading error message is returned: {code} 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 {code} This issue was fixed in MySQL 8.0.21: {code} 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. | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ {code} and {code} 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). {code} |
| Link | This issue is part of MDEV-28906 [ MDEV-28906 ] |
| Assignee | Yuchen Pei [ JIRAUSER52627 ] |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.0 [ 28320 ] | |
| Fix Version/s | 11.1 [ 28549 ] | |
| Fix Version/s | 11.2 [ 28603 ] |
| Fix Version/s | 10.10 [ 27530 ] |
| Fix Version/s | 11.0 [ 28320 ] |
| Fix Version/s | 10.4 [ 22408 ] |
| Fix Version/s | 11.1 [ 28549 ] |
| Fix Version/s | 11.2(EOL) [ 28603 ] |