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 2020/04/29 14:37:05 UTC
[GitHub] [shardingsphere] cherrylzhao opened a new issue #5386: getIndexInfo exception from sharding-proxy for postgresql
cherrylzhao opened a new issue #5386:
URL: https://github.com/apache/shardingsphere/issues/5386
## Bug Report
### Which version of ShardingSphere did you use?
\> 4.0.0
### Which project did you use? Sharding-JDBC or Sharding-Proxy?
sharding-proxy
### Expected behavior
succeed getIndexInfo through metadata, code is as below
``` java
@Test
public void assertProxy() throws SQLException {
Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:3307/sharding_db?user=root&password=root");
ResultSet resultSet = connection.getMetaData().getIndexInfo(null, null, "t_order", false, true);
while (resultSet.next()) {
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
System.out.println(String.format("%s: %s", resultSetMetaData.getColumnLabel(i), resultSet.getObject(i)));
}
}
}
```
### Actual behavior
```
org.postgresql.util.PSQLException: ERROR: column am.amcanorder does not exist
Position: 427
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
at org.postgresql.jdbc.PgDatabaseMetaData.getIndexInfo(PgDatabaseMetaData.java:2401)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ProxyTest.assertProxy(ProxyTest.java:34)
```
### Reason analyze (If you can)
it seems sharding-proxy could not compatible with postgresql greater than 9.4, maybe we should enhance handshake packet
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
1. start the proxy, test it using above code
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #5386: getIndexInfo exception from sharding-proxy for postgresql
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #5386:
URL: https://github.com/apache/shardingsphere/issues/5386#issuecomment-770380466
I tested it on ShardingSphere-Proxy 5.0.0-RC1-SNAPSHOT(commit: 2770484c6a0a13d8401eb309d0dac38c373a4c30), proxy side has SQL parsing exception:
```
line 1:370 no viable alternative at input 'SELECTtmp.TABLE_CAT,tmp.TABLE_SCHEM,tmp.TABLE_NAME,tmp.NON_UNIQUE,tmp.INDEX_QUALIFIER,tmp.INDEX_NAME,tmp.TYPE,tmp.ORDINAL_POSITION,trim(both'"'frompg_catalog.pg_get_indexdef(tmp.CI_OID,tmp.ORDINAL_POSITION,false))ASCOLUMN_NAME,CASEtmp.AM_NAMEWHEN'btree'THENCASEtmp.I_INDOPTION[tmp.ORDINAL_POSITION-1]&1'
line 1:370 no viable alternative at input 'SELECTtmp.TABLE_CAT,tmp.TABLE_SCHEM,tmp.TABLE_NAME,tmp.NON_UNIQUE,tmp.INDEX_QUALIFIER,tmp.INDEX_NAME,tmp.TYPE,tmp.ORDINAL_POSITION,trim(both'"'frompg_catalog.pg_get_indexdef(tmp.CI_OID,tmp.ORDINAL_POSITION,false))ASCOLUMN_NAME,CASEtmp.AM_NAMEWHEN'btree'THENCASEtmp.I_INDOPTION[tmp.ORDINAL_POSITION-1]&1'
[ERROR] 2021-01-31 21:02:58.689 [pool-10-thread-1] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
com.google.common.util.concurrent.UncheckedExecutionException: org.antlr.v4.runtime.misc.ParseCancellationException
at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2051)
at com.google.common.cache.LocalCache.get(LocalCache.java:3951)
at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974)
at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958)
at com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4964)
at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine.parse(SQLStatementParserEngine.java:48)
at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse0(ShardingSphereSQLParserEngine.java:67)
at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:56)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.binary.parse.PostgreSQLComParseExecutor.execute(PostgreSQLComParseExecutor.java:57)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:93)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.antlr.v4.runtime.misc.ParseCancellationException: null
at org.antlr.v4.runtime.BailErrorStrategy.recover(BailErrorStrategy.java:51)
at org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.selectNoParens(PostgreSQLStatementParser.java:2925)
at org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.select(PostgreSQLStatementParser.java:2644)
at org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.execute(PostgreSQLStatementParser.java:836)
at org.apache.shardingsphere.sql.parser.postgresql.parser.PostgreSQLParser.parse(PostgreSQLParser.java:37)
at org.apache.shardingsphere.sql.parser.core.parser.SQLParserExecutor.twoPhaseParse(SQLParserExecutor.java:60)
at org.apache.shardingsphere.sql.parser.core.parser.SQLParserExecutor.parse(SQLParserExecutor.java:45)
at org.apache.shardingsphere.sql.parser.api.SQLParserEngine.parse(SQLParserEngine.java:51)
at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserExecutor.parse(SQLStatementParserExecutor.java:47)
at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:40)
at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:29)
at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529)
at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278)
at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155)
at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045)
... 13 common frames omitted
Caused by: org.antlr.v4.runtime.NoViableAltException: null
at org.antlr.v4.runtime.atn.ParserATNSimulator.noViableAlt(ParserATNSimulator.java:2026)
at org.antlr.v4.runtime.atn.ParserATNSimulator.execATN(ParserATNSimulator.java:467)
at org.antlr.v4.runtime.atn.ParserATNSimulator.adaptivePredict(ParserATNSimulator.java:393)
at org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.selectNoParens(PostgreSQLStatementParser.java:2763)
... 26 common frames omitted
```
The executed SQL was intercepted on client:
```
SELECT tmp.TABLE_CAT, tmp.TABLE_SCHEM, tmp.TABLE_NAME, tmp.NON_UNIQUE, tmp.INDEX_QUALIFIER, tmp.INDEX_NAME, tmp.TYPE, tmp.ORDINAL_POSITION, trim(both '"' from pg_catalog.pg_get_indexdef(tmp.CI_OID, tmp.ORDINAL_POSITION, false)) AS COLUMN_NAME, CASE tmp.AM_NAME WHEN 'btree' THEN CASE tmp.I_INDOPTION[tmp.ORDINAL_POSITION - 1] & 1 WHEN 1 THEN 'D' ELSE 'A' END ELSE NULL END AS ASC_OR_DESC, tmp.CARDINALITY, tmp.PAGES, tmp.FILTER_CONDITION FROM (SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ct.relname AS TABLE_NAME, NOT i.indisunique AS NON_UNIQUE, NULL AS INDEX_QUALIFIER, ci.relname AS INDEX_NAME, CASE i.indisclustered WHEN true THEN 1 ELSE CASE am.amname WHEN 'hash' THEN 2 ELSE 3 END END AS TYPE, (information_schema._pg_expandarray(i.indkey)).n AS ORDINAL_POSITION, ci.reltuples AS CARDINALITY, ci.relpages AS PAGES, pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILT
ER_CONDITION, ci.oid AS CI_OID, i.indoption AS I_INDOPTION, am.amname AS AM_NAME FROM pg_catalog.pg_class ct JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) JOIN pg_catalog.pg_index i ON (ct.oid = i.indrelid) JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) JOIN pg_catalog.pg_am am ON (ci.relam = am.oid) WHERE true AND ct.relname = E'numeric_tc') AS tmp ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION
```
The client exception:
```
2021-01-31 21:00:58 [INFO] [main] [c.z.h.HikariDataSource][82] HikariPool-1 - Start completed.
dataSource=HikariDataSource (HikariPool-1)
Exception in thread "main" org.postgresql.util.PSQLException: org.antlr.v4.runtime.misc.ParseCancellationException
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:310)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:296)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:273)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:226)
at org.postgresql.jdbc.PgDatabaseMetaData.getIndexInfo(PgDatabaseMetaData.java:2514)
at com.zaxxer.hikari.pool.ProxyDatabaseMetaData.getIndexInfo(ProxyDatabaseMetaData.java:209)
at com.zaxxer.hikari.pool.HikariProxyDatabaseMetaData.getIndexInfo(HikariProxyDatabaseMetaData.java)
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org