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 2022/10/10 06:37:39 UTC
[GitHub] [shardingsphere] inotgaoshou opened a new issue, #19514: Cross-database association query error
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 . 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 which named “needu”.
this table of 'person_chat_msg ' is in databse which named “needu-shard”.
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #19514: Cross-database association query error
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #19514:
URL: https://github.com/apache/shardingsphere/issues/19514#issuecomment-1407259940
Closed due to no response.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] inotgaoshou commented on issue #19514: Cross-database association query error
Posted by GitBox <gi...@apache.org>.
inotgaoshou commented on issue #19514:
URL: https://github.com/apache/shardingsphere/issues/19514#issuecomment-1193557804
hi @strongduanmu , Thanks a lot for the reply quickly.I do not set these sharding tables binding。This morning, I also find through the content of the binding table in the issue.I'm not sure if the problem is a bug or a problem with my own configuration. If it is a configuration problem, how can I configure it, thank you very much!
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang closed issue #19514: Cross-database association query error
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang closed issue #19514: Cross-database association query error
URL: https://github.com/apache/shardingsphere/issues/19514
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] closed issue #19514: Cross-database association query error
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #19514: Cross-database association query error
URL: https://github.com/apache/shardingsphere/issues/19514
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] commented on issue #19514: Cross-database association query error
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #19514:
URL: https://github.com/apache/shardingsphere/issues/19514#issuecomment-1272349252
Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #19514: Cross-database association query error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #19514:
URL: https://github.com/apache/shardingsphere/issues/19514#issuecomment-1193544606
Hi @inotgaoshou, does these sharding tables are binding tables?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] inotgaoshou commented on issue #19514: Cross-database association query error
Posted by GitBox <gi...@apache.org>.
inotgaoshou commented on issue #19514:
URL: https://github.com/apache/shardingsphere/issues/19514#issuecomment-1194939446
> You can try to config binding tables first. If you encounter any exception, you can reply to me.
I don't know how to configure it, can you give me an example,thanks. I have not configured it before version 4.1, but it can be used normally.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #19514: Cross-database association query error
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #19514:
URL: https://github.com/apache/shardingsphere/issues/19514#issuecomment-1386496374
Hi @inotgaoshou ,
Is your problem solved?
Here is the configuration document: https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/yaml-config/rules/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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] inotgaoshou commented on issue #19514: Cross-database association query error
Posted by GitBox <gi...@apache.org>.
inotgaoshou commented on issue #19514:
URL: https://github.com/apache/shardingsphere/issues/19514#issuecomment-1194939183
> You can try to config binding tables first. If you encounter any exception, you can reply to me.
I don't know how to configure it, can you give me an example,thanks. I have not configured it before version 4.1, but it can be used normally.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #19514: Cross-database association query error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #19514:
URL: https://github.com/apache/shardingsphere/issues/19514#issuecomment-1193575935
You can try to config binding tables first. If you encounter any exception, you can reply to me.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] inotgaoshou commented on issue #19514: Cross-database association query error
Posted by GitBox <gi...@apache.org>.
inotgaoshou commented on issue #19514:
URL: https://github.com/apache/shardingsphere/issues/19514#issuecomment-1194934245
I don't quite understand the configuration or meaning of the binding table. My problem now is that the table of users in the associated table cannot be recognized, because the 'users' table is in another database.
like set java code below?
```
shardingRuleConfig.getBroadcastTables().add("users");
```
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org