You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/08/04 07:46:01 UTC
[GitHub] [shardingsphere] dmbj opened a new issue #6611: sharding scaling sync realtime data not surpport union pk
dmbj opened a new issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611
### Which version of ShardingSphere did you use?
scaling&proxy 4.1.1, MSQL 5.7.26
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
scaling,proxy
### Expected behavior
sync realtime data
### Actual behavior
scaling got an exception,and proxy trace some log
### Reason analyze (If you can)
not surpport union pk well,binlog explain SQL statement wrong and update statement not surpport sharding column
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
1、sharding target table use union pk;
2、use scaling&proxy sync data，history task sync work well；
3、sync realtime data，test update SQL such as:
`UPDATE `t_qrcode_consume_details` SET `amount`=100 WHERE `order_no`='15362822300000061787990021341006' AND `user_id`='101001191';`
some log:
**scaling exception:**
`[ERROR] 14:21:26.375 [pool-1-thread-8] o.a.s.s.c.e.e.SyncTaskExecuteCallback - RealtimeDataSyncTask realtime-appbiz_data_test execute exception exit
org.apache.shardingsphere.shardingscaling.core.exception.SyncTaskExecuteException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`code_type` = '00',`channel` = '00'' at line 1
at org.apache.shardingsphere.shardingscaling.core.execute.executor.writer.AbstractJDBCWriter.write(AbstractJDBCWriter.java:90)
at org.apache.shardingsphere.shardingscaling.core.execute.executor.writer.AbstractJDBCWriter.run(AbstractJDBCWriter.java:72)
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: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`code_type` = '00',`channel` = '00'' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.shardingscaling.core.execute.executor.writer.AbstractJDBCWriter.executeUpdate(AbstractJDBCWriter.java:142)
at org.apache.shardingsphere.shardingscaling.core.execute.executor.writer.AbstractJDBCWriter.flush(AbstractJDBCWriter.java:106)
at org.apache.shardingsphere.shardingscaling.core.execute.executor.writer.AbstractJDBCWriter.write(AbstractJDBCWriter.java:81)
... 6 common frames omitted`
**proxy log:**
`[INFO ] 14:21:26.355 [ShardingSphere-Command-3] ShardingSphere-SQL - Logic SQL: UPDATE `t_qrcode_consume_details` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.355 [ShardingSphere-Command-3] ShardingSphere-SQL - SQLStatement: UpdateStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.UpdateStatement@3924366e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@503bd098), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@503bd098)
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_0` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_1` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_2` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_3` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_4` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_5` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_6` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_7` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_8` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_9` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_10` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_11` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_12` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_13` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_14` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_15` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_16` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_17` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_18` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:26.356 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE `t_qrcode_consume_details_19` SET `amount` = 100 WHERE `order_no` = '15362822300000061787990021341006',`code_type` = '00',`channel` = '00'
[INFO ] 14:21:29.269 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x19a8f131, L:/0.0.0.0:3307] READ: [id: 0x1fe8faa7, L:/192.168.100.217:3307 - R:/192.168.100.217:44742]
[INFO ] 14:21:29.270 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x19a8f131, L:/0.0.0.0:3307] READ COMPLETE`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] dmbj commented on issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
dmbj commented on issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611#issuecomment-669020973
i have test master version(5.0.0-RC1-SNAPSHOT in scaling&proxy),union pk surpport now,sync data work fine.
3Q @Lucas-307
however i found incrementalData update statement still not surpport sharding column,it will full scan all sharding.
is a plan to fix it?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] dmbj commented on issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
dmbj commented on issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611#issuecomment-672434199
> @dmbj scaling job request body's sourceRule do not have any shardingColumns.
>
> ```
> "sourceRule": "tables:\n t_qrcode_consume_details:\n actualDataNodes: ds_0.t_qrcode_consume_details\n"
> ```
>
> Scaling connect Proxy as a standard jdbc connect which do not have shardingColumns api. So, I can't get it.
> And we will enhance it after execute by ss-jdbc which can get sharding rules.
i see,3Q
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 edited a comment on issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
Lucas-307 edited a comment on issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611#issuecomment-671251947
@dmbj Give me your create table sql, let me check it.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] dmbj closed issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
dmbj closed issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611#issuecomment-671251947
@dmbj Give me you create table sql, let me check it.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611#issuecomment-668496446
Please try it in master version again, I remember we fixed it in master.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611#issuecomment-670369933
@dmbj I remember it surpport sharding column now.
see: org.apache.shardingsphere.scaling.core.execute.executor.importer.AbstractJDBCImporter#executeUpdate
`RecordUtil.extractConditionColumns()` include `ShardingColumns`. It's not useful?
```java
private void executeUpdate(final Connection connection, final DataRecord record) throws SQLException {
List<Column> conditionColumns = RecordUtil.extractConditionColumns(record, rdbmsConfiguration.getShardingColumnsMap().get(record.getTableName()));
List<Column> values = new ArrayList<>();
values.addAll(RecordUtil.extractUpdatedColumns(record));
values.addAll(conditionColumns);
String updateSql = sqlBuilder.buildUpdateSQL(record, conditionColumns);
PreparedStatement ps = connection.prepareStatement(updateSql);
for (int i = 0; i < values.size(); i++) {
ps.setObject(i + 1, values.get(i).getValue());
}
ps.execute();
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611#issuecomment-671755426
@dmbj scaling job request body's sourceRule do not have any shardingColumns.
```
"sourceRule": "tables:\n t_qrcode_consume_details:\n actualDataNodes: ds_0.t_qrcode_consume_details\n"
```
Scaling connect Proxy as a standard jdbc connect which do not have shardingColumns api. So, I can't get it.
And we will enhance it after execute by ss-jdbc which can get sharding rules.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] dmbj commented on issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
dmbj commented on issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611#issuecomment-668911539
> Please try it in master version again, I remember we fixed it in master.
ok,ths, i will try later.scaling & proxy both use master version?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] dmbj commented on issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
dmbj commented on issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611#issuecomment-670405480
> @dmbj I remember it surpport sharding column now.
>
> see: org.apache.shardingsphere.scaling.core.execute.executor.importer.AbstractJDBCImporter#executeUpdate
>
> `RecordUtil.extractConditionColumns()` include `ShardingColumns`. It's not useful?
>
> ```java
> private void executeUpdate(final Connection connection, final DataRecord record) throws SQLException {
> List<Column> conditionColumns = RecordUtil.extractConditionColumns(record, rdbmsConfiguration.getShardingColumnsMap().get(record.getTableName()));
> List<Column> values = new ArrayList<>();
> values.addAll(RecordUtil.extractUpdatedColumns(record));
> values.addAll(conditionColumns);
> String updateSql = sqlBuilder.buildUpdateSQL(record, conditionColumns);
> PreparedStatement ps = connection.prepareStatement(updateSql);
> for (int i = 0; i < values.size(); i++) {
> ps.setObject(i + 1, values.get(i).getValue());
> }
> ps.execute();
> }
> ```
In 5.0.0-RC1-SNAPSHOT scaling&proxy i test update SQL in origin table,through scaling&proxy sync increaseTimeData.
I trace proxy log,found binlog explain SQL print logic SQL as:
`[INFO ] 14:49:05.733 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: UPDATE `t_qrcode_consume_details` SET `amount` = 200 WHERE `order_no` = '0010282823892772727272211119237262694955' and `code_type` = '00' and `channel` = '04'`
so it still full scan all sharding.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611#issuecomment-671298128
@dmbj ShardingColumns is extract from sourceRule. my test sourceRule like this, it is work well.
BTW: extract from `source rule` is not reasonable. but we can't get the `destination rule` from proxy. it will enhance after execute by ss-jdbc.
```yaml
tables:
test_pk:
actualDataNodes: ds$->{0..1}.test_pk_$->{0..1}
databaseStrategy:
standard:
shardingColumn: id1
shardingAlgorithmName: test_pk_db_inline
logicTable: t_order
tableStrategy:
standard:
shardingColumn: id2
shardingAlgorithmName: test_pk_tbl_inline
shardingAlgorithms:
test_pk_db_inline:
type: INLINE
props:
algorithm.expression: ds$->{id1 % 2}
test_pk_tbl_inline:
type: INLINE
props:
algorithm.expression: test_pk_$->{id2 % 2}
```
create table sql:
```sql
create table test_pk_0 (
id1 int,
id2 int,
val varchar(12),
primary key (id1,id2)
);
```
sql include ShardingColumns `id1` and `id2`, proxy log:
```
Logic SQL: UPDATE `test_pk` SET `val` = 'ddd' WHERE `id1` = 3 and `id2` = 3
Actual SQL: ds_0 ::: UPDATE `test_pk_1` SET `val` = 'ddd' WHERE `id1` = 3 and `id2` = 3
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] dmbj commented on issue #6611: sharding scaling sync realtime data not surpport union pk
Posted by GitBox <gi...@apache.org>.
dmbj commented on issue #6611:
URL: https://github.com/apache/shardingsphere/issues/6611#issuecomment-671690749
@Lucas-307 here some local config info:
```
CREATE TABLE `t_qrcode_consume_details` (
`order_no` varchar(40) NOT NULL COMMENT '订单唯一ID',
`user_id` varchar(30) DEFAULT NULL COMMENT '用户ID，对应于爱南宁App loginAccount，如果是专用码则为二维码ID',
`code_type` varchar(4) NOT NULL COMMENT '码类型。00：身份码；01：专用码；02：渠道自有码。当为专用码时，不需要判断channel',
`channel` varchar(4) NOT NULL COMMENT '渠道编码。00：地铁；01：BRT；02：公交；03：食堂卡；04：青秀山；05：动物园；06：24小时书吧；07：发改委门禁',
`update_time` datetime DEFAULT NULL COMMENT '渠道方最后提供该订单修改日期',
`amount` bigint(11) DEFAULT NULL COMMENT '订单金额。单位：分',
`scene` varchar(300) DEFAULT NULL COMMENT '订单场景。可填写状态变更站点等信息',
`extra` text COMMENT '额外数据。可单独与渠道商量额外传输的数据。比如进出站日期',
`paid_charge_id` varchar(40) DEFAULT NULL COMMENT '补缴支付凭证号',
`init_status` varchar(4) DEFAULT NULL COMMENT '订单初始化状态，记录该笔订单，最开始的订单状态',
`status` varchar(4) DEFAULT NULL COMMENT '订单状态。00：待支付；01：已支付；02：支付失败；03-已退款',
`gmt_create` datetime DEFAULT NULL COMMENT '记录创建日期',
`gmt_modify` datetime DEFAULT NULL COMMENT '记录修改日期',
`rev` varchar(200) DEFAULT NULL COMMENT '冗余字段',
`pay_channel` varchar(4) DEFAULT NULL,
`pay_order_no` varchar(60) DEFAULT NULL,
PRIMARY KEY (`order_no`,`code_type`,`channel`),
KEY `INDEX_QCD_USER_ID` (`user_id`) USING BTREE,
KEY `INDEX_PAY_ORDERNO` (`pay_order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='二维码消费记录表';
```
proxy config-sharding.yaml
```yaml
rules:
- !SHARDING
tables:
t_qrcode_consume_details:
actualDataNodes: ds_0.t_qrcode_consume_details_$->{0..19}
tableStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: t_qrcode_consume_details_inline
shardingAlgorithms:
t_qrcode_consume_details_inline:
type: INLINE
props:
algorithm.expression: t_qrcode_consume_details_$->{user_id.toBigInteger() % 20}
```
scaling job request body:
```
{
"ruleConfiguration": {
"sourceDatasource": "ds_0: !!org.apache.shardingsphere.orchestration.core.common.yaml.config.YamlDataSourceConfiguration\n dataSourceClassName: com.zaxxer.hikari.HikariDataSource\n props:\n jdbcUrl: jdbc:mysql://192.168.100.169:3306/appbiz_data_test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true\n username: root\n password: 'test123'\n connectionTimeout: 30000\n idleTimeout: 60000\n maxLifetime: 1800000\n maxPoolSize: 200\n minPoolSize: 10\n maintenanceIntervalMilliseconds: 30000\n readOnly: false\n",
"sourceRule": "tables:\n t_qrcode_consume_details:\n actualDataNodes: ds_0.t_qrcode_consume_details\n",
"destinationDataSources": {
"name": "dt_0",
"password": "sharding",
"url": "jdbc:mysql://192.168.100.217:3307/sharding_test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false",
"username": "sharding"
}
},
"jobConfiguration": {
"concurrency": 10
}
}
```
when inventoryDataTask finished sync increaseData, test update SQL:
```
UPDATE t_qrcode_consume_detailsSETamount=100 WHERE order_no='15362822300000061787990021341006' AND user_id='101001191'
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org