inotgaoshou opened a new issue, #19514:
URL: https://github.com/apache/shardingsphere/issues/19514
## Bug Report
When the version was upgraded from 4.1 to 5.1.2, the statement that could be
executed correctly across the database before， now execute again occurred and
error occurred r. The specific exception information is as follows
```
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: Table
'needu_shard.users' doesn't exist
### The error may exist in file
[/Users/user/oversea/oversea_server/needu-sharding/target/classes/shardingMapper/PersonChatMsgMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select COUNT(DISTINCT to_uid) as userCount, from_uid as uid
from person_chat_msg pcm LEFT JOIN users u on pcm.from_uid=u.uid
where msg_time BETWEEN ? and ? and u.create_time BETWEEN ? and ? and
pcm.custom_msg_type in (1,2,3)
group by from_uid
### Cause: java.sql.SQLSyntaxErrorException: Table 'needu_shard.users'
doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException:
Table 'needu_shard.users' doesn't exist
at
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy97.selectList(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at
com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:158)
at
com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:76)
at
com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:62)
at com.sun.proxy.$Proxy527.getStatisticsCountBy(Unknown Source)
```
and my configure rules。
```
private String timeShardingAlgorithm = "timeShardingAlgorithm";
private String mapperLocations = "classpath*:/shardingMapper/*.xml";
private String rangeDate = "${2021..2022}${['01','02','03',
'04','05','06', '07','08','09', '10','11','12']}";
@Bean(name = "shardSource")
@ConfigurationProperties(prefix = "spring.datasource.db2")
public HikariDataSource shardDbDataSource() {
HikariDataSource hikariDataSource = new HikariDataSource();
return hikariDataSource;
}
@Resource(name = "shardSource")
private DataSource dataSource;
@Autowired
private Environment environment;
@Bean("shardingDataSource")
public DataSource shardingDataSource() throws SQLException {
ShardingRuleConfiguration shardingRuleConfig = new
ShardingRuleConfiguration();
Collection<ShardingTableRuleConfiguration> tableRuleConfigs =
shardingRuleConfig.getTables();
//私聊记录按月分表
tableRuleConfigs.add(getPersonChatMsgShardingTableRuleConfiguration());
Date date = new Date();
Date endTime = DateTimeUtil.getEndTimeOfThisMonth(date);
Calendar instance = Calendar.getInstance();
instance.setTime(DateTimeUtil.getBeginTimeOfMonth(date));
instance.add(Calendar.MONTH, -3);
Date startTime = instance.getTime();
String datetimeLower = DateTimeUtil.convertDate(startTime);
String datetimeUpper =
DateTimeUtil.convertDate(endTime);//,DateTimeUtil.DATE_FORMAT_YEAR_MONTH
log.info("datetimeLower:{},datetimeUpper:{}",datetimeLower,datetimeUpper);
// 配置时间分区分表算法
Properties tableShardingAlgorithmrProps = new Properties();
tableShardingAlgorithmrProps.setProperty("algorithm-expression",
"community_dynamic_comment_${dynamic_id % 10}");
shardingRuleConfig.getShardingAlgorithms().put(dynamicCommentShardingAlgorithm,
new ShardingSphereAlgorithmConfiguration(AlgorithmTypeConstants.inline,
tableShardingAlgorithmrProps));
tableShardingAlgorithmrProps.setProperty("datetime-pattern",
"yyyy-MM-dd HH:mm:ss");
tableShardingAlgorithmrProps.setProperty("datetime-lower",
datetimeLower);
tableShardingAlgorithmrProps.setProperty("datetime-upper",
datetimeUpper);
tableShardingAlgorithmrProps.setProperty("sharding-suffix-pattern",
DateTimeUtil.DATE_FORMAT_YEAR_MONTH);
tableShardingAlgorithmrProps.setProperty("datetime-interval-amount",
"1");
tableShardingAlgorithmrProps.setProperty("datetime-interval-unit",
"MONTHS");
shardingRuleConfig.getShardingAlgorithms().put(timeShardingAlgorithm, new
ShardingSphereAlgorithmConfiguration(AlgorithmTypeConstants.interval,
tableShardingAlgorithmrProps));
//属性配置
Properties props = new Properties();
//是否打印执行的sql语句
boolean sqlShow = true;
props.setProperty("sql-show", String.valueOf(sqlShow));
props.setProperty("data-source-aggregation-enabled", "true");
props.setProperty("sql-federation-enabled", "true");
// 创建 ShardingSphereDataSource
DataSource dataSource =
ShardingSphereDataSourceFactory.createDataSource(createDataSourceMap(),
Collections.singleton(shardingRuleConfig), props);
return dataSource;
}
@Bean("shardingSqlSessionFactory")
@DependsOn({"shardingDataSource", "pageInterceptor",
"paginationInterceptor"})
public MybatisSqlSessionFactoryBean
sqlSessionFactoryBean(@Qualifier("shardingDataSource")DataSource
shardingDataSource,
Interceptor
pageInterceptor, PaginationInterceptor paginationInterceptor) throws
IOException {
MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new
MybatisSqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(shardingDataSource);
org.springframework.core.io.Resource[] pathMatchingResource =
ResourceUtil.getPathMatchingResource(mapperLocations);
sqlSessionFactoryBean.setMapperLocations(pathMatchingResource);
sqlSessionFactoryBean.setPlugins(new Interceptor[]{pageInterceptor,
paginationInterceptor});
return sqlSessionFactoryBean;
}
@Bean("shardingTransactionManager")
@DependsOn({"shardingDataSource"})
public DataSourceTransactionManager
transactionManager(@Qualifier("shardingDataSource") DataSource
shardingDataSource) {
return new DataSourceTransactionManager(shardingDataSource);
}
/**
* 创建数据源
* @return
*/
private Map<String, DataSource> createDataSourceMap() {
Map<String, DataSource> result = new HashMap<>(4);
result.put("shard", dataSource);
return result;
}
/**
* 私聊记录分表策略
* @return
*/
private ShardingTableRuleConfiguration
getPersonChatMsgShardingTableRuleConfiguration() {
String logicTable = "person_chat_msg";
String actualDataNodes = "shard.person_chat_msg_"+rangeDate;
ShardingTableRuleConfiguration result = new
ShardingTableRuleConfiguration(logicTable, actualDataNodes);
String shardingColumn = "msg_time";
result.setTableShardingStrategy(new
StandardShardingStrategyConfiguration(shardingColumn, timeShardingAlgorithm));
return result;
}
```
this table of 'users ' is in another databse.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
5.1.2
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
can cross database join query
### Actual behavior
query error
### Reason analyze (If you can)
The database where the users table is located cannot be identified。
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
above
### Example codes for reproduce this issue (such as a github link).
none.
sql eg：
```
<select id="getStatisticsListBy">
select to_uid as toUid, count(from_uid) as msgCount,from_uid as uid,
pb.handle_result as handleResult,pb.update_by as updateBy
from person_chat_msg pcm LEFT JOIN **users** u on pcm.from_uid=u.uid
LEFT JOIN poach_block pb on pcm.from_uid=pb.uid
where msg_time BETWEEN #{msgBeginTime} and #{msgEndTime} and
u.create_time BETWEEN #{signBeginTime} and #{signEndTime}
and pcm.custom_msg_type in (1,2,3)
<if test="handleResult != null and handleResult != 0">
and pb.handle_result = #{handleResult}
</if>
<if test="handleResult != null and handleResult == 0">
and (pb.handle_result is null or pb.handle_result = 0)
</if>
<if test="uidList != null and uidList.size > 0">
and pcm.from_uid in
<foreach collection="uidList" item="uid" open="(" separator=","
close=")">
#{uid}
</foreach>
</if>
group by from_uid, to_uid
</select>
```
--
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.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
