Skip to content

How to Resolve ERROR 1486 (HY000) in MySQL

  • by
When we partitioned a table by a timestamp-typed column "order_time" in table "orders", it generated an error 1486 like this:
mysql> ALTER TABLE orders PARTITION BY LIST(year(order_time)) (PARTITION p2013 VALUES IN (2013) ) ;
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

Since the function year() could return different results by different time zones, especially when the timestamp are around the conjunctions of adjacent years. In other words, year() is indeterministic for timestamp. Therefore, year() can not be used as the boundary of timestamp-based partitions.

A solution to this problem is to use UNIX_TIMESTAMP instead of YEAR, but first, you should know the boundary of UNIX_TIMESTAMP between year 2013 and 2014.
mysql> select unix_timestamp('2014-01-01 00:00:00');
+---------------------------------------+
| unix_timestamp('2014-01-01 00:00:00') |
+---------------------------------------+
|                            1388505600 |
+---------------------------------------+
1 row in set (0.00 sec)

Now, let's try it again.
mysql> ALTER TABLE orders PARTITION BY RANGE(unix_timestamp(order_time)) (PARTITION p2013 VALUES LESS THAN (1388505600) ) ;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

The error indicates the primary key must contain the partition key.

Add this column to the primary key.
mysql> ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY (id, order_time);
Query OK, 12 rows affected (0.03 sec)
Records: 12  Duplicates: 0  Warnings: 0

Now, let's try to create partitions again.
mysql> ALTER TABLE orders PARTITION BY RANGE(unix_timestamp(order_time)) (PARTITION p2013 VALUES LESS THAN (1388505600) ) ;
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

The error indicates all the unique index must contain the partition key.

Add this column to all the unique indexes.
mysql> ALTER TABLE orders DROP INDEX order_id_UNIQUE , ADD UNIQUE INDEX order_id_UNIQUE (id, order_time) USING BTREE ;
Query OK, 12 rows affected (0.03 sec)
Records: 12  Duplicates: 0  Warnings: 0

Now, let's try to create partitions again.
mysql> ALTER TABLE orders PARTITION BY RANGE(unix_timestamp(order_time)) (PARTITION p2013 VALUES LESS THAN (1388505600) ) ;
Query OK, 12 rows affected (0.03 sec)
Records: 12  Duplicates: 0  Warnings: 0

This time, we create the partition successfully.

Let's see the query route.
mysql> explain partitions select * from orders;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | orders | p2013      | ALL  | NULL          | NULL | NULL    | NULL |   12 |       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Yes, the partition is working now.

An alternative is to give up using the timestamp-typed column as the partition key and use a date-typed column instead, in this case, we call it "order_date" as the partition key:
mysql> ALTER TABLE orders PARTITION BY RANGE(year(order_date)) SUBPARTITION BY HASH(month(order_date)) SUBPARTITIONS 12 (PARTITION p2013 VALUES LESS THAN (2014) , PARTITION p2014 VALUES LESS THAN (2015) ) ;
Query OK, 10 rows affected (0.25 sec)
Records: 10  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE orders DROP PARTITION p2014 ;
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

Leave a Reply

Your email address will not be published.