18.5 Restrictions and Limitations on Partitioning

18.5.1 Partitioning Keys, Primary Keys, and Unique Keys
18.5.2 Partitioning Limitations Relating to Storage Engines
18.5.3 Partitioning Limitations Relating to Functions
18.5.4 Partitioning and Table-Level Locking

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

Prohibited constructs.  Beginning with MySQL 5.1.12, the following constructs are not permitted in partitioning expressions:

For a list of SQL functions which are permitted in partitioning expressions, see Section 18.5.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; see Section 18.2, “Partitioning Types”, for more information).

Beginning with MySQL 5.1.23, the DIV operator is also supported, and the / operator is not permitted. (Bug #30188, Bug #33182)

Beginning with MySQL 5.1.12, the bit operators |, &, ^, <<, >>, and ~ are not permitted in partitioning expressions.

HANDLER statements.  In MySQL 5.1, the HANDLER statement is not supported with partitioned tables.

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 in Section 5.1.7, “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.

Examples.  The following examples illustrate some changes in behavior of partitioned tables due to a change in the server SQL mode:

  1. Error handling.  Suppose that you create a partitioned table whose partitioning expression is one such as column DIV 0 or column MOD 0, as shown here:

    mysql> CREATE TABLE tn (c1 INT)
        ->     PARTITION BY LIST(1 DIV c1) (
        ->       PARTITION p0 VALUES IN (NULL),
        ->       PARTITION p1 VALUES IN (1)
        -> );
    Query OK, 0 rows affected (0.05 sec)
    

    The default behavior for MySQL is to return NULL for the result of a division by zero, without producing any errors:

    mysql> SELECT @@sql_mode;
    +------------+
    | @@sql_mode |
    +------------+
    |            |
    +------------+
    1 row in set (0.00 sec)
    
    
    mysql> INSERT INTO tn VALUES (NULL), (0), (1);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    

    However, changing the server SQL mode to treat division by zero as an error and to enforce strict error handling causes the same INSERT statement to fail, as shown here:

    mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> INSERT INTO tn VALUES (NULL), (0), (1);
    ERROR 1365 (22012): Division by 0
    
  2. Table accessibility.  Sometimes a change in the server SQL mode can make partitioned tables unusable. The following CREATE TABLE statement can be executed successfully only if the NO_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 the NO_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
    

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

Performance considerations.  Some affects 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 (that does not use the NDB storage engine) is 1024. This number includes subpartitions.

The maximum possible number of user-defined partitions for a table using the NDBCLUSTER storage engine is determined according to the version of the MySQL Cluster software being used, the number of data nodes, and other factors. See NDB 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 as Got error ... from storage engine: Out of resources when opening file, you may be able to address the issue by increasing the value of the open_files_limit system variable. However, this is dependent on the operating system, and may not be possible or advisable on all platforms; see Section B.5.2.18, “'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 also File 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; no InnoDB 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.

FULLTEXT indexes.  Partitioned tables do not support FULLTEXT indexes or searches. This includes partitioned tables employing the MyISAM storage engine.

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

Temporary tables.  As of MySQL 5.1.8, temporary tables cannot be partitioned. (Bug #17497)

Log tables.  Beginning with MySQL 5.1.20, it is no longer possible to partition the log tables; beginning with that version, an ALTER TABLE ... PARTITION BY ... statement on such a table fails with an error. (Bug #27816)

Data type of partitioning key.  A partitioning key must be either an integer column or an expression that resolves to an integer. The column or expression value may also be NULL. (See Section 18.2.6, “How MySQL Partitioning Handles NULL.)

The lone exception to this restriction occurs when partitioning by [LINEAR] KEY, where it is possible to use columns of other types as partitioning keys, because MySQL's internal key-hashing functions produce the correct data type from these types. For example, the following CREATE TABLE statement is valid:

CREATE TABLE tkc (c1 CHAR)
PARTITION BY KEY(c1)
PARTITIONS 4;

This exception does not apply to BLOB or TEXT column types.

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

Issues with subpartitions.  Subpartitions must use HASH or KEY partitioning. Only RANGE and LIST partitions may be subpartitioned; HASH and KEY partitions cannot be subpartitioned.

Currently, 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 by KEY, 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).

Query cache not supported.  The query cache is not supported for partitioned tables. Beginning with MySQL 5.1.63, the query cache is automatically disabled for queries involving partitioned tables, and cannot be enabled for such queries. (Bug #53775)

Key cache not supported.  Caching is not supported for partitioned tables. The CACHE INDEX and LOAD INDEX INTO CACHE statements, when you attempt to use them on tables having user-defined partitioning, fail with the errors The storage engine for the table doesn't support assign_to_keycache and The storage engine for the table doesn't support preload_keys, respectively. This issue is fixed in MySQL 5.5.

DELAYED option not supported.  Use of INSERT DELAYED to insert rows into a partitioned table is not supported. Beginning with MySQL 5.1.23, attempting to do so fails with an error (see Bug #31210).

DATA DIRECTORY and INDEX DIRECTORY options.  DATA DIRECTORY and INDEX DIRECTORY are subject to the following restrictions when used with partitioned tables:

Repairing and rebuilding partitioned tables.  The statements CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE are supported for partitioned tables beginning with MySQL 5.1.27. (See Bug #20129.)

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. Both of these statements were added in MySQL 5.1.5. See Section 13.1.7, “ALTER TABLE Syntax”, for more information about these two statements.

mysqlcheck, myisamchk, and myisampack are not supported with partitioned tables.