why can not connet with pgAdmin4? #13058

penghuazhou · 2021-10-15T04:53:27Z

version: shardingsphere-pxoxy 4.1.1

thow errors when use DBeaver or pgAdmin to connect, but it well to use pgsql.

[WARN ] 12:43:32.243 [ShardingSphere-5] c.zaxxer.hikari.pool.ProxyConnection - HikariPool-2 - Connection org.postgresql.jdbc.PgConnection@4bffc
0e marked as broken because of SQLSTATE(08006), ErrorCode(0)
org.postgresql.util.PSQLException: The server's client_encoding parameter was changed to UNICODE. The JDBC driver requires client_encoding to b
e UTF8 for correct operation.
at org.postgresql.core.v3.QueryExecutorImpl.receiveParameterStatus(QueryExecutorImpl.java:2604)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2219)
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.execute(PgStatement.java:266)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:1168)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:103)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.wrapper.StatementExecutorWrapper.executeSQL(StatementExecutorWrap
per.java:130)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.execute.callback.ProxySQLExecuteCallback.executeSQL(ProxySQLExecu
teCallback.java:82)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.execute.callback.ProxySQLExecuteCallback.executeSQL(ProxySQLExecu
teCallback.java:77)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.execute.callback.ProxySQLExecuteCallback.executeSQL(ProxySQLExecu
teCallback.java:46)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:82)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)
at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.lambda$asyncExecute$0(ExecutorEngine.java:110)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
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)

RaigorJiang · 2021-10-15T13:44:56Z

Hi @penghuazhou
Can you try the latest version 5.0.0-beta? It may be improved.

sandynz · 2021-10-16T07:56:24Z

Hi @penghuazhou , since psql works, according to PSQLException: The server's client_encoding parameter was changed to UNICODE. The JDBC driver requires client_encoding to be UTF8 for correct operation, could you check client_encoding in Dbeaver and set to UTF8 as exception recommended?

RaigorJiang · 2021-10-16T10:43:57Z

@penghuazhou
Please try to set JDBC parameter allowEncodingChanges=true for your connection.

tristaZero · 2021-10-17T06:27:09Z

@taojintianxia Hi It looks like night building can help users test conveniently.

penghuazhou · 2021-10-18T01:34:12Z

@penghuazhou Please try to set JDBC parameter allowEncodingChanges=true for your connection.

@RaigorJiang
it return error 'did' now，and no error message in shardingsphere log.

RaigorJiang · 2021-10-18T02:22:53Z

Hi @penghuazhou Can you try the latest version 5.0.0-beta? It may be improved.

Have you try the latest version?

penghuazhou · 2021-10-18T06:43:15Z

@tristaZero @RaigorJiang
why I cannot connect to shardingsphere with go language framework gorm(https://gorm.io/zh_CN/docs/index.html)?I can connetc with go language framework go-pg, also I can connect it with pgsql. Do shardingSphere-proxy not suggest user go language?

version: shardingsphere-proxy 4.1.1, postpresql(database):14.0

[INFO ] 09:36:13.648 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0xac2c2499, L:/0:0:0:0:0:0:0:0:3307] READ COMPLETE
line 1:0 no viable alternative at input ';'
line 1:0 mismatched input ';' expecting {SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, SET, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, CALL, START, RESET, SHOW}
[ERROR] 09:36:13.802 [pool-6-thread-1] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of ;
at org.apache.shardingsphere.sql.parser.core.parser.SQLParserExecutor.execute(SQLParserExecutor.java:49)
at org.apache.shardingsphere.sql.parser.SQLParserEngine.parse0(SQLParserEngine.java:79)
at org.apache.shardingsphere.sql.parser.SQLParserEngine.parse(SQLParserEngine.java:61)
at org.apache.shardingsphere.shardingproxy.backend.text.TextProtocolBackendHandlerFactory.newInstance(TextProtocolBackendHandlerFactory.java:69)
at org.apache.shardingsphere.shardingproxy.frontend.postgresql.command.query.text.PostgreSQLComQueryExecutor.(PostgreSQLComQueryExecutor.java:65)
at org.apache.shardingsphere.shardingproxy.frontend.postgresql.command.PostgreSQLCommandExecutorFactory.newInstance(PostgreSQLCommandExecutorFactory.java:58)
at org.apache.shardingsphere.shardingproxy.frontend.postgresql.command.PostgreSQLCommandExecuteEngine.getCommandExecutor(PostgreSQLCommandExecuteEngine.java:60)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:92)
at org.apache.shardingsphere.shardingproxy.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)

RaigorJiang · 2021-10-18T07:44:10Z

Hi @penghuazhou
What Proxy supports is a database protocol, not a programming language or framework.
If Proxy cannot be used normally, we need to analyze what the input SQL statement is, please do not provide information about a specific language or framework. This is not helpful to us.

In addition, compared to version 4.x, 5.0 has more support for SQL statements, which is why I ask you if you can try the new version.

penghuazhou · 2021-10-18T08:17:50Z

@RaigorJiang , I use master code, It have a error message "'ServerManager' object has no attribute 'user_info'"

RaigorJiang · 2021-10-18T08:40:49Z

@penghuazhou
Please adjust the log level of Proxy to DEBUG, and then observe the SQL received by Proxy.
This configuration is in logback.xml:

<logger name="org.apache.shardingsphere" level="debug" additivity="false">
        <appender-ref ref="console" />
    </logger>

And the key words of log: Execute packet type

penghuazhou · 2021-10-18T09:02:48Z

@RaigorJiang

+--------+-------------------------------------------------+----------------+
|00000000| 51 00 00 01 19 0a 53 45 4c 45 43 54 0a 20 20 20 |Q.....SELECT. |
|00000010| 20 64 62 2e 6f 69 64 20 61 73 20 64 69 64 2c 20 | db.oid as did, |
|00000020| 64 62 2e 64 61 74 6e 61 6d 65 2c 20 64 62 2e 64 |db.datname, db.d|
|00000030| 61 74 61 6c 6c 6f 77 63 6f 6e 6e 2c 0a 20 20 20 |atallowconn,. |
|00000040| 20 70 67 5f 65 6e 63 6f 64 69 6e 67 5f 74 6f 5f | pg_encoding_to_|
|00000050| 63 68 61 72 28 64 62 2e 65 6e 63 6f 64 69 6e 67 |char(db.encoding|
|00000060| 29 20 41 53 20 73 65 72 76 65 72 65 6e 63 6f 64 |) AS serverencod|
|00000070| 69 6e 67 2c 0a 20 20 20 20 68 61 73 5f 64 61 74 |ing,. has_dat|
|00000080| 61 62 61 73 65 5f 70 72 69 76 69 6c 65 67 65 28 |abase_privilege(|
|00000090| 64 62 2e 6f 69 64 2c 20 27 43 52 45 41 54 45 27 |db.oid, 'CREATE'|
|000000a0| 29 20 61 73 20 63 61 6e 63 72 65 61 74 65 2c 20 |) as cancreate, |
|000000b0| 64 61 74 6c 61 73 74 73 79 73 6f 69 64 2c 0a 20 |datlastsysoid,. |
|000000c0| 20 20 20 64 61 74 69 73 74 65 6d 70 6c 61 74 65 | datistemplate|
|000000d0| 0a 46 52 4f 4d 0a 20 20 20 20 70 67 5f 63 61 74 |.FROM. pg_cat|
|000000e0| 61 6c 6f 67 2e 70 67 5f 64 61 74 61 62 61 73 65 |alog.pg_database|
|000000f0| 20 64 62 0a 57 48 45 52 45 20 64 62 2e 64 61 74 | db.WHERE db.dat|
|00000100| 6e 61 6d 65 20 3d 20 63 75 72 72 65 6e 74 5f 64 |name = current_d|
|00000110| 61 74 61 62 61 73 65 28 29 00 |atabase(). |
+--------+-------------------------------------------------+----------------+
[DEBUG] 2021-10-18 16:57:14.694 [Connection-2-ThreadExecutor] o.a.s.p.f.p.c.PostgreSQLCommandExecutorFactory - Execute packet type: SIMPLE_QUERY, value: org.apache.shardingsphere.db.protocol.postgresql.packet.command.query.text.PostgreSQLComQueryPacket@1224f7ac
[DEBUG] 2021-10-18 16:57:14.760 [nioEventLoopGroup-3-2] o.a.s.db.protocol.codec.PacketCodec - Write to client 34771aad :
+-------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+--------+-------------------------------------------------+----------------+
|00000000| 45 00 00 00 3e 53 45 52 52 4f 52 00 56 45 52 52 |E...>SERROR.VERR|
|00000010| 4f 52 00 43 35 38 30 30 30 00 4d 53 63 68 65 6d |OR.C58000.MSchem|
|00000020| 61 20 27 70 67 5f 63 61 74 61 6c 6f 67 27 20 64 |a 'pg_catalog' d|
|00000030| 6f 65 73 6e 27 74 20 65 78 69 73 74 2e 00 00 |oesn't exist... |
+--------+-------------------------------------------------+----------------+
[DEBUG] 2021-10-18 16:57:14.760 [nioEventLoopGroup-3-2] o.a.s.db.protocol.codec.PacketCodec - Write to client 34771aad :
+-------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+--------+-------------------------------------------------+----------------+
|00000000| 5a 00 00 00 05 49 |Z....I |
+--------+-------------------------------------------------+----------------+

RaigorJiang · 2021-10-18T09:06:38Z

It seems that the connection has been successful, but the database and table information is incorrect, because the way Postgre obtains information is different from that of MySQL, and this part needs to be improved.

RaigorJiang · 2021-10-18T09:07:24Z

But you can execute your own business SQL without worrying about whether the metadata is displayed correctly.

penghuazhou · 2021-10-18T09:18:12Z

but I can not use pgadmin4，do you have a gui tools to connect to shardsphere-proxy?

penghuazhou · 2021-10-18T09:19:30Z

@RaigorJiang but I can not use pgadmin4，do you have a gui tools(a postgresql database client tool) to connect to shardsphere-proxy?

RaigorJiang · 2021-10-18T09:22:05Z

Can you try Navicat or DataGrip?
https://shardingsphere.apache.org/document/current/en/others/faq/#6-proxy-how-to-use-a-suitable-database-tools-connecting-shardingsphere-proxy

penghuazhou · 2021-10-18T09:30:41Z

do you know why return "'pg_catalog' doesn't exist"? It exists in database

|00000000| 45 00 00 00 3e 53 45 52 52 4f 52 00 56 45 52 52 |E...>SERROR.VERR|
|00000010| 4f 52 00 43 35 38 30 30 30 00 4d 53 63 68 65 6d |OR.C58000.MSchem|
|00000020| 61 20 27 70 67 5f 63 61 74 61 6c 6f 67 27 20 64 |a 'pg_catalog' d|
|00000030| 6f 65 73 6e 27 74 20 65 78 69 73 74 2e 00 00 |oesn't exist... |

RaigorJiang · 2021-10-18T10:07:13Z

do you know why return "'pg_catalog' doesn't exist"? It exists in database

Because when you are using ShardingSphere-Proxy, the schema is what you defined in Proxy configuration.
Users cannot directly access the metadata of a physical database, because in a distributed database system, the information of a single database may not be complete.

However, access to metadata is very important to some SQL clients, so this part still needs support, and we are already sorting out related plans.

penghuazhou · 2021-10-18T11:55:27Z

@RaigorJiang DataGrip：2021.1 can execute sql, but can not show databases message

RaigorJiang · 2021-10-18T12:53:13Z

Yes, because the way postgre queries schema and table metadata is different from MySQL, this is a known issue, and we will fix it later.

penghuazhou · 2021-10-19T07:29:56Z

thanks

tristaZero · 2021-10-24T10:50:11Z

@RaigorJiang This will be our next improvement for the next release? Do we have any plans?

RaigorJiang · 2021-10-25T02:09:22Z

Yes, plans will be sorted out this week, so stay tuned~

sandynz added the type: question label Oct 16, 2021

RaigorJiang added db: PostgreSQL in: proxy labels Oct 18, 2021

penghuazhou closed this as completed Oct 19, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

why can not connet with pgAdmin4? #13058

why can not connet with pgAdmin4? #13058

penghuazhou commented Oct 15, 2021 •

edited

Loading

RaigorJiang commented Oct 15, 2021

sandynz commented Oct 16, 2021

RaigorJiang commented Oct 16, 2021

tristaZero commented Oct 17, 2021

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021 •

edited

Loading

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 18, 2021

penghuazhou commented Oct 18, 2021 •

edited

Loading

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 19, 2021

tristaZero commented Oct 24, 2021

RaigorJiang commented Oct 25, 2021

why can not connet with pgAdmin4? #13058

why can not connet with pgAdmin4? #13058

Comments

penghuazhou commented Oct 15, 2021 • edited Loading

RaigorJiang commented Oct 15, 2021

sandynz commented Oct 16, 2021

RaigorJiang commented Oct 16, 2021

tristaZero commented Oct 17, 2021

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021 • edited Loading

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 18, 2021

penghuazhou commented Oct 18, 2021 • edited Loading

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 18, 2021

RaigorJiang commented Oct 18, 2021

penghuazhou commented Oct 19, 2021

tristaZero commented Oct 24, 2021

RaigorJiang commented Oct 25, 2021

penghuazhou commented Oct 15, 2021 •

edited

Loading

RaigorJiang commented Oct 18, 2021 •

edited

Loading

penghuazhou commented Oct 18, 2021 •

edited

Loading