[关闭]
@survivorZzz 2019-01-15T12:56:31.000000Z 字数 13150 阅读 1672

When starting spring boot application with Sharding Sphere throws 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 :

  1. 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: 转义符之后字符缺失或非法
  2. at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189) ~[spring-beans-4.3.14.RELEASE.jar:4.3.14.RELEASE]
  3. at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:588) ~[spring-beans-4.3.14.RELEASE.jar:4.3.14.RELEASE]
  4. ... 69 common frames omitted
  5. Caused by: io.shardingsphere.core.exception.ShardingException: java.sql.SQLDataException: ORA-01424: 转义符之后字符缺失或非法
  6. at io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer.load(TableMetaDataInitializer.java:65) ~[sharding-core-3.0.0.jar:na]
  7. at io.shardingsphere.core.metadata.ShardingMetaData.<init>(ShardingMetaData.java:46) ~[sharding-core-3.0.0.jar:na]
  8. at io.shardingsphere.shardingjdbc.jdbc.core.ShardingContext.<init>(ShardingContext.java:63) ~[sharding-jdbc-core-3.0.0.jar:na]
  9. at io.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource.getShardingContext(ShardingDataSource.java:85) ~[sharding-jdbc-core-3.0.0.jar:na]
  10. at io.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource.<init>(ShardingDataSource.java:65) ~[sharding-jdbc-core-3.0.0.jar:na]
  11. at io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory.createDataSource(ShardingDataSourceFactory.java:51) ~[sharding-jdbc-core-3.0.0.jar:na]
  12. at io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration.dataSource(SpringBootConfiguration.java:66) ~[sharding-jdbc-spring-boot-starter-3.0.0.jar:na]
  13. at io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$ab946d0d.CGLIB$dataSource$0(<generated>) ~[sharding-jdbc-spring-boot-starter-3.0.0.jar:na]
  14. at io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$ab946d0d$$FastClassBySpringCGLIB$$ce9cec15.invoke(<generated>) ~[sharding-jdbc-spring-boot-starter-3.0.0.jar:na]
  15. at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:228) ~[spring-core-4.3.14.RELEASE.jar:4.3.14.RELEASE]
  16. at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:358) ~[spring-context-4.3.14.RELEASE.jar:4.3.14.RELEASE]
  17. at io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$ab946d0d.dataSource(<generated>) ~[sharding-jdbc-spring-boot-starter-3.0.0.jar:na]
  18. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
  19. at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
  20. at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
  21. at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
  22. at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:162) ~[spring-beans-4.3.14.RELEASE.jar:4.3.14.RELEASE]
  23. ... 70 common frames omitted
  24. Caused by: java.sql.SQLDataException: ORA-01424: 转义符之后字符缺失或非法
  25. at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) ~[ojdbc6_myself.jar:11.2.0.4.0]
  26. at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6_myself.jar:11.2.0.4.0]
  27. at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) ~[ojdbc6_myself.jar:11.2.0.4.0]
  28. at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) ~[ojdbc6_myself.jar:11.2.0.4.0]
  29. at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) ~[ojdbc6_myself.jar:11.2.0.4.0]
  30. at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[ojdbc6_myself.jar:11.2.0.4.0]
  31. at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208) ~[ojdbc6_myself.jar:11.2.0.4.0]
  32. at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886) ~[ojdbc6_myself.jar:11.2.0.4.0]
  33. at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175) ~[ojdbc6_myself.jar:11.2.0.4.0]
  34. at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296) ~[ojdbc6_myself.jar:11.2.0.4.0]
  35. at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613) ~[ojdbc6_myself.jar:11.2.0.4.0]
  36. at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657) ~[ojdbc6_myself.jar:11.2.0.4.0]
  37. at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495) ~[ojdbc6_myself.jar:11.2.0.4.0]
  38. at oracle.jdbc.OracleDatabaseMetaData.getTables(OracleDatabaseMetaData.java:3078) ~[ojdbc6_myself.jar:11.2.0.4.0]
  39. at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader.isTableExist(TableMetaDataLoader.java:116) ~[sharding-core-3.0.0.jar:na]
  40. at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader.load(TableMetaDataLoader.java:93) ~[sharding-core-3.0.0.jar:na]
  41. at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader.access$100(TableMetaDataLoader.java:51) ~[sharding-core-3.0.0.jar:na]
  42. at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader$1.execute(TableMetaDataLoader.java:83) ~[sharding-core-3.0.0.jar:na]
  43. at io.shardingsphere.core.executor.ShardingExecuteEngine.syncGroupExecute(ShardingExecuteEngine.java:182) ~[sharding-core-3.0.0.jar:na]
  44. at io.shardingsphere.core.executor.ShardingExecuteEngine.groupExecute(ShardingExecuteEngine.java:158) ~[sharding-core-3.0.0.jar:na]
  45. at io.shardingsphere.core.executor.ShardingExecuteEngine.groupExecute(ShardingExecuteEngine.java:136) ~[sharding-core-3.0.0.jar:na]
  46. at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader.load(TableMetaDataLoader.java:76) ~[sharding-core-3.0.0.jar:na]
  47. at io.shardingsphere.core.metadata.table.executor.TableMetaDataLoader.load(TableMetaDataLoader.java:70) ~[sharding-core-3.0.0.jar:na]
  48. at io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer.loadDefaultTables(TableMetaDataInitializer.java:83) ~[sharding-core-3.0.0.jar:na]
  49. at io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer.load(TableMetaDataInitializer.java:63) ~[sharding-core-3.0.0.jar:na]
  50. ... 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:

  1. public synchronized ResultSet getTables(String var1, String var2, String var3, String[] var4) throws SQLException {
  2. 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";
  3. String var6 = " c.comments AS remarks\n";
  4. String var7 = " NULL AS remarks\n";
  5. String var8 = " FROM all_objects o, all_tab_comments c\n";
  6. String var9 = " FROM all_objects o\n";
  7. String var10 = " WHERE o.owner LIKE :1 ESCAPE '/'\n AND o.object_name LIKE :2 ESCAPE '/'\n";
  8. String var11 = " AND o.owner = c.owner (+)\n AND o.object_name = c.table_name (+)\n";
  9. boolean var12 = false;
  10. String var13 = "";
  11. String var14 = "";
  12. if (var4 != null) {
  13. var13 = " AND o.object_type IN ('xxx'";
  14. var14 = " AND o.object_type IN ('xxx'";
  15. for(int var15 = 0; var15 < var4.length; ++var15) {
  16. if (var4[var15].equals("SYNONYM")) {
  17. var13 = var13 + ", '" + var4[var15] + "'";
  18. var12 = true;
  19. } else {
  20. var13 = var13 + ", '" + var4[var15] + "'";
  21. var14 = var14 + ", '" + var4[var15] + "'";
  22. }
  23. }
  24. var13 = var13 + ")\n";
  25. var14 = var14 + ")\n";
  26. } else {
  27. var12 = true;
  28. var13 = " AND o.object_type IN ('TABLE', 'SYNONYM', 'VIEW')\n";
  29. var14 = " AND o.object_type IN ('TABLE', 'VIEW')\n";
  30. }
  31. String var25 = " ORDER BY table_type, table_schem, table_name\n";
  32. 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";
  33. String var17 = " c.comments AS remarks\n";
  34. String var18 = " NULL AS remarks\n";
  35. String var19 = " FROM all_synonyms s, all_objects o, all_tab_comments c\n";
  36. String var20 = " FROM all_synonyms s, all_objects o\n";
  37. 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";
  38. String var22 = "";
  39. var22 = var22 + var5;
  40. if (this.connection.getRemarksReporting()) {
  41. var22 = var22 + var6 + var8;
  42. } else {
  43. var22 = var22 + var7 + var9;
  44. }
  45. var22 = var22 + var10;
  46. if (this.connection.getRestrictGetTables()) {
  47. var22 = var22 + var14;
  48. } else {
  49. var22 = var22 + var13;
  50. }
  51. if (this.connection.getRemarksReporting()) {
  52. var22 = var22 + var11;
  53. }
  54. if (var12 && this.connection.getRestrictGetTables()) {
  55. var22 = var22 + "UNION\n" + var16;
  56. if (this.connection.getRemarksReporting()) {
  57. var22 = var22 + var17 + var19;
  58. } else {
  59. var22 = var22 + var18 + var20;
  60. }
  61. var22 = var22 + var21;
  62. if (this.connection.getRemarksReporting()) {
  63. var22 = var22 + var11;
  64. }
  65. }
  66. var22 = var22 + var25;
  67. PreparedStatement var23 = this.connection.prepareStatement(var22);
  68. var23.setString(1, var2 == null ? "%" : var2);
  69. var23.setString(2, var3 == null ? "%" : var3);
  70. if (var12 && this.connection.getRestrictGetTables()) {
  71. var23.setString(3, var2 == null ? "%" : var2);
  72. var23.setString(4, var3 == null ? "%" : var3);
  73. }
  74. OracleResultSet var24 = (OracleResultSet)var23.executeQuery();
  75. var24.closeStatementOnClose();
  76. return var24;
  77. }

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 :

  1. /**
  2. * Load all table meta data.
  3. *
  4. * @param shardingRule sharding rule
  5. * @return all table meta data
  6. */
  7. public Map<String, TableMetaData> load(final ShardingRule shardingRule) {
  8. Map<String, TableMetaData> result = new HashMap<>();
  9. try {
  10. result.putAll(loadShardingTables(shardingRule));
  11. result.putAll(loadDefaultTables(shardingRule));
  12. } catch (final SQLException ex) {
  13. throw new ShardingException(ex);
  14. }
  15. return result;
  16. }

pay attention to the 11 line, function io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer#loadDefaultTables be executed:

  1. private Map<String, TableMetaData> loadDefaultTables(final ShardingRule shardingRule) throws SQLException {
  2. Map<String, TableMetaData> result = new HashMap<>(shardingRule.getTableRules().size(), 1);
  3. Optional<String> actualDefaultDataSourceName = shardingRule.findActualDefaultDataSourceName();
  4. if (actualDefaultDataSourceName.isPresent()) {
  5. for (String each : getAllTableNames(actualDefaultDataSourceName.get())) {
  6. result.put(each, tableMetaDataLoader.load(each, shardingRule));
  7. }
  8. }
  9. return result;
  10. }

after that, this function io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer#getAllTableNames invoked:

  1. private Collection<String> getAllTableNames(final String dataSourceName) throws SQLException {
  2. Collection<String> result = new LinkedHashSet<>();
  3. try (Connection connection = connectionManager.getConnection(dataSourceName);
  4. ResultSet resultSet = connection.getMetaData().getTables(dataSourceName, null, null, new String[]{"TABLE"})) {
  5. while (resultSet.next()) {
  6. String tableName = resultSet.getString("TABLE_NAME");
  7. if (!tableName.contains("$")) {
  8. result.add(tableName);
  9. }
  10. }
  11. }
  12. return result;
  13. }

i set a breakpoint at the 12 line, and the result variable contains values like this:
debug1.png-158.2kB

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 :

  1. if (!tableName.contains("$") && !tableName.contains("/")) {
  2. result.add(tableName);
  3. }

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:

  1. sharding:
  2. jdbc:
  3. datasource:
  4. names: ds0,ds1
  5. #数据源1
  6. ds0:
  7. type: com.zaxxer.hikari.HikariDataSource
  8. driver-class-name: oracle.jdbc.OracleDriver
  9. jdbc-url: xxx
  10. username: xxx
  11. password: xxx
  12. validation-query: SELECT 1 from dual
  13. #数据源2
  14. ds1:
  15. type: com.zaxxer.hikari.HikariDataSource
  16. driver-class-name: oracle.jdbc.OracleDriver
  17. jdbc-url: xxx
  18. username: xxx
  19. password: xxx
  20. validation-query: SELECT 1 from dual
  21. config:
  22. sharding:
  23. tables:
  24. BILL_IM_IMPORT:
  25. database-strategy:
  26. standard:
  27. sharding-column: LOCNO
  28. precise-algorithm-class-name: com.xxx.xxx.config.sharding.algorithm.DataSourcePreciseShardingAlgorithm
  29. broadcast-tables: LOOKUPDTL,LOCNO_URL_REF
  30. config-map:
  31. I: ds0
  32. E: ds1
  33. props:
  34. sql.show: true

And the oracle version is:
debug2.png-4.3kB

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:

  1. <!-- sharding-jdbc start -->
  2. <dependency>
  3. <groupId>io.shardingsphere</groupId>
  4. <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  5. <version>3.0.0</version>
  6. </dependency>
  7. <dependency>
  8. <groupId>io.shardingsphere</groupId>
  9. <artifactId>sharding-jdbc-spring-namespace</artifactId>
  10. <version>3.0.0</version>
  11. </dependency>
  12. <!-- sharding-jdbc end-->

Looking forward to your reply! Thank you !

添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注