MySQL Forums
Forum List  »  Partitioning

Re: Syntax of creating partitions by binary column
Posted by: Jonathan Stephens
Date: March 09, 2007 12:03AM

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
Orlando, Florida, USA

MySQL Dev Zone
MySQL Server Documentation
Oracle

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Syntax of creating partitions by binary column
3625
March 09, 2007 12:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.