26.6 Restrictions and Limitations on Partitioning (original) (raw)

This section discusses current restrictions and limitations on MySQL partitioning support.

Prohibited constructs. The following constructs are not permitted in partitioning expressions:

For a list of SQL functions which are permitted in partitioning expressions, seeSection 26.6.3, “Partitioning Limitations Relating to Functions”.

Arithmetic and logical operators. Use of the arithmetic operators+,-, and* is permitted in partitioning expressions. However, the result must be an integer value or NULL (except in the case of[LINEAR] KEY partitioning, as discussed elsewhere in this chapter; seeSection 26.2, “Partitioning Types”, for more information).

The DIV operator is also supported; the / operator is not permitted.

The bit operators|,&,^,<<,>>, and~ are not permitted in partitioning expressions.

Server SQL mode. Tables employing user-defined partitioning do not preserve the SQL mode in effect at the time that they were created. As discussed elsewhere in this Manual (seeSection 7.1.11, “Server SQL Modes”), the results of many MySQL functions and operators may change according to the server SQL mode. Therefore, a change in the SQL mode at any time after the creation of partitioned tables may lead to major changes in the behavior of such tables, and could easily lead to corruption or loss of data. For these reasons, it is strongly recommended that you never change the server SQL mode after creating partitioned tables.

For one such change in the server SQL mode making a partitioned tables unusable, consider the followingCREATE TABLE statement, which can be executed successfully only if theNO_UNSIGNED_SUBTRACTION mode is in effect:

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
    ->   PARTITION BY RANGE(c1 - 10) (
    ->     PARTITION p0 VALUES LESS THAN (-5),
    ->     PARTITION p1 VALUES LESS THAN (0),
    ->     PARTITION p2 VALUES LESS THAN (5),
    ->     PARTITION p3 VALUES LESS THAN (10),
    ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
    -> );
ERROR 1563 (HY000): Partition constant is out of partition function domain

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode;
+-------------------------+
| @@sql_mode              |
+-------------------------+
| NO_UNSIGNED_SUBTRACTION |
+-------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
    ->   PARTITION BY RANGE(c1 - 10) (
    ->     PARTITION p0 VALUES LESS THAN (-5),
    ->     PARTITION p1 VALUES LESS THAN (0),
    ->     PARTITION p2 VALUES LESS THAN (5),
    ->     PARTITION p3 VALUES LESS THAN (10),
    ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.05 sec)

If you remove theNO_UNSIGNED_SUBTRACTION server SQL mode after creating tu, you may no longer be able to access this table:

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tu;
ERROR 1563 (HY000): Partition constant is out of partition function domain
mysql> INSERT INTO tu VALUES (20);
ERROR 1563 (HY000): Partition constant is out of partition function domain

See also Section 7.1.11, “Server SQL Modes”.

Server SQL modes also impact replication of partitioned tables. Disparate SQL modes on source and replica can lead to partitioning expressions being evaluated differently; this can cause the distribution of data among partitions to be different in the source's and replica's copies of a given table, and may even cause inserts into partitioned tables that succeed on the source to fail on the replica. For best results, you should always use the same server SQL mode on the source and on the replica.

Performance considerations. Some effects of partitioning operations on performance are given in the following list:

Maximum number of partitions. The maximum possible number of partitions for a given table not using the NDB storage engine is 8192. This number includes subpartitions.

The maximum possible number of user-defined partitions for a table using the NDB storage engine is determined according to the version of the NDB Cluster software being used, the number of data nodes, and other factors. SeeNDB and user-defined partitioning, for more information.

If, when creating tables with a large number of partitions (but less than the maximum), you encounter an error message such asGot error ... from storage engine: Out of resources when opening file, you may be able to address the issue by increasing the value of theopen_files_limit system variable. However, this is dependent on the operating system, and may not be possible or advisable on all platforms; seeSection B.3.2.16, “File Not Found and Similar Errors”, for more information. In some cases, using large numbers (hundreds) of partitions may also not be advisable due to other concerns, so using more partitions does not automatically lead to better results.

See alsoFile system operations.

Foreign keys not supported for partitioned InnoDB tables. Partitioned tables using the InnoDB storage engine do not support foreign keys. More specifically, this means that the following two statements are true:

  1. No definition of an InnoDB table employing user-defined partitioning may contain foreign key references; no InnoDB table whose definition contains foreign key references may be partitioned.
  2. No InnoDB table definition may contain a foreign key reference to a user-partitioned table; noInnoDB table with user-defined partitioning may contain columns referenced by foreign keys.

The scope of the restrictions just listed includes all tables that use the InnoDB storage engine.CREATE TABLE and ALTER TABLE statements that would result in tables violating these restrictions are not allowed.

ALTER TABLE ... ORDER BY. An ALTER TABLE ... ORDER BY_`column`_ statement run against a partitioned table causes ordering of rows only within each partition.

ADD COLUMN ... ALGORITHM=INSTANT. Once you performALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT on a partitioned table, it is no longer possible to exchange partitions with this table.

Effects on REPLACE statements by modification of primary keys. It can be desirable in some cases (seeSection 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”) to modify a table's primary key. Be aware that, if your application uses REPLACE statements and you do this, the results of these statements can be drastically altered. See Section 15.2.12, “REPLACE Statement”, for more information and an example.

FULLTEXT indexes. Partitioned tables do not support FULLTEXT indexes or searches.

Spatial columns. Columns with spatial data types such as POINT or GEOMETRY cannot be used in partitioned tables.

Temporary tables. Temporary tables cannot be partitioned.

Log tables. It is not possible to partition the log tables; anALTER TABLE ... PARTITION BY ... statement on such a table fails with an error.

Data type of partitioning key. A partitioning key must be either an integer column or an expression that resolves to an integer. Expressions employingENUM columns cannot be used. The column or expression value may also be NULL; see Section 26.2.7, “How MySQL Partitioning Handles NULL”.

There are two exceptions to this restriction:

  1. When partitioning by [LINEAR]KEY, it is possible to use columns of any valid MySQL data type other thanTEXT orBLOB as partitioning keys, because the internal key-hashing functions produce the correct data type from these types. For example, the following twoCREATE TABLE statements are valid:
CREATE TABLE tkc (c1 CHAR)  
PARTITION BY KEY(c1)  
PARTITIONS 4;  
CREATE TABLE tke  
    ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') )  
PARTITION BY LINEAR KEY(c1)  
PARTITIONS 6;
  1. When partitioning by RANGE COLUMNS orLIST COLUMNS, it is possible to use string,DATE, andDATETIME columns. For example, each of the following CREATE TABLE statements is valid:
CREATE TABLE rc (c1 INT, c2 DATE)  
PARTITION BY RANGE COLUMNS(c2) (  
    PARTITION p0 VALUES LESS THAN('1990-01-01'),  
    PARTITION p1 VALUES LESS THAN('1995-01-01'),  
    PARTITION p2 VALUES LESS THAN('2000-01-01'),  
    PARTITION p3 VALUES LESS THAN('2005-01-01'),  
    PARTITION p4 VALUES LESS THAN(MAXVALUE)  
);  
CREATE TABLE lc (c1 INT, c2 CHAR(1))  
PARTITION BY LIST COLUMNS(c2) (  
    PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),  
    PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'),  
    PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)  
);

Neither of the preceding exceptions applies toBLOB orTEXT column types.

Subqueries. A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL.

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 allowed 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 a prefix for one of them. This statement is rejected with an error, as shown here:

mysql> USE d;
Database changed
mysql> CREATE TABLE t1 (
    ->     a VARCHAR(10000),
    ->     b VARCHAR(25),
    ->     c VARCHAR(10),
    ->     PRIMARY KEY (a(10), b, c)
    -> ) PARTITION BY KEY() PARTITIONS 2;
ERROR 6123 (HY000): Column 'd.t1.a' having prefix key part 'a(10)' in the
PARTITION BY KEY() clause is not supported.

For general information about partitioning tables by key, seeSection 26.2.5, “KEY Partitioning”.

Issues with subpartitions. Subpartitions must use HASH orKEY partitioning. OnlyRANGE and LIST partitions may be subpartitioned; HASH andKEY partitions cannot be subpartitioned.

SUBPARTITION BY KEY requires that the subpartitioning column or columns be specified explicitly, unlike the case with PARTITION BY KEY, where it can be omitted (in which case the table's primary key column is used by default). Consider the table created by this statement:

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
);

You can create a table having the same columns, partitioned byKEY, using a statement such as this one:

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
)
PARTITION BY KEY()
PARTITIONS 4;

The previous statement is treated as though it had been written like this, with the table's primary key column used as the partitioning column:

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
)
PARTITION BY KEY(id)
PARTITIONS 4;

However, the following statement that attempts to create a subpartitioned table using the default column as the subpartitioning column fails, and the column must be specified for the statement to succeed, as shown here:

mysql> CREATE TABLE ts (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(30)
    -> )
    -> PARTITION BY RANGE(id)
    -> SUBPARTITION BY KEY()
    -> 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. The directory specified in a DATA DIRECTORY clause must be known to InnoDB. For more information, seeUsing the DATA DIRECTORY Clause.

Repairing and rebuilding partitioned tables. The statements CHECK TABLE,OPTIMIZE TABLE,ANALYZE TABLE, andREPAIR 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. SeeSection 15.1.9, “ALTER TABLE Statement”, for more information about these two statements.

ANALYZE, CHECK,OPTIMIZE, REPAIR, andTRUNCATE operations are supported with subpartitions. SeeSection 15.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.