Error Executing mysqldump SQL Content in TiDB

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: mysqldump的sql内容在TiDB执行报错

| username: CAICAI

[Test Environment] TiDB
[TiDB Version] v7.1.0
[Issue] When using mysqldump to export SQL from MySQL and executing it directly in TiDB, an error occurs because TiDB does not ignore comment-type SQL, as shown below:

/!50717 SELECT COUNT() INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘performance_schema’ AND TABLE_NAME = ‘session_variables’ */

1064 - You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 64 near “@rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘performance_schema’ AND TABLE_NAME = ‘session_variables’ */”

Is there a setting to ignore such comments?

2
| username: hey-hoho | Original post link

In MySQL, this kind of comment will not be ignored either; you can test it yourself.

TiDB’s behavior is basically consistent with MySQL, with slight differences. The documentation is quite clear:

3
| username: CAICAI | Original post link

This kind of SQL can be executed on MySQL because it is exported with many such comments through mysqldump. It can be executed directly in MySQL, but it will report an error in TiDB. So, I wonder if there is any parameter setting to ignore it.

When paying attention, there will be a semicolon after such comments, which will cause TiDB to report a syntax error, but it is not a problem when executed in MySQL.

4
| username: redgame | Original post link

It is not supported, right…

5
| username: 我是咖啡哥 | Original post link

The key issue here is that the SQL syntax within your so-called comment is not supported by TiDB.

SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables'

/*!50717 This is not a regular comment. The number inside is the version number, and it will be executed if the version is greater than this.
Both MySQL and TiDB work this way.

6
| username: hey-hoho | Original post link

The TiDB error is due to the SELECT INTO syntax, and it has nothing to do with comments.

7
| username: zhanggame1 | Original post link

Here’s a simple solution: export with the parameter --skip-comments and there will be no comments.

8
| username: ShawnYan | Original post link

How about batch changing the version number?

9
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.