TiFlash 不支持alter...add column xx bit类型字段

gzp1 · 2020 年6 月 2 日 03:45

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

【TiDB 版本】：v4.0.0
【问题描述】：tiflash 在接收来自PD的table schema时，总会卡在同一张表上：

020.06.02 11:32:29.486936 [ 3 ] <Information> SchemaBuilder: Creating table test(1673).fund_xx(1697) with statement: CREATE TABLE `db_1673`.`t_1697`(xx)
2020.06.02 11:32:29.487466 [ 3 ] <Error> SchemaSyncService: DB::SchemaSyncService::SchemaSyncService(DB::Context&)::<lambda()>: Sync schemas failed by Syntax error: Not a valid integer

tiflash希望构建的数据表是：

CREATE TABLE `db_1673`.`t_1697`(`is_qdii` UInt64, `is_period` UInt64, `is_floating_net` UInt64, `_tidb_rowid` Int64) Engine = DeltaMerge((`_tidb_rowid`),

细细排查后，发现这张表有3个bit类型的字段。附表结构：

CREATE TABLE `fund_xx` (
  `is_qdii` bit(1) NOT NULL DEFAULT b'0',
  `is_period` bit(1) NOT NULL DEFAULT b'0',
  `is_floating_net` bit(1) NOT NULL DEFAULT b'0' COMMENT '浮动净值型'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='摘要'

又跑到clickhouse的官网上，发现好像不支持bit类型的数据。

求解，如何绕过这个问题？

yilong · 2020 年6 月 2 日 04:08

您好，应该是支持的，能否提供完整的表结构，多谢

gzp1 · 2020 年6 月 2 日 06:03

CREATE TABLE `fund_xx` (
  `fund_1` varchar(6) COLLATE utf8_unicode_ci NOT NULL COMMENT '基代',
  `fund_2` varchar(40) COLLATE utf8_unicode_ci NOT NULL COMMENT '基名',
  `fund_3` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '金名',
  `fund_4` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '基金名全称',
  `status` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '状态',
  `fund_5` varchar(5) COLLATE utf8_unicode_ci NOT NULL COMMENT '基型',
  `fund_6` varchar(5) COLLATE utf8_unicode_ci NOT NULL COMMENT '产品',
  `corp_id` varchar(30) COLLATE utf8_unicode_ci NOT NULL COMMENT '基司',
  `corp_7` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '基字',
  `is_qdii` bit(1) NOT NULL DEFAULT b'0',
  `is_period` bit(1) NOT NULL DEFAULT b'0',
  `is_floating_net` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否是浮动净值型',
  `setup_date` date DEFAULT NULL COMMENT '基金成立日期',
  `end_date` date DEFAULT NULL COMMENT '基金终止日期',
  `managers` json DEFAULT NULL COMMENT '基金经理',
  `nav` json DEFAULT NULL COMMENT '最新净值数据',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`fund_1`),
  KEY `IDX_FUND_2` (`fund_2`),
  KEY `IDX_FUND_3` (`fund_3`),
  KEY `idx_updated_on` (`updated_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='摘要'

JaySon-Huang · 2020 年6 月 2 日 06:14

您好，我这边以这个 schema 建表，似乎能够顺利同步至 tiflash。

请帮忙确认下下面几个方面

是否开启了new_collation。在 TiDB 运行下面语句，贴下结果

select VARIABLE_VALUE from mysql.tidb where VARIABLE_NAME='new_collation_enabled';

麻烦上传下出错前后一段时间内 TiFlash 的相关日志。

gzp1 · 2020 年6 月 2 日 06:16

未开启 new_collation_enabled

JaySon-Huang · 2020 年6 月 2 日 06:18

方便的话麻烦上传下出错时间 TiFlash 相关日志，我们分析一下哈~

gzp1 · 2020 年6 月 2 日 06:29

不太方便上传，

出错前后涉及大量的表名，目前tiflash.log 刷了接近5小时的log，内容都是一样的。卡在了test.fund_xx上

gzp1 · 2020 年6 月 2 日 06:40

附tiflash_error.log

tiflash_error.log (1.6 MB)

JaySon-Huang · 2020 年6 月 2 日 06:44

你好，麻烦提供下tiflash.log里面，完整的这一行

Creating table test(1).fund_xx(84) with statement: CREATE TABLE ...

gzp1 · 2020 年6 月 2 日 06:45

稍等，我把字段名替换为上述fund_{num}

gzp1 · 2020 年6 月 2 日 07:05

基本上一直在报这一段：

2020.06.02 15:01:16.174391 [ 17 ] <Debug> SchemaBuilder: Analyzing column :fund_1 type 15
2020.06.02 15:01:16.174396 [ 17 ] <Debug> SchemaBuilder: Analyzing column :fund_2 type 15
2020.06.02 15:01:16.174399 [ 17 ] <Debug> SchemaBuilder: Analyzing column :fund_3 type 15
2020.06.02 15:01:16.174403 [ 17 ] <Debug> SchemaBuilder: Analyzing column :fund_4 type 15
2020.06.02 15:01:16.174407 [ 17 ] <Debug> SchemaBuilder: Analyzing column :status type 254
2020.06.02 15:01:16.174411 [ 17 ] <Debug> SchemaBuilder: Analyzing column :fund_5 type 15
2020.06.02 15:01:16.174418 [ 17 ] <Debug> SchemaBuilder: Analyzing column :fund_6 type 15
2020.06.02 15:01:16.174421 [ 17 ] <Debug> SchemaBuilder: Analyzing column :corp_id type 15
2020.06.02 15:01:16.174424 [ 17 ] <Debug> SchemaBuilder: Analyzing column :fund_7 type 15
2020.06.02 15:01:16.174431 [ 17 ] <Debug> SchemaBuilder: Analyzing column :is_qdii type 16
2020.06.02 15:01:16.174435 [ 17 ] <Debug> SchemaBuilder: Analyzing column :is_period type 16
2020.06.02 15:01:16.174439 [ 17 ] <Debug> SchemaBuilder: Analyzing column :is_floating_net type 16
2020.06.02 15:01:16.174443 [ 17 ] <Debug> SchemaBuilder: Analyzing column :setup_date type 10
2020.06.02 15:01:16.174447 [ 17 ] <Debug> SchemaBuilder: Analyzing column :end_date type 10
2020.06.02 15:01:16.174451 [ 17 ] <Debug> SchemaBuilder: Analyzing column :managers type 245
2020.06.02 15:01:16.174454 [ 17 ] <Debug> SchemaBuilder: Analyzing column :nav type 245
2020.06.02 15:01:16.174458 [ 17 ] <Debug> SchemaBuilder: Analyzing column :created_on type 7
2020.06.02 15:01:16.174462 [ 17 ] <Debug> SchemaBuilder: Analyzing column :updated_on type 7
2020.06.02 15:01:16.174732 [ 17 ] <Information> SchemaBuilder: Creating table test(1673).fund_xx(1697) with statement: CREATE TABLE `db_1673`.`t_1697`(`fund_1` String, `fund_2` String, `fund_3` Nullable(String), `fund_4` Nullable(String), `status` String, `fund_5` String, `fund_6` String, `corp_id` String, `fund_7` Nullable(String), `is_qdii` UInt64, `is_period` UInt64, `is_floating_net` UInt64, `setup_date` Nullable(MyDate), `end_date` Nullable(MyDate), `managers` Nullable(String), `nav` Nullable(String), `created_on` MyDateTime(0), `updated_on` MyDateTime(0), `_tidb_rowid` Int64) Engine = DeltaMerge((`_tidb_rowid`), '{"cols":[{"comment":"\\u57FA\\u91D1\\u4EE3\\u7801","default":null,"id":1,"name":{"L":"fund_1","O":"fund_1"},"offset":0,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":4099,"Flen":6,"Tp":15}},{"comment":"\\u57FA\\u91D1\\u540D\\u79F0","default":null,"id":2,"name":{"L":"fund_2","O":"fund_2"},"offset":1,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":4105,"Flen":40,"Tp":15}},{"comment":"\\u57FA\\u91D1\\u540D\\u79F0\\u62FC\\u97F3","default":null,"id":3,"name":{"L":"fund_3","O":"fund_3"},"offset":2,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":8,"Flen":30,"Tp":15}},{"comment":"\\u57FA\\u91D1\\u540D\\u5168\\u79F0","default":null,"id":4,"name":{"L":"fund_4","O":"fund_4"},"offset":3,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":0,"Flen":100,"Tp":15}},{"comment":"\\u72B6\\u6001","default":"0","id":5,"name":{"L":"status","O":"status"},"offset":4,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":1,"Flen":1,"Tp":254}},{"comment":"\\u57FA\\u91D1\\u6295\\u8D44\\u7C7B\\u578B","default":null,"id":6,"name":{"L":"fund_5","O":"fund_5"},"offset":5,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":4097,"Flen":5,"Tp":15}},{"comment":"\\u4EA7\\u54C1\\u7C7B\\u578B","default":null,"id":7,"name":{"L":"fund_6","O":"fund_6"},"offset":6,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":4097,"Flen":5,"Tp":15}},{"comment":"\\u57FA\\u91D1\\u516C\\u53F8","default":null,"id":8,"name":{"L":"corp_id","O":"corp_id"},"offset":7,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":4097,"Flen":30,"Tp":15}},{"comment":"\\u57FA\\u91D1\\u7BA1\\u7406\\u4EBA\\u540D\\u5B57","default":null,"id":9,"name":{"L":"fund_7","O":"fund_7"},"offset":8,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":0,"Flen":100,"Tp":15}},{"comment":"","default":"","id":10,"name":{"L":"is_qdii","O":"is_qdii"},"offset":9,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":33,"Flen":1,"Tp":16}},{"comment":"","default":"","id":11,"name":{"L":"is_period","O":"is_period"},"offset":10,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":33,"Flen":1,"Tp":16}},{"comment":"\\u662F\\u5426\\u662F\\u6D6E\\u52A8\\u51C0\\u503C\\u578B","default":"","id":18,"name":{"L":"is_floating_net","O":"is_floating_net"},"offset":11,"origin_default":"","state":5,"type":{"Decimal":0,"Elems":null,"Flag":33,"Flen":1,"Tp":16}},{"comment":"\\u57FA\\u91D1\\u6210\\u7ACB\\u65E5\\u671F","default":null,"id":12,"name":{"L":"setup_date","O":"setup_date"},"offset":12,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":128,"Flen":10,"Tp":10}},{"comment":"\\u57FA\\u91D1\\u7EC8\\u6B62\\u65E5\\u671F","default":null,"id":13,"name":{"L":"end_date","O":"end_date"},"offset":13,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":128,"Flen":10,"Tp":10}},{"comment":"\\u57FA\\u91D1\\u7ECF\\u7406","default":null,"id":14,"name":{"L":"managers","O":"managers"},"offset":14,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":128,"Flen":-1,"Tp":245}},{"comment":"\\u6700\\u65B0\\u51C0\\u503C\\u6570\\u636E","default":null,"id":15,"name":{"L":"nav","O":"nav"},"offset":15,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":128,"Flen":-1,"Tp":245}},{"comment":"","default":"CURRENT_TIMESTAMP","id":16,"name":{"L":"created_on","O":"created_on"},"offset":16,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":129,"Flen":19,"Tp":7}},{"comment":"","default":"CURRENT_TIMESTAMP","id":17,"name":{"L":"updated_on","O":"updated_on"},"offset":17,"origin_default":null,"state":5,"type":{"Decimal":0,"Elems":null,"Flag":8329,"Flen":19,"Tp":7}}],"comment":"\\u57FA\\u91D1\\u4FE1\\u606F\\u6458\\u8981","id":1697,"name":{"L":"fund_xx","O":"fund_xx"},"partition":null,"pk_is_handle":false,"schema_version":4112,"state":5,"update_timestamp":416873280980910081}')
2020.06.02 15:01:16.175235 [ 17 ] <Error> SchemaSyncService: DB::SchemaSyncService::SchemaSyncService(DB::Context&)::<lambda()>: Sync schemas failed by Syntax error: Not a valid integer

JaySon-Huang · 2020 年6 月 2 日 07:12

收到，我这边先分析下

JaySon-Huang · 2020 年6 月 2 日 08:50

麻烦在tidb中使用这个语句确认下各个部件的版本hash值哈~

select type,version,git_hash from information_schema.cluster_info;

gzp1 · 2020 年6 月 2 日 09:18

MySQL [(none)]> select type,version,git_hash from information_schema.cluster_info;
+---------+--------------------+------------------------------------------+
| type    | version            | git_hash                                 |
+---------+--------------------+------------------------------------------+
| tidb    | 5.7.25-TiDB-v4.0.0 | 689a6b6439ae7835947fcaccf329a3fc303986cb |
| tidb    | 5.7.25-TiDB-v4.0.0 | 689a6b6439ae7835947fcaccf329a3fc303986cb |
| pd      | 4.0.0              | 56d4c3d2237f5bf6fb11a794731ed1d95c8020c2 |
| pd      | 4.0.0              | 56d4c3d2237f5bf6fb11a794731ed1d95c8020c2 |
| pd      | 4.0.0              | 56d4c3d2237f5bf6fb11a794731ed1d95c8020c2 |
| tikv    | 4.0.0              | 198a2cea01734ce8f46d55a29708f123f9133944 |
| tikv    | 4.0.0              | 198a2cea01734ce8f46d55a29708f123f9133944 |
| tiflash | v4.0.0-rc.2        | 09bd9e6b9a271b1fcd25c676083104a97f18739a |
| tiflash | v4.0.0-rc.2        | 09bd9e6b9a271b1fcd25c676083104a97f18739a |
| tiflash | v4.0.0             | c51c2c5c18860aaef3b5853f24f8e9cefea167eb |
| tikv    | 4.0.0-rc           | f45d0c963df3ee4b1011caf5eb146cacd1fbbad8 |
| tikv    | 4.0.0              | 198a2cea01734ce8f46d55a29708f123f9133944 |
| tikv    | 4.0.0              | 198a2cea01734ce8f46d55a29708f123f9133944 |
| tikv    | 4.0.0-rc           | f45d0c963df3ee4b1011caf5eb146cacd1fbbad8 |
+---------+--------------------+------------------------------------------+
14 rows in set (0.01 sec)

这里有几个4.0.0-rc 和 rc.2，是我之前升级的，但是应该是被下线了

JaySon-Huang · 2020 年6 月 2 日 09:28

确认下 is_floating_net 这一列，是先建好表，然后再通过 alter table ... add column 的形式添加上去的吗？
目前查到是 TiFlash 在处理 add column 时，对带默认值的 bit 类型实现上有 bug 。

gzp1 · 2020 年6 月 2 日 09:30

这个有点久远了…

不过，遇到过不少修改bit类型字段的需求。

bit类型在线上比较常用，少说也有100张表用到了该类型…

gzp1 · 2020 年6 月 2 日 09:33

查了下内部工单…的确是后加的

ALTER TABLE `test`.`fund_xx` ADD COLUMN `is_floating_net` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否是浮动净值型' AFTER `is_period`;

JaySon-Huang · 2020 年6 月 2 日 09:39

请问下目前是运行在线上环境还是测试环境？
暂时有几个方法，不过处理起来影响可能都会比较大，需要您这边评估下是否可行：

把出问题的表，在 tidb 处 drop 了，直接以目前的 DDL 建表，然后再重启 TiFlash 进程
把所有 TiFlash 节点缩容下线，然后重新扩容。

这两种方法应该都可以解决目前的问题。

但若还是需要 add column 添加带默认值的 bit 类型，这个问题还是会再出现，需要我们修复后提供新的 TiFlash binary进行替换。

gzp1 · 2020 年6 月 2 日 09:44

目前是线上环境

我们想试用下tiflash，但是这个问题，导致无法使用tiflash。

方法一：要改的表挺多…这个tidb集群存在有半年了，处理了不少bit类型的字段…都要drop，会影响很多job

方法二：”把所有 TiFlash 节点缩容下线，然后重新扩容。“ 这个tiflash节点是我刚加入的… 而且线上应该只有一个tiflash节点,有点不太明白

gzp1 · 2020 年6 月 2 日 09:48

其实这个表我不需要导入 tiflash…

如果能有个过滤机制就好了