You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/02/09 14:25:27 UTC
[GitHub] [shardingsphere] linghengqian opened a new issue #15327: ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] linghengqian commented on issue #15327: ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X
Posted by GitBox <gi...@apache.org>.
linghengqian commented on issue #15327:
URL: https://github.com/apache/shardingsphere/issues/15327#issuecomment-1065802085
Note that this seems to be my misunderstanding. Referring to https://github.com/h2database/h2database/issues/2180, I submitted a PR to verify. This is because an old JDBC driver is used to access H2Database. Wrong. All I'm not sure about what to do next, importing two versions of H2's JDBC driver in the POM? It's a bit beyond imagination.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuichenchuxin commented on issue #15327: ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #15327:
URL: https://github.com/apache/shardingsphere/issues/15327#issuecomment-1034445953
This sql is to load tables and columns from database.
But the sql can't be used in H2database 2.X.
Maybe we need to use different sql according to different versions.
Btw, it can work normally. because when the dialect load has error, then we'll use default load.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] linghengqian edited a comment on issue #15327: ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X
Posted by GitBox <gi...@apache.org>.
linghengqian edited a comment on issue #15327:
URL: https://github.com/apache/shardingsphere/issues/15327#issuecomment-1065802085
Note that this seems to be my misunderstanding. Referring to https://github.com/h2database/h2database/issues/2180, I submitted a PR to verify. This is because an old JDBC driver is used to access H2Database wrong. All I'm not sure about what to do next, importing two versions of H2's JDBC driver in the POM? It's a bit beyond imagination.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] linghengqian removed a comment on issue #15327: ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X
Posted by GitBox <gi...@apache.org>.
linghengqian removed a comment on issue #15327:
URL: https://github.com/apache/shardingsphere/issues/15327#issuecomment-1065802085
Note that this seems to be my misunderstanding. Referring to https://github.com/h2database/h2database/issues/2180, I submitted a PR to verify. This is because an old JDBC driver is used to access H2Database wrong. All I'm not sure about what to do next, importing two versions of H2's JDBC driver in the POM? It's a bit beyond imagination.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] linghengqian commented on issue #15327: ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X
Posted by GitBox <gi...@apache.org>.
linghengqian commented on issue #15327:
URL: https://github.com/apache/shardingsphere/issues/15327#issuecomment-1056896371
> This sql is to load tables and columns from database.
> But the sql can't be used in H2database 2.X.
> Maybe we need to use different sql according to different versions.
> Btw, it can work normally. because when the dialect load has error, then we'll use default load.
>
This leads to a topic I don't understand. If I need to fetch a class from H2Database in JUnit, how do I get the version, artifactId, groupId of the corresponding component of the class? Does this require a specific Maven plugin?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuichenchuxin commented on issue #15327: ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #15327:
URL: https://github.com/apache/shardingsphere/issues/15327#issuecomment-1058729527
I think we can use mock() currently, and test it yourself.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org