问题澄清
下游 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 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)
#临时手动将默认有的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，成功插入伴有warning
mysql> 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 老师！