PolarDB:Usage

Limits

• The DLM feature is supported only on partitioned tables, not subpartitioned tables. Partitioned tables must be of the RANGE COLUMN type.

• You cannot use the DLM feature on a partitioned table where a global secondary index (GSI) is created.

• You cannot directly modify a DLM policy for a PolarDB for MySQL cluster. To modify a DLM policy, you must delete the policy and then create a new policy.

• If a DLM policy is created on the current table, DDL operations such as adding or removing columns and modifying data types may cause inconsistent schemas between the archived table and original table. As a result, the archived data cannot be parsed. Before you perform such DDL operations, you must delete the DLM policy on the current table. To use the DLM feature later, you can create another DLM policy and specify the archived table name as a new table name. The new table name cannot be the same as the previous archived table name.

• We recommend that you use interval range partitioning to automatically create partitions and use the DLM feature to archive cold data from partitions to OSS.

• You must configure the DLM feature by executing the CREATE TABLE or ALTER TABLE statement.

• DLM policies are not displayed when the SHOW CREATE TABLE statement is executed. You can view the DLM policies that are created on all tables from the mysql.dlm_policies table.

Usage notes

• After cold data is archived, the archived table in OSS is read-only and provides poor query performance. You must test whether the archived table meets your query performance requirements in advance.

• After a partition in a partitioned table is archived to OSS, data in the partition becomes read-only. SQL statements cannot be executed on the partitioned table.

• When you perform a backup operation, data that is transferred to OSS is not backed up, and data stored in OSS cannot be restored by point of time.

Syntax

Examples

• Archive cold data to OSS foreign tables

  1. Create a DLM policy

     The following example creates a partitioned table named sales. The table uses the order_time column as the partition key and adopts interval partitioning. Additionally, an interval policy and a DLM policy are defined for the table.

     • Interval policy: When data falls out of the partitioning interval of one year, a new partition is automatically created.

     • DLM policy: When the table contains more than three partitions, the DLM policy is executed.

       • If the OSS foreign table named sales_history does not exist, create sales_history and transfer cold data to sales_history.

       • If the OSS foreign table named sales_history exists, transfer cold data to sales_history.

     Note

     To create interval range partitioned tables, some requirements must be met. For more information about interval usage, see Interval range partitioning.

     1. Create a table named sales with a DLM policy defined.

        CREATE TABLE `sales` (
          `id` int DEFAULT NULL,
          `name` varchar(20) DEFAULT NULL,
          `order_time` datetime DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
        (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
        DLM ADD POLICY test_policy TIER TO TABLE ENGINE=CSV STORAGE=OSS READ ONLY
        STORAGE TABLE_NAME = 'sales_history' ON (PARTITIONS OVER 3);

        The DLM policy is named test_policy. When the table contains more than three partitions, cold data is archived from the current table to OSS in the CSV format. The archived table is named sales_history and is read-only.

     2. The DLM policies of the current table are stored in the mysql.dlm_policies system table. You can view the details of the DLM policies in this system table. For more information about the mysql.dlm_policies system table, see Table formats. Execute the following DDL policy to view the mysql.dlm_policies system table:

        SELECT * FROM mysql.dlm_policies\G
        *************************** 1. row ***************************
                           Id: 1
                 Table_schema: test
                   Table_name: sales
                  Policy_name: test_policy
                  Policy_type: TABLE
                 Archive_type: PARTITION COUNT
                 Storage_mode: READ ONLY
               Storage_engine: CSV
                Storage_media: OSS
          Storage_schema_name: test
           Storage_table_name: sales_history
              Data_compressed: OFF
         Compressed_algorithm: NULL
                      Enabled: ENABLED
              Priority_number: 10300
        Tier_partition_number: 3
               Tier_condition: NULL
                   Extra_info: null
                      Comment: NULL
        1 row in set (0.00 sec)            

        The sales table contains three partitions. No data is archived.

     3. Insert 3,000 rows of test data to the sales table to trigger automatic creation of interval partitions.

        DROP PROCEDURE IF EXISTS proc_batch_insert;
        delimiter $$
        CREATE PROCEDURE proc_batch_insert(IN begin INT, IN end INT, IN name VARCHAR(20))
        BEGIN
        SET @insert_stmt = concat('INSERT INTO ', name, ' VALUES(? , ?, ?);');
        PREPARE stmt from @insert_stmt;
        WHILE begin <= end DO
        SET @ID1 = begin;
        SET @NAME = CONCAT(begin+begin*281313, '@stiven');
        SET @TIME = from_days(begin + 737600);
        EXECUTE stmt using @ID1, @NAME, @TIME;
        SET begin = begin + 1;
        END WHILE;
        END;
        $$
        delimiter ;
        CALL proc_batch_insert(1, 3000, 'sales');

     4. Automatic creation of interval partitions is triggered. The sales table contains more than three partitions. The new table has the following structure:

        SHOW CREATE TABLE sales\G
        *************************** 1. row ***************************
               Table: sales
        Create Table: CREATE TABLE `sales` (
          `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
        /*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
        /*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
        1 row in set (0.03 sec)

        The table contains more than three partitions. Data can be archived because the execution requirement for the DLM policy is met.

  2. Execute the DLM policy

     1. You can execute SQL statements to directly execute DLM policies or use the MySQL event feature to periodically execute DLM policies. If the O&M start time of your cluster is 1:00 every day starting from October 11, 2022, the DLM policy is executed at 1:00 every day. You can create the following event:

        CREATE EVENT dlm_system_base_event
               ON SCHEDULE EVERY 1 DAY
            STARTS '2022-10-11 01:00:00'
            do CALL 
        dbms_dlm.execute_all_dlm_policies();

        At 1:00 every day, this event begins to execute DLM policies on all tables.

     2. After the DML policy is executed, the sales table has the following structure:

        SHOW CREATE TABLE sales\G
        *************************** 1. row ***************************
               Table: sales
        Create Table: CREATE TABLE `sales` (
          `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
        /*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ INTERVAL(YEAR, 1)
        /*!50500 (PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
        1 row in set (0.01 sec)

        The current table contains only three partitions.

     3. You can view the execution records of DLM policies in the mysql.dlm_progress table. For more information about the mysql.dlm_progress table, see Table formats. View the mysql.dlm_progress table. Execution records of the current DLM policy:

        SELECT * FROM mysql.dlm_progress\G
        *************************** 1. row ***************************
                          Id: 4
                Table_schema: test
                  Table_name: sales
                 Policy_name: test_policy
                 Policy_type: TABLE
              Archive_option: PARTITIONS OVER 3
              Storage_engine: CSV
               Storage_media: OSS
             Data_compressed: OFF
        Compressed_algorithm: NULL
          Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
               Archive_stage: ARCHIVE_COMPLETE
          Archive_percentage: 100
          Archived_file_info: [{"origin": "p20200101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256316304018.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "p20210101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256316532347.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "p20220101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256316789675.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "_p20230101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256317088201.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "_p20240101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256317332186.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "_p20250101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256317596882.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}]
                  Start_time: 2023-01-09 17:25:16
                    End_time: 2023-01-09 17:25:18
                  Extra_info: null
        1 row in set (0.03 sec)

        The cold data originally stored in the p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, and _p20250101000000 partitions is transferred to OSS foreign tables.

     4. View the structure of an OSS foreign table.

        SHOW CREATE TABLE sales_history\G
        *************************** 1. row ***************************
               Table: sales_history
        Create Table: CREATE TABLE `sales_history` (
          `id` int(11) DEFAULT NULL,
          `name` varchar(20) DEFAULT NULL,
          `order_time` datetime DEFAULT NULL
        ) ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ CONNECTION='default_oss_server/sales_history'
        1 row in set (0.00 sec)

        The table is a CSV-formatted table whose data is stored in OSS.

     5. Query data in the sales and sales_history tables.

        SELECT COUNT(*) FROM sales;
        +----------+
        | count(*) |
        +----------+
        |      984 |
        +----------+
        1 row in set (0.01 sec)
        
        SELECT COUNT(*) FROM sales_history;
        +----------+
        | count(*) |
        +----------+
        |     2016 |
        +----------+
        1 row in set (0.57 sec)           

        The preceding results show that the total number of data rows is exactly 3,000, which is consistent with the number of data rows originally inserted into the sales table.

• Archive partitions in a partitioned table to OSS

  1. Create a DLM policy

     The following example creates a partitioned table named sales. The table uses the order_time column as the partition key and adopts interval partitioning. Additionally, an interval policy and a DLM policy are defined for the table.

     • Interval policy: When data falls out of the partitioning interval of one year, a new partition is automatically created.

     • DLM policy: When the table contains more than three partitions, the DLM policy is executed to transfer previous partitions to OSS.

     1. Create a table named sales.

        CREATE TABLE `sales` (
          `id` int DEFAULT NULL,
          `name` varchar(20) DEFAULT NULL,
          `order_time` datetime DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
        (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
        DLM ADD POLICY policy_part2part TIER TO PARTITION ENGINE=CSV STORAGE=OSS READ ONLY ON (PARTITIONS OVER 3);

        The DLM policy of the table is named policy_part2part. When the table contains more than three partitions, previous partitions are transferred to OSS.

     2. View DLM policies in the mysql.dlm_policies table.

        SELECT * FROM mysql.dlm_policies\G

        Sample result:

        *************************** 1. row ***************************
                           Id: 2
                 Table_schema: test
                   Table_name: sales
                  Policy_name: policy_part2part
                  Policy_type: PARTITION
                 Archive_type: PARTITION COUNT
                 Storage_mode: READ ONLY
               Storage_engine: CSV
                Storage_media: OSS
          Storage_schema_name: NULL
           Storage_table_name: NULL
              Data_compressed: OFF
         Compressed_algorithm: NULL
                      Enabled: ENABLED
              Priority_number: 10300
        Tier_partition_number: 3
               Tier_condition: NULL
                   Extra_info: null
                      Comment: NULL
        1 row in set (0.03 sec)

     3. Use the proc_batch_insert stored procedure to insert test data into the sales table to trigger automatic creation of interval partitions.

        CALL proc_batch_insert(1, 3000, 'sales');

        The following results indicate that the data is inserted:

        Query OK, 1 row affected, 1 warning (0.99 sec)

     4. Execute the following statement to view the structure of the sales table:

        SHOW CREATE TABLE sales \G

        The table has the following structure:

        *************************** 1. row ***************************
               Table: sales
        Create Table: CREATE TABLE `sales` (
          `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
        /*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
        /*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
        1 row in set (0.03 sec)

  2. Execute the DLM policy

     1. Execute the following statement to execute the DLM policy:

        CALL dbms_dlm.execute_all_dlm_policies();

     2. View the execution records of the DLM policy in the mysql.dlm_progress table.

        SELECT * FROM mysql.dlm_progress \G

        Sample result:

        *************************** 1. row ***************************
                          Id: 4
                Table_schema: test
                  Table_name: sales
                 Policy_name: policy_part2part
                 Policy_type: PARTITION
              Archive_option: PARTITIONS OVER 3
              Storage_engine: CSV
               Storage_media: OSS
             Data_compressed: OFF
        Compressed_algorithm: NULL
          Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
               Archive_stage: ARCHIVE_COMPLETE
          Archive_percentage: 100
          Archived_file_info: null
                  Start_time: 2023-09-11 18:04:39
                    End_time: 2023-09-11 18:04:40
                  Extra_info: null
        1 row in set (0.02 sec)

     3. Execute the following statement to view the structure of the sales table:

        SHOW CREATE TABLE sales \G

        Sample result:

        *************************** 1. row ***************************
               Table: sales
        Create Table: CREATE TABLE `sales` (
          `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 CONNECTION='default_oss_server'
        /*!99990 800020205 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
        /*!99990 800020205 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = CSV,
         PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = CSV,
         PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = CSV,
         PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = CSV,
         PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = CSV,
         PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = CSV,
         PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
        1 row in set (0.03 sec)

        The preceding results show that the p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, and _p20250101000000 partitions in the sales table are transferred to OSS. Only the _p20260101000000, _p20270101000000, and _p20280101000000 partitions that store hot data in the table are retained in InnoDB. The sales table becomes a hybrid partitioned table. For more information about how to query data in a hybrid partitioned table, see the "Query data in a hybrid partition table" section of the Create a hybrid partitioned table topic.

• Delete cold data

  1. Create a DLM policy

     The following example creates a partitioned table named sales. The table uses the order_time column as the partition key and adopts interval partitioning. Additionally, an interval policy and a DLM policy are defined for the table.

     • Interval policy: When data falls out of the partitioning interval of one year, a new partition is automatically created.

     • DLM policy: When the table contains more than three partitions, the DLM policy is executed to delete cold data.

     1. Create a table named sales with a DLM policy defined.

        CREATE TABLE `sales` (
          `id` int DEFAULT NULL,
          `name` varchar(20) DEFAULT NULL,
          `order_time` datetime DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
        (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
        DLM ADD POLICY test_policy TIER TO NONE ON (PARTITIONS OVER 3);

        The DLM policy of the table is named test_policy. The policy is executed when the table contains more than three partitions. Cold data is deleted when the DLM policy is executed.

     2. View the mysql.dlm_policies table. Details of the current DLM policy:

        SELECT * FROM mysql.dlm_policies\G
        *************************** 1. row ***************************
                           Id: 4
                 Table_schema: test
                   Table_name: sales
                  Policy_name: test_policy
                  Policy_type: NONE
                 Archive_type: PARTITION COUNT
                 Storage_mode: NULL
               Storage_engine: NULL
                Storage_media: NULL
          Storage_schema_name: NULL
           Storage_table_name: NULL
              Data_compressed: OFF
         Compressed_algorithm: NULL
                      Enabled: ENABLED
              Priority_number: 50000
        Tier_partition_number: 3
               Tier_condition: NULL
                   Extra_info: null
                      Comment: NULL
        1 row in set (0.01 sec)

     3. Insert test data to the sales table to trigger automatic creation of interval partitions. Use the proc_batch_insert stored procedure to insert new data. The table has the following structure:

        CALL proc_batch_insert(1, 3000, 'sales');
        Query OK, 1 row affected, 1 warning (0.99 sec)
        
        SHOW CREATE TABLE sales \G
        *************************** 1. row ***************************
               Table: sales
        Create Table: CREATE TABLE `sales` (
          `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
        /*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
        /*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
        1 row in set (0.03 sec)

  2. Execute the DLM policy

     1. Execute the following SQL statement to directly execute the DLM policy:

        CALL dbms_dlm.execute_all_dlm_policies();

     2. The mysql.dlm_progress table contains the following execution records of the current DLM policy:

        SELECT * FROM mysql.dlm_progress \G
        *************************** 1. row ***************************
                          Id: 1
                Table_schema: test
                  Table_name: sales
                 Policy_name: test_policy
                 Policy_type: NONE
              Archive_option: PARTITIONS OVER 3
              Storage_engine: NULL
               Storage_media: NULL
             Data_compressed: OFF
        Compressed_algorithm: NULL
          Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
               Archive_stage: ARCHIVE_COMPLETE
          Archive_percentage: 100
          Archived_file_info: null
                  Start_time: 2023-01-09 17:31:24
                    End_time: 2023-01-09 17:31:24
                  Extra_info: null
        1 row in set (0.03 sec)

        The cold data originally stored in the p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, and _p20250101000000 partitions is deleted.

     3. The sales table has the following structure:

        SHOW CREATE TABLE sales \G
        *************************** 1. row ***************************
               Table: sales
        Create Table: CREATE TABLE `sales` (
          `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
        /*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
        /*!50500 (PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
         PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
        1 row in set (0.02 sec)

• Create or delete a DLM policy by executing the ALTER TABLE statement

  • Create a DLM policy by executing the ALTER TABLE statement.

    ALTER TABLE t DLM ADD POLICY test_policy TIER TO TABLE ENGINE=CSV STORAGE=OSS READ ONLY
    STORAGE TABLE_NAME = 'sales_history' ON (PARTITIONS OVER 3);

    The DLM policy of the t table is named test_policy. The policy is executed when the table contains more than three partitions. When the policy is executed, data in the previous partitions of the t table is archived to OSS, and the archived table in OSS is named sales_history.

  • Enable the test_policy DLM policy on the t table.

    ALTER TABLE t DLM ENABLE POLICY test_policy;

  • Disable the test_policy DLM policy on the t table.

    ALTER TABLE t DLM DISABLE POLICY test_policy;

  • Delete the test_policy DLM policy on the t table.

    ALTER TABLE t DLM DROP POLICY test_policy;

Troubleshoot execution errors

After DLM policies are executed, errors may occur due to inappropriate configurations. Error records are stored in the mysql.dlm_progress table. Execute the following statement to view the error records:

SELECT * FROM mysql.dlm_progress WHERE Archive_stage = "ARCHIVE_ERROR";

Find the details of an error in the Extra_info field, confirm the cause of the error, and then delete the current error record or change the Archive_stage field value of the current error record to ARCHIVE_COMPLETE. Execute the call dbms_dlm.execute_all_dlm_policies; statement to manually execute the DLM policy or wait for the next execution cycle to automatically execute the DLM policy.