【 使用环境 】生产环境
【 OB or 其他组件 】
OMS
【 使用版本 】
OB 3.1.3
MySQL 5.7.2
OMS 4.2.3_CE
【问题描述】
OMS同步，增量阶段里的一张表一直失败。有一些错误日志
974
at com.oceanbase.oms.connector.jdbc.sink.Writer.flushRecords(Writer.java:193)
975
at com.oceanbase.oms.connector.jdbc.sink.DefaultJDBCSink.offer(DefaultJDBCSink.java:60)
976
at com.oceanbase.connector.framework.threadmanager.sinktask.SyncSinkConnectorTask.run(SyncSinkConnectorTask.java:47)
977
at java.lang.Thread.run(Thread.java:853)
978
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘__pk_increment’ in ‘table’
979
at sun.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
980
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
981
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
982
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
983
at com.mysql.jdbc.Util.getInstance(Util.java:408)
984
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
985
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
986
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
987
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
988
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
989
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
990
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
991
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2124)
992
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2058)
993
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5158)
994
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2043)
995
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeUpdate(DruidPooledPreparedStatement.java:255)
996
at com.oceanbase.oms.connector.jdbc.sink.Writer.realWriteDML(Writer.java:559)
997
at com.oceanbase.oms.connector.jdbc.sink.Writer.writeDML(Writer.java:610)
998
at com.oceanbase.oms.connector.jdbc.sink.Writer.executeInRetry(Writer.java:484)
999
… 6 common frames omitted
1000
[2024-10-16 16:01:56.047] [INFO] [queue_slot1-()-TransactionScheduler] [SerialBridgeTask [queue_slot1-()-TransactionScheduler] stopped]
OceanBase 社区已接收您的帖子，正在跟进中。
源端表结构和目标端结构贴一下
REATE TABLE union_music_name_v2 (
music_id varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘音乐id’,
music_type int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘音乐type’,
music_name varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘音乐名称’,
audio_finger_print_id varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘音频指纹id’,
artist_name varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘音乐作者’,
music_label_name varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘音乐标签’,
music_quality_score int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘音乐质量分’,
music_popularity_score int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘音乐时下热度’,
lyric_resources varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘歌词资源’,
music_authority_score int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘音乐权威性分’,
original_song_flag int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘歌曲原唱情况’,
UNIQUE KEY idx_music_id_type (music_id, music_type) BLOCK_SIZE 16384 LOCAL,
KEY idx_afid (audio_finger_print_id) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = COMPACT COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
CREATE TABLE union_music_name_v2 ( |
| | music_id varchar(256) NOT NULL DEFAULT ‘’, |
| | music_type int(11) NOT NULL DEFAULT ‘0’, |
| | music_name varchar(256) NOT NULL DEFAULT ‘’, |
| | audio_finger_print_id varchar(256) NOT NULL DEFAULT ‘’, |
| | artist_name varchar(256) NOT NULL DEFAULT ‘’, |
| | music_label_name varchar(256) NOT NULL DEFAULT ‘’, |
| | music_quality_score int(11) NOT NULL DEFAULT ‘0’, |
| | music_popularity_score int(11) NOT NULL DEFAULT ‘0’, |
| | lyric_resources varchar(256) NOT NULL DEFAULT ‘’, |
| | music_authority_score int(11) NOT NULL DEFAULT ‘0’, |
| | original_song_flag int(11) NOT NULL DEFAULT ‘0’, |
| | PRIMARY KEY (music_id,music_type), |
| | KEY idx_afid (audio_finger_print_id) |
| | )
OMS 升级到最新版4.2.6试试，我们本地使用你上面的结构测试没有问题，注意的是数据源配置这边需要使用__oceanbase_inner_drc_user这个特定用户，上面的表是没有主键的；你也可以尝试在4.2.3 中建一个数据源，用户使用__oceanbase_inner_drc_user
我试了另一个表，无主键和唯一键，全量同步直接被跳过了
CREATE TABLE photo_sds_content ( |
| | photo_id bigint(20) unsigned NOT NULL, |
| | left int(11) NOT NULL DEFAULT ‘0’, |
| | top int(11) NOT NULL DEFAULT ‘0’, |
| | width int(11) NOT NULL DEFAULT ‘0’, |
| | height int(11) NOT NULL, |
| | prob decimal(10,8) NOT NULL DEFAULT ‘0’, |
| | category varchar(100) NOT NULL DEFAULT ‘’, |
| | create_time bigint(20) NOT NULL DEFAULT ‘0’, |
| | ext varchar(1024), |
| | KEY idx_photo_id (photo_id) |
| | )
[2024-10-18 15:41:20.500] [INFO] [SliceTableProvider] [initTableIndexs tableSize: 2]
806
[2024-10-18 15:41:20.500] [INFO] [SliceTableProvider] [SHOW VARIABLES LIKE ‘version_comment’]
807
[2024-10-18 15:41:20.518] [INFO] [SliceTableProvider] [SELECT/+query_timeout(1800000000)/ DB.DATABASE_NAME AS TABLE_SCHEMA, T.TABLE_NAME AS TABLE_NAME, CASE WHEN P.PART_NAME IS NOT NULL THEN 1 ELSE 0 END, CASE WHEN SP.SUB_PART_NAME IS NOT NULL THEN 1 ELSE 0 END FROM OCEANBASE.__ALL_TABLE_V2 T JOIN OCEANBASE.__ALL_DATABASE DB ON T.DATABASE_ID = DB.DATABASE_ID AND T.TENANT_ID = DB.TENANT_ID LEFT JOIN ( SELECT TENANT_ID, TABLE_ID, PART_ID, PART_NAME, HIGH_BOUND_VAL, LIST_VAL, TABLESPACE_ID, GMT_CREATE, COMMENT, ROW_NUMBER() OVER(PARTITION BY TENANT_ID,TABLE_ID ORDER BY PART_IDX) AS PART_POSITION FROM OCEANBASE.__ALL_PART ) P ON T.TABLE_ID = P.TABLE_ID AND T.TENANT_ID = P.TENANT_ID LEFT JOIN ( SELECT TENANT_ID, TABLE_ID, PART_ID, SUB_PART_ID, SUB_PART_NAME, HIGH_BOUND_VAL, LIST_VAL, TABLESPACE_ID, GMT_CREATE, COMMENT, ROW_NUMBER() OVER(PARTITION BY TENANT_ID,TABLE_ID,PART_ID ORDER BY SUB_PART_IDX) AS SUB_PART_POSITION FROM OCEANBASE.__ALL_SUB_PART ) SP ON T.TABLE_ID = SP.TABLE_ID AND P.PART_ID = SP.PART_ID AND T.TENANT_ID = SP.TENANT_ID LEFT JOIN OCEANBASE.__ALL_TENANT_TABLESPACE TP ON TP.TABLESPACE_ID = IFNULL(SP.TABLESPACE_ID, P.TABLESPACE_ID) AND TP.TENANT_ID = T.TENANT_ID LEFT JOIN OCEANBASE.__ALL_TABLE_STAT TS ON T.TENANT_ID = TS.TENANT_ID AND TS.TABLE_ID = T.TABLE_ID AND TS.PARTITION_ID = CASE T.PART_LEVEL WHEN 0 THEN T.TABLE_ID WHEN 1 THEN P.PART_ID WHEN 2 THEN SP.SUB_PART_ID END WHERE T.TABLE_TYPE IN (3,6,8,9,14) AND ((P.PART_NAME IS NOT NULL AND LENGTH(P.PART_NAME) > 0) OR (SP.SUB_PART_NAME IS NOT NULL AND LENGTH(SP.SUB_PART_NAME) > 0)) AND (DB.DATABASE_NAME,T.TABLE_NAME) in ((‘gifshow’,‘photo_sds_content’),(‘gifshow’,‘vip_comment_score’))]
808
[2024-10-18 15:41:20.532] [INFO] [SliceTableProvider] [initTablePartitionColumns tableSize: 2]
809
[2024-10-18 15:41:20.533] [INFO] [SliceTableProvider] [afterTableLoadMetrics: initTablePartitionColumns:11.554,initTableIndexs:19.166,initTableColumns:113.687, tableSize: 2]
810
[2024-10-18 15:41:20.538] [INFO] [SliceTableProvider] [gifshow.photo_sds_content slice by ob __pk_increment]
811
[2024-10-18 15:41:20.539] [INFO] [SliceTableProvider] [gifshow.vip_comment_score slice by ob __pk_increment]
812
[2024-10-18 15:41:20.542] [INFO] [SliceTableProvider] [JDBCProcessor finished]
813
[2024-10-18 15:41:20.544] [INFO] [SliceTableProvider] [gifshow.photo_sds_content not support pkuk ]
814
[2024-10-18 15:41:20.544] [INFO] [SliceTableProvider] [gifshow.photo_sds_content not support pkuk ]
815
[2024-10-18 15:41:20.544] [INFO] [SliceTableProvider] [gifshow.vip_comment_score not support pkuk ]
数据源需要使用__oceanbase_inner_drc_user这个用户才能迁移无主键和唯一键表
“clientId”:
string"jdbc:oceanbase://10.28.55.149:2883?useUnicode=true&characterEncoding=utf8&sendConnectionAttributes=false&allowLoadLocalInfile=false&autoDeserialize=false&allowLocalInfile=false&allowUrlInLocalInfile=false&useSSL=false&allowPublicKeyRetrieval=true-mmu-mmu"
“cluster”:
string"mmu"
“endpointId”:
string"e_65zfaq3x9puo"
“instance”:
string"10.28.55.149:2883"
“password”:
string"******"
“tenant”:
string"mmu"
“username”:
string"__oceanbase_inner_drc_user@mmu#mmu"
使用这用户了也不行
非常感谢您的反馈，但我们这边用您给的结构测试没有发现问题。
oms4.2.3 CE
源端 ob 3.1.5 , 目的端 mysql 5.7
数据源这边需要填写drc用户和__oceanbase_inner_drc_user 密码。
这边组件状态都是正常的，全量增量数据也能正常同步过去。
使用结构迁移和自己建表的全量和增量都是正常的。
另一个表不行，表结构是这样的，oms使用426版本
全量同步直接跳过了，增量可以正常同步。
CREATE TABLE photo_sds_content (
photo_id bigint(20) unsigned NOT NULL COMMENT ‘视频ID’,
left int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘left’,
top int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘top’,
width int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘width’,
height int(11) NOT NULL COMMENT ‘height’,
prob float(10,8) NOT NULL DEFAULT ‘0’ COMMENT ‘prob’,
category varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘分类’,
create_time bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘创建时间’,
ext varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT ‘扩展字段’,
KEY idx_photo_id (photo_id) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = COMPACT COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
报错信息是这样
[2024-10-18 15:41:20.500] [INFO] [SliceTableProvider] [initTableIndexs tableSize: 2]
806
[2024-10-18 15:41:20.500] [INFO] [SliceTableProvider] [SHOW VARIABLES LIKE ‘version_comment’]
807
[2024-10-18 15:41:20.518] [INFO] [SliceTableProvider] [SELECT/+query_timeout(1800000000) / DB.DATABASE_NAME AS TABLE_SCHEMA, T.TABLE_NAME AS TABLE_NAME, CASE WHEN P.PART_NAME IS NOT NULL THEN 1 ELSE 0 END, CASE WHEN SP.SUB_PART_NAME IS NOT NULL THEN 1 ELSE 0 END FROM OCEANBASE.__ALL_TABLE_V2 T JOIN OCEANBASE.__ALL_DATABASE DB ON T.DATABASE_ID = DB.DATABASE_ID AND T.TENANT_ID = DB.TENANT_ID LEFT JOIN ( SELECT TENANT_ID, TABLE_ID, PART_ID, PART_NAME, HIGH_BOUND_VAL, LIST_VAL, TABLESPACE_ID, GMT_CREATE, COMMENT, ROW_NUMBER() OVER(PARTITION BY TENANT_ID,TABLE_ID ORDER BY PART_IDX) AS PART_POSITION FROM OCEANBASE.__ALL_PART ) P ON T.TABLE_ID = P.TABLE_ID AND T.TENANT_ID = P.TENANT_ID LEFT JOIN ( SELECT TENANT_ID, TABLE_ID, PART_ID, SUB_PART_ID, SUB_PART_NAME, HIGH_BOUND_VAL, LIST_VAL, TABLESPACE_ID, GMT_CREATE, COMMENT, ROW_NUMBER() OVER(PARTITION BY TENANT_ID,TABLE_ID,PART_ID ORDER BY SUB_PART_IDX) AS SUB_PART_POSITION FROM OCEANBASE.__ALL_SUB_PART ) SP ON T.TABLE_ID = SP.TABLE_ID AND P.PART_ID = SP.PART_ID AND T.TENANT_ID = SP.TENANT_ID LEFT JOIN OCEANBASE.__ALL_TENANT_TABLESPACE TP ON TP.TABLESPACE_ID = IFNULL(SP.TABLESPACE_ID, P.TABLESPACE_ID) AND TP.TENANT_ID = T.TENANT_ID LEFT JOIN OCEANBASE.__ALL_TABLE_STAT TS ON T.TENANT_ID = TS.TENANT_ID AND TS.TABLE_ID = T.TABLE_ID AND TS.PARTITION_ID = CASE T.PART_LEVEL WHEN 0 THEN T.TABLE_ID WHEN 1 THEN P.PART_ID WHEN 2 THEN SP.SUB_PART_ID END WHERE T.TABLE_TYPE IN (3,6,8,9,14) AND ((P.PART_NAME IS NOT NULL AND LENGTH(P.PART_NAME) > 0) OR (SP.SUB_PART_NAME IS NOT NULL AND LENGTH(SP.SUB_PART_NAME) > 0)) AND (DB.DATABASE_NAME,T.TABLE_NAME) in ((‘gifshow’,‘photo_sds_content’),(‘gifshow’,‘vip_comment_score’))]
808
[2024-10-18 15:41:20.532] [INFO] [SliceTableProvider] [initTablePartitionColumns tableSize: 2]
809
[2024-10-18 15:41:20.533] [INFO] [SliceTableProvider] [afterTableLoadMetrics: initTablePartitionColumns:11.554,initTableIndexs:19.166,initTableColumns:113.687, tableSize: 2]
810
[2024-10-18 15:41:20.538] [INFO] [SliceTableProvider] [gifshow.photo_sds_content slice by ob __pk_increment]
811
[2024-10-18 15:41:20.539] [INFO] [SliceTableProvider] [gifshow.vip_comment_score slice by ob __pk_increment]
812
[2024-10-18 15:41:20.542] [INFO] [SliceTableProvider] [JDBCProcessor finished]
813
[2024-10-18 15:41:20.544] [INFO] [SliceTableProvider] [gifshow.photo_sds_content not support pkuk ]
814
[2024-10-18 15:41:20.544] [INFO] [SliceTableProvider] [gifshow.photo_sds_content not support pkuk ]
815
[2024-10-18 15:41:20.544] [INFO] [SliceTableProvider] [gifshow.vip_comment_score not support pkuk
目前这个链路暂不支持无主键和无唯一键的表
什么意思？不是说能同步无主键表吗？
是的，想办法在源端加个uk吧
业务上没有全局唯一列，这个没法搞吧
不能加uk，就无法使用OMS迁移了,增量这块OMS对于没有pk/uk的其实就是把所有字段当做pk/uk,实际上是有数据重复风险，遇到update和delete也可能出现数据问题