linghengqian opened a new issue #15327: URL: https://github.com/apache/shardingsphere/issues/15327
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Which version of ShardingSphere did you use? 5.0.0 ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-JDBC ### Expected behavior This Issue is an addition to https://github.com/apache/shardingsphere/pull/14617. The expected result is a normal integration of ShardingSphere JDBC 5.0.0 with H2database 2.X. I provide a reproducible Example Source at the end . ### Actual behavior ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X. When the SpringBoot project started, ShardingSphere executed the wrong SQL.Log as follows ```shell 2022-02-09 22:14:47.635 INFO 3392 --- [ main] c.lingh.ShardingsphereH2TestApplication : No active profile set, falling back to default profiles: default 2022-02-09 22:14:48.543 INFO 3392 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'stringToNoneShardingStrategyConfigurationConverter' of type [org.apache.shardingsphere.spring.boot.converter.StringToNoneShardingStrategyConfigurationConverter] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying) 2022-02-09 22:14:48.559 INFO 3392 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere-org.apache.shardingsphere.spring.boot.prop.SpringBootPropertiesConfiguration' of type [org.apache.shardingsphere.spring.boot.prop.SpringBootPropertiesConfiguration] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying) 2022-02-09 22:14:48.685 INFO 3392 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration' of type [org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration$$EnhancerBySpringCGLIB$$a9c354b] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying) 2022-02-09 22:14:48.920 INFO 3392 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http) 2022-02-09 22:14:48.926 INFO 3392 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat] 2022-02-09 22:14:48.926 INFO 3392 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.56] 2022-02-09 22:14:49.014 INFO 3392 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext 2022-02-09 22:14:49.014 INFO 3392 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1206 ms 2022-02-09 22:14:49.376 INFO 3392 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2022-02-09 22:14:49.465 INFO 3392 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. 2022-02-09 22:14:49.480 INFO 3392 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Starting... 2022-02-09 22:14:49.480 INFO 3392 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Start completed. 2022-02-09 22:14:49.480 INFO 3392 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Starting... 2022-02-09 22:14:49.496 INFO 3392 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Start completed. 2022-02-09 22:14:49.574 ERROR 3392 --- [ main] .a.s.i.m.s.b.l.TableMetaDataLoaderEngine : Dialect load table meta data error org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "type_name" not found; SQL statement: SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND TABLE_NAME IN ('t_order0') ORDER BY ORDINAL_POSITION [42122-210] at org.h2.message.DbException.getJdbcSQLException(DbException.java:521) ~[h2-2.1.210.jar:2.1.210] at org.h2.message.DbException.getJdbcSQLException(DbException.java:496) ~[h2-2.1.210.jar:2.1.210] at org.h2.message.DbException.get(DbException.java:227) ~[h2-2.1.210.jar:2.1.210] at org.h2.message.DbException.get(DbException.java:203) ~[h2-2.1.210.jar:2.1.210] at org.h2.expression.ExpressionColumn.getColumnException(ExpressionColumn.java:248) ~[h2-2.1.210.jar:2.1.210] at org.h2.expression.ExpressionColumn.optimizeOther(ExpressionColumn.java:230) ~[h2-2.1.210.jar:2.1.210] at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:213) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.query.Select.optimizeExpressionsAndPreserveAliases(Select.java:1281) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.query.Select.prepare(Select.java:1174) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.Parser.prepareCommand(Parser.java:557) ~[h2-2.1.210.jar:2.1.210] at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:615) ~[h2-2.1.210.jar:2.1.210] at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:553) ~[h2-2.1.210.jar:2.1.210] at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116) ~[h2-2.1.210.jar:2.1.210] at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:92) ~[h2-2.1.210.jar:2.1.210] at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288) ~[h2-2.1.210.jar:2.1.210] at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337) ~[HikariCP-4.0.3.jar:na] at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-4.0.3.jar:na] at org.apache.shardingsphere.infra.metadata.schema.builder.loader.dialect.H2TableMetaDataLoader.loadColumnMetaDataMap(H2TableMetaDataLoader.java:81) ~[shardingsphere-infra-common-5.0.0.jar:5.0.0] at org.apache.shardingsphere.infra.metadata.schema.builder.loader.dialect.H2TableMetaDataLoader.load(H2TableMetaDataLoader.java:70) ~[shardingsphere-infra-common-5.0.0.jar:5.0.0] at org.apache.shardingsphere.infra.metadata.schema.builder.loader.TableMetaDataLoaderEngine.lambda$loadByDialect$0(TableMetaDataLoaderEngine.java:89) ~[shardingsphere-infra-common-5.0.0.jar:5.0.0] at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[na:1.8.0_312] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_312] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_312] at java.lang.Thread.run(Thread.java:853) ~[na:1.8.0_312] 2022-02-09 22:14:49.606 ERROR 3392 --- [ main] .a.s.i.m.s.b.l.TableMetaDataLoaderEngine : Dialect load table meta data error org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "type_name" not found; SQL statement: SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND TABLE_NAME IN ('user') ORDER BY ORDINAL_POSITION [42122-210] at org.h2.message.DbException.getJdbcSQLException(DbException.java:521) ~[h2-2.1.210.jar:2.1.210] at org.h2.message.DbException.getJdbcSQLException(DbException.java:496) ~[h2-2.1.210.jar:2.1.210] at org.h2.message.DbException.get(DbException.java:227) ~[h2-2.1.210.jar:2.1.210] at org.h2.message.DbException.get(DbException.java:203) ~[h2-2.1.210.jar:2.1.210] at org.h2.expression.ExpressionColumn.getColumnException(ExpressionColumn.java:248) ~[h2-2.1.210.jar:2.1.210] at org.h2.expression.ExpressionColumn.optimizeOther(ExpressionColumn.java:230) ~[h2-2.1.210.jar:2.1.210] at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:213) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.query.Select.optimizeExpressionsAndPreserveAliases(Select.java:1281) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.query.Select.prepare(Select.java:1174) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.Parser.prepareCommand(Parser.java:557) ~[h2-2.1.210.jar:2.1.210] at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:615) ~[h2-2.1.210.jar:2.1.210] at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:553) ~[h2-2.1.210.jar:2.1.210] at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116) ~[h2-2.1.210.jar:2.1.210] at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:92) ~[h2-2.1.210.jar:2.1.210] at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288) ~[h2-2.1.210.jar:2.1.210] at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337) ~[HikariCP-4.0.3.jar:na] at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-4.0.3.jar:na] at org.apache.shardingsphere.infra.metadata.schema.builder.loader.dialect.H2TableMetaDataLoader.loadColumnMetaDataMap(H2TableMetaDataLoader.java:81) ~[shardingsphere-infra-common-5.0.0.jar:5.0.0] at org.apache.shardingsphere.infra.metadata.schema.builder.loader.dialect.H2TableMetaDataLoader.load(H2TableMetaDataLoader.java:70) ~[shardingsphere-infra-common-5.0.0.jar:5.0.0] at org.apache.shardingsphere.infra.metadata.schema.builder.loader.TableMetaDataLoaderEngine.lambda$loadByDialect$0(TableMetaDataLoaderEngine.java:89) ~[shardingsphere-infra-common-5.0.0.jar:5.0.0] at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[na:1.8.0_312] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_312] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_312] at java.lang.Thread.run(Thread.java:853) ~[na:1.8.0_312] 2022-02-09 22:14:50.649 INFO 3392 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path '' 2022-02-09 22:14:50.655 INFO 3392 --- [ main] c.lingh.ShardingsphereH2TestApplication : Started ShardingsphereH2TestApplication in 3.339 seconds (JVM running for 4.071) ``` ### Reason analyze (If you can) H2 Database 2.X changes the structure of the table `INFORMATION_SCHEMA`, and it behaves normally on H2 Database 1.X. Also give you relevant information. @jeremie1112 ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. The executed SQL is`SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND TABLE_NAME IN ('user') ORDER BY ORDINAL_POSITION` ### Example codes for reproduce this issue (such as a github link). I provide a minimal reproduction sample. Just execute the startup class directly. https://github.com/linghengqian/shardingsphere-h2-test -- 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]
