Known Incompatibility Issues with Third-Party Tools

Note

The Unsupported features section lists the unsupported features in TiDB, including:

• Stored procedures and functions
• Triggers
• Events
• User-defined functions
• SPATIAL functions, data types and indexes
• XA syntax

The preceding unsupported features are expected behavior and are not listed in this document. For more details, see MySQL Compatibility.

The incompatibility issues listed in this document are found in some third-party tools supported by TiDB.

General incompatibility

SELECT CONNECTION_ID() returns a 64-bit integer in TiDB

Description

The SELECT CONNECTION_ID() function returns a 64-bit integer in TiDB, such as 2199023260887, while it returns a 32-bit integer in MySQL, such as 391650.

Way to avoid

In a TiDB application, to avoid data overflow, you should use a 64-bit integer or string type to store the result of SELECT CONNECTION_ID(). For example, you can use Long or String in Java and use string in JavaScript or TypeScript.

TiDB does not maintain Com_* counters

Description

MySQL maintains a series of server status variables starting with Com_ to keep track of the total number of operations you have performed on the database. For example, Com_select records the total number of SELECT statements initiated by MySQL since it was last started (even if the statements were not queried successfully). TiDB does not maintain these variables. You can use the statement SHOW GLOBAL STATUS LIKE ‘Com_%’ to see the difference between TiDB and MySQL.

Way to avoid

Do not use these variables. One common scenario is monitoring. TiDB is well observable and does not require querying from server status variables. For custom monitoring tools, refer to TiDB Monitoring Framework Overview.

Do not use these variables. One common scenario is monitoring. TiDB Cloud is well observable and does not require querying from server status variables. For more information about TiDB Cloud monitoring services, refer to Monitor a TiDB Cluster.

TiDB distinguishes between TIMESTAMP and DATETIME in error messages

Description

TiDB error messages distinguish between TIMESTAMP and DATETIME, while MySQL does not, and returns them all as DATETIME. That is, MySQL incorrectly converts TIMESTAMP type error messages to DATETIME type.

Way to avoid

Do not use the error messages for string matching. Instead, use Error Codes for troubleshooting.

Do not use the error messages for string matching. Instead, use Error Codes for troubleshooting.

TiDB does not support the CHECK TABLE statement

Description

The CHECK TABLE statement is not supported in TiDB.

Way to avoid

To check the consistency of data and corresponding indexes, you can use the ADMIN CHECK [TABLE|INDEX] statement in TiDB.

Compatibility with MySQL JDBC

The test version is MySQL Connector/J 8.0.29.

The default collation is inconsistent

Description

The collations of MySQL Connector/J are stored on the client side and distinguished by the server version.

The following table lists known client-side and server-side collation inconsistencies in character sets:

Way to avoid

Set the collation manually, and do not rely on the client-side default collation. The client-side default collation is stored by the MySQL Connector/J configuration file.

The NO_BACKSLASH_ESCAPES parameter does not take effect

Description

In TiDB, you cannot use the NO_BACKSLASH_ESCAPES parameter without escaping the \ character. For more details, track this issue.

Way to avoid

Do not use NO_BACKSLASH_ESCAPES with \ in TiDB, but use \\ in SQL statements.

The INDEX_USED related parameters are not supported

Description

TiDB does not set the SERVER_QUERY_NO_GOOD_INDEX_USED and SERVER_QUERY_NO_INDEX_USED parameters in the protocol. This will cause the following parameters to be returned inconsistently with the actual situation:

• com.mysql.cj.protocol.ServerSession.noIndexUsed()
• com.mysql.cj.protocol.ServerSession.noGoodIndexUsed()

Way to avoid

Do not use the noIndexUsed() and noGoodIndexUsed() functions in TiDB.

The enablePacketDebug parameter is not supported

Description

TiDB does not support the enablePacketDebug parameter. It is a MySQL Connector/J parameter used for debugging that will keep the buffer of the data packet. This might cause the connection to close unexpectedly. DO NOT turn it on.

Way to avoid

Do not set the enablePacketDebug parameter in TiDB.

The UpdatableResultSet is not supported

Description

TiDB does not support UpdatableResultSet. DO NOT specify the ResultSet.CONCUR_UPDATABLE parameter and DO NOT update data inside the ResultSet.

Way to avoid

To ensure data consistency by transaction, you can use UPDATE statements to update data.

MySQL JDBC bugs

useLocalTransactionState and rewriteBatchedStatements are true at the same time will cause the transaction to fail to commit or roll back

Description

When using MySQL Connector/J 8.0.32 or an earlier version, if the useLocalTransactionState and rewriteBatchedStatements parameters are set to true at the same time, the transaction might fail to commit. You can reproduce with this code.

Way to avoid

Note

useConfigs=maxPerformance includes a group of configurations. For detailed configurations in MySQL Connector/J 8.0 and MySQL Connector/J 5.1, see mysql-connector-j 8.0 and mysql-connector-j 5.1 respectively. You need to disable useLocalTransactionState when using maxPerformance. That is, use useConfigs=maxPerformance&useLocalTransactionState=false.

This bug has been fixed in MySQL Connector/J 8.0.33. Considering updates for the 8.0.x series have ceased, it is strongly recommended to upgrade your MySQL Connector/J to the latest General Availability (GA) version for improved stability and performance.

Connector is incompatible with the server version earlier than 5.7.5

Description

The database connection might hang under certain conditions when using MySQL Connector/J 8.0.31 or an earlier version with a MySQL server < 5.7.5 or a database using the MySQL server < 5.7.5 protocol (such as TiDB earlier than v6.3.0). For more details, see the Bug Report.

Way to avoid

This bug has been fixed in MySQL Connector/J 8.0.32. Considering updates for the 8.0.x series have ceased, it is strongly recommended to upgrade your MySQL Connector/J to the latest General Availability (GA) version for improved stability and performance.

TiDB also fixes it in the following ways:

• Client side: This bug has been fixed in pingcap/mysql-connector-j and you can use the pingcap/mysql-connector-j instead of the official MySQL Connector/J.
• Server side: This compatibility issue has been fixed since TiDB v6.3.0 and you can upgrade the server to v6.3.0 or later versions.

Compatibility with Sequelize

The compatibility information described in this section is based on Sequelize v6.32.1.

According to the test results, TiDB supports most of the Sequelize features (using MySQL as the dialect).

Unsupported features are:

• GEOMETRY is not supported.
• Modification of integer primary key is not supported.
• PROCEDURE is not supported.
• The READ-UNCOMMITTED and SERIALIZABLE isolation levels are not supported.
• Modification of a column’s AUTO_INCREMENT attribute is not allowed by default.
• FULLTEXT, HASH, and SPATIAL indexes are not supported.
• sequelize.queryInterface.showIndex(Model.tableName); is not supported.
• sequelize.options.databaseVersion is not supported.
• Adding a foreign key reference using queryInterface.addColumn is not supported.

Modification of integer primary key is not supported

Description

Modification of integer primary key is not supported. TiDB uses primary key as an index for data organization if the primary key is integer type. Refer to Issue #18090 and Clustered Indexes for more details.

The READ-UNCOMMITTED and SERIALIZABLE isolation levels are not supported

Description

TiDB does not support the READ-UNCOMMITTED and SERIALIZABLE isolation levels. If the isolation level is set to READ-UNCOMMITTED or SERIALIZABLE, TiDB throws an error.

Way to avoid

Use only the isolation level that TiDB supports: REPEATABLE-READ or READ-COMMITTED.

If you want TiDB to be compatible with other applications that set the SERIALIZABLE isolation level but not depend on SERIALIZABLE, you can set tidb_skip_isolation_level_check to 1. In such case, TiDB ignores the unsupported isolation level error.

Modification of a column’s AUTO_INCREMENT attribute is not allowed by default

Description

Adding or removing the AUTO_INCREMENT attribute of a column via ALTER TABLE MODIFY or ALTER TABLE CHANGE command is not allowed by default.

Way to avoid

Refer to the restrictions of AUTO_INCREMENT.

To allow the removal of the AUTO_INCREMENT attribute, set @@tidb_allow_remove_auto_inc to true.

FULLTEXT, HASH, and SPATIAL indexes are not supported

Description

FULLTEXT, HASH, and SPATIAL indexes are not supported.