Clickhouse-server MaterializeMySQL引擎对MySQL数据库的一些限制


1.修改表的字段名不支持,同步会停止:(增加,删除表字段可以)

Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: Cannot rename and modify the same column `type` in a single ALTER query.


2.有json字段类型表不支持,同步会停止:
Received exception from server (version 20.11.3):
Code: 50. DB::Exception: Received from localhost:9000. DB::Exception: Unknown data type family: json.
 
3.表一定要有主键,否则同步会停止:
<Error> MaterializeMySQLSyncThread: Code: 48, e.displayText() = DB::Exception: The tmp.rsc cannot be materialized, because there is no primary keys., Stack trace (when copying this message, always include the lines below):


CREATE DATABASE tmp ENGINE = MaterializeMySQL('192.168.66.33:3306', 'tmp', 'ch_reader', 'Ch_reader123.');


4.     
CURRENT_TIMESTAMP不支持
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '合单时间',
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,


需要修改成:
`create_time` DATETIME NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
`update_time` DATETIME NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',




 `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
 `updated_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '添加时间',
需要修改成:
 `created_at` timestamp(0) NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',
 `updated_at` timestamp(0) NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间', 


报错如下:
Received exception from server (version 20.11.3):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Missing columns: 'CURRENT_TIMESTAMP' while processing query: 'CAST(CURRENT_TIMESTAMP AS create_time_tmp_alter9139787142241123519, 'DateTime') AS create_time, create_time_tmp_alter9139787142241123519, CAST(CURRENT_TIMESTAMP AS update_time_tmp_alter14358748934525946664, 'DateTime') AS update_time, update_time_tmp_alter14358748934525946664, CAST(1 AS status_tmp_alter2418398556718579620, 'Int8') AS status, status_tmp_alter2418398556718579620', required columns: 'CURRENT_TIMESTAMP', source columns: 'status' 'update_time' 'create_time' '_version' 'num' 'uuname' '_sign' 'main_id' 'uuid' 'id': default expression and column type are incompatible.. 


5.不支持savepoint   方法,在事务中添加savepoint name 会使复制停止
DB:Exception: ParseQueryEvent: Unsupported query event: SAVEPOINT 's1'
方法:
        在应用中删除savepoint方法




6.不支持bit类型的字段类型
Code: 50. DB::Exception: Received from localhost:9000. DB::Exception: Unknown data type family: BIT. 


7.不支持ENUM枚举类型:
Code: 50. DB::Exception: Received from localhost:9000. DB::Exception: Unknown data type family: enum. Maybe you meant: ['Enum','Enum8'].


 
分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS