Because the read-only analysis instance uses columnar storage to build data, it does not support some MySQL usage scenarios. This document lists the current data loading limitations for TDSQL Boundless read-only analysis instances.
Primary Keys and Unique Keys
TDSQL Boundless read-only analysis instances support loading tables without primary keys or unique keys into columnar storage and allow DDL changes to table primary keys. However, loading is still not supported in the following scenarios:
All fields in the table are of the following types, and there are no fields of other types: TIME, DATE, TIMESTAMP, DATETIME, FLOAT, DOUBLE.
After a DDL change, only the field types mentioned above remain in the table.
Attention:
For query performance, we recommend setting a primary key or unique key for tables loaded into the read-only analysis instance. Tables without a primary key may have lower query performance in the read-only analysis instance than tables with a primary key.
Floating-Point Primary Keys
Tables that use FLOAT or DOUBLE fields as primary keys cannot be loaded into the read-only analysis instance. When floating-point types are used as primary keys, the correctness of precise data deduplication and sorting operations cannot be guaranteed.
Object Types Not Loaded
The read-only analysis instance does not load or synchronize the following objects:
Stored Procedure
User Defined Function (UDF)
Trigger
Foreign Key Constraints
Events
Index
Columnar storage does not support the creation of the special objects mentioned above.
Spatial data type
Tables that contain spatial data type fields (such as GEOMETRY, POINT, LINESTRING, POLYGON, and so on) cannot be loaded into the read-only analysis instance.
Temporary Tables
Because changes to temporary table data are not recorded in the Binlog, the read-only analysis instance cannot load data from temporary tables.
Behavior After Table Rename
After a table is loaded into the read-only analysis instance, if you perform a RENAME TABLE operation on that table, the renamed table is automatically loaded into the read-only analysis instance. If you then create a new table with the same name as the original table in the read-write instance, that new table is also automatically loaded into the read-only analysis instance.
Example:
1. Table A has been loaded into the read-only analysis instance and built as columnar storage.
2. If you execute RENAME TABLE A TO B in the read-write instance, table A is renamed to table B synchronously in the read-only analysis instance.
3. If you create table A again in the read-write instance, that table is automatically loaded into the read-only analysis instance.
High-Precision Decimal Primary Keys
Tables that use excessively long DECIMAL fields as primary keys cannot be loaded as columnar storage. The maximum Decimal precision for columnar storage in a read-only analysis instance is 76 digits (corresponding to the internal Decimal256 type). Decimal primary keys that exceed this precision are not supported.
Column-level permission
The read-only analysis instance synchronizes query permissions for all users on objects from the read-write instance by default, but does not synchronize column-level permissions. Column-level permission control cannot be implemented in the read-only analysis instance.
Unsupported Data Types
Some data types cannot be loaded into the read-only analysis instance. For a detailed list, see Data Types. If a table contains unsupported data types, that table cannot be loaded. Unsupported Table Structures
The read-only analysis instance does not support the Generated Column syntax, including virtual columns (VIRTUAL) and stored columns (STORED). Tables that contain generated columns cannot be loaded into the read-only analysis instance.
Unsupported Field Type Conversions
The read-only analysis instance does not support some field type conversions. If you make unsupported modifications to field types in the TDSQL Boundless read-write instance, the data loading task in the read-only analysis instance is aborted, and the loading status of all tables changes to Paused. For details on supported type conversions, see Description of Supported Type Conversion Functions. Attention:
If you modify a table field type in TDSQL Boundless and that type change is not supported by the read-only analysis instance, the loading status of all tables is paused. To resume usage, remove the table from loading and then reload it.
Partition Table DDL Synchronization
The read-only analysis instance supports loading partition tables and supports the following DDL changes to partition tables on the source side.
Supported Partition Operations:
RANGE and LIST partition table DROP PARTITION/SUBPARTITION TEMPLATE.
RANGE and LIST partition table ADD PARTITION/SUBPARTITION TEMPLATE.
Supported Partition Data Types (Non-Function Scenarios):
Integer types: UINT8, UINT16, UINT32, UINT64, INT8, INT16, INT32, INT64, YEAR, BIT.
Time types: DATE, DATETIME, TIMESTAMP.
Supported Partition Functions:
|
YEAR
| DATE,DATETIME,TIMESTAMP
|
MONTH
| DATE,DATETIME,TIMESTAMP
|
QUARTER
| DATE,DATETIME,TIMESTAMP
|
DAY / DAYOFMONTH
| DATE,DATETIME,TIMESTAMP
|
DAYOFWEEK / WEEKDAY
| DATE,DATETIME,TIMESTAMP
|
DAYOFYEAR
| DATE,DATETIME,TIMESTAMP
|
TO_DAYS
| DATE, DATETIME, TIMESTAMP, and strings representing dates
|
TO_SECONDS
| DATE,DATETIME,TIMESTAMP
|
UNIX_TIMESTAMP
| TIMESTAMP
|
DDL Synchronization for Regular Tables
DDL operations performed on tables in the TDSQL Boundless read-write instance are normally synchronized to the read-only analysis instance. However, the following scenarios cause tables to behave abnormally in the read-only analysis instance:
After a DDL operation is performed on a table, if the table's structure falls into any of the unsupported scenarios described above in this document, the object cannot be used normally in the read-only analysis instance.
Performing a primary key change operation (adding, deleting, or modifying a primary key) on a table with more than 1 million rows causes the synchronization of that table to the read-only analysis instance to be paused.
Note:
Changes made to tables using PT tools (pt-online-schema-change), Gh-ost, or Alibaba Cloud DMS lock-free schema change can be synchronized normally to the read-only analysis instance.
To avoid triggering a synchronization pause, it is recommended to use online DDL tools such as PT tools to perform primary key change operations on tables with more than 1 million rows. After synchronization is paused, you need to unload the table and then reload it.