tiflow icon indicating copy to clipboard operation
tiflow copied to clipboard
verified publisher icon pingcap

DM: MariaDB and MySQL specific DDL options cause failures

Open dveeden opened this issue 1 month ago • 5 comments

What did you do?

Use MariaDB or MySQL specific DDL options

This is with MariaDB 11.8 and MySQL 8.4.7

{
  "level": "ERROR",
  "time": "2025/11/28 15:50:59.789 +01:00",
  "caller": "ddl.go:959",
  "message": "parse ddl",
  "task": "mariadb-to-tidb",
  "unit": "binlog replication",
  "event": "query",
  "query event context": "{schema: test, originSQL: create table t2  (id int primary key, c1 varchar(255)) transactional=0, startLocation: position: (mariadb-log.000002, 690), gtid-set: 0-1-2, endLocation: position: (mariadb-log.000002, 836), gtid-set: 0-1-3, lastLocation: position: (mariadb-log.000002, 836), gtid-set: 0-1-3, re-sync: , needHandleDDLs: , trackInfos: }"
}
{
  "level": "ERROR",
  "time": "2025/11/28 15:50:59.795 +01:00",
  "caller": "subtask.go:361",
  "message": "unit process error",
  "subtask": "mariadb-to-tidb",
  "unit": "Sync",
  "error information": "ErrCode:36067 ErrClass:\"sync-unit\" ErrScope:\"internal\" ErrLevel:\"high\" Message:\"startLocation: [position: (mariadb-log.000002, 690), gtid-set: 0-1-2], endLocation: [position: (mariadb-log.000002, 836), gtid-set: 0-1-3], origin SQL: [create table t2  (id int primary key, c1 varchar(255)) transactional=0]: parse DDL: create table t2  (id int primary key, c1 varchar(255)) transactional=0\" RawCause:\"line 1 column 68 near \\\"transactional=0\\\" \" Workaround:\"Please confirm your DDL statement is correct and needed. For TiDB compatible DDL, see https://docs.pingcap.com/tidb/stable/mysql-compatibility#ddl. You can use `handle-error` command to skip or replace the DDL or add a binlog filter rule to ignore it if the DDL is not needed.\" "
}

create table t2  (id int primary key, c1 varchar(255)) transactional=0

{
  "level": "WARN",
  "time": "2025/11/28 15:45:24.731 +01:00",
  "caller": "syncer.go:1863",
  "message": "error happened when load table structure from dump files",
  "task": "mariadb-to-tidb",
  "unit": "binlog replication",
  "error": "line 27 column 79 near \"PAGE_CHECKSUM=0\" ",
  "errorVerbose": "line 27 column 79 near \"PAGE_CHECKSUM=0\" \ngithub.com/pingcap/errors.Trace\n\tgithub.com/pingcap/[email protected]/juju_adaptor.go:15\ngithub.com/pingcap/tidb/pkg/parser.(*Parser).ParseSQL\n\tgithub.com/pingcap/tidb/pkg/[email protected]/yy_parser.go:183\ngithub.com/pingcap/tidb/pkg/parser.(*Parser).ParseOneStmt\n\tgithub.com/pingcap/tidb/pkg/[email protected]/yy_parser.go:204\ngithub.com/pingcap/tiflow/dm/syncer.(*Syncer).loadTableStructureFromDump\n\tgithub.com/pingcap/tiflow/dm/syncer/syncer.go:3071\ngithub.com/pingcap/tiflow/dm/syncer.(*Syncer).Run\n\tgithub.com/pingcap/tiflow/dm/syncer/syncer.go:1861\ngithub.com/pingcap/tiflow/dm/syncer.(*Syncer).Process\n\tgithub.com/pingcap/tiflow/dm/syncer/syncer.go:757\nruntime.goexit\n\truntime/asm_amd64.s:1693"
}

{
  "level": "WARN",
  "time": "2025/11/28 15:45:24.731 +01:00",
  "caller": "syncer.go:3073",
  "message": "fail to parse statement for creating table in schema tracker",
  "task": "mariadb-to-tidb",
  "unit": "binlog replication",
  "db": "mysql",
  "path": "./dumped_data.mariadb-to-tidb",
  "file": "mysql.help_topic-schema.sql",
  "statement": "CREATE TABLE `help_topic` (\n  `help_topic_id` int(10) unsigned NOT NULL,\n  `name` char(64) NOT NULL,\n  `help_category_id` smallint(5) unsigned NOT NULL,\n  `description` text NOT NULL,\n  `example` text NOT NULL,\n  `url` text NOT NULL,\n  PRIMARY KEY (`help_topic_id`),\n  UNIQUE KEY `name` (`name`)\n) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1 TRANSACTIONAL=0 COMMENT='help topics'",
  "error": "line 10 column 79 near \"PAGE_CHECKSUM=1 TRANSACTIONAL=0 COMMENT='help topics'\" ",
  "errorVerbose": "line 10 column 79 near \"PAGE_CHECKSUM=1 TRANSACTIONAL=0 COMMENT='help topics'\" \ngithub.com/pingcap/errors.Trace\n\tgithub.com/pingcap/[email protected]/juju_adaptor.go:15\ngithub.com/pingcap/tidb/pkg/parser.(*Parser).ParseSQL\n\tgithub.com/pingcap/tidb/pkg/[email protected]/yy_parser.go:183\ngithub.com/pingcap/tidb/pkg/parser.(*Parser).ParseOneStmt\n\tgithub.com/pingcap/tidb/pkg/[email protected]/yy_parser.go:204\ngithub.com/pingcap/tiflow/dm/syncer.(*Syncer).loadTableStructureFromDump\n\tgithub.com/pingcap/tiflow/dm/syncer/syncer.go:3071\ngithub.com/pingcap/tiflow/dm/syncer.(*Syncer).Run\n\tgithub.com/pingcap/tiflow/dm/syncer/syncer.go:1861\ngithub.com/pingcap/tiflow/dm/syncer.(*Syncer).Process\n\tgithub.com/pingcap/tiflow/dm/syncer/syncer.go:757\nruntime.goexit\n\truntime/asm_amd64.s:1693"
}

{
  "level": "ERROR",
  "time": "2025/11/28 17:59:55.298 +01:00",
  "caller": "ddl.go:959",
  "message": "parse ddl",
  "task": "mariadb-to-tidb",
  "unit": "binlog replication",
  "event": "query",
  "query event context": "{schema: test, originSQL: create table t2 (id int primary key) AUTOEXTEND_SIZE=4M, startLocation: position: (binlog.000002, 619), gtid-set: 00000000-0000-0000-0000-000000000000:0, endLocation: position: (binlog.000002, 762), gtid-set: 00000000-0000-0000-0000-000000000000:0, lastLocation: position: (binlog.000002, 762), gtid-set: 00000000-0000-0000-0000-000000000000:0, re-sync: , needHandleDDLs: , trackInfos: }"
}
{
  "level": "ERROR",
  "time": "2025/11/28 17:59:55.306 +01:00",
  "caller": "subtask.go:361",
  "message": "unit process error",
  "subtask": "mariadb-to-tidb",
  "unit": "Sync",
  "error information": "ErrCode:36067 ErrClass:\"sync-unit\" ErrScope:\"internal\" ErrLevel:\"high\" Message:\"startLocation: [position: (binlog.000002, 619), gtid-set: 00000000-0000-0000-0000-000000000000:0], endLocation: [position: (binlog.000002, 762), gtid-set: 00000000-0000-0000-0000-000000000000:0], origin SQL: [create table t2 (id int primary key) AUTOEXTEND_SIZE=4M]: parse DDL: create table t2 (id int primary key) AUTOEXTEND_SIZE=4M\" RawCause:\"line 1 column 52 near \\\"AUTOEXTEND_SIZE=4M\\\" \" Workaround:\"Please confirm your DDL statement is correct and needed. For TiDB compatible DDL, see https://docs.pingcap.com/tidb/stable/mysql-compatibility#ddl. You can use `handle-error` command to skip or replace the DDL or add a binlog filter rule to ignore it if the DDL is not needed.\" "
}

So these cause issues:

  • [ ] PAGE_CHECKSUM=1 (MariaDB)
  • [ ] TRANSACTIONAL=0 (MariaDB)
  • [ ] AUTOEXTEND_SIZE=4M (MySQL 8.0 and 8.4)

Note that according to the docs both only apply to Aria tables. And looks like system tables might be in Aria format.

I used this task config:

name: mariadb-to-tidb
task-mode: all
mysql-instances:
  - source-id: "mariadb-01"
    block-allow-list: "test"
target-database:
  host: "127.0.0.1"
  port: 4000
  user: "root"
block-allow-list:
  test:
    do-dbs: ["*"]

So system tables are not expected to cause issues...

dveeden avatar Nov 28 '25 15:11 dveeden

/label MariaDB

dveeden avatar Nov 28 '25 15:11 dveeden

/severity minor

dveeden avatar Nov 28 '25 15:11 dveeden

Note that there is also CREATE TABLE ... ( ) START TRANSACTION which is an internal option that is used to binlog CREATE TABLE ... SELECT.

So

CREATE TABLE t SELECT 1;

Would be binlogged as something like this:

CREATE TABLE t (id INT) START TRANSACTION;
INSERT INTO t VALUES (1);
COMMIT;

I think I exclude this for now as ignoring the START TRANSACTION is not safe.

dveeden avatar Nov 29 '25 08:11 dveeden

also another issue about CREATE TABLE ... ( ) START TRANSACTION https://github.com/pingcap/tiflow/issues/12409

lance6716 avatar Nov 29 '25 12:11 lance6716

Note that these are excluded for now:

  • CREATE TABLE ... ( ) START TRANSACTION (from MySQL)
  • CREATE TABLE ... ( ) WITH SYSTEM VERSIONING (from MariaDB)

These could be handled separately at a later stage.

dveeden avatar Nov 30 '25 11:11 dveeden