MySQL 파티션 추가 삭제 - Error Code: 1493 - 파티션 테이블 - REORGANIZE PARTITION

Last Updated on 12월 27, 2023 by Jade(정현호)

안녕하세요.
이번 포스팅에서는 월별로 되어있는 range 파티션에 파티션 추가 관련하여 maxvalue 유무, 앞에 추가 또는 뒤에 추가에 따른 명령어 사용 등을 확인해보도록 하겠습니다.

Contents

테스트 테이블 생성
파티션 앞에 추가
파티션 끝에 추가
- REORGANIZE 실행 후 통계정보
파티션 삭제

테스트 테이블 생성

아래와 같이 테스트용 테이블을 생성하도록 하겠습니다.

mysql> use test_db;

mysql> CREATE TABLE `tb_part_test` (
  `seq` int(11) NOT NULL AUTO_INCREMENT,
  `col1` decimal(3,1) DEFAULT NULL,
  `col2` decimal(5,1) DEFAULT NULL,
  `col3` decimal(5,1) DEFAULT NULL,
  `col4` varchar(20) DEFAULT NULL,
  `datetime` varchar(13) NOT NULL COMMENT '날짜(yyyy-mm-dd;hh)',
  PRIMARY KEY (`seq`,`datetime`),
  KEY `datetime_01` (`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(`datetime`)
(PARTITION p202101 VALUES LESS THAN ('2021-02-01:00') ENGINE = InnoDB,
 PARTITION p202102 VALUES LESS THAN ('2021-03-01:00') ENGINE = InnoDB,
 PARTITION p202103 VALUES LESS THAN ('2021-04-01:00') ENGINE = InnoDB,
 PARTITION p202104 VALUES LESS THAN ('2021-05-01:00') ENGINE = InnoDB,
 PARTITION p202105 VALUES LESS THAN ('2021-06-01:00') ENGINE = InnoDB,
 PARTITION p202106 VALUES LESS THAN ('2021-07-01:00') ENGINE = InnoDB,
 PARTITION p202107 VALUES LESS THAN ('2021-08-01:00') ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN ('2021-09-01:00') ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN ('2021-10-01:00') ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN ('2021-11-01:00') ENGINE = InnoDB,
 PARTITION p202111 VALUES LESS THAN ('2021-12-01:00') ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN ('2022-01-01:00') ENGINE = InnoDB,
 PARTITION p999999 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
 ;

파티션 앞에 추가

파티션 앞에 추가 시 에는 maxvalue 파티션의 유무와 상관없이 add partition 사용시 1493 에러가 발생되게 됩니다.

mysql> alter table test_db.tb_part_test add partition (
    partition p202012 values less than ('2021-01-01:00') engine=innodb 
);

SQL Error [1493] [HY000]: VALUES LESS THAN value must be strictly increasing for each partition

maxvalue 파티션 유무와 상관없이 맨 앞에 파티션추가시 Error Code: 1493 가 발생하며 이러한 경우 reorganize table 을 통해 진행해야 합니다.

p202101 파티션을 지정하여 REORGANIZE PARTITION 를 수행합니다.

mysql> alter table test_db.tb_part_test
REORGANIZE PARTITION p202101 into (
    partition p202012 values less than ('2021-01-01:00') engine=innodb,
    partition p202101 values less than ('2021-02-01:00') engine=innodb
);

참고로 8.0 에서는 Online DDL 기능이 강회되어 range 파티션에 대한 REORGANIZE PARTITION 작업도 INPLACE ALGORITHM 사용이 가능합니다.

mysql> alter table test_db.tb_part_test ALGORITHM=INPLACE,
REORGANIZE PARTITION p202101 into (
    partition p202012 values less than ('2021-01-01:00') engine=innodb,
    partition p202101 values less than ('2021-02-01:00') engine=innodb
);

MySQL Online DDL 관련된 내용은 아래 포스팅을 참조하시면 됩니다.

MySQL Online DDL - InnoDB Online DDL - Alter Online

Fast Index Creation 기능이 추가된 5.5 버전의 DDL 처리에 대한 부분이 개선되어 Mysql 5.6 부터 DDL 작업에 대해서 Online DDL 이 추가 되었습니다.

MySQL 5.7에서 range 파티션에 대해서 reorganize partition 작업은 COPY 방식으로 수행됩니다.(ALGORITHM=INPLACE 사용시 에러, INPLACE 방식을 미지원)

Error Code: 1846. ALGORITHM=COPY/INPLACE is not supported. Reason: Partition specific operations do not yet support LOCK/ALGORITHM. Try ALGORITHM=DEFAULT.

파티션 끝에 추가

이번에는 파티션 끝에 추가를 진행하겠습니다 p202112 이 있고 p202201 파티션 추가가 필요한 상황입니다.

이 경우 maxvalue 파티션 유무에 따라 수행 방법이 달라 지게 됩니다. maxvalue 파티션이 없을 경우 아래와 같이 alter table 로 추가할 수 있습니다.

mysql> alter table test_db.tb_part_test 
add partition (
 PARTITION p202201 VALUES LESS THAN ('2022-02-01:00') ENGINE = InnoDB
 );

이 경우도 MySQL 8.0 에서는 ALGORITHM=INPLACE 를 사용할 수 있습니다.

maxvalue 파티션이 있는 경우에 add partition 로 추가하게 되면 Error Code: 1493 가 발생되게 됩니다

mysql> alter table test_db.tb_part_test
add partition (
 PARTITION p202201 VALUES LESS THAN ('2022-02-01:00') ENGINE = InnoDB
 );

Error Code: 1493. VALUES LESS THAN value must be strictly increasing for each partition

이럴 경우 REORGANIZE PARTITION 를 사용해야 합니다.
maxvalue 파티션은 p999999 에 대해서 REORGANIZE PARTITION 를 수행을 하면 됩니다.

mysql> ALTER TABLE test_db.tb_part_test
REORGANIZE PARTITION p999999 into (    
  partition p202201 values less than ('2022-02-01:00') engine=innodb,
  PARTITION p999999 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
);

이 경우도 MySQL 8.0 에서는 ALGORITHM=INPLACE 를 사용 할 수 있습니다.

REORGANIZE 실행 후 통계정보

REORGANIZE 실행 후 통계정보 관련하여 내용을 확인해보기 위해서 먼저 아래와 같이 테스트 테이블을 생성하도록 하겠습니다.

CREATE TABLE `TB_TEST_PART` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `BUSINESS_DATE` varchar(8) NOT NULL,
  `ORDER_CODE` varchar(50) NOT NULL,
  PRIMARY KEY (`ID`, `BUSINESS_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
partition by range COLUMNS(`BUSINESS_DATE`) (
PARTITION p202201 VALUES LESS THAN ('20220201') ENGINE = InnoDB,
PARTITION p202202 VALUES LESS THAN ('20220301') ENGINE = InnoDB,
partition p999999 values less than MAXVALUE ENGINE=InnoDB);

파티션은 2022년 1월 파티션, 2022년 2월 파티션 그리고 Maxvalue 파티션을 생성하였습니다.

그리고 아래와 같이 데이터를 입력하도록 하겠습니다.

insert into TB_TEST_PART(BUSINESS_DATE,ORDER_CODE) values('20220101',LEFT(UUID(), 8)),
('20220102',LEFT(UUID(), 8)),('20220103',LEFT(UUID(), 8)),('20220104',LEFT(UUID(), 8))
,('20220105',LEFT(UUID(), 8)),('20220106',LEFT(UUID(), 8)),
('20220107',LEFT(UUID(), 8)),('20220108',LEFT(UUID(), 8)),
('20220109',LEFT(UUID(), 8)),('20220110',LEFT(UUID(), 8));


insert into TB_TEST_PART(BUSINESS_DATE,ORDER_CODE) values('20220201',LEFT(UUID(), 8)),
('20220202',LEFT(UUID(), 8)),('20220203',LEFT(UUID(), 8)),('20220204',LEFT(UUID(), 8))
,('20220205',LEFT(UUID(), 8)),('20220206',LEFT(UUID(), 8)),
('20220207',LEFT(UUID(), 8)),('20220208',LEFT(UUID(), 8)),
('20220209',LEFT(UUID(), 8)),('20220210',LEFT(UUID(), 8));


insert into TB_TEST_PART(BUSINESS_DATE,ORDER_CODE) values('20220301',LEFT(UUID(), 8)),
('20220302',LEFT(UUID(), 8)),('20220303',LEFT(UUID(), 8)),('20220304',LEFT(UUID(), 8))
,('20220305',LEFT(UUID(), 8)),('20220306',LEFT(UUID(), 8)),
('20220307',LEFT(UUID(), 8)),('20220308',LEFT(UUID(), 8)),
('20220309',LEFT(UUID(), 8)),('20220310',LEFT(UUID(), 8));


insert into TB_TEST_PART(BUSINESS_DATE,ORDER_CODE) values('20220401',LEFT(UUID(), 8)),
('20220404',LEFT(UUID(), 8)),('20220404',LEFT(UUID(), 8)),('20220401',LEFT(UUID(), 8))
,('20220404',LEFT(UUID(), 8)),('20220404',LEFT(UUID(), 8)),
('20220404',LEFT(UUID(), 8)),('20220404',LEFT(UUID(), 8));

2022년 1월 ~ 4월까지의 데이터를 입력하였으며, 1월~3월까지는 10건, 4월은 8건을 입력하였습니다.
통계정보를 확인하면 아래와 같이 확인할 수 있습니다.

SELECT TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION POSITION,
PARTITION_DESCRIPTION PART_DESC, TABLE_ROWS,AVG_ROW_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE 1=1
and TABLE_NAME='TB_TEST_PART';

+--------------+----------------+----------+------------+------------+----------------+
| TABLE_NAME   | PARTITION_NAME | POSITION | PART_DESC  | TABLE_ROWS | AVG_ROW_LENGTH |
+--------------+----------------+----------+------------+------------+----------------+
| TB_TEST_PART | p202201        |        1 | '20220201' |         10 |           1638 |
| TB_TEST_PART | p202202        |        2 | '20220301' |         10 |           1638 |
| TB_TEST_PART | p999999        |        3 | MAXVALUE   |         18 |            910 |
+--------------+----------------+----------+------------+------------+----------------+

위에서 입력한 내용과 같이 1월 과 2월 파티션에는 각각 10건씩 입력되어 있으며, p999999 파티션에는 18건이 입력되어 있는 상태입니다.

위와 같은 상황에서 REORGANIZE PARTITION 를 수행하여 3, 4, 5월 파티션을 추가하도록 하겠습니다.
그리고 지금은 2022년 4월 이라고 가정하도록 하겠습니다.

ALTER TABLE TB_TEST_PART
REORGANIZE PARTITION p999999 INTO (
 PARTITION p202203 VALUES LESS THAN ('20220401') ENGINE = InnoDB,
 PARTITION p202204 VALUES LESS THAN ('20220501') ENGINE = InnoDB,
 PARTITION p202205 VALUES LESS THAN ('20220601') ENGINE = InnoDB,
 PARTITION p999999 VALUES less than maxvalue engine=InnoDB
);

추가가 완료된 다음에 4월달 일자가 지나서 2건이 더 추가되었다고 가정하여 2건을 더 입력하도록 하겠습니다
(모든 달이 10건씩 입력이 된 상태)

insert into TB_TEST_PART(BUSINESS_DATE,ORDER_CODE) values
('20220409',LEFT(UUID(), 8)),('20220410',LEFT(UUID(), 8));

그 다음에 다시 통계정보를 확인해보도록 하겠습니다.

SELECT TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION POSITION,
PARTITION_DESCRIPTION PART_DESC, TABLE_ROWS,AVG_ROW_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE 1=1
and TABLE_NAME='TB_TEST_PART';

+--------------+----------------+----------+------------+------------+----------------+
| TABLE_NAME   | PARTITION_NAME | POSITION | PART_DESC  | TABLE_ROWS | AVG_ROW_LENGTH |
+--------------+----------------+----------+------------+------------+----------------+
| TB_TEST_PART | p202201        |        1 | '20220201' |         10 |           1638 |
| TB_TEST_PART | p202202        |        2 | '20220301' |         10 |           1638 |
| TB_TEST_PART | p202203        |        3 | '20220401' |          0 |              0 | <!!-- 통계정보가 없음
| TB_TEST_PART | p202204        |        4 | '20220501' |         10 |           1638 |
| TB_TEST_PART | p202205        |        5 | '20220601' |          0 |              0 |
| TB_TEST_PART | p999999        |        6 | MAXVALUE   |          0 |              0 |
+--------------+----------------+----------+------------+------------+----------------+

3월 통계정보는 갱신이 안되고 있는 상태를 확인할 수 있습니다. 왜냐하면 지금은 4월달 이기 때문에 3월은 지나간 달이고 그에 따라서 추가적인 데이터가 들어오거나 갱신이 없는 상황이기 때문 입니다.(시나리오상 가정입니다)

이와 같은 시나리오 처럼 기존의 maxvaue 에 있던 데이터를 REORGANIZE PARTITION 를 하였을 경우, 해당 파티션이 시간이 지나서 추가적인 데이터의 갱신이나 추가(INSERT)가 없을 경우 통계정보가 갱신이 안될 수도 있습니다.

통계정보 자동 갱신에 대한 조건 과 관련된 추가 내용은 아래 포스팅을 참고하시면 됩니다.

MySQL 통계정보 - 테이블 통계 - 인덱스 통계

안녕하세요 이번 포스팅에서는 MySQL 의 통계정보에 대해서 테이블 통계 와 인덱스 통계 각각에 대해서 확인 해보도록 하겠습니다.

그래서 REORGANIZE PARTITION 이후 통계정보가 없을 경우 파티션 단위로 통계정보를 갱신해주는 작업도 상황에 따라서 필요할 수도 있습니다. 파티션 단위의 통계정보 갱신은 다음과 같이 진행할 수 있습니다.

ALTER TABLE 테이블 ANALYZE PARTITION 파티션명

3월달 파티션의 통계정보를 갱신하고 통계정보를 다시 확인해 보도록 하겠습니다.

ALTER TABLE TB_TEST_PART ANALYZE PARTITION p202203;

+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| test_db.TB_TEST_PART | analyze | status   | OK       |
+----------------------+---------+----------+----------+



SELECT TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION POSITION,
PARTITION_DESCRIPTION PART_DESC, TABLE_ROWS,AVG_ROW_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE 1=1
and TABLE_NAME='TB_TEST_PART';

+--------------+----------------+----------+------------+------------+----------------+
| TABLE_NAME   | PARTITION_NAME | POSITION | PART_DESC  | TABLE_ROWS | AVG_ROW_LENGTH |
+--------------+----------------+----------+------------+------------+----------------+
| TB_TEST_PART | p202201        |        1 | '20220201' |         10 |           1638 |
| TB_TEST_PART | p202202        |        2 | '20220301' |         10 |           1638 |
| TB_TEST_PART | p202203        |        3 | '20220401' |         10 |           1638 |<!!-- 통계정보가 갱신됨
| TB_TEST_PART | p202204        |        4 | '20220501' |         10 |           1638 |
| TB_TEST_PART | p202205        |        5 | '20220601' |          0 |              0 |
| TB_TEST_PART | p999999        |        6 | MAXVALUE   |          0 |              0 |
+--------------+----------------+----------+------------+------------+----------------+

파티션 삭제

위에서 생성한 파티션을 다시 삭제하도록 하겠습니다. 삭제는 maxvalue 파티션과 무관하게 아래의 명령어로 삭제를 할 수 있습니다.

-- 문법 : ALTER TABLE 테이블명 DROP PARTITION 파티션명;
mysql> ALTER TABLE test_db.tb_part_test DROP PARTITION p202012;

mysql> ALTER TABLE test_db.tb_part_test DROP PARTITION p202201;

연관된 다른 글

MySQL Clustered Index 와 Fast Index Creation In InnoDB

Clustered Index(클러스터형 인덱스)기본 키(PK) 인덱스에 대한 InnoDB 용어입니다 InnoDB의 테이블은 Primary Key 값을 기반으로 구성되며 Cluster 라는 용어 의미대로 PK 값이 비슷한 레코드끼리 묶어서 저장되게 됩니다.

MySQL Online DDL - InnoDB Online DDL - Alter Online

안녕하세요 이번 포스팅에서는 MySQL Index 의 online DDL 에 대한 내용과 버전 별 내용을 확인 해보도록 하겠습니다.

MySQL Partition 파티션(1) - 정의와 기능 설명 파티션 제약사항

파티션이란 MySQL 서버 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 기능을 의미 합니다.

MySQL Partition 파티션(2) - 파티션 종류 와 파티션 테이블 생성 과 변경

안녕하세요 이번 포스팅에서는 파티션 의 종류 와 MySQL에서의 파티션 테이블 생성 과 변경에 대해서 확인 해보도록 하겠습니다.

MySQL 통계정보 - 테이블 통계 - 인덱스 통계

안녕하세요 이번 포스팅에서는 MySQL 의 통계정보에 대해서 테이블 통계 와 인덱스 통계 각각에 대해서 확인 해보도록 하겠습니다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io

bug.mysql.com

Statistics

MySQL 파티션 추가 삭제 - Error Code: 1493 - 파티션 테이블 - REORGANIZE PARTITION

테스트 테이블 생성

파티션 앞에 추가

파티션 끝에 추가

REORGANIZE 실행 후 통계정보

파티션 삭제

You may also like...

테스트 테이블 생성

파티션 앞에 추가

파티션 끝에 추가

REORGANIZE 실행 후 통계정보

파티션 삭제

You may also like...

MySQL 8.0 - Partial update of JSON values(Partial Update of LOB) LOB 의 부분 업데이트

MySQL - 히스토그램 - Histogram - MySQL 8.0 - 통계정보

MySQL - 확장 검색 - 전문검색 FullText Search - Real MySQL 8.0