写点什么

TiCDC 同步 SQL_MODE 相关

  • 2024-10-25
    北京
  • 本文字数:4363 字

    阅读完需:约 14 分钟

作者: Brian 原文来源:https://tidb.net/blog/91f38d0b

问题澄清

下游 users 表中 username 列默认为非空,所以 ticdc 应该会同步报错。但是为何 ticdc 并没有同步报错,user_id = 2 同步成功,userame 的数据还和上游不一样?

问题背景

集群版本:v6.5.3


工具版本:v6.5.3


上游表结构:username 列为 default null


mysql> show create table users;+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                         |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| users | CREATE TABLE `users` (  `user_id` int(11) NOT NULL,  `username` varchar(50) DEFAULT NULL,  PRIMARY KEY (`user_id`) /*T![clustered_index] CLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


下游表结构:username 列为 not null


mysql> show create table users;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                     |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| users | CREATE TABLE `users` (  `user_id` int(11) NOT NULL,  `username` varchar(50) NOT NULL,  PRIMARY KEY (`user_id`) /*T![clustered_index] CLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


上游执行语句:


mysql> insert into users (user_id ) values (2); Query OK, 1 row affected (0.00 sec)
mysql> select * from users;+---------+----------+| user_id | username |+---------+----------+| 2 | NULL |+---------+----------+1 row in set (0.00 sec)
复制代码


下游表数据:


mysql> select * from users;+---------+----------+| user_id | username |+---------+----------+|       2 |          |+---------+----------+1 row in set (0.00 sec)
复制代码

问题分析

1.dashboard–SQL statement

上游执行


insert into users (user_id ) values (2);
复制代码


查看下游实际执行的 SQL 为:


INSERT INTO `work`.`users` (`user_id`, `username`) VALUES(2, NULL) 
复制代码

2.sql mode 查看

当前 SQL mode 上下游相同,均为默认 SQL MODE


mysql> show variables like 'sql_mode';+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+| Variable_name | Value                                                                                                                                     |+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


SQL_MODE 中包含 strict_trans_tables 按理说不应该出现这种违反约束的问题

3. 分析源码

ticdc 同步会临时改变 SQL MODE 如下代码:


会将 STRICT_TRANS_TABLES,STRICT_ALL_TABLES disable 掉。


func AdjustSQLModeCompatible(sqlModes string) (string, error) {    needDisable := []string{       "NO_ZERO_IN_DATE",       "NO_ZERO_DATE",       "ERROR_FOR_DIVISION_BY_ZERO",       "NO_AUTO_CREATE_USER",       "STRICT_TRANS_TABLES",       "STRICT_ALL_TABLES",    }    needEnable := []string{       "IGNORE_SPACE",       "NO_AUTO_VALUE_ON_ZERO",       "ALLOW_INVALID_DATES",    }    disable := strings.Join(needDisable, ",")    enable := strings.Join(needEnable, ",")
mode, err := tmysql.GetSQLMode(sqlModes) if err != nil { return sqlModes, err } disableMode, err2 := tmysql.GetSQLMode(disable) if err2 != nil { return sqlModes, err2 } enableMode, err3 := tmysql.GetSQLMode(enable) if err3 != nil { return sqlModes, err3 } // About this bit manipulation, details can be seen // https://github.com/pingcap/dm/pull/1869#discussion_r669771966 mode = (mode &^ disableMode) | enableMode
return GetSQLModeStrBySQLMode(mode), nil}
复制代码

4.tidb 将严格模式禁用后

如下:


#tidb数据库中默认的SQL mode mysql> show variables like '%sql_mode%';+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+| Variable_name | Value                                                                                                                                     |+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

#表结构中,username有约数据not nullmysql> show create table users;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| users | CREATE TABLE `users` ( `user_id` int(11) NOT NULL, `username` varchar(50) NOT NULL, PRIMARY KEY (`user_id`) /*T![clustered_index] CLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

#临时手动将默认有的STRICT_TRANS_TABLES禁用mysql> set session sql_mode ="ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";Query OK, 0 rows affected (0.00 sec)

#username列插入null,成功插入伴有warningmysql> insert into users values(4,null);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> show warnings;+---------+------+----------------------------------+| Level | Code | Message |+---------+------+----------------------------------+| Warning | 1048 | Column 'username' cannot be null |+---------+------+----------------------------------+1 row in set (0.00 sec)
复制代码

根因总结

上下游表结构不完全不一致,上游 username 列 default null,下游 username 列 not null。


Ticdc v6.5.3 同步是会临时将 STRICT_TRANS_TABLES,STRICT_ALL_TABLES 禁用,这样即使下游表列约束为 not null,也可以成功插入 null 值,但是会伴随着对应 warning

问题后续

https://github.com/pingcap/tiflow/pull/10644/files 第一次发版是 v8.0.0


上述 PR 合并之前,首先查询下游 SQL 模式,然后进行配置。该 PR 合并之后,不再查询下游,直接基于 TiDB 的默认 SQL 模式进行配置。使用配置后的 SQL 模式创建到下游系统的连接。

备注

关于此 case 的解决在此要特别感谢产研 jinling 老师!


发布于: 刚刚阅读数: 5
用户头像

TiDB 社区官网:https://tidb.net/ 2021-12-15 加入

TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目,旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/

评论

发布
暂无评论
TiCDC 同步 SQL_MODE 相关_6.x 实践_TiDB 社区干货传送门_InfoQ写作社区