For
RANGE,
LIST, and [[b]LINEAR[/b]]
HASH partitioning, the value used as the partitioning key must be an integer. You must either use a function in the partitioning expression that converts the
VARBINARY to an integer, or use [[b]LINEAR[/b]]
KEY partitioning.
Here's the table:
mysql> select version();
+-------------------+
| version() |
+-------------------+
| 5.1.17-beta-debug |
+-------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE a1 (
-> a VARBINARY(16) NOT NULL DEFAULT '' PRIMARY KEY,
-> b VARCHAR(20) NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.16 sec)
This fails because the
a column value is not an integer:
mysql> ALTER TABLE a1 PARTITION BY HASH(a) PARTITIONS 4;
ERROR 1479 (HY000): The PARTITION function returns the wrong type
This works because
KEY or
LINEAR KEY can use a column of any type:
mysql> ALTER TABLE a1 PARTITION BY KEY(a) PARTITIONS 4;
Query OK, 0 rows affected (0.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
We remove the
KEY partitioning so we can try something else:
mysql> ALTER TABLE a1 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
These partitioning schemes work because each of the
ASCII() and
ORD() functions returns an integer:
mysql> ALTER TABLE a1 PARTITION BY HASH( ASCII(a) ) PARTITIONS 4;
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE a1 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE a1 PARTITION BY HASH( ORD(a) ) PARTITIONS 4;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
Similar issues arise when trying to partition based on
DATE,
TIME, or
DATETIME columns - because these are also not integer values - as discussed here:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html
Jon Stephens
MySQL Documentation Team @ Oracle
MySQL Dev Zone
MySQL Server Documentation
Oracle