MySQL job - ddl - create table - column - GENERATED ALWAYS cause dtle job failed

[asiroliu]

Description

GENERATED ALWAYS cause dtle job failed whatever full or incr

Steps to reproduce the issue

1. create table with GENERATED ALWAYS column on src mysql

CREATE TABLE t5 (sidea DOUBLE,sideb DOUBLE,sidec DOUBLE GENERATED ALWAYS AS (SQRT(sidea * sidea + sideb * sideb)));
INSERT t5 (sidea, sideb) VALUES(1,1),(3,4),(6,8);

2. create database level job
3. the table is copy to dest mysql
4. dest dtle get error

2021-10-12T14:16:19.687+0800 [DEBUG] client.driver_mgr.dtle: publish. msg: driver=dtle @module=dtle.extractor gno=0 job=ddl_create_table_column-migration msgLen=114 nSeg=1 spanLen=114 subject=ddl_create_table_column-migration_full_complete timestamp=2021-10-12T14:16:19.687+0800
2021-10-12T14:16:19.687+0800 [DEBUG] client.driver_mgr.dtle: publish: driver=dtle gno=0 iSeg=0 job=ddl_create_table_column-migration partLen=114 subject=ddl_create_table_column-migration_full_complete @module=dtle.extractor timestamp=2021-10-12T14:16:19.687+0800
2021-10-12T14:16:19.783+0800 [ERROR] client.driver_mgr.dtle: onError: driver=dtle @module=dtle.extractor err="applier error/restart: tx.Exec. queryStart replace in seq 0: Error 3105: The value specified for generated column 'sidec' in table 't5' is not allowed." job=ddl_create_table_column-migration timestamp=2021-10-12T14:16:19.783+0800
2021-10-12T14:16:19.783+0800 [DEBUG] client.driver_mgr.dtle: extractor shutdown: driver=dtle @module=dtle.extractor job=ddl_create_table_column-migration timestamp=2021-10-12T14:16:19.783+0800
2021-10-12T14:16:19.783+0800 [INFO]  client.driver_mgr.dtle: extractor shutdown: driver=dtle @module=dtle.extractor job=ddl_create_table_column-migration timestamp=2021-10-12T14:16:19.783+0800
2021-10-12T14:16:19.783+0800 [ERROR] client.driver_mgr.dtle: unexpected error on publish: driver=dtle err="nats: connection closed" job=ddl_create_table_column-migration @module=dtle.extractor timestamp=2021-10-12T14:16:19.783+0800
2021-10-12T14:16:19.783+0800 [ERROR] client.driver_mgr.dtle: onError: driver=dtle @module=dtle.extractor err="sendFullComplete: nats: connection closed" job=ddl_create_table_column-migration timestamp=2021-10-12T14:16:19.783+0800
2021-10-12T14:16:19.783+0800 [INFO]  client.driver_mgr.dtle: Shutting down: driver=dtle @module=dtle.extractor job=ddl_create_table_column-migration timestamp=2021-10-12T14:16:19.783+0800

Output of ./dtle version:**

9.9.9.9-master-a901a50

Additional information

(e.g. issue happens only occasionally)

Additional details (log, config, job config etc):

[ghost]

Error 3105: The value specified for generated column 'sidec' in table 't5' is not allowed.

推测dtle在目标端使用了replace ... (sidea, sideb, sidec) values (?,?,?). 对于generated column, 不能指定它的值.

[qq122356]

我也发一个我这边的虚拟列无法同步的情况

Steps to reproduce the issue

1. 建测试表 CREATE TABLE demo_tbl ( id int(11) NOT NULL AUTO_INCREMENT, mobile_type int(11) DEFAULT NULL, app_type tinyint(4) NOT NULL, version_name varchar(20) DEFAULT NULL, versions varchar(20) NOT NULL, url varchar(255) DEFAULT NULL, operator varchar(36) DEFAULT NULL, operating_time datetime DEFAULT NULL, version_num int(11) DEFAULT NULL, version_describe varchar(250) DEFAULT NULL, version_push_type tinyint(4) DEFAULT NULL, enable tinyint(4) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

2. 创建虚拟列（同步成功） ALTER TABLE demo.demo_tbl ADD COLUMN lastver int(11) AS ((version_num + 10)) VIRTUAL NULL AFTER enable;

3. 插入数据 （同步失败） INSERT INTO demo.demo_tbl ( id, mobile_type, app_type, version_name, versions, url, operator, operating_time, version_num, version_describe, version_push_type, enable ) VALUES ( 8, 8, 8, NULL, '1.2.8', NULL, '888', '2021-12-31 08:31:11', 8, '888', 8, 8 );

4. 进入 http://192.168.110.201:4646/ 查看job状态

[ximenluffy]

是的，我也遇到了这个问题，建议后续fix dtle在全量初始化的时候，dtle在目标端使用了replace ... (sidea, sideb, sidec) values (?,?,?). 对于generated column, 不能指定它的值.建议 1、初始化检查是否包含虚拟列的表 information_schema.COLUMNS 表 EXTRA 字段包含（STORED GENERATED，VIRTUAL GENERATED），