This document introduces the use limits and compatibility of the analysis engine feature.
Note:
The analysis engine does not support other special scenarios except for the ones mentioned in this document.
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. Therefore, the efficiency in scaling scenarios cannot match that of read-write instances and regular read-only instances.
The analysis engine does not support high availability in a single-node scenario. If a node fails, this analysis engine will become unavailable. If you need the analysis engine to provide continuous service, apply for multi-node analysis engines.
Syntax Limits
In the analysis engine, only read-only query statements can be executed, and no data change operations can be performed, including DDL and DML operations.
The analysis engine only supports SELECT query statements. However, a few keywords and syntaxes are still not supported in SELECT statements. For details, see SELECT Statement Description. The analysis engine temporarily does not support full-text search syntax.
The analysis engine temporarily does not support custom functions and stored procedures.
If the analysis engine is enabled for a TDSQL-C for MySQL cluster, performing certain data change operations in the "read-write instance" may cause data loading errors of the analysis engine. For details, see Data Loading Limitations. Table Limits
The recommended maximum number of tables in the analysis engine varies in different versions.
1.2404.x: The maximum number of tables is 2,000.
2.2410.x: The maximum number of tables is 20,000.
The analysis engine has a limited supported range and scenarios for partition tables. For details, see Data Loading Limits. The analysis engine has limited support for data types. When unsupported data types exist, tables cannot be loaded into the analysis engine. For details, see Data Types. Value Limits
In the analysis engine, the size of supported values of regular columns cannot exceed 16 MB by default. If any column value in a table exceeds 16 MB, an error will be reported when the table is loaded into the analysis engine, and loading will be paused. At this point, you can submit an application to modify the maximum value size supported for regular columns. SQL_MODE
Similar to MySQL, the analysis engine supports setting the SQL Mode globally or at the session level by using the SET [SESSION | GLOBAL] sql_mode='modes' statement. You can also use SELECT @@sql_mode to query the current SQL Mode.
The analysis engine supports the following common MySQL system SQL_MODEs. Any unmentioned SQL_MODEs are not supported. Although the analysis engine supports these SQL_MODEs, some of them are not applicable in the analysis engine, such as NO_AUTO_CREATE_USER and NO_ENGINE_SUBSTITUTION.
|
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 this has no actual effect in an 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 this has no actual effect in an analysis engine). |
Character Set and Collation
A character set (character set) is a set of symbols and encodings. The default character set in an 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.
The character sets and collation currently supported by the analysis engine are as follows in the table:
|
utf8 | UTF-8 Unicode | utf8_bin | 3 |
utf8mb4 | UTF-8 Unicode | utf8mb4_bin | 4 |
Note:
When the objects in the read-write instance use other character sets, there is no impact on data loading into the analysis engine. However, some special characters will have exceptions when they are queried in the analysis engine. At the same time, inconsistent sorting results may occur due to different collations.
Other Behavior Descriptions
The return results of executing SELECT...GROUP BY expr in an analysis engine are consistent with MySQL 8.0. By default, there is no sorting, which is different from MySQL 5.7 where sorting is enabled by default. Therefore, this logic applies to analysis engines built on both MySQL 5.7 and MySQL 8.0.