-> SUBPARTITIONS 4 -> ( -> PARTITION p0 VALUES LESS THAN (100), -> PARTITION p1 VALUES LESS THAN (MAXVALUE) -> ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’) mysql> CREATE TABLE ts ( -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(30) -> ) -> PARTITION BY RANGE(id) -> SUBPARTITION BY KEY(id) -> SUBPARTITIONS 4 -> ( -> PARTITION p0 VALUES LESS THAN (100), -> PARTITION p1 VALUES LESS THAN (MAXVALUE) -> ); Query OK, 0 rows affected (0.07 sec) 

This is a known issue (see Bug #51470). 

DATA DIRECTORY and INDEX DIRECTORY options.  Table-level DATA DIRECTORY  and INDEX DIRECTORY  options are ignored (see Bug #32091). You can employ these options for individual partitions or subpartitions of InnoDB  tables. As of MySQL 8.0.21, the directory specified in a DATA DIRECTORY clause must be known to InnoDB . For more information, see Using the DATA DIRECTORY Clause. 

Repairing and rebuilding partitioned tables.  The statements CHECK TABLE , OPTIMIZE TABLE , ANALYZE TABLE , and REPAIR TABLE  are supported for partitioned tables. 

In addition, you can use ALTER TABLE ... REBUILD PARTITION  to rebuild one or more partitions of a partitioned table; ALTER TABLE ... REORGANIZE PARTITION  also causes partitions to be rebuilt. See Section 13.1.9, “ALTER TABLE Statement” , for more information about these two statements. 

ANALYZE , CHECK , OPTIMIZE , REPAIR , and TRUNCATE  operations are supported with subpartitions. See Section 13.1.9.1, “ALTER TABLE Partition Operations” . 

File name delimiters for partitions and subpartitions.  Table partition and subpartition file names include generated delimiters such as #P#  and #SP# . The lettercase of such delimiters can vary and should not be depended upon. 

## 24.6.1 Partitioning Keys, Primary Keys, and Unique Keys 

This section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have. 

In other words, every unique key on the table must use every column in the table’s partitioning expression . (This also includes the table’s primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation statements is invalid: 

CREATE TABLE t1 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2) ) PARTITION BY HASH(col3) PARTITIONS 4; 

