drds 解决问题_otter 同步 RDS 到 DRDS 出现拆分键无法更新问题

你好，已经参考过 https://github.com/alibaba/otter/issues/225，但是没有解决我的问题

麻烦请教一下：

场景如下：

商品和sku表，一比多，因为查询sku经常根据itemId查询，使用DRDS时，我选择了itemId作为拆分键，但是sku表还有个主键id。现在我用otter实现 RDS到DRDS的增量数据同步时出现了问题(binlog解析并合并出来的EventType为U，EventData里面存在拆分键，报无法更新)

具体报错信息如下：

pid:1 nid:1 exception:setl:com.alibaba.otter.node.etl.load.exception.LoadException: java.util.concurrent.ExecutionException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into db_item.parana_skus(sku_code , item_id , shop_id , status , specification , model , outer_sku_id , outer_shop_id , image , name , extra_price_json , price , attrs_json , stock_type , stock_quantity , extra , created_at , updated_at , thumbnail , layer , full_price_json , base_sku_id , channel_sku_id , id) values (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) on duplicate key update sku_code=values(sku_code) , item_id=values(item_id) , shop_id=values(shop_id) , status=values(status) , specification=values(specification) , model=values(model) , outer_sku_id=values(outer_sku_id) , outer_shop_id=values(outer_shop_id) , image=values(image) , name=values(name) , extra_price_json=values(extra_price_json) , price=values(price) , attrs_json=values(attrs_json) , stock_type=values(stock_type) , stock_quantity=values(stock_quantity) , extra=values(extra) , created_at=values(created_at) , updated_at=values(updated_at) , thumbnail=values(thumbnail) , layer=values(layer) , full_price_json=values(full_price_json) , base_sku_id=values(base_sku_id) , channel_sku_id=values(channel_sku_id)]; SQL state [HY000]; error code [4506]; [e1769824fc9b000-1][10.0.164.138:3306][db_item]ERR-CODE: [TDDL-4506][ERR_MODIFY_SHARD_COLUMN] Column 'item_id' is a sharding key of table 'parana_skus', which is forbidden to be modified. More: [http://middleware.alibaba-inc.com/faq/faqByFaqCode.html?faqCode=TDDL-4506]; nested exception is java.sql.SQLException: [e1769824fc9b000-1][10.0.164.138:3306][db_item]ERR-CODE: [TDDL-4506][ERR_MODIFY_SHARD_COLUMN] Column 'item_id' is a sharding key of table 'parana_skus', which is forbidden to be modified. More: [http://middleware.alibaba-inc.com/faq/faqByFaqCode.html?faqCode=TDDL-4506]

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)

at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812)

at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868)

at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2.doInTransaction(DbLoadAction.java:625)

at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)

at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:617)

at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:545)

at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doTwoPhase(DbLoadAction.java:462)

at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doLoad(DbLoadAction.java:275)

at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.load(DbLoadAction.java:161)

at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$FastClassByCGLIB$$d932a4cb.invoke()

at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)

at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)

at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$EnhancerByCGLIB$$80fd23c2.load()

at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:198)

at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:189)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

at java.lang.Thread.run(Thread.java:748)

Caused by: java.sql.SQLException: [e1769824fc9b000-1][10.0.164.138:3306][db_item]ERR-CODE: [TDDL-4506][ERR_MODIFY_SHARD_COLUMN] Column 'item_id' is a sharding key of table 'parana_skus', which is forbidden to be modified. More: [http://middleware.alibaba-inc.com/faq/faqByFaqCode.html?faqCode=TDDL-4506]

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)

at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)

at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)

at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)

at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818)

at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)

... 21 more

PairId: 4 , TableId: 8 , EventType : U , Time : 1545984991000

Consistency : , Mode :

---Pks

EventColumn[index=0,columnType=-5,columnName=id,columnValue=21210532,isNull=false,isKey=true,isUpdate=true]

---oldPks

---Columns

EventColumn[index=1,columnType=12,columnName=sku_code,columnValue=3444549,isNull=false,isKey=false,isUpdate=true]

EventColumn[index=2,columnType=-5,columnName=item_id,columnValue=16421310,isNull=false,isKey=false,isUpdate=true]

EventColumn[index=3,columnType=-5,columnName=shop_id,columnValue=134527,isNull=false,isKey=false,isUpdate=true]

EventColumn[index=4,columnType=-7,columnName=status,columnValue=0,isNull=false,isKey=false,isUpdate=true]

EventColumn[index=5,columnType=12,columnName=specification,columnValue=,isNull=true,isKey=false,isUpdate=true]

EventColumn[index=6,columnType=12,columnName=model,columnValue=,isNull=true,isKey=false,isUpdate=true]

EventColumn[index=7,columnType=12,columnName=outer_sku_id,columnValue=,isNull=true,isKey=false,isUpdate=true]

EventColumn[index=8,columnType=12,columnName=outer_shop_id,columnValue=,isNull=true,isKey=false,isUpdate=true]

EventColumn[index=9,columnType=12,columnName=image,columnValue=,isNull=true,isKey=false,isUpdate=true]

EventColumn[index=10,columnType=12,columnName=name,columnValue=,isNull=true,isKey=false,isUpdate=true]

EventColumn[index=11,columnType=12,columnName=extra_price_json,columnValue=,isNull=true,isKey=false,isUpdate=true]

EventColumn[index=12,columnType=4,columnName=price,columnValue=113277,isNull=false,isKey=false,isUpdate=true]

EventColumn[index=13,columnType=12,columnName=attrs_json,columnValue=,isNull=true,isKey=false,isUpdate=true]

EventColumn[index=14,columnType=-6,columnName=stock_type,columnValue=0,isNull=false,isKey=false,isUpdate=true]

EventColumn[index=15,columnType=4,columnName=stock_quantity,columnValue=999999999,isNull=false,isKey=false,isUpdate=true]

EventColumn[index=16,columnType=-4,columnName=extra,columnValue=,isNull=true,isKey=false,isUpdate=true]

EventColumn[index=17,columnType=93,columnName=created_at,columnValue=2018-12-06 18:09:45,isNull=false,isKey=false,isUpdate=true]

EventColumn[index=18,columnType=93,columnName=updated_at,columnValue=2018-12-28 16:16:31,isNull=false,isKey=false,isUpdate=true]

EventColumn[index=19,columnType=12,columnName=thumbnail,columnValue=,isNull=true,isKey=false,isUpdate=true]

EventColumn[index=20,columnType=-6,columnName=layer,columnValue=11,isNull=false,isKey=false,isUpdate=true]

EventColumn[index=21,columnType=12,columnName=full_price_json,columnValue={"marketPrice":129900,"channelPrice":113277,"platformPrice":129900},isNull=false,isKey=false,isUpdate=true]

EventColumn[index=22,columnType=-5,columnName=base_sku_id,columnValue=,isNull=true,isKey=false,isUpdate=true]

EventColumn[index=23,columnType=-5,columnName=channel_sku_id,columnValue=,isNull=true,isKey=false,isUpdate=true]

---Sql

insert into db_item.parana_skus(sku_code , item_id , shop_id , status , specification , model , outer_sku_id , outer_shop_id , image , name , extra_price_json , price , attrs_json , stock_type , stock_quantity , extra , created_at , updated_at , thumbnail , layer , full_price_json , base_sku_id , channel_sku_id , id) values (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) on duplicate key update sku_code=values(sku_code) , item_id=values(item_id) , shop_id=values(shop_id) , status=values(status) , specification=values(specification) , model=values(model) , outer_sku_id=values(outer_sku_id) , outer_shop_id=values(outer_shop_id) , image=values(image) , name=values(name) , extra_price_json=values(extra_price_json) , price=values(price) , attrs_json=values(attrs_json) , stock_type=values(stock_type) , stock_quantity=values(stock_quantity) , extra=values(extra) , created_at=values(created_at) , updated_at=values(updated_at) , thumbnail=values(thumbnail) , layer=values(layer) , full_price_json=values(full_price_json) , base_sku_id=values(base_sku_id) , channel_sku_id=values(channel_sku_id)