This topic answers frequently asked questions about PolarDB cold data archiving.
Handling the "[Data Lifecycle Management] DLM storage engine is not support. The value of polar_dlm_storage_mode is OFF." error
You must enable the cold data archiving feature in the PolarDB console. For more information, see Enable cold data archiving.
The Cold Data Archiving menu is missing
After you enable the cold data archiving feature, there might be a short delay. Refresh the page after a few minutes, and the Cold Data Archiving menu appears.
Supported file formats for cold data
The supported formats are CSV and ORC.
Verifying a successful archive
After the archiving process is complete, you can use one of the following methods to verify the archive status:
Run the
SHOW CREATE TABLE table_name;command to view the table definition and confirm the archiving status.For a common table, the operation is successful if the output contains
STORAGE OSSorCONNECTION='default_oss_server'.For a partitioned table, the operation is successful if the partition definition shows
ENGINE=CSV/ORC.
Check the status in the console.
Log on to the PolarDB console. In the left-side navigation pane for the target cluster, choose . If the table name appears in the list, the data has been archived to OSS.
How can I fix the "[Data Lifecycle Management] errmsg: Not allowed to archive the archived table." error?
This error occurs because the table has already been archived. Verify that the specified table is not an archived table.
Viewing the size of archived data
You can view the size of archived cold data in the console or by querying the archived table directly on the cluster. For more information, see View cold data archiving information.
How to query archived cold data
After archiving data from common tables and partitioned tables, you can use the following methods to query the archived data:
For a common table, you can query archived data just as you would hot data. No changes to your access methods are required.
For a partitioned table, see Query a hybrid partition to learn how to query the data.
How to modify archived cold data
Direct modification of cold data in CSV or ORC format that is archived to OSS is not supported. You can use the ALTER TABLE table_name ENGINE=InnoDB syntax to import the table data from OSS back to PolarStore. After you modify the data, use the ALTER TABLE t ENGINE = CSV STORAGE OSS; script to archive the table data to OSS. For more information, see Import data from OSS to PolarStore.
How to delete archived cold data
Use a privileged account to delete an archived table.
First, use DROP TABLE to drop the archive table. Then, delete the archived cold data by using CALL dbms_oss.delete_table_file('database_name', 'table_name');.
How is automatic data archiving triggered?
Automatic data archiving is triggered by an event. For more information, see Manage hot and cold data.
Logs for automatic data archiving
Logs for automatically archived data are recorded in the mysql.dlm_progress table. You can use an account with permissions on the mysql database to view these logs. For more information, see the mysql.dlm_progress table.
Viewing the automatic archiving policy
The policies for automatically archiving data are recorded in the mysql.dlm_policies table. You can use an account that has permissions on the mysql database to view them. For more information, see the mysql.dlm_policies table.
Archiving data to a custom OSS bucket
Currently, you cannot archive data to your own OSS engine. You can use select into ossoutfile to export data to an OSS engine. For more information, see Serially export data from a local table to an OSS engine.
The original table after data archiving
No. After data is archived, PolarDB only stores the table's metadata. The actual data is stored in OSS and no longer consumes your cluster's storage space.
How to resolve the OSS error: error message: The OSS Access Key Id you provided does not exist in our records. error?
Error messages that start with OSS error: are returned by OSS. For more information about the specific causes, see OSS error codes. The causes of and solutions to these errors are as follows:
Cause: The temporary access credential has expired.
Solution: Use your AccessKey pair to request a new temporary access credential from your application server. For more information, see Obtain a temporary access credential.
Archived empty tables in the console
This is expected behavior. When you archive an empty table, PolarDB does not create a corresponding file in OSS. Therefore, no information about the table appears in the console.
Renaming an archived table
Renaming a table archived to OSS modifies file information on OSS. The operation runs at approximately 100 MB/s, allowing you to estimate the total time based on the data volume.
ERROR 8185: "Change partition not support..." error
This feature, which allows archiving individual partitions of a partitioned table to OSS, is currently in beta. To enable it, go to Quota Center. Find the quota name by using the quota ID polardb_mysql_hybrid_partition, and then click Apply in the Actions column.
Data size changes after archiving
The storage space required for archived data may change due to data format conversion. The change in size depends on the original storage engine:
If the original table uses the InnoDB engine, the archived data size is likely to decrease.
If the original table uses the X-Engine, the archived data size may increase.
Data archived in CSV format generally occupies more space than data archived in ORC format.
Archived data visibility in OSS buckets
PolarDB stores cold data in a system-managed OSS bucket, not in a bucket within your own Alibaba Cloud account. Therefore, you cannot view the files in your OSS console and must use the PolarDB console to view and manage archived data.
"Statement violates GTID consistency" error
This error occurs because archived tables in CSV or ORC format do not support transactions. A Global Transaction Identifier (GTID) requires all tables in a transaction to be transactional. Even if the stored procedure does not perform a write operation, calling it creates a transactional context that is incompatible with the non-transactional archived table. To resolve this, you can change the table's storage engine to X-Engine, which supports transactions and also offers storage cost savings.
"The last packet successfully received" timeout error
This error indicates that the client connection timed out during the data restoration process. This can happen with large datasets. To resolve this, increase the minimum PCU specification for your PolarDB for MySQL cluster and retry the operation.
PolarDB for MySQL Enabling cold data archiving on older versions
You must upgrade your PolarDB for MySQL cluster to a version that supports this feature. For more information about available versions and how to upgrade, see Version management.
PolarDB for MySQL Effect of archiving on table size
The size of tables in PolarDB for MySQL changes when their data is archived to OSS. The extent of this change depends on several factors:
For an InnoDB table without indexes, the archived data is compressed and is typically smaller than the original table.
For an InnoDB table with indexes, the indexes are removed during archiving, which further reduces the final data size.
For an X-Engine table, the archived data size may be larger than the original table. This is because X-Engine already provides a high compression ratio locally, which may be more efficient than the compression used for the archived data.
PolarDB supports archiving data in CSV and ORC formats. The ORC format provides a higher compression ratio, resulting in smaller files in OSS compared to the CSV format. For more information, see Archive data in CSV or ORC format.