[MySQL] SQL Error [1486] [HY000]: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

개요

아래와 같은 구조의 product 테이블이 존재한다고 하자.

CREATE TABLE product (
    id INT NOT NULL,
    name TEXT,
    crt_date varchar(30) NOT NULL,
    PRIMARY KEY(ID, crt_date)
);

테이블의 crt_date는 "YYYY-MM-DD HH:MM:SS" 형식의 데이터로, 다음 SQL을 실행하여 crt_date을 기준으로 수평 파티셔닝(range)을 하려고 한다. 

ALTER TABLE product PARTITION BY RANGE(TO_DAYS(crt_date))(
  PARTITION p20201230 VALUES LESS THAN (TO_DAYS('2020-12-31')),
  PARTITION p20201231 VALUES LESS THAN (TO_DAYS('2021-01-01')),
  PARTITION p20210101 VALUES LESS THAN (TO_DAYS('2021-01-02'))
);

하지만 SQL을 실행 시 아래와 같은 오류 메시지와 함께 실행에 실패했다.

SQL Error [1486] [HY000]: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

원인을 확인하고 해결해보자.

 

 

원인

파티셔닝 키는 정수, 또는 정수로 해석되는 식이어야 한다. 단, 아래의 두 가지 상황은 예외적으로 허용한다.

 

• [LINEAR] KEY로 파티셔닝할 때 MySQL의 내부 키 해싱 함수가 올바른 데이터 형식을 생성하므로 TEXT 또는 BLOB 이외의 유효한 MySQL 데이터 형식의 열을 파티셔닝 키로 사용할 수 있다.

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

CREATE TABLE tke 
    ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') ) 
PARTITION BY LINEAR KEY(c1) 
PARTITIONS 6;

• RANGE COLUMNS 또는 LIST COLUMNS로 파티셔닝할 때 문자열, DATE 및 DATETIME 컬럼을 사용할 수 있다.

단, BLOB 또는 TEXT 컬럼형에는 예외가 적용되지 않는다.

 

즉, 문자형 테이터 컬럼으로 파티셔닝을 시도한 게 원인이었다.

 

 

해결

마침 crt_date 데이터가 DATETIME 형식과 동일한 형식으로 저장되므로, crt_date의 데이터 타입을 DATETIME으로 변경하여 해결했다.

 

컬럼 데이터 타입 변경

ALTER TABLE product 
  MODIFY crt_date DATETIME DEFAULT CURRENT_TIMESTAMP;

 

파티셔닝 재시도 후 파티셔닝 확인

ALTER TABLE product PARTITION BY RANGE(TO_DAYS(crt_date))(
  PARTITION p20201230 VALUES LESS THAN (TO_DAYS('2020-12-31')),
  PARTITION p20201231 VALUES LESS THAN (TO_DAYS('2021-01-01')),
  PARTITION p20210101 VALUES LESS THAN (TO_DAYS('2021-01-02'))
);

-- 파티셔닝 확인
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'product';

정상적으로 파티셔닝이 이뤄진 것을 볼 수 있다.

 

 

참고 문서

https://stackoverflow.com/questions/18630927/mysql-database-error-constant-random-or-timezone-dependent-expressions-in-sub