Closed PaleWhiteDot closed 3 months ago
@PaleWhiteDot Thank you very much for your feedback. I will investigate this issue later.
Me too !!!
database: Oracle
exception: Failed to instantiate [org.apache.shardingsphere.shardingjdbc.spring.datasource.SpringShardingDataSource]: Constructor threw exception; nested exception is java.sql.SQLException: ORA-00942: 表或视图不存在
Oracle table is in schema A, but read tables from schema B。 It took a whole afternoon to debug source codes , then i find shardingsphere read wrong tables.
Please help me !
@PaleWhiteDot Thank you very much for your feedback. I will investigate this issue later.
Me too !!!
database: Oracle exception: Failed to instantiate [org.apache.shardingsphere.shardingjdbc.spring.datasource.SpringShardingDataSource]: Constructor threw exception; nested exception is java.sql.SQLException: ORA-00942: 表或视图不存在
Oracle table is in schema A, but read tables from schema B。 It took a whole afternoon to debug source codes , then i find shardingsphere read wrong tables.
Please help me !
@PaleWhiteDot Thank you very much for your feedback. I will investigate this issue later.
shardingsphere version : 4.1.1 spring version: 4.1.3-release
@PaleWhiteDot @chenxk I have tried to reproduce this problem, but failed. Can you provide steps to reproduce this exception?
@PaleWhiteDot @chenxk I have tried to reproduce this problem, but failed. Can you provide steps to reproduce this exception?
I found where the question is? In this method:PhysicalIndexMetaDataLoader.load(final Connection connection, final String table), will invoke connection.getSchema() and then will invoke the instance MetaDataConnectionAdapter.getSchema()
public String getSchema() {
return DatabaseMetaDataDialectHandlerFactory.findHandler(databaseType).map(handler -> handler.getSchema(connection)).orElse(getSchema(connection));
}
handle.getSchema(connection) like this(in this method OracleDatabaseMetaDataDialectHandler.getSchema())
@Override
public String getSchema(final Connection connection) {
try {
return Optional.ofNullable(connection.getMetaData().getUserName()).map(String::toUpperCase).orElse(null);
} catch (final SQLException ignored) {
return null;
}
}
The code try to get my username as the schema, undoubted it is impossible be null,so this place
handler.getSchema(connection)).orElse(getSchema(connection))
won't invoke orElse logic.But If invoke orElse i will get the correct schema
@PaleWhiteDot @chenxk I have tried to reproduce this problem, but failed. Can you provide steps to reproduce this exception?
I think the username could not be null, so the orElse logic could't be invoke always.
@PaleWhiteDot @chenxk I have tried to reproduce this problem, but failed. Can you provide steps to reproduce this exception?
I found where the question is? In this method:PhysicalIndexMetaDataLoader.load(final Connection connection, final String table), will invoke connection.getSchema() and then will invoke the instance MetaDataConnectionAdapter.getSchema()
public String getSchema() { return DatabaseMetaDataDialectHandlerFactory.findHandler(databaseType).map(handler -> handler.getSchema(connection)).orElse(getSchema(connection)); }
handle.getSchema(connection) like this(in this method OracleDatabaseMetaDataDialectHandler.getSchema())
@Override public String getSchema(final Connection connection) { try { return Optional.ofNullable(connection.getMetaData().getUserName()).map(String::toUpperCase).orElse(null); } catch (final SQLException ignored) { return null; } }
The code try to get my username as the schema, undoubted it is impossible be null,so this place
handler.getSchema(connection)).orElse(getSchema(connection))
won't invoke orElse logic.But If invoke orElse i will get the correct schema
I also solved it like this!
@PaleWhiteDot @chenxk I have tried to reproduce this problem, but failed. Can you provide steps to reproduce this exception?
I found where the question is? In this method:PhysicalIndexMetaDataLoader.load(final Connection connection, final String table), will invoke connection.getSchema() and then will invoke the instance MetaDataConnectionAdapter.getSchema()
public String getSchema() { return DatabaseMetaDataDialectHandlerFactory.findHandler(databaseType).map(handler -> handler.getSchema(connection)).orElse(getSchema(connection)); }
handle.getSchema(connection) like this(in this method OracleDatabaseMetaDataDialectHandler.getSchema())
@Override public String getSchema(final Connection connection) { try { return Optional.ofNullable(connection.getMetaData().getUserName()).map(String::toUpperCase).orElse(null); } catch (final SQLException ignored) { return null; } }
The code try to get my username as the schema, undoubted it is impossible be null,so this place
handler.getSchema(connection)).orElse(getSchema(connection))
won't invoke orElse logic.But If invoke orElse i will get the correct schema
I also solved it like this!
I'm not solved it, I just found where the question is.I don't know how to solve it.
i found that i can get the correct schema by orElse(getSchema(connection) because i set schema=msg
ds1: !!com.zaxxer.hikari.HikariDataSource
driverClassName: oracle.jdbc.driver.OracleDriver
jdbcUrl: jdbc:oracle:thin:@//******/message
username: MSG_CNT
schema: msgs
but orElse place never be invoked. What should i do to solved this problem?
i found that i can get the correct schema by orElse(getSchema(connection) because i set schema=msg
ds1: !!com.zaxxer.hikari.HikariDataSource driverClassName: oracle.jdbc.driver.OracleDriver jdbcUrl: jdbc:oracle:thin:@//******/message username: MSG_CONNECT schema: msg
but orElse place never be invoked. What should i do to solved this problem?
@PaleWhiteDot @chenxk I have tried to reproduce this problem, but failed. Can you provide steps to reproduce this exception?
I found where the question is? In this method:PhysicalIndexMetaDataLoader.load(final Connection connection, final String table), will invoke connection.getSchema() and then will invoke the instance MetaDataConnectionAdapter.getSchema()
public String getSchema() { return DatabaseMetaDataDialectHandlerFactory.findHandler(databaseType).map(handler -> handler.getSchema(connection)).orElse(getSchema(connection)); }
handle.getSchema(connection) like this(in this method OracleDatabaseMetaDataDialectHandler.getSchema())
@Override public String getSchema(final Connection connection) { try { return Optional.ofNullable(connection.getMetaData().getUserName()).map(String::toUpperCase).orElse(null); } catch (final SQLException ignored) { return null; } }
The code try to get my username as the schema, undoubted it is impossible be null,so this place
handler.getSchema(connection)).orElse(getSchema(connection))
won't invoke orElse logic.But If invoke orElse i will get the correct schema
I also solved it like this!
I'm not solved it, I just found where the question is.I don't know how to solve it.
Temporary solution。 ,My oracle userName and SCHEMA are the same , just a coincidence !
@chenxk @PaleWhiteDot Thank you very much for your detailed investigation. I checked the closed issue. This logic was added to solve other oracle metadata issues, refer to #6366.
I think we should adjust the priority of the schema. If the user configures the schema in the datasource, the value configured by the user should be taken first.
ds1: !!com.zaxxer.hikari.HikariDataSource
driverClassName: oracle.jdbc.driver.OracleDriver
jdbcUrl: jdbc:oracle:thin:@//******/message
username: MSG_CONNECT
schema: msg
@chenxk @PaleWhiteDot Thank you very much for your detailed investigation. I checked the closed issue. This logic was added to solve other oracle metadata issues, refer to #6366.
I think we should adjust the priority of the schema. If the user configures the schema in the datasource, the value configured by the user should be taken first.
ds1: !!com.zaxxer.hikari.HikariDataSource driverClassName: oracle.jdbc.driver.OracleDriver jdbcUrl: jdbc:oracle:thin:@//******/message username: MSG_CNT schema: msgs
Does that mean I cant't use shardingjdbc until you fix it.Is there another solution?
@PaleWhiteDot If you are interested, you can try to submit a PR to fix this problem. BTW, 5.0.0-alpha
still has some bugs to be solved, which is not suitable for production environment.
Has the problem been solved? I use sharding-jdbc-spring-boot-starter(4.1.1),have the same problem.
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: 表或视图不存在
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:108) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:887) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1158) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1093) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1402) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1285) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1687) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:394) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111) ~[HikariCP-3.2.0.jar:na]
at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java) ~[HikariCP-3.2.0.jar:na]
at org.apache.shardingsphere.sql.parser.binder.metadata.column.ColumnMetaDataLoader.load(ColumnMetaDataLoader.java:75) ~[shardingsphere-sql-parser-binder-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.sql.parser.binder.metadata.schema.SchemaMetaDataLoader.load(SchemaMetaDataLoader.java:84) ~[shardingsphere-sql-parser-binder-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.sql.parser.binder.metadata.schema.SchemaMetaDataLoader.load(SchemaMetaDataLoader.java:76) ~[shardingsphere-sql-parser-binder-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.core.metadata.ShardingMetaDataLoader.loadDefaultSchemaMetaData(ShardingMetaDataLoader.java:142) ~[sharding-core-common-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.core.metadata.ShardingMetaDataLoader.load(ShardingMetaDataLoader.java:126) ~[sharding-core-common-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.ShardingRuntimeContext.loadSchemaMetaData(ShardingRuntimeContext.java:65) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.MultipleDataSourcesRuntimeContext.createMetaData(MultipleDataSourcesRuntimeContext.java:57) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.MultipleDataSourcesRuntimeContext.<init>(MultipleDataSourcesRuntimeContext.java:51) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.ShardingRuntimeContext.<init>(ShardingRuntimeContext.java:49) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource.<init>(ShardingDataSource.java:54) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory.createDataSource(ShardingDataSourceFactory.java:48) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration.shardingDataSource(SpringBootConfiguration.java:103) ~[sharding-jdbc-spring-boot-starter-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$24832e93.CGLIB$shardingDataSource$1(<generated>) ~[sharding-jdbc-spring-boot-starter-4.1.1.jar:4.1.1]
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$24832e93$$FastClassBySpringCGLIB$$a75dc4f.invoke(<generated>) ~[sharding-jdbc-spring-boot-starter-4.1.1.jar:4.1.1]
at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:244) ~[spring-core-5.2.12.RELEASE.jar:5.2.12.RELEASE]
at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:331) ~[spring-context-5.2.12.RELEASE.jar:5.2.12.RELEASE]
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$24832e93.shardingDataSource(<generated>) ~[sharding-jdbc-spring-boot-starter-4.1.1.jar:4.1.1]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0-262]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0-262]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0-262]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0-262]
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154) ~[spring-beans-5.2.12.RELEASE.jar:5.2.12.RELEASE]
... 59 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00942: 表或视图不存在 at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0] ... 96 common frames omitted
finally,I hack the org.apache.shardingsphere.sql.parser.binder.metadata.util.JdbcUtil.java ,solve my problem. sharding-jdbc-spring-boot-starter(4.1.1)
public static String getSchema(Connection connection, String databaseType) { String result = null;
try {
if ("Oracle".equals(databaseType)) {
return null; ==============> here modify to my schema
}
result = connection.getSchema();
} catch (SQLException var4) {
}
return result;
}
mysql dialect has same question in version v4.1.1 when using shadow datasource and there are more than one tables in other database i find schema lost when debug. exception below:
Caused by: java.sql.SQLSyntaxErrorException: Unknown table '{other table in other database}' in information_schema
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1936)
at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
at com.mysql.cj.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:3182)
at com.mysql.cj.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:3170)
at com.mysql.cj.jdbc.IterateBlock.doForAll(IterateBlock.java:50)
at com.mysql.cj.jdbc.DatabaseMetaData.getPrimaryKeys(DatabaseMetaData.java:3223)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.metadata.MultipleDatabaseMetaData.getPrimaryKeys(MultipleDatabaseMetaData.java:142)
at org.apache.shardingsphere.sql.parser.binder.metadata.column.ColumnMetaDataLoader.loadPrimaryKeys(ColumnMetaDataLoader.java:115)
at org.apache.shardingsphere.sql.parser.binder.metadata.column.ColumnMetaDataLoader.load(ColumnMetaDataLoader.java:60)
at org.apache.shardingsphere.sql.parser.binder.metadata.schema.SchemaMetaDataLoader.load(SchemaMetaDataLoader.java:84)
at org.apache.shardingsphere.sql.parser.binder.metadata.schema.SchemaMetaDataLoader.load(SchemaMetaDataLoader.java:76)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.ShadowRuntimeContext.loadSchemaMetaData(ShadowRuntimeContext.java:69)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.SingleDataSourceRuntimeContext.createMetaData(SingleDataSourceRuntimeContext.java:56)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.SingleDataSourceRuntimeContext.<init>(SingleDataSourceRuntimeContext.java:50)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.ShadowRuntimeContext.<init>(ShadowRuntimeContext.java:48)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShadowDataSource.<init>(ShadowDataSource.java:46)
at org.apache.shardingsphere.shardingjdbc.api.ShadowDataSourceFactory.createDataSource(ShadowDataSourceFactory.java:66)
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration.shadowDataSource(SpringBootConfiguration.java:139)
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$4a3fbefa.CGLIB$shadowDataSource$4(<generated>)
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$4a3fbefa$$FastClassBySpringCGLIB$$210ceb83.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:244)
at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:363)
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$4a3fbefa.shadowDataSource(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154)
this step loadAllTableNames
load all table of all database
public static SchemaMetaData load(final DataSource dataSource, final int maxConnectionCount, final String databaseType) throws SQLException {
List<String> tableNames;
try (Connection connection = dataSource.getConnection()) {
tableNames = loadAllTableNames(connection, databaseType);
}
log.info("Loading {} tables' meta data.", tableNames.size());
if (0 == tableNames.size()) {
return new SchemaMetaData(Collections.emptyMap());
}
List<List<String>> tableGroups = Lists.partition(tableNames, Math.max(tableNames.size() / maxConnectionCount, 1));
Map<String, TableMetaData> tableMetaDataMap = 1 == tableGroups.size()
? load(dataSource.getConnection(), tableGroups.get(0), databaseType) : asyncLoad(dataSource, maxConnectionCount, tableNames, tableGroups, databaseType);
return new SchemaMetaData(tableMetaDataMap);
}
Hello , this issue has not received a reply for several days. This issue is supposed to be closed.
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
The version 4.1.1 is out of service, please try version 5.5.x
Version information
springBoot:2.1.4.RELEASE shardingSphere:5.0.0-alpha database: oracle
Which project did you use? Sharding-JDBC or Sharding-Proxy?
Sharding-JDBC
config
Description
my oracle username is MSG_CONNECT,oracle table is in schema MSG.i known shardingjdbc cannot surppot schema,so i set synonyms in oracle.But when i start up my application that i got the error 'table or views does not exist'.i review the code and found the follow code in class ShardingMetaDataLoader.load()
it according isCheckingMetaData to check PhysicalTableMetaData. i think it check PhysicalTableMetaData from default schema 'MSG_CONNECT',but my table is in schema 'MSG',so it throw a error 'table or views does not exist'. And then i add the setting 'check-table-metadata-enabled=true',it does not check PhysicalTableMetaData any more.but the next method parallelLoadTables() will invoke same method of PhysicalTableMetaDataLoader.load() method,but it is according to future.So at last i will get the same error 'table or views does not exist' or somethimes time out.
How can i deal with it? i'm trying to set hikari param schema=msg,connectionInitSql=alter session set current_schema=msg,but it doesn't work. If shardingjdbc don't check table exist when i start my application, i think i will run it because i set the synonyms in oracle.