表描述里带双引号 " 时，show create table 导出的建表语句会建表失败 · Issue #47733 · StarRocks/starrocks

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement . We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

该问题是在使用跨集群同步工具时发现的，源实例2.5.21，目标实例3.2.8。同步建表的时候如果源表描述里带双引号"，就会建表失败
后续发现是show create table COMMENT 外层为双引号导致的。

跨集群同步工具报的ERROR：

24/06/27 02:45:17 ERROR [ddl-handler] execDDLJob(Utils.java:153): Failed to execute CREATE TABLE `to_xsz_user_verify_info` (
  `id_` bigint(20) NOT NULL COMMENT "id",
  `identity_type` varchar(200) NULL COMMENT "实名类型："第三方实名验证"；"后台认证审核验收"",
) ENGINE=OLAP 
PRIMARY KEY(`id_`)
COMMENT "收支-用户实名认证表"
DISTRIBUTED BY HASH(`id_`) BUCKETS 24 
PROPERTIES (
"storage_volume" = "builtin_storage_volume",
"replication_num" = "3",
"in_memory" = "false",
"storage_format" = "DEFAULT",
"enable_persistent_index" = "true",
"compression" = "LZ4"
); of dsj_ods on the TARGET cluster.
java.sql.SQLSyntaxErrorException: Getting syntax error at line 10, column 51. Detail message: Unexpected input '第三方实名验证', the most similar input is {',', ')'}.
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121) ~[starrocks-cluster-sync-2.0-jar-with-dependencies.jar:?]
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[starrocks-cluster-sync-2.0-jar-with-dependencies.jar:?]
        at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763) ~[starrocks-cluster-sync-2.0-jar-with-dependencies.jar:?]
        at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648) ~[starrocks-cluster-sync-2.0-jar-with-dependencies.jar:?]
        at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[starrocks-cluster-sync-2.0-jar-with-dependencies.jar:?]
        at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[starrocks-cluster-sync-2.0-jar-with-dependencies.jar:?]
        at com.starrocks.common.Utils.execDDLJob(Utils.java:149) ~[starrocks-cluster-sync-2.0-jar-with-dependencies.jar:?]
        at com.starrocks.sync.SyncJob.executionDDL(SyncJob.java:156) ~[starrocks-cluster-sync-2.0-jar-with-dependencies.jar:?]
        at com.starrocks.sync.SyncJob.lambda$start$1(SyncJob.java:96) ~[starrocks-cluster-sync-2.0-jar-with-dependencies.jar:?]
        at java.lang.Thread.run(Thread.java:829) ~[?:?]
Steps to reproduce the behavior (Required)
建表时 identity_type 字段 comment 为外层引号为单引号 '

CREATE TABLE tmp_20240702_to_xsz_user_verify_info (

id_ bigint(20) NOT NULL COMMENT "id",

identity_type varchar(200) NULL COMMENT '实名类型："第三方实名验证"；"后台认证审核验收"',

update_time datetime NULL COMMENT "更新时间",

realname_time datetime NULL COMMENT "实名时间",

etl_time datetime NULL COMMENT "ETL时间"

) ENGINE=OLAP

PRIMARY KEY(id_)

COMMENT "用户实名认证表"

DISTRIBUTED BY HASH(id_) BUCKETS 24

PROPERTIES (

"replication_num" = "3",

"in_memory" = "false",

"storage_format" = "DEFAULT",

"enable_persistent_index" = "true",

"compression" = "LZ4"

Expected behavior (Required)
预期查看建表语句应与原来一致（mysql 的 show create table comment 外层也为单引号）

show create table tmp_20240702_to_xsz_user_verify_info \G;
Real behavior (Required)
实际查看建表语句，该字段外层引号为双引号 "

show create table tmp_20240702_to_xsz_user_verify_info \G;

identity_type varchar(200) NULL COMMENT "实名类型："第三方实名验证"；"后台认证审核验收"",
StarRocks version (Required)
2.5.21 dc2bcdb