tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TDSQL-C for MySQL
    Documentation
    Download PDF

    Compatibility and Use Limits

    Last updated: 2024-12-17 17:03:47
    Download PDF
      This document introduces the use limits and compatibility of the read-only analysis engine feature.
      Note:
      Except for the unsupported and special scenarios mentioned in this document, the read-only analysis engine is compatible with all current MySQL syntax and capabilities.

      Product Architecture and Availability Differences

      The read-write and read-only instances of TDSQL-C for MySQL, implemented by using the separated storage and computing architecture, possess natural advantages in elastic performance and scalability. However, the read-only analysis engine is different from read-write instances and regular read-only instances. To ensure extreme performance in complex query scenarios, the read-only analysis engine uses a centralized storage and computing architecture, in which the data is stored on local disks of the same server where the compute node resides. Due to this distinction, you should pay attention to the following differences:
      Scaling the read-only analysis engine requires data migration, so its efficiency in scaling scenarios cannot match that of read/write instances and regular read-only instances. (The current version does not support scaling.)
      In single-node scenarios, the read-only analysis engine does not have high availability. If a node fails, the read-only analysis engine will be unavailable. If you require the read-only analysis engine to provide continuous service, you should apply for multiple read-only analysis engines.

      Data Loading Limits

      Since the read-only analysis engine builds data through columnar storage, it may be not supported in some special MySQL use cases as shown below:
      Tables without primary keys and unique keys cannot be loaded into the read-only analysis engine.
      When a table has neither a primary key nor a unique key, it cannot be loaded into the read-only analysis engine. The table must contain a primary key or a unique key. In the LibraDB engine, the primary key or unique key of the table will be used by default for building columnar data.
      Tables using float and double field types for primary keys cannot be loaded into the read-only analysis engine.
      Float and double fields are floating-point field types. The data of tables using these two field types for primary keys cannot be loaded into the read-only analysis engine.
      Stored procedures, custom functions, triggers, foreign key constraints, events, and indexes cannot be loaded into the read-only analysis engine.
      The above special objects cannot be built in columnar storage.
      Tables containing unsupported field types can be loaded into the read-only analysis engine, but related columns cannot be queried.
      The read-only analysis engine does not support querying the following field types: SET, ENUM, Spatial Data Types, and JSON.
      All DDL statements targeting partition tables will not take effect in the read-only analysis engine.
      Partition tables can be loaded into the read-only analysis engine and support queries by default. However, in the read-only analysis engine, partition tables are built as single tables, so they do not support synchronization of related DDL operations for partitions, such as REBUILD PARTITION, OPTIMIZE PARTITION, REPAIR PARTITION, CHECK PARTITION, EXCHANGE PARTITION, DROP PARTITION, and MERGE PARTITION. Additionally, queries on individual subpartitions are not supported in the read-only analysis engine.
      Note:
      When the DROP SUBPARTITION, TRUNCATE PARTITION, or EXCHANGE PARTITION operation is performed on a partition table in TDSQL-C for MySQL, the table cannot be queried in the read-only analysis engine. To restore using it, you need to unload this table and then load the data again.
      Temporary tables cannot be loaded into the read-only analysis engine.
      Since changes to temporary table data are not logged, the data of temporary tables cannot be loaded into the read-only analysis engine.
      Modifications to primary keys of tables are not supported.
      Because the column-based table reorganizes data based on the primary key or unique key of the row-based table, tables of TDSQL-C for MySQL allow no modifications to the primary key, such as deleting the primary key, changing the primary key to another field, and adding a field as part of a composite primary key. When there is no primary key, the column-based table is built based on the unique key. In such cases, modifications to the unique key are also not allowed.
      Note:
      If the primary key is modified in TDSQL-C for MySQL, this table in the read-only analysis engine will stop synchronization and cannot be queried. To restore using it, you need to unload this table and then load the data again.
      A table loaded into the analysis engine is renamed.
      After a table is loaded into the analysis engine and renamed by using the rename statement, the renamed table will also be automatically loaded into the analysis engine. If a new table with the same name as that of the renamed table is created, this new table will also be automatically loaded into the analysis engine.
      Operations are not supported before table creation.
      Before a table is created, if it is preloaded into the analysis engine by using commands or the entire database is loaded into the analysis engine, the table cannot be automatically loaded into the analysis engine when the create table ... select statement is executed in TDSQL-C for MySQL. If you want to load this table into the analysis engine, you should create it first.
      Column-level permissions are not supported.
      The read-only analysis engine will synchronize all users' query permissions on objects in the read-write instance by default, but will not synchronize column-level permissions.
      Certain data types are not supported.
      The read-only analysis engine does not support certain data types. If an object contains unsupported data types, the table cannot be loaded into the analysis engine.

      Syntax Limits

      The read-only analysis engine can only execute read-only query statements, but cannot perform any data change operations, including DDL and DML operations.
      The read-only analysis engine only supports SELECT query statements. However, a few keywords and syntax in the SELECT statements are still not supported. For details, see SELECT Statement Instructions.
      The read-only analysis engine supports CTE syntax and window functions. However, there are still some limits for using the CTE syntax and window functions. For details, see CTE Syntax Use Instructions and Window Function Use Instructions.
      The read-only analysis engine does not support full-text search syntax currently.

      Value Limits

      In the read-only analysis engine, the size of supported values of regular columns is 1 MB by default, up to 5 MB. If any field exceeds this size, submit a ticket for handling. If any column value exceeds this size, an error will be reported and the loading into the analysis engine will be stopped.

      SQL_MODE

      Similar to MySQL, the read-only analysis engine supports setting the global or session-level SQL mode through the SET [SESSION | GLOBAL] sql_mode='modes' statement. You can also query the current SQL mode using SELECT @@sql_mode.
      The read-only analysis engine supports the following common MySQL system SQL_MODEs. Any SQL_MODEs not mentioned are not supported. However, you should note that although these SQL_MODEs are supported, some might not be applicable in the read-only analysis engine, such as NO_AUTO_CREATE_USER and NO_ENGINE_SUBSTITUTION.
      Name
      Meaning
      PIPES_AS_CONCAT
      Interprets || as the string concatenation operator (+) but not as OR, like CONCAT().
      ANSI_QUOTES
      Treats " as an identifier. If ANSI_QUOTES is enabled, only quotes within single quotes are considered string literals. Double quotes are interpreted as identifiers, so they cannot be used to enclose strings.
      IGNORE_SPACE
      If this mode is enabled, the system ignores spaces. For example, user and user are considered the same.
      ONLY_FULL_GROUP_BY
      If a column that is neither processed by an aggregation function nor included in a GROUP BY clause appears in a SELECT, HAVING, or ORDER BY clause, this SQL statement is invalid.
      NO_UNSIGNED_SUBTRACTION
      In subtraction operations, if an operand has no sign, the result is not marked as UNSIGNED (supported).
      NO_BACKSLASH_ESCAPES
      If this mode is enabled, the backslash symbol \\ represents itself only.
      STRICT_TRANS_TABLES
      For a transactional storage engine in strict mode, the entire statement is rolled back after invalid values are inserted.
      STRICT_ALL_TABLES
      For transactional tables, the entire transaction statement is rolled back after invalid values are written.
      NO_ZERO_IN_DATE
      In strict mode, dates with the month or day containing 0 are not accepted. If the IGNORE option is used, we insert 0000-00-00 for such dates. In non-strict mode, these dates are accepted but a warning will be generated.
      NO_ZERO_DATE
      In strict mode, 0000-00-00 is not treated as a valid date. You can still use the IGNORE option to insert dates consisting of 0. In non-strict mode, the date is accepted but a warning will be generated.
      ALLOW_INVALID_DATES
      It does not check the validity of the entire date, but only checks whether month values are between 1 and 12, and day values are between 1 and 31. This applies only to DATE and DATETIME columns. The TIMESTAMP column requires checking the validity of the entire date.
      ERROR_FOR_DIVISION_BY_ZERO
      When this mode is enabled, the system generates an error if the dividend is a zero value during INSERT or UPDATE. When this mode is not enabled, the system generates a warning if the dividend is zero, and replaces it with NULL.
      REAL_AS_FLOAT
      Treats REAL as a synonym for FLOAT, but not as a synonym for DOUBLE.
      NO_DIR_IN_CREATE
      Ignores all INDEX DIRECTORY and DATA DIRECTORY instructions during creation of a table. This option is useful only for replica servers.
      NO_AUTO_CREATE_USER
      Prevents GRANT from automatically creating new users, except when a password is specified (but it has no actual effect in the read-only analysis engine).
      NO_ENGINE_SUBSTITUTION
      If the required storage engine is disabled or not compiled, this mode can prevent the automatic replacement of the storage engine (but it has no actual effect in the read-only analysis engine).

      Character Set and Collation

      A character set (character set) is a set of symbols and encodings. The default character set in the read-only analysis engine is utf8mb4.
      A collation is a set of rules for comparing and sorting characters in a character set. For example, in a binary collation, the comparison result of A and a is that they are different.
      Currently, the character sets and collations supported by the read-only analysis engine are shown in the table below:
      Character Set
      Description
      Supported Collation
      Maximum Size
      utf8
      UTF-8 Unicode
      utf8_bin
      3
      utf8mb4
      UTF-8 Unicode
      utf8mb4_bin
      4
      Note:
      When objects in the read-write instance use other character sets, loading data into the read-only analysis engine is not affected. However, certain special characters may cause exceptions during queries in the read-only analysis engine, and different collations may lead to inconsistent sorting results.

      Other Behavior Descriptions

      After SELECT ...... GROUP BY expr is executed in the read-only analysis engine, the returned results are not sorted by default, consistent with MySQL 8.0. This differs from MySQL 5.7, in which the returned results are sorted by default. Therefore, no matter whether the read-only analysis engine is built in MySQL 5.7 or MySQL 8.0, it has the same logic.
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy