@survivorZzz
2019-01-15T12:56:31.000000Z
字数 13150
阅读 1672
java.sql.SQLDataException: ORA-01424: 转义符之后字符缺失或非法Hi there:
firstly, thank you guys did great jobs on sharding sphere project, that's amazing!
while i am using sharding jdbc on my company's project and starting the Springboot Application starter Class , the console got a exception looks like this :
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'dataSource' threw exception; nested exception is io.shardingsphere.core.exception.ShardingException: java.sql.SQLDataException: ORA-01424: 转义符之后字符缺失或非法at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189) ~[spring-beans-4.3.14.RELEASE.jar:4.3.14.RELEASE]at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:588) ~[spring-beans-4.3.14.RELEASE.jar:4.3.14.RELEASE]... 69 common frames omittedCaused by: io.shardingsphere.core.exception.ShardingException: java.sql.SQLDataException: ORA-01424: 转义符之后字符缺失或非法at io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer.load(TableMetaDataInitializer.java:65) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.core.metadata.ShardingMetaData.<init>(ShardingMetaData.java:46) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.shardingjdbc.jdbc.core.ShardingContext.<init>(ShardingContext.java:63) ~[sharding-jdbc-core-3.0.0.jar:na]at io.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource.getShardingContext(ShardingDataSource.java:85) ~[sharding-jdbc-core-3.0.0.jar:na]at io.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource.<init>(ShardingDataSource.java:65) ~[sharding-jdbc-core-3.0.0.jar:na]at io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory.createDataSource(ShardingDataSourceFactory.java:51) ~[sharding-jdbc-core-3.0.0.jar:na]at io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration.dataSource(SpringBootConfiguration.java:66) ~[sharding-jdbc-spring-boot-starter-3.0.0.jar:na]at io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$ab946d0d.CGLIB$dataSource$0(<generated>) ~[sharding-jdbc-spring-boot-starter-3.0.0.jar:na]at io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$ab946d0d$$FastClassBySpringCGLIB$$ce9cec15.invoke(<generated>) ~[sharding-jdbc-spring-boot-starter-3.0.0.jar:na]at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:228) ~[spring-core-4.3.14.RELEASE.jar:4.3.14.RELEASE]at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:358) ~[spring-context-4.3.14.RELEASE.jar:4.3.14.RELEASE]at io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$ab946d0d.dataSource(<generated>) ~[sharding-jdbc-spring-boot-starter-3.0.0.jar:na]at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:162) ~[spring-beans-4.3.14.RELEASE.jar:4.3.14.RELEASE]... 70 common frames omittedCaused by: java.sql.SQLDataException: ORA-01424: 转义符之后字符缺失或非法at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495) ~[ojdbc6_myself.jar:11.2.0.4.0]at oracle.jdbc.OracleDatabaseMetaData.getTables(OracleDatabaseMetaData.java:3078) ~[ojdbc6_myself.jar:11.2.0.4.0]at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader.isTableExist(TableMetaDataLoader.java:116) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader.load(TableMetaDataLoader.java:93) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader.access$100(TableMetaDataLoader.java:51) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader$1.execute(TableMetaDataLoader.java:83) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.core.executor.ShardingExecuteEngine.syncGroupExecute(ShardingExecuteEngine.java:182) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.core.executor.ShardingExecuteEngine.groupExecute(ShardingExecuteEngine.java:158) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.core.executor.ShardingExecuteEngine.groupExecute(ShardingExecuteEngine.java:136) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader.load(TableMetaDataLoader.java:76) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader.load(TableMetaDataLoader.java:70) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer.loadDefaultTables(TableMetaDataInitializer.java:83) ~[sharding-core-3.0.0.jar:na]at io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer.load(TableMetaDataInitializer.java:63) ~[sharding-core-3.0.0.jar:na]... 86 common frames omitted
with the console exception stack trace, i had debugged and tracked how this could hanpend, finnaly, i found this function oracle.jdbc.OracleDatabaseMetaData#getTables of the ojdbc6.jar throwed this exception. This function looks like this:
public synchronized ResultSet getTables(String var1, String var2, String var3, String[] var4) throws SQLException {String var5 = "SELECT NULL AS table_cat,\n o.owner AS table_schem,\n o.object_name AS table_name,\n o.object_type AS table_type,\n";String var6 = " c.comments AS remarks\n";String var7 = " NULL AS remarks\n";String var8 = " FROM all_objects o, all_tab_comments c\n";String var9 = " FROM all_objects o\n";String var10 = " WHERE o.owner LIKE :1 ESCAPE '/'\n AND o.object_name LIKE :2 ESCAPE '/'\n";String var11 = " AND o.owner = c.owner (+)\n AND o.object_name = c.table_name (+)\n";boolean var12 = false;String var13 = "";String var14 = "";if (var4 != null) {var13 = " AND o.object_type IN ('xxx'";var14 = " AND o.object_type IN ('xxx'";for(int var15 = 0; var15 < var4.length; ++var15) {if (var4[var15].equals("SYNONYM")) {var13 = var13 + ", '" + var4[var15] + "'";var12 = true;} else {var13 = var13 + ", '" + var4[var15] + "'";var14 = var14 + ", '" + var4[var15] + "'";}}var13 = var13 + ")\n";var14 = var14 + ")\n";} else {var12 = true;var13 = " AND o.object_type IN ('TABLE', 'SYNONYM', 'VIEW')\n";var14 = " AND o.object_type IN ('TABLE', 'VIEW')\n";}String var25 = " ORDER BY table_type, table_schem, table_name\n";String var16 = "SELECT NULL AS table_cat,\n s.owner AS table_schem,\n s.synonym_name AS table_name,\n 'SYNONYM' AS table_table_type,\n";String var17 = " c.comments AS remarks\n";String var18 = " NULL AS remarks\n";String var19 = " FROM all_synonyms s, all_objects o, all_tab_comments c\n";String var20 = " FROM all_synonyms s, all_objects o\n";String var21 = " WHERE s.owner LIKE :3 ESCAPE '/'\n AND s.synonym_name LIKE :4 ESCAPE '/'\n AND s.table_owner = o.owner\n AND s.table_name = o.object_name\n AND o.object_type IN ('TABLE', 'VIEW')\n";String var22 = "";var22 = var22 + var5;if (this.connection.getRemarksReporting()) {var22 = var22 + var6 + var8;} else {var22 = var22 + var7 + var9;}var22 = var22 + var10;if (this.connection.getRestrictGetTables()) {var22 = var22 + var14;} else {var22 = var22 + var13;}if (this.connection.getRemarksReporting()) {var22 = var22 + var11;}if (var12 && this.connection.getRestrictGetTables()) {var22 = var22 + "UNION\n" + var16;if (this.connection.getRemarksReporting()) {var22 = var22 + var17 + var19;} else {var22 = var22 + var18 + var20;}var22 = var22 + var21;if (this.connection.getRemarksReporting()) {var22 = var22 + var11;}}var22 = var22 + var25;PreparedStatement var23 = this.connection.prepareStatement(var22);var23.setString(1, var2 == null ? "%" : var2);var23.setString(2, var3 == null ? "%" : var3);if (var12 && this.connection.getRestrictGetTables()) {var23.setString(3, var2 == null ? "%" : var2);var23.setString(4, var3 == null ? "%" : var3);}OracleResultSet var24 = (OracleResultSet)var23.executeQuery();var24.closeStatementOnClose();return var24;}
As you can see, two Sql statments will be executed, both them contains Where segments with the Oracle keyword ESCAPE with the char / behind, so that When the third paramter of this function var3 contains the char '/' but without _ or % behind it, the java.sql.SQLDataException: ORA-01424: 转义符之后字符缺失或非法 will be throwed.
check this out, when my spring boot application with sharding JDBC bootstaps, this function io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer#getAllTableNames will invoke the function of Oracle driver told above, then Exception throwed. I debuged this function, and found that, when my application bootstraps, function io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer#loadexecuted :
/*** Load all table meta data.** @param shardingRule sharding rule* @return all table meta data*/public Map<String, TableMetaData> load(final ShardingRule shardingRule) {Map<String, TableMetaData> result = new HashMap<>();try {result.putAll(loadShardingTables(shardingRule));result.putAll(loadDefaultTables(shardingRule));} catch (final SQLException ex) {throw new ShardingException(ex);}return result;}
pay attention to the 11 line, function io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer#loadDefaultTables be executed:
private Map<String, TableMetaData> loadDefaultTables(final ShardingRule shardingRule) throws SQLException {Map<String, TableMetaData> result = new HashMap<>(shardingRule.getTableRules().size(), 1);Optional<String> actualDefaultDataSourceName = shardingRule.findActualDefaultDataSourceName();if (actualDefaultDataSourceName.isPresent()) {for (String each : getAllTableNames(actualDefaultDataSourceName.get())) {result.put(each, tableMetaDataLoader.load(each, shardingRule));}}return result;}
after that, this function io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer#getAllTableNames invoked:
private Collection<String> getAllTableNames(final String dataSourceName) throws SQLException {Collection<String> result = new LinkedHashSet<>();try (Connection connection = connectionManager.getConnection(dataSourceName);ResultSet resultSet = connection.getMetaData().getTables(dataSourceName, null, null, new String[]{"TABLE"})) {while (resultSet.next()) {String tableName = resultSet.getString("TABLE_NAME");if (!tableName.contains("$")) {result.add(tableName);}}}return result;}
i set a breakpoint at the 12 line, and the result variable contains values like this:

As you can see, the default datasource has some tables named SYS_xxxxxxx and the xxxxxx contains the char / which does'n followed by char _ or char %.
So, i am sure about that my oracle database has some tables named SYS_xxx/xxx, and thoes tables created by oracle itself. So, i think with the function i showed above , the if condition expression should be :
if (!tableName.contains("$") && !tableName.contains("/")) {result.add(tableName);}
Or maybe, my opinion is wrong ? If the answer is "yes!", could you guys please tell me how to solve this problem, thank you!
this is the sharding config snippet from my spring boot .yml config file:
sharding:jdbc:datasource:names: ds0,ds1#数据源1ds0:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: oracle.jdbc.OracleDriverjdbc-url: xxxusername: xxxpassword: xxxvalidation-query: SELECT 1 from dual#数据源2ds1:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: oracle.jdbc.OracleDriverjdbc-url: xxxusername: xxxpassword: xxxvalidation-query: SELECT 1 from dualconfig:sharding:tables:BILL_IM_IMPORT:database-strategy:standard:sharding-column: LOCNOprecise-algorithm-class-name: com.xxx.xxx.config.sharding.algorithm.DataSourcePreciseShardingAlgorithmbroadcast-tables: LOOKUPDTL,LOCNO_URL_REFconfig-map:I: ds0E: ds1props:sql.show: true
And the oracle version is:

And the oracle jdbc driver is "ojdbc6.jar" with version (Oracle Database 11g with the 2 version (11.2.0.4) JDBC Driver , i downloaded it from this link).
And in my project i added Sharding Sphere maven dependent like this:
<!-- sharding-jdbc start --><dependency><groupId>io.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>3.0.0</version></dependency><dependency><groupId>io.shardingsphere</groupId><artifactId>sharding-jdbc-spring-namespace</artifactId><version>3.0.0</version></dependency><!-- sharding-jdbc end-->
Looking forward to your reply! Thank you !