ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X #15327

linghengqian · 2022-02-09T14:25:12Z

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

Searched open and closed GitHub issues.
Read documentation: ShardingSphere Doc.

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.1.0

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

This Issue is an addition to #14617. The expected result is a normal integration of ShardingSphere JDBC 5.1.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

2022-02-17 19:03:45.678  INFO 14512 --- [           main] c.lingh.ShardingsphereH2TestApplication  : No active profile set, falling back to default profiles: default
2022-02-17 19:03:46.617  INFO 14512 --- [           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-17 19:03:46.633  INFO 14512 --- [           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-17 19:03:46.945  INFO 14512 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration' of type [org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration$$EnhancerBySpringCGLIB$$fe587a8a] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-02-17 19:03:47.133  INFO 14512 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2022-02-17 19:03:47.149  INFO 14512 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2022-02-17 19:03:47.149  INFO 14512 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.56]
2022-02-17 19:03:47.258  INFO 14512 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2022-02-17 19:03:47.258  INFO 14512 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1377 ms
2022-02-17 19:03:47.774  INFO 14512 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2022-02-17 19:03:47.961  INFO 14512 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2022-02-17 19:03:47.961  INFO 14512 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Starting...
2022-02-17 19:03:47.977  INFO 14512 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Start completed.
2022-02-17 19:03:47.977  INFO 14512 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-3 - Starting...
2022-02-17 19:03:47.977  INFO 14512 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-3 - Start completed.
2022-02-17 19:03:48.040 ERROR 14512 --- [           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.1.0.jar:5.1.0]
	at org.apache.shardingsphere.infra.metadata.schema.builder.loader.dialect.H2TableMetaDataLoader.load(H2TableMetaDataLoader.java:70) ~[shardingsphere-infra-common-5.1.0.jar:5.1.0]
	at org.apache.shardingsphere.infra.metadata.schema.builder.loader.TableMetaDataLoaderEngine.lambda$loadByDialect$0(TableMetaDataLoaderEngine.java:88) ~[shardingsphere-infra-common-5.1.0.jar:5.1.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-17 19:03:48.055 ERROR 14512 --- [           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.1.0.jar:5.1.0]
	at org.apache.shardingsphere.infra.metadata.schema.builder.loader.dialect.H2TableMetaDataLoader.load(H2TableMetaDataLoader.java:70) ~[shardingsphere-infra-common-5.1.0.jar:5.1.0]
	at org.apache.shardingsphere.infra.metadata.schema.builder.loader.TableMetaDataLoaderEngine.lambda$loadByDialect$0(TableMetaDataLoaderEngine.java:88) ~[shardingsphere-infra-common-5.1.0.jar:5.1.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-17 19:03:49.165  INFO 14512 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2022-02-17 19:03:49.165  INFO 14512 --- [           main] c.lingh.ShardingsphereH2TestApplication  : Started ShardingsphereH2TestApplication in 3.892 seconds (JVM running for 4.686)

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

I honestly don't know why the select statement is used instead of partly using the DatabaseMetaData class to handle metadata needed in ShardingSphere JDBC. Related to #11994 ?

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

The executed SQL isSELECT 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

The text was updated successfully, but these errors were encountered:

tuichenchuxin · 2022-02-10T03:08:10Z

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.

linghengqian · 2022-03-02T12:52:03Z

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?

tuichenchuxin · 2022-03-04T01:17:14Z

I think we can use mock() currently, and test it yourself.

linghengqian · 2022-07-12T06:04:32Z

In my personal testing case, using the 1.x H2database client, which is the client used by the current ShardingSphere master branch, to access the server side of 2.x's H2DataBase is not allowed, and vice versa seems to have a similar problem.
update in 2022.07.15: Considering that ShardingSphere's master branch is using JOOQ 3.4, it does not support H2DataBase 2.x. So I tend to solve this problem softly, that is, only change the SQL that corresponds to the execution, without upgrading H2DataBase to 2.x.
update in 2022.07.20: Some important classes are changed in the master branch, refer to Merge memory and standalone mode #19001 . I'll try something else after the release of Apache ShardingSphere 5.1.3.
update in 2022.08.02: Wait for Redesign JDBCRepository and H2Repository #19655 to be resolved. Then I can see if I need to write an extra class for H2DataBase 2.x.

huangxinjian · 2022-08-02T09:46:39Z

You are very patient and I admire your ability to keep track of issues！

linghengqian · 2022-08-02T10:15:33Z

You are very patient and I admire your ability to keep track of issues！

In fact, there is a simplest fix for this issue, but I have not been able to write a reasonable unit test for it. 🤦‍♂️ Because this involves two different versions of H2Database. I pushed the relevant PR a while ago . I can only guarantee that the PR is passed in my local test example.
I'd appreciate it if anyone would be willing to write unit tests.

terrymanu · 2022-08-03T13:33:46Z

I just carry my comments from the PR:

The H2 database is the substitute for test only of MySQL, and version 1.x is the explicit dependency. So it is unnecessary to judge the major database version.
If the version 2.x of H2 is not compatible, I wonder how to test MySQL in memory.

To sum up, what is the proposal to upgrade H2 version from 1.x to 2.x?

linghengqian · 2022-08-03T16:07:27Z

I'm not sure if anyone in the community must need h2database 1.x compatibility. If there is no need for this, I can turn off Fix compatibility with H2Database 2.x #19806, then change the h2.version of ShardingSphere to 2.x and fix the unit tests, once and for all solve Spring Boot OSS 2.7.0 and later versions report an error at startup.
Waiting for Redesign JDBCRepository and H2Repository #19841 to be merged.

linghengqian mentioned this issue Feb 9, 2022

Bump h2 from 1.4.200 to 2.1.210 in /third-part-samples/shardingSphere-jdbc-sample dynamic-datasource/dynamic-datasource-samples#14

Closed

linghengqian mentioned this issue Mar 12, 2022

Improve the base version of H2Database's JDBC driver to 2.X. #16020

Closed

strongduanmu mentioned this issue Mar 21, 2022

[New Feature] Support system database and schema query statements for MySQL and PostgreSQL #16234

Closed

6 tasks

linghengqian mentioned this issue Aug 2, 2022

Fix compatibility with H2Database 2.x #19806

Closed

This was referenced Aug 4, 2022

Update H2Database to 2.x #19878

Closed

Update H2DataBase to 2.x and drop support for H2Database 1.x #19964

Merged

terrymanu closed this as completed in #19964 Aug 8, 2022

terrymanu added this to the 5.1.3 milestone Aug 8, 2022

terrymanu added in: kernel type: enhancement labels Aug 8, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X #15327

ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X #15327

linghengqian commented Feb 9, 2022 •

edited

Loading

tuichenchuxin commented Feb 10, 2022

linghengqian commented Mar 2, 2022

tuichenchuxin commented Mar 4, 2022

linghengqian commented Jul 12, 2022 •

edited

Loading

huangxinjian commented Aug 2, 2022

linghengqian commented Aug 2, 2022

terrymanu commented Aug 3, 2022 •

edited

Loading

linghengqian commented Aug 3, 2022 •

edited

Loading

ShardingSphere incorrectly handles table INFORMATION_SCHEMA for H2Database 2.X #15327

ShardingSphere incorrectly handles table INFORMATION_SCHEMA for H2Database 2.X #15327

Comments

linghengqian commented Feb 9, 2022 • edited Loading

Bug Report

Which version of ShardingSphere did you use?

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

Expected behavior

Actual behavior

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

tuichenchuxin commented Feb 10, 2022

linghengqian commented Mar 2, 2022

tuichenchuxin commented Mar 4, 2022

linghengqian commented Jul 12, 2022 • edited Loading

huangxinjian commented Aug 2, 2022

linghengqian commented Aug 2, 2022

terrymanu commented Aug 3, 2022 • edited Loading

linghengqian commented Aug 3, 2022 • edited Loading

ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X #15327

ShardingSphere incorrectly handles table `INFORMATION_SCHEMA` for H2Database 2.X #15327

linghengqian commented Feb 9, 2022 •

edited

Loading

linghengqian commented Jul 12, 2022 •

edited

Loading

terrymanu commented Aug 3, 2022 •

edited

Loading

linghengqian commented Aug 3, 2022 •

edited

Loading