[Feature] paimon-mysql-cdc supports parsing gh-ost ddl records

Search before asking

[X] I searched in the issues and found nothing similar.

Motivation

gh-ost is a triggerless online schema migration solution for MySQL.

When we use the gh-ost tool, it generates multiple DDL statements. For example, when adding a column c to table tb1 using the gh-ost tool, these DDL statements demonstrate how gh-ost works.

DROP TABLE IF EXISTS `menghuiyu`.`_tb1_gho`
DROP TABLE IF EXISTS `menghuiyu`.`_tb1_del`
DROP TABLE IF EXISTS `menghuiyu`.`_tb1_ghc`
create /* gh-ost */ table `menghuiyu`.`_tb1_ghc` (\n\t\t\tid bigint auto_increment,\n\t\t\tlast_update timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\t\t\thint varchar(64) charset ascii not null,\n\t\t\tvalue varchar(4096) charset ascii not null,\n\t\t\tprimary key(id),\n\t\t\tunique key hint_uidx(hint)\n\t\t) auto_increment=256
create /* gh-ost */ table `menghuiyu`.`_tb1_gho` like `menghuiyu`.`tb1`
alter /* gh-ost */ table `menghuiyu`.`_tb1_gho` add column c varchar(255)
create /* gh-ost */ table `menghuiyu`.`_tb1_del` (\n\t\t\tid int auto_increment primary key\n\t\t) engine=InnoDB comment='ghost-cut-over-sentry'
DROP TABLE IF EXISTS `menghuiyu`.`_tb1_del`
rename /* gh-ost */ table `menghuiyu`.`tb1` to `menghuiyu`.`_tb1_del`
rename /* gh-ost */ table `menghuiyu`.`_tb1_gho` to `menghuiyu`.`tb1`
DROP TABLE IF EXISTS `menghuiyu`.`_tb1_ghc`
DROP TABLE IF EXISTS `menghuiyu`.`_tb1_del`

MySQL CDC captures these DDL statements and synchronizes them to the sink, the sink cannot recognize the gh-ost tables (_tb1_gho, _tb1_ghc, _tb1_del) in these DDL statements and we only need to synchronize the alter operation to the sink. Therefore, we need to extract the alter statement and restore the gh-ost tables to the original table tb1.

The above is referenced from #7750 of apache-inlong

Currently paimon cannot handle this situation, related issue：#3364、#3450

Solution

No response

Anything else?

No response

Are you willing to submit a PR?

[X] I'm willing to submit a PR!

apache / paimon