Column index prefixes not supported for key partitioning.  When creating a table that is partitioned by key, any columns in the partitioning key which use column prefixes are not used in the table’s partitioning function. Consider the following CREATE TABLE  statement, which has three VARCHAR columns, and whose primary key uses all three columns and specifies prefixes for two of them: 

CREATE TABLE t1 ( a VARCHAR(10000), b VARCHAR(25), c VARCHAR(10), PRIMARY KEY (a(10), b, c(2)) ) PARTITION BY KEY() PARTITIONS 2; 

This statement is accepted, but the resulting table is actually created as if you had issued the following statement, using only the primary key column which does not include a prefix (column b ) for the partitioning key: 

CREATE TABLE t1 ( a VARCHAR(10000), b VARCHAR(25), c VARCHAR(10), PRIMARY KEY (a(10), b, c(2)) ) PARTITION BY KEY(b) PARTITIONS 2; 

Prior to MySQL 8.0.21, no warning was issued or any other indication provided that this occurred, except in the event that all columns specified for the partitioning key used prefixes, in which case the statement failed, but with a misleading error message, as shown here: 

mysql> CREATE TABLE t2 ( -> a VARCHAR(10000), -> b VARCHAR(25), -> c VARCHAR(10), -> PRIMARY KEY (a(10), b(5), c(2)) -> ) PARTITION BY KEY() PARTITIONS 2; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function 

This also occured when performing ALTER TABLE  or when upgrading such tables. 

This permissive behavior is deprecated as of MySQL 8.0.21 (and is subject to removal in a future version of MySQL). Beginning with MySQL 8.0.21, using one or more columns having a prefix in the partitioning key results in a warning for each such column, as shown here: 

mysql> CREATE TABLE t1 ( -> a VARCHAR(10000), -> b VARCHAR(25), -> c VARCHAR(10), -> PRIMARY KEY (a(10), b, c(2)) -> ) PARTITION BY KEY() PARTITIONS 2; Query OK, 0 rows affected, 2 warnings (1.25 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1681 Message: 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. *************************** 2. row *************************** Level: Warning Code: 1681 Message: 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. 

