PolarDB:DDL for cold data

Check whether OSS META is enabled

Run SHOW CREATE TABLE. If OSS META=1 appears in the output, OSS META is enabled.

show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` varchar(1000) DEFAULT NULL
) /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */
1 row in set (0.00 sec)

Enable OSS META for new tables

Set the use_oss_meta parameter to ON. This applies to all three archiving scenarios: InnoDB non-partitioned tables archived to OSS foreign tables, InnoDB partitioned tables archived to OSS foreign tables, and InnoDB tables archived to OSS partitions.

Verify the parameter is active:

show variables like "use_oss_meta";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| use_oss_meta  | ON    |
+---------------+-------+
1 row in set (0.03 sec)

When use_oss_meta is ON, newly archived tables automatically include the OSS META marker. The following examples show the marker appearing after archiving a non-partitioned table and a partitioned table:

alter table t engine = csv storage oss;
Query OK, 3 rows affected (2.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` varchar(1000) DEFAULT NULL
) /*!50100 */ /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */
1 row in set (0.00 sec)


alter table t1 change partition p0 engine = orc;
Query OK, 0 rows affected (1.95 sec)
Records: 0  Duplicates: 0  Warnings: 0


show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020223 OSS META=1 */ CONNECTION='default_oss_server'
/*!99990 800020205 PARTITION BY RANGE  COLUMNS(id)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = ORC,
 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (40) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (60) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (70) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (80) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (90) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (110) ENGINE = InnoDB) */
1 row in set (0.00 sec)

Enable OSS META for existing tables

Run REPAIR TABLE to add OSS META to an existing table:

repair table t;
+--------+--------+----------+----------+
| Table  | Op     | Msg_type | Msg_text |
+--------+--------+----------+----------+
| test.t | repair | status   | OK       |
+--------+--------+----------+----------+
1 row in set (0.84 sec)

show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` varchar(1000) DEFAULT NULL
) /*!50100 */ /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */
1 row in set (0.00 sec)

Disable OSS META

Run ALTER TABLE ... DISABLE OSS META to remove the OSS META marker:

alter table t disable oss meta;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` varchar(1000) DEFAULT NULL
) /*!50100 */ /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */
1 row in set (0.00 sec)

Column operations

Footnote 1 — Add a column: The Instant ADD COLUMN feature adds columns only to the end of a table with OSS META enabled. If the table has no primary key, set implicit_primary_key to OFF to avoid conflicts with an automatically generated implicit primary key column. If the cluster does not support Instant ADD COLUMN, use the COPY algorithm instead — a table rebuild is required, though concurrent reads are allowed during the rebuild.

Footnote 2 — Change utf8mb3 to utf8mb4: Changing a column's character set from utf8mb3 to utf8mb4 modifies only metadata when all three conditions are met:

• The column type is CHAR, VARCHAR, ENUM, or TEXT

• No indexes are created on the column

• The maximum storage length of the column (before and after conversion) remains either below 256 bytes or above 255 bytes

If any condition is not met, the COPY algorithm is used — the table is locked and only reads are allowed during the rebuild. To force INSTANT and get an immediate error if it is not applicable:

ALTER TABLE test modify column b char(1) CHARACTER SET utf8mb4 default null,algorithm = INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

Footnote 3 — ENUM or SET column: Metadata-only change applies only when new elements are added to the end of the ENUM or SET column and the storage size of the data type remains unchanged. Otherwise, the COPY algorithm is used with a full table rebuild.

Table operations

Footnote 1 — Rename a table: When renaming a table stored in OSS, the OSS data files are renamed rather than rewritten. Speed is proportional to table size and is slightly slower than other INSTANT operations that modify only metadata.