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/09/11 03:36:26 UTC
[GitHub] [shardingsphere] fantacy2001 opened a new issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
fantacy2001 opened a new issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401
it is the same with #7227
sharding jdbc 4.0.0
mybatis spring
is single insert
sql mapper:
like "insert into table values();"
sharding config ： only sharding db , Custom keyGenerator : UidGenerator
in one Transactional , Loop in the java code to call single insert Dao（insertByStockMoveDetail）, like ：
for(A a : aList){
insertByStockMoveDetail(a);
}
The insertion ID obtained each time in the loop is the first.
remove the transaction, it's OK
It is not similar with #6895 . when the second insert sql, the generator ids 'length is two , but the columnIndex is 1, so the result only take the first one :
<img width="960" alt="key" src="https://user-images.githubusercontent.com/49382774/92853396-5a57f280-f422-11ea-932b-b02b566e034c.png">
----------------------------------------------------------------
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] fantacy2001 commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-694076949
> > There is no problem with the generate key, and there is no problem inserting into the db, but there is a problem when getting the key
>
> Hi @fantacy2001 Thanks for your feedback. That's precisely a bug, same with #6895. However, @terrymanu is working on this one. Let us leave him some time. :-)
ok, thanks!
----------------------------------------------------------------
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] fantacy2001 commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-692611985
> But these cases(multi-values and/or batch) are complex for generated keys, and now haven't completely resolved in master/5.x yet.
There is no problem with the generate key, and there is no problem inserting into the db, but there is a problem when getting the key
----------------------------------------------------------------
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] kimmking commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-691028633
----------------------------------------------------------------
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] tristaZero commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-690862289
----------------------------------------------------------------
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] kimmking edited a comment on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-691967424
You can try 4.1.1
But these cases(multi-values and/or batch) are complex for generated keys, and now haven't completely resolved in master/5.x yet.
1. https://github.com/apache/shardingsphere/issues/4573
----------------------------------------------------------------
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] kimmking commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-691028633
can you show your config rules, logic sql, actual sql ?
----------------------------------------------------------------
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] fantacy2001 commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-692610809
> You can try 4.1.1
> But these cases(multi-values and/or batch) are complex for generated keys, and now haven't completely resolved in master/5.x yet.
>
> 1. #4573
The same problem occurs in version 4.1.1
----------------------------------------------------------------
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] tristaZero commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-690862289
@fantacy2001 Hi thanks for your issue and debugging.
Since @terrymanu is taking charge of the relevant issue, could you give this one a look? Thanks.
----------------------------------------------------------------
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] kimmking commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-691028633
----------------------------------------------------------------
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] kimmking commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-691967424
You can try 4.1.1
But these cases(multi-values and/or batch) are complex for generated keys, and now haven't completely resolved yet.
1. https://github.com/apache/shardingsphere/issues/4573
----------------------------------------------------------------
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] fantacy2001 commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-690892534
----------------------------------------------------------------
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] fantacy2001 commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-690892534
> @fantacy2001 Hi thanks for your issue and debugging.
> Since @terrymanu is taking charge of the relevant issue, could you give this one a look? Thanks.
when the second sql is executed, in method setGeneratedValues, the generatedValues has two id, then sqlRouteResult.getGeneratedKey().get().getGeneratedValues().addAll(generatedValues ) ,so sqlRouteResult has two id , pass on mybatis's method getResult
<img width="960" alt="key22" src="https://user-images.githubusercontent.com/49382774/92869925-e7587700-f435-11ea-9dab-35e817fa4bc7.png">
----------------------------------------------------------------
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] fantacy2001 commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-690892534
----------------------------------------------------------------
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] tristaZero commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-693152337
> There is no problem with the generate key, and there is no problem inserting into the db, but there is a problem when getting the key
Hi @fantacy2001 Thanks for your feedback. That's precisely a bug, same with #6895. However, @terrymanu is working on this one. Let us leave him some time. :-)
----------------------------------------------------------------
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] tristaZero commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-690862289
----------------------------------------------------------------
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] tristaZero closed issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401
----------------------------------------------------------------
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] fantacy2001 commented on issue #7401: sharding jdbc, when insert config useGeneratedKeys , in one Transactional , the ID obtained by batch insertion is the first one
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #7401:
URL: https://github.com/apache/shardingsphere/issues/7401#issuecomment-691184682
> can you show your config rules, logic sql, actual sql ?
> can you show your config rules, logic sql, actual sql ?
version 4.0.0
Because I redefined sharding's access with Java, the configuration file is different from the official one。
It is divided into two libraries, each of which has one master and two slaves。
sharding field： tenant_id
KeyGenerator is baidu UID
the customize sharding Algorithm ，implements PreciseShardingAlgorithm
2020-09-12 00:03:19 [INFO] [main] - ShardingSphere-SQL.log():99 - Logic SQL: insert into stock_move_detail
( tenant_id,
move_no,
warehouse_no,
is_delete,
create_time,
update_time,
owner_no,
sku_no,
product_level,
location_no,
container_no,
lot_no,
package_no,
inbound_date,
stock_md5 )
values
( ?,
?,
?,
0,
now(),
now(),
?,
?,
?,
?,
?,
?,
?,
?,
? )
2020-09-12 00:03:19 [INFO] [main] - ShardingSphere-SQL.log():99 - SQLStatement: InsertSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@73d76b9d, tablesContext=TablesContext(tables=[Table(name=stock_move_detail, alias=Optional.absent())], schema=Optional.absent())), columnNames=[tenant_id, move_no, warehouse_no, is_delete, create_time, update_time, owner_no, sku_no, product_level, location_no, container_no, lot_no, package_no, inbound_date, stock_md5], insertValueContexts=[InsertValueContext(parametersCount=12, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=948, stopIndex=948, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=963, stopIndex=963, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=978, stopIndex=978, parameterMarkerIndex=2), LiteralExpressionSegment(startIndex=993, stopIndex=993, literals=0), CommonExpressionSegment(startIndex=100
8, stopIndex=1012, text=now()), CommonExpressionSegment(startIndex=1027, stopIndex=1031, text=now()), ParameterMarkerExpressionSegment(startIndex=1063, stopIndex=1063, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=1108, stopIndex=1108, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=1179, stopIndex=1179, parameterMarkerIndex=5), ParameterMarkerExpressionSegment(startIndex=1224, stopIndex=1224, parameterMarkerIndex=6), ParameterMarkerExpressionSegment(startIndex=1282, stopIndex=1282, parameterMarkerIndex=7), ParameterMarkerExpressionSegment(startIndex=1327, stopIndex=1327, parameterMarkerIndex=8), ParameterMarkerExpressionSegment(startIndex=1398, stopIndex=1398, parameterMarkerIndex=9), ParameterMarkerExpressionSegment(startIndex=1643, stopIndex=1643, parameterMarkerIndex=10), ParameterMarkerExpressionSegment(startIndex=1701, stopIndex=1701, parameterMarkerIndex=11), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSeg
ment(startIndex=0, stopIndex=0, parameterMarkerIndex=12))], parameters=[jd, jsllaooslso12lsooso, 26, owmer123, sku123, 0, D-1-1-1, jdcontainer, lot12345678, uom, 2020-09-12 00:03:18.298, 8025ead2171db31e7579c8e5a6b7bf3a])])
2020-09-12 00:03:19 [INFO] [main] - ShardingSphere-SQL.log():99 - Actual SQL: ds_1 ::: insert into stock_move_detail
( tenant_id,
move_no,
warehouse_no,
is_delete,
create_time,
update_time,
owner_no,
sku_no,
product_level,
location_no,
container_no,
lot_no,
package_no,
inbound_date,
stock_md5 , id)
values
(?, ?, ?, 0, now(), now(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [jd, jsllaooslso12lsooso, 26, owmer123, sku123, 0, D-1-1-1, jdcontainer, lot12345678, uom, 2020-09-12T00:03:18.298+0800, 8025ead2171db31e7579c8e5a6b7bf3a, **13359925271016960**]
2020-09-12 00:03:19 [DEBUG] [main] - com.jd.jcloud.wms.inventory.dao.stockMove.StockMoveDetailDao.insertByStockMoveDetail.debug():139 - <== Updates: 1
2020-09-12 00:03:19 [DEBUG] [main] - com.jd.jcloud.wms.inventory.dao.stockMove.StockMoveDetailDao.insertByStockMoveDetail.debug():139 - <== Updates: 1
result1:**13359925271016960**
2020-09-12 00:03:19 [INFO] [main] - ShardingSphere-SQL.log():99 - Rule Type: sharding
2020-09-12 00:03:19 [INFO] [main] - ShardingSphere-SQL.log():99 - Logic SQL: insert into stock_move_detail
( tenant_id,
move_no,
warehouse_no,
is_delete,
create_time,
update_time,
owner_no,
sku_no,
product_level,
location_no,
container_no,
lot_no,
package_no,
inbound_date,
stock_md5 )
values
( ?,
?,
?,
0,
now(),
now(),
?,
?,
?,
?,
?,
?,
?,
?,
? )
2020-09-12 00:03:19 [INFO] [main] - ShardingSphere-SQL.log():99 - SQLStatement: InsertSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@73d76b9d, tablesContext=TablesContext(tables=[Table(name=stock_move_detail, alias=Optional.absent())], schema=Optional.absent())), columnNames=[tenant_id, move_no, warehouse_no, is_delete, create_time, update_time, owner_no, sku_no, product_level, location_no, container_no, lot_no, package_no, inbound_date, stock_md5], insertValueContexts=[InsertValueContext(parametersCount=12, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=948, stopIndex=948, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=963, stopIndex=963, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=978, stopIndex=978, parameterMarkerIndex=2), LiteralExpressionSegment(startIndex=993, stopIndex=993, literals=0), CommonExpressionSegment(startIndex=100
8, stopIndex=1012, text=now()), CommonExpressionSegment(startIndex=1027, stopIndex=1031, text=now()), ParameterMarkerExpressionSegment(startIndex=1063, stopIndex=1063, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=1108, stopIndex=1108, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=1179, stopIndex=1179, parameterMarkerIndex=5), ParameterMarkerExpressionSegment(startIndex=1224, stopIndex=1224, parameterMarkerIndex=6), ParameterMarkerExpressionSegment(startIndex=1282, stopIndex=1282, parameterMarkerIndex=7), ParameterMarkerExpressionSegment(startIndex=1327, stopIndex=1327, parameterMarkerIndex=8), ParameterMarkerExpressionSegment(startIndex=1398, stopIndex=1398, parameterMarkerIndex=9), ParameterMarkerExpressionSegment(startIndex=1643, stopIndex=1643, parameterMarkerIndex=10), ParameterMarkerExpressionSegment(startIndex=1701, stopIndex=1701, parameterMarkerIndex=11), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSeg
ment(startIndex=0, stopIndex=0, parameterMarkerIndex=12))], parameters=[jd, 2jsllaooslso12lsooso, 26, owmer123, sku121, 0, D-1-1-2, jdcontainer, lot12345671, uom, 2020-09-12 00:03:19.52, 168feaf01d5bd5319666f15b97fec37c])])
2020-09-12 00:03:19 [INFO] [main] - ShardingSphere-SQL.log():99 - Actual SQL: ds_1 ::: insert into stock_move_detail
( tenant_id,
move_no,
warehouse_no,
is_delete,
create_time,
update_time,
owner_no,
sku_no,
product_level,
location_no,
container_no,
lot_no,
package_no,
inbound_date,
stock_md5 , id)
values
(?, ?, ?, 0, now(), now(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [jd, 2jsllaooslso12lsooso, 26, owmer123, sku121, 0, D-1-1-2, jdcontainer, lot12345671, uom, 2020-09-12T00:03:19.520+0800, 168feaf01d5bd5319666f15b97fec37c, **13359925271016961**]
2020-09-12 00:03:19 [DEBUG] [main] - com.jd.jcloud.wms.inventory.dao.stockMove.StockMoveDetailDao.insertByStockMoveDetail.debug():139 - <== Updates: 1
2020-09-12 00:03:19 [DEBUG] [main] - com.jd.jcloud.wms.inventory.dao.stockMove.StockMoveDetailDao.insertByStockMoveDetail.(): - <== Updates: 1
result2:**13359925271016960**
2020-09-12 00:03:19 [INFO] [main] - org.springframework.test.context.transaction.TransactionContext.endTransaction():136 - Committed transaction for test context [DefaultTestContext@f56c2a0 testClass = StockMoveTest, testInstance = com.jd.jcloud.wms.stockMove.StockMoveTest@58bf45ca, testMethod = testInsertByStockMoveDetail@StockMoveTest, testException = [null], mergedContextConfiguration = [MergedContextConfiguration@42c661d4 testClass = StockMoveTest, locations = '{classpath*:spring/config/spring-config.xml, classpath*:spring/config/spring-config-sharding.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]].
----------------------------------------------------------------
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