yyTraveler opened a new issue, #33368:
URL: https://github.com/apache/shardingsphere/issues/33368
## Question
i try to use the java api to config the sharding-jdbc 5.5.0
even if i have already config the SingleRuleConfiguration class, when doing
the select query, the target table still turns out to be **Table or view
'lcz_speech' does not exist.**
here are some tips:
1. i see the basicDataSource has been loaded (not null) when debugging , you
can see its xml config below.
2. the spring service can be started up successfully and all other tables
can do select queries regularly except the lcz_speech table meet this problem.
i have tried to rename the table in mysql as lcz_speech_bak, but still doesn't
work.
3. till now, i haven't seen actual data node tables appeared in mysql, such
lcz_speech_202402
the stacktrace
```shell
org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException:
Table or view 'lcz_speech' does not exist.
at
org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.lambda$checkTableExists$4(SimpleTableSegmentBinder.java:151)
~[shardingsphere-infra-binder-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41)
~[shardingsphere-infra-exception-core-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.checkTableExists(SimpleTableSegmentBinder.java:148)
~[shardingsphere-infra-binder-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.bind(SimpleTableSegmentBinder.java:84)
~[shardingsphere-infra-binder-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.infra.binder.segment.from.TableSegmentBinder.bind(TableSegmentBinder.java:55)
~[shardingsphere-infra-binder-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.lambda$bind$1(SelectStatementBinder.java:60)
~[shardingsphere-infra-binder-5.5.0.jar:5.5.0]
at java.util.Optional.map(Optional.java:215) ~[na:1.8.0_131]
at
org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:60)
~[shardingsphere-infra-binder-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:48)
~[shardingsphere-infra-binder-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bindDMLStatement(SQLBindEngine.java:82)
~[shardingsphere-infra-binder-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:72)
~[shardingsphere-infra-binder-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:63)
~[shardingsphere-infra-binder-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:207)
~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:172)
~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
at
org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:94)
~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
at
org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at
org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at
org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1885)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:910)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2554)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2540)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.loader.Loader.list(Loader.java:2365)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at
org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1909)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at
org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at
com.xuelang.scrapy.service.common.strategy.dao.SpeechDao.pageSpeechByStrategy(SpeechDao.java:187)
~[scrapy.service-1.0.0-SNAPSHOT.jar:na]
at
com.xuelang.scrapy.service.common.strategy.service.SpeechServiceImpl.pageSpeechByStrategy(SpeechServiceImpl.java:121)
~[scrapy.service-1.0.0-SNAPSHOT.jar:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
~[na:1.8.0_131]
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
~[na:1.8.0_131]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[na:1.8.0_131]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
~[spring-tx-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
~[spring-tx-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
~[spring-tx-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at com.sun.proxy.$Proxy98.pageSpeechByStrategy(Unknown Source) ~[na:na]
at
com.xuelang.scrapy.web.controller.v1.scrapy.SpeechController.pageSpeechByStrategy(SpeechController.java:77)
~[classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
~[na:1.8.0_131]
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
~[na:1.8.0_131]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[na:1.8.0_131]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
at
org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:682)
[servlet-api.jar:na]
at
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:765)
[servlet-api.jar:na]
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
[catalina.jar:8.5.85]
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[catalina.jar:8.5.85]
at
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
[tomcat-websocket.jar:8.5.85]
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[catalina.jar:8.5.85]
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[catalina.jar:8.5.85]
at
com.github.xiaoymin.knife4j.spring.filter.SecurityBasicAuthFilter.doFilter(SecurityBasicAuthFilter.java:90)
[knife4j-spring-2.0.5.jar:na]
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[catalina.jar:8.5.85]
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[catalina.jar:8.5.85]
at com.xuelang.scrapy.common.xss.XssFilter.doFilter(XssFilter.java:61)
[scrapy.common-1.0.0-SNAPSHOT.jar:na]
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[catalina.jar:8.5.85]
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[catalina.jar:8.5.85]
```
the config class i use
```java
@Configuration
public class ShardingConfiguration {
private static final String DATA_SOURCE_NAME = "basicDataSource";
@Resource(name = "basicDataSource")
private DataSource dataSource;
@Bean("shardingDataSource")
public DataSource getShardingSphereDataSource() throws SQLException {
ModeConfiguration modeConfiguration = new
ModeConfiguration("Standalone", new
StandalonePersistRepositoryConfiguration("JDBC", new Properties()));
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put(DATA_SOURCE_NAME, dataSource);
Collection<RuleConfiguration> ruleConfigs = Arrays.asList(
createSingleRuleConfiguration(),
createShardingRuleConfiguration()
);
Properties props = new Properties();
props.setProperty("sql.show", "true");
DataSource dataSource =
ShardingSphereDataSourceFactory.createDataSource(DATA_SOURCE_NAME,
modeConfiguration, dataSourceMap, ruleConfigs, props);
return dataSource;
}
private SingleRuleConfiguration createSingleRuleConfiguration() {
SingleRuleConfiguration singleRuleConfiguration = new
SingleRuleConfiguration();
singleRuleConfiguration.setTables(Collections.singletonList(
DATA_SOURCE_NAME + ".*"));
return singleRuleConfiguration;
}
private ShardingRuleConfiguration createShardingRuleConfiguration() {
ShardingRuleConfiguration result = new ShardingRuleConfiguration();
result.getTables().add(getLczSpeechTableRuleConfiguration());
result.getBindingTableGroups().add(new
ShardingTableReferenceRuleConfiguration("sharding_mapping_rules",
"lcz_speech"));
result.setDefaultTableShardingStrategy(new
StandardShardingStrategyConfiguration("create_time",
"standard-time-range-configuration"));
Properties props = new Properties();
props.setProperty("datetime-pattern", "yyyy-MM-dd HH:mm:ss");
props.setProperty("datetime-lower", "2023-01-01 00:00:00");
props.setProperty("datetime-upper", "2026-12-01 00:00:00");
props.setProperty("sharding-suffix-pattern","yyyyMM");
props.setProperty("datetime-interval-amount", "1");
props.setProperty("datetime-interval-unit", "MONTHS");
// new AlgorithmConfiguration(type_str, Properties);
result.getShardingAlgorithms().put("standard-time-range-configuration", new
AlgorithmConfiguration("INTERVAL", props));
return result;
}
private ShardingTableRuleConfiguration
getLczSpeechTableRuleConfiguration() {
ShardingTableRuleConfiguration result = new
ShardingTableRuleConfiguration("lcz_speech", DATA_SOURCE_NAME +
".lcz_speech_${202301..202612}");
result.setTableShardingStrategy(new
StandardShardingStrategyConfiguration("create_time",
"standard-time-range-configuration"));
return result;
}
}
```
the basicDataSource calss
```xml
<bean id="basicDataSource"
class="org.apache.commons.dbcp2.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${jdbc.className}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="defaultAutoCommit" value="${jdbc.autoCommit}" />
<property name="maxTotal"
value="${jdbc.maximum.connection.count}" />
<property name="minIdle"
value="${jdbc.minimum.connection.count}"></property>
</bean>
```
--
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]
