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.
Availability Difference
The analysis engine temporarily does not support configuration changes.
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 TencentDB for MySQL, performing certain data change operations in the "primary instance" may cause exceptions in data loading of the analysis engine. For details, see Data Loading Limits. Table Limits
The recommended maximum number of tables in the analysis engine varies in different versions.
1.2404.x: Maximum number of tables is 2,000.
2.2410.x and later: 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 default value size limit for regular columns is 16 MB. If there are tables with column values exceeding 16 MB, an error will occur and the loading into the analysis engine will be paused. At this time, you can submit a ticket to modify the maximum value size supported by 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. However, it should be noted that 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 | Treat || as the string concatenation operator (+) (same as CONCAT()), rather than OR. |
ANSI_QUOTES | Treat " as an identifier. If ANSI_QUOTES is enabled, only those within single quotation marks will be regarded as String Literals, and double quotes are treated as identifiers. Therefore, double quotes cannot be used to quote strings. |
IGNORE_SPACE | If this mode is enabled, the system ignores spaces. For example, "user" and "user " are the same. |
ONLY_FULL_GROUP_BY | If a column that is not processed by an aggregate function or not included in GROUP BY appears in SELECT, HAVING, or ORDER BY, this SQL is invalid. |
NO_UNSIGNED_SUBTRACTION | In a subtraction operation, if an operand has no sign, do not mark the result as UNSIGNED (supported). |
NO_BACKSLASH_ESCAPES | If this mode is enabled, the backslash symbol (\\) only represents itself. |
STRICT_TRANS_TABLES | Enable strict mode for the transaction storage engine and roll back the entire statement after inserting an illegal value. |
STRICT_ALL_TABLES | For transactional tables, roll back the entire transaction statement after writing an illegal value. |
NO_ZERO_IN_DATE | In strict mode, dates with 0 in the month or day part are not accepted. If the IGNORE option is used, we insert "0000-00-00" for similar dates. In non-strict mode, such dates can be accepted, but a warning will be generated. |
NO_ZERO_DATE | In strict mode, do not treat "0000-00-00" as a valid date. You can still insert 0 dates with the IGNORE option. In non-strict mode, this date can be accepted, but a warning will be generated. |
ALLOW_INVALID_DATES | Do not check the legitimacy of all dates. Only check whether the month value is between 1 and 12 and whether the date value is between 1 and 31. This only applies to DATE and DATETIME columns. All legitimacy of TIMESTAMP columns needs to be checked. |
ERROR_FOR_DIVISION_BY_ZERO | Enable this mode. During the INSERT or UPDATE process, when the dividend is 0, the system generates an error. If this mode is not enabled, when the dividend is 0, the system generates a warning and replaces it with NULL. |
REAL_AS_FLOAT | Treat REAL as a synonym for FLOAT rather than a synonym for DOUBLE. |
NO_DIR_IN_CREATE | When a table is created, ignore all INDEX DIRECTORY and DATA DIRECTORY instructions. This option is only useful for replication servers. |
NO_AUTO_CREATE_USER | Prevent GRANT from automatically creating users, except when specifying a password (but this has no actual effect in an analysis engine). |
NO_ENGINE_SUBSTITUTION | If the required storage engine is disabled or not compiled, it can prevent automatic replacement of the storage engine (but this has no actual effect in an analysis engine). |
Character Set and Collation
A character set is a collection of symbols and codes. The default character set in an analysis engine is utf8mb4.
A collation is a rule for comparing characters and the sort order of characters within a character set. For example, the result of comparing A and a is different in a binary collation.
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.