TiDB 3.0.2 binlog同步 MariaDB 10.3 外键约束，导致 drainer中断

🪐 TiDB 技术问题 备份&数据迁移
1

场景：

 使用TiDB 3.0.2  pump/drainer 同步数据到 MariaDB 10.3
 上游：TiDB 3.0.2
 下游：MariaDB 10.3
  开发Spring boot activity 工作流， 工作流自动创建的数据库表中带有添加外键的Sql语句

我做了什么：

 创建数据库表的SQL语句中，包含添加外键约束的语句，这个操作会在TiDB与MariaDB中都创建了相同格式的表，但不同的是，TiDB因为特性它并不会创建外键约束，而MariaDB中确根据语句成功添加了外键约束

问题：

因为这一点，当我没有按照MariaDB的规范在TiDB中删除表时，引发了drainer 中断代码如下

[2019/08/20 13:42:44.882 +08:00] [INFO] [syncer.go:246] ["handleSuccess quit"]
[2019/08/20 13:42:44.882 +08:00] [ERROR] [server.go:270] ["syncer exited abnormal"]
[error="Error 1451: Cannot delete or update a parent row: a foreign key constraint fails (`dev2_pfizer_activiti_wangxq`.`act_ge_bytearray`, CONSTRAINT `ACT_FK_BYTEARR_DEPL` FOREIGN KEY (`DEPLOYMENT_ID_`) REFERENCES `act_re_deployment` (`ID_`))"] 
[errorVerbose="Error 1451: Cannot delete or update a parent row: a foreign key constraint fails (`dev2_pfizer_activiti_wangxq`.`act_ge_bytearray`, CONSTRAINT `ACT_FK_BYTEARR_DEPL` FOREIGN KEY (`DEPLOYMENT_ID_`) REFERENCES `act_re_deployment` (`ID_`))
github.com/pingcap/errors.AddStack
	/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174
github.com/pingcap/errors.Trace
	/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15
github.com/pingcap/tidb-binlog/pkg/loader.(*tx).autoRollbackExec
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/executor.go:87
github.com/pingcap/tidb-binlog/pkg/loader.(*executor).singleExec
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/executor.go:274
github.com/pingcap/tidb-binlog/pkg/loader.(*executor).singleExecRetry.func1
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/executor.go:243
github.com/pingcap/tidb-binlog/pkg/util.RetryContext
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/util/util.go:168
github.com/pingcap/tidb-binlog/pkg/loader.(*executor).singleExecRetry
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/executor.go:242
github.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).execByHash.func1
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:351
golang.org/x/sync/errgroup.(*Group).Go.func1
	/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/golang.org/x/sync@v0.0.0-20181221193216-37e7f081c4d4/errgroup/errgroup.go:57
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1337"]
[2019/08/20 13:42:44.882 +08:00] [INFO] [util.go:66] [Exit] [name=syncer]
[2019/08/20 13:42:44.882 +08:00] [INFO] [server.go:406] ["begin to close drainer server"]
[2019/08/20 13:42:44.885 +08:00] [INFO] [server.go:371] ["has already update status"] [id=dev10:8249]
[2019/08/20 13:42:44.886 +08:00] [INFO] [server.go:410] ["commit status done"]
[2019/08/20 13:42:44.886 +08:00] [INFO] [util.go:66] [Exit] [name=heartbeat]
[2019/08/20 13:42:44.886 +08:00] [INFO] [collector.go:130] ["publishBinlogs quit"]
[2019/08/20 13:42:44.886 +08:00] [INFO] [pump.go:72] ["pump is closing"] [id=dev10:8250]
[2019/08/20 13:42:44.886 +08:00] [INFO] [util.go:66] [Exit] [name=collect]
[2019/08/20 13:42:44.886 +08:00] [INFO] [main.go:73] ["drainer exit"]
[2019/08/20 13:42:44.886 +08:00] [INFO] [server.go:425] ["drainer exit"]
2

把下游 MariaDB 的外键约束删除掉，重启 drainer 试下

3

我做了如下操作

Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 974
Server version: 10.3.12-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [information_schema]> use information_schema;
Database changed
MariaDB [information_schema]>
MariaDB [information_schema]>
MariaDB [information_schema]> SELECT
    -> CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ' ;' )  AS 删除外键
    -> FROM
    -> information_schema.TABLE_CONSTRAINTS c
    -> WHERE
    -> c.CONSTRAINT_TYPE = 'FOREIGN KEY';
+--------------------------------------------------------------------------------------------------+
| 删除外键                                                                                         |
+--------------------------------------------------------------------------------------------------+
| ALTER TABLE dev2_pfizer_activiti_mandy.act_ru_task DROP FOREIGN KEY ACT_FK_TASK_PROCDEF ;        |
| ALTER TABLE dev2_pfizer_activiti_mandy.act_ru_task DROP FOREIGN KEY ACT_FK_TASK_PROCINST ;       |
| ALTER TABLE dev2_pfizer_activiti_mandy.act_ru_event_subscr DROP FOREIGN KEY ACT_FK_EVENT_EXEC ;  |
| ALTER TABLE dev2_pfizer_activiti_mandy.act_procdef_info DROP FOREIGN KEY ACT_FK_INFO_JSON_BA ;   |
| ALTER TABLE dev2_pfizer_activiti_mandy.act_procdef_info DROP FOREIGN KEY ACT_FK_INFO_PROCDEF ;   |
| ALTER TABLE dev2_pfizer_activiti_mandy.act_re_model DROP FOREIGN KEY ACT_FK_MODEL_DEPLOYMENT ;   |
| ALTER TABLE dev2_pfizer_activiti_mandy.act_re_model DROP FOREIGN KEY ACT_FK_MODEL_SOURCE ;       |
| ALTER TABLE dev2_pfizer_activiti_mandy.act_re_model DROP FOREIGN KEY ACT_FK_MODEL_SOURCE_EXTRA ; |
| ALTER TABLE dev2_pfizer_activiti_mandy.act_ru_variable DROP FOREIGN KEY ACT_FK_VAR_BYTEARRAY ;   |
| ALTER TABLE dev2_pfizer_activiti_mandy.act_ru_variable DROP FOREIGN KEY ACT_FK_VAR_EXE ;         |
| ALTER TABLE dev2_pfizer_activiti_mandy.act_ru_variable DROP FOREIGN KEY ACT_FK_VAR_PROCINST ;    |
| ALTER TABLE dev2_pfizer_activiti_mandy.act_ru_job DROP FOREIGN KEY ACT_FK_JOB_EXCEPTION ;        |
+--------------------------------------------------------------------------------------------------+
12 rows in set (0.06 sec)

MariaDB [information_schema]>

批量执行删除外键语句

4

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。