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

场景:

 使用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_`))\ngithub.com/pingcap/errors.AddStack\n\t/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*tx).autoRollbackExec\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/executor.go:87\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*executor).singleExec\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/executor.go:274\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*executor).singleExecRetry.func1\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/executor.go:243\ngithub.com/pingcap/tidb-binlog/pkg/util.RetryContext\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/util/util.go:168\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*executor).singleExecRetry\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/executor.go:242\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).execByHash.func1\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:351\ngolang.org/x/sync/errgroup.(*Group).Go.func1\n\t/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/golang.org/x/sync@v0.0.0-20181221193216-37e7f081c4d4/errgroup/errgroup.go:57\nruntime.goexit\n\t/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"]

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

我做了如下操作

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]>

批量执行删除外键语句