Skip to content

How to Resolve ERROR 1564 (HY000): This partition function is not allowed

  • MySQL

ERROR 1564 (HY000)

When we used round() function for the partition key, we got ERROR 1564 (HY000).

mysql> alter table sales partition by range(unix_timestamp(order_time)) partitions 1 (partition p2013 values less than (round(unix_timestamp('2014-01-01 00:00:00'))) engine=innodb);
ERROR 1564 (HY000): This partition function is not allowed

OK, round() is not allowed, and what function should I use to convert it back to integer?

According to MySQL :: MySQL 8.0 Reference Manual :: 24.6.3 Partitioning Limitations Relating to Functions. There're 23 functions are allowable to be used for partition key, round() is not included.

  • ABS()
  • CEILING()
  • DATEDIFF()
  • DAY()
  • DAYOFMONTH()
  • DAYOFWEEK()
  • DAYOFYEAR()
  • EXTRACT()
  • FLOOR()
  • HOUR()
  • MICROSECOND()
  • MINUTE()
  • MOD()
  • MONTH()
  • QUARTER()
  • SECOND()
  • TIME_TO_SEC()
  • TO_DAYS()
  • TO_SECONDS()
  • UNIX_TIMESTAMP() (with TIMESTAMP columns)
  • WEEKDAY()
  • YEAR()
  • YEARWEEK()

Leave a Reply

Your email address will not be published. Required fields are marked *