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 2021/03/11 11:59:50 UTC
[GitHub] [shardingsphere] footmanFF opened a new issue #9637: error sql route when select concurrently
footmanFF opened a new issue #9637:
URL: https://github.com/apache/shardingsphere/issues/9637
### the full test case I use
https://gitee.com/footmanff/sharding-jdbc-demos/blob/master/sharding-jdbc-3.1.0-bug-test/src/main/java/com/footmanff/sharding/jdbc/sample/Test1.java
### information
version：3.1.0
project：sharding-jdbc
### logic sql
```
select * from order where user_id =? // 1
select * from t_record where user_id =? // 1
```
### db and table
I use two DB, sharding_jdbc_demo_db0 and sharding_jdbc_demo_db1：
#### sharding_jdbc_demo_db0：
```
CREATE TABLE `order_0` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `order_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_record_0` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
CREATE TABLE `t_record_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
```
#### sharding_jdbc_demo_db1：
```
CREATE TABLE `order_0` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `order_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_record_0` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
CREATE TABLE `t_record_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
```
### datasource sharding rule and table sharding rule
```java
@Bean
public DataSource dataSource() throws SQLException {
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
// t_record
shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration1());
// order
shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration2());
Map<String, Object> configMap = new HashMap<>();
Properties props = new Properties();
props.put("sql.show", "true");
return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, configMap, props);
}
TableRuleConfiguration getOrderTableRuleConfiguration1() {
TableRuleConfiguration result = new TableRuleConfiguration();
result.setLogicTable("t_record");
result.setActualDataNodes("sharding_jdbc_demo_db${0..1}.t_record_${0..1}");
result.setKeyGeneratorColumnName("order_id");
result.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "sharding_jdbc_demo_db${user_id % 2}"));
result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_record_${order_id % 2}"));
return result;
}
TableRuleConfiguration getOrderTableRuleConfiguration2() {
TableRuleConfiguration result = new TableRuleConfiguration();
result.setLogicTable("order");
result.setActualDataNodes("sharding_jdbc_demo_db${0..1}.order_${0..1}");
result.setKeyGeneratorColumnName("order_id");
result.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "sharding_jdbc_demo_db${user_id % 2}"));
result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "order_${user_id % 2}"));
return result;
}
public Map<String, DataSource> createDataSourceMap() {
// 配置第 1 个数据源
DruidDataSource dataSource1 = new DruidDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://localhost:3306/sharding_jdbc_demo_db0");
dataSource1.setUsername("root");
dataSource1.setPassword("root");
// 配置第 2 个数据源
DruidDataSource dataSource2 = new DruidDataSource();
dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
dataSource2.setUrl("jdbc:mysql://localhost:3306/sharding_jdbc_demo_db1");
dataSource2.setUsername("root");
dataSource2.setPassword("root");
Map<String, DataSource> result = new HashMap<>();
result.put("sharding_jdbc_demo_db0", dataSource1);
result.put("sharding_jdbc_demo_db1", dataSource2);
return result;
}
```
### my test case
```java
@Test
public void t1() throws Exception {
ApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");
final DataSource dataSource = ctx.getBean(DataSource.class);
ExecutorService executorService = Executors.newFixedThreadPool(100);
int count = 1000;
CountDownLatch countDownLatch = new CountDownLatch(count);
for (int i = 0; i < count; i++) {
final int a = i;
executorService.submit(() -> {
if (a % 2 == 0) {
queryOrder(dataSource);
} else {
queryRecord(dataSource);
}
countDownLatch.countDown();
});
}
countDownLatch.await();
}
private void queryOrder(DataSource dataSource) {
try {
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select * from order where user_id =?");
preparedStatement.setLong(1, 1L);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
long id = resultSet.getLong(1);
long orderId = resultSet.getLong(2);
long userId = resultSet.getLong(3);
System.out.println("order( " + id + ", " + orderId + ", " + userId + ")");
}
preparedStatement.close();
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
private void queryRecord(DataSource dataSource) {
try {
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select * from t_record where user_id =?");
preparedStatement.setLong(1, 1L);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
long id = resultSet.getLong(1);
long orderId = resultSet.getLong(2);
long userId = resultSet.getLong(3);
System.out.println("t_record(" + id + ", " + orderId + ", " + userId + ")");
}
preparedStatement.close();
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
```
### Expected behavior
this sql only execute on db `sharding_jdbc_demo_db1` and table `order_1`
```
select * from order where user_id =? // 1
```
### Actual behavior
I saw actual sql execute log like this：
```
[INFO] [pool-4-thread-67] ShardingSphere-SQL - Rule Type: sharding
[INFO] [pool-4-thread-43] ShardingSphere-SQL - Logic SQL: select * from order where user_id =?
[INFO] [pool-4-thread-43] ShardingSphere-SQL - SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@d8f0ccc), containStar=true, firstSelectItemStartPosition=7, selectListLastPosition=9, groupByLastPosition=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subQueryStatement=null, subQueryStatements=[], subQueryConditions=[])
[INFO] [pool-4-thread-43] ShardingSphere-SQL - Actual SQL: sharding_jdbc_demo_db0 ::: select * from order_0 where user_id =? ::: [[1]]
[INFO] [pool-4-thread-43] ShardingSphere-SQL - Actual SQL: sharding_jdbc_demo_db0 ::: select * from order_1 where user_id =? ::: [[1]]
[INFO] [pool-4-thread-43] ShardingSphere-SQL - Actual SQL: sharding_jdbc_demo_db1 ::: select * from order_0 where user_id =? ::: [[1]]
[INFO] [pool-4-thread-43] ShardingSphere-SQL - Actual SQL: sharding_jdbc_demo_db1 ::: select * from order_1 where user_id =? ::: [[1]]
```
the sql executed on all database and all table.
### my analyze
i guess the io.shardingsphere.core.parsing.antlr.extractor.impl.FromWhereExtractor cause the problem.
FromWhereExtractor is used as single instance. but the field called predicateSegmentExtractor is created every time execute the method called extract of FromWhereExtractor：
```java
/**
* From clause extractor.
*
* @author duhongjun
*/
public final class FromWhereExtractor implements OptionalSQLSegmentExtractor {
private PredicateExtractor predicateSegmentExtractor;
/**
* Extract SQL segment from SQL AST.
*
* @param ancestorNode ancestor node of AST
* @param rootNode root node of AST
* @return SQL segment
*/
public Optional<FromWhereSegment> extract(final ParserRuleContext ancestorNode, final ParserRuleContext rootNode) {
// ...
FromWhereSegment result = new FromWhereSegment();
predicateSegmentExtractor = new PredicateExtractor(result.getTableAliases());
// ...
}
}
```
tableAliases(Map<String, String>) is shared by the field predicateSegmentExtractor. i guess this is the caused.
the returned FromWhereSegment of method extract will wrong.
the field tableName of class ColumnSegment my be wrong. this cause the wrong sql route.
----------------------------------------------------------------
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] footmanFF commented on issue #9637: error sql route when select concurrently
Posted by GitBox <gi...@apache.org>.
footmanFF commented on issue #9637:
URL: https://github.com/apache/shardingsphere/issues/9637#issuecomment-796691378
this module contain all ths source I test.
https://gitee.com/footmanff/sharding-jdbc-demos/tree/master/sharding-jdbc-3.1.0-bug-test
see Test1.t1
----------------------------------------------------------------
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] footmanFF closed issue #9637: error sql route when select concurrently
Posted by GitBox <gi...@apache.org>.
footmanFF closed issue #9637:
URL: https://github.com/apache/shardingsphere/issues/9637
----------------------------------------------------------------
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] avalon5666 commented on issue #9637: error sql route when select concurrently
Posted by GitBox <gi...@apache.org>.
avalon5666 commented on issue #9637:
URL: https://github.com/apache/shardingsphere/issues/9637#issuecomment-796782021
Fix after 4.0.0.M1, #1967
----------------------------------------------------------------
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] footmanFF commented on issue #9637: error sql route when select concurrently
Posted by GitBox <gi...@apache.org>.
footmanFF commented on issue #9637:
URL: https://github.com/apache/shardingsphere/issues/9637#issuecomment-796797118
----------------------------------------------------------------
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