drainer failed to exec ddl if the downstream use proxysql

[jwongz]

version:v2.1.4 syncer.to mysql(is a TiDB)

sql:

CREATE TABLE films (
  id int(11),
  release_year int(11),
  category_id int(11),
  rating decimal(3,2)
);
insert into films values
(1,2015,1,8.00),
(2,2015,2,8.50),
(3,2015,3,9.00),
(4,2016,2,8.20),
(5,2016,1,8.40),
(6,2017,2,7.00);

error log :

{"log":"2019/07/03 16:44:08 pump.go:115: \u001b[0;37m[info] [pump 13274bc26df3:10060] create pull binlogs client\u001b[0m\n","stream":"stderr","time":"2019-07-03T08:44:08.719759354Z"}
{"log":"2019/07/03 16:44:08 pump.go:115: \u001b[0;37m[info] [pump 33576518e473:10060] create pull binlogs client\u001b[0m\n","stream":"stderr","time":"2019-07-03T08:44:08.719763484Z"}
{"log":"2019/07/03 16:44:08 pump.go:115: \u001b[0;37m[info] [pump 7410ddec094a:10060] create pull binlogs client\u001b[0m\n","stream":"stderr","time":"2019-07-03T08:44:08.719767392Z"}
{"log":"2019/07/03 16:44:09 syncer.go:503: \u001b[0;37m[info] [ddl][start]use `binlog`; CREATE TABLE films (\n","stream":"stderr","time":"2019-07-03T08:44:09.669498645Z"}
{"log":"  id int(11),\n","stream":"stderr","time":"2019-07-03T08:44:09.669534124Z"}
{"log":"  release_year int(11),\n","stream":"stderr","time":"2019-07-03T08:44:09.669542576Z"}
{"log":"  category_id int(11),\n","stream":"stderr","time":"2019-07-03T08:44:09.669549275Z"}
{"log":"  rating decimal(3,2)\n","stream":"stderr","time":"2019-07-03T08:44:09.669581963Z"}
{"log":");[commit ts]409506059518476290\u001b[0m\n","stream":"stderr","time":"2019-07-03T08:44:09.669588491Z"}
{"log":"2019/07/03 16:44:09 sql.go:107: \u001b[0;31m[error] exec sqls[[use `binlog`; CREATE TABLE films (\n","stream":"stderr","time":"2019-07-03T08:44:09.679585095Z"}
{"log":"  id int(11),\n","stream":"stderr","time":"2019-07-03T08:44:09.679603484Z"}
{"log":"  release_year int(11),\n","stream":"stderr","time":"2019-07-03T08:44:09.679610162Z"}
{"log":"  category_id int(11),\n","stream":"stderr","time":"2019-07-03T08:44:09.67963325Z"}
{"log":"  rating decimal(3,2)\n","stream":"stderr","time":"2019-07-03T08:44:09.679637648Z"}
{"log":");]] commit failed Error 1105: line 1 column 12 near \"`; CREATE TABLE films (\n","stream":"stderr","time":"2019-07-03T08:44:09.679641773Z"}
{"log":"  id int(11),\n","stream":"stderr","time":"2019-07-03T08:44:09.67964633Z"}
{"log":"  release_year int(11),\n","stream":"stderr","time":"2019-07-03T08:44:09.679650288Z"}
{"log":"  category_id int(11),\n","stream":"stderr","time":"2019-07-03T08:44:09.679654275Z"}
{"log":"  rating decimal(3,2)\n","stream":"stderr","time":"2019-07-03T08:44:09.679658037Z"}
{"log":")`\" (total length 121)\u001b[0m\n","stream":"stderr","time":"2019-07-03T08:44:09.679661988Z"}

and the docker whitch include drainer restart agen and agen

[july2993]

what's downstream mysql version, can you try the create table sql directly in you downstream mysql. it's ok i using v2.1.4 drainer for me and downstream is tidb

2019/07/03 22:42:18 syncer.go:503: ^[[0;37m[info] [ddl][start]use `test`; CREATE TABLE films (
  id int(11),
  release_year int(11),
  category_id int(11),
  rating decimal(3,2)
);[commit ts]409512165583093766^[[0m
2019/07/03 22:42:18 syncer.go:299: ^[[0;37m[info] [write save point]409512165583093766^[[0m
2019/07/03 22:42:18 syncer.go:512: ^[[0;37m[info] [ddl][end]use `test`; CREATE TABLE films (
  id int(11),
  release_year int(11),
  category_id int(11),
  rating decimal(3,2)
);[commit ts]409512165583093766^[[0m
2019/07/03 22:42:23 syncer.go:299: ^[[0;37m[info] [write save point]409512166920552449^[[0m
2019/07/03 22:42:26 syncer.go:299: ^[[0;37m[info] [write save point]409512167706984449^[[0m
2019/07/03 22:42:32 syncer.go:299: ^[[0;37m[info] [write save point]409512169293742081^[[0m

[jwongz]

what's downstream mysql version, can you try the create table sql directly in you downstream mysql. it's ok i using v2.1.4 drainer for me and downstream is tidb

2019/07/03 22:42:18 syncer.go:503: ^[[0;37m[info] [ddl][start]use `test`; CREATE TABLE films (
  id int(11),
  release_year int(11),
  category_id int(11),
  rating decimal(3,2)
);[commit ts]409512165583093766^[[0m
2019/07/03 22:42:18 syncer.go:299: ^[[0;37m[info] [write save point]409512165583093766^[[0m
2019/07/03 22:42:18 syncer.go:512: ^[[0;37m[info] [ddl][end]use `test`; CREATE TABLE films (
  id int(11),
  release_year int(11),
  category_id int(11),
  rating decimal(3,2)
);[commit ts]409512165583093766^[[0m
2019/07/03 22:42:23 syncer.go:299: ^[[0;37m[info] [write save point]409512166920552449^[[0m
2019/07/03 22:42:26 syncer.go:299: ^[[0;37m[info] [write save point]409512167706984449^[[0m
2019/07/03 22:42:32 syncer.go:299: ^[[0;37m[info] [write save point]409512169293742081^[[0m

syncer.to is proxysql and tidb

why your log don't have "\n“?

[july2993]

you can run this sql in you proxysql:

use `test`; CREATE TABLE films (
  id int(11),
  release_year int(11),
  category_id int(11),
  rating decimal(3,2)
);

i think the proxy don't support this. I have beed dispaly in multiline for \n, should be same as you log

[jwongz]

yeah, this issue is look like the same with https://github.com/pingcap/tidb/issues/8784

[july2993]

can you try using the drainer version from here http://download.pingcap.org/tidb-binlog-cluster-latest-linux-amd64.tar.gz, 3.0, it will execute use <db> first then execute the DDL in the same session, i think the proxysql you use will support this?

[jwongz]

can you try using the drainer version from here http://download.pingcap.org/tidb-binlog-cluster-latest-linux-amd64.tar.gz, 3.0, it will execute use <db> first then execute the DDL in the same session, i think the proxysql you use will support this?

thanks，I will try it

[jwongz]

fixed in version3.0