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/01/26 09:41:07 UTC
[GitHub] [shardingsphere] PingFeng233 opened a new issue #15090: postgresql select result did not match.
PingFeng233 opened a new issue #15090:
URL: https://github.com/apache/shardingsphere/issues/15090
### Question
1. When I query this data through ShardingSphere-Proxy connection, the query result of field and datas did not match.
2. Do not query through ShardingSphere-Proxy:
3. Query through ShardingSphere-Proxy:
4. logs
```
Thanks for using Atomikos! Evaluate http://www.atomikos.com/Main/ExtremeTransactions for advanced features and professional support
or register at http://www.atomikos.com/Main/RegisterYourDownload to disable this message and receive FREE tips & advice
[INFO ] 2022-01-26 09:34:26.638 [main] o.a.s.p.i.i.AbstractBootstrapInitializer - Database name is `PostgreSQL`, version is `14.1 (Debian 14.1-1.pgdg110+1)`
[INFO ] 2022-01-26 09:34:26.794 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy start success.
[INFO ] 2022-01-26 09:34:38.371 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: set client_encoding to 'UNICODE'
[INFO ] 2022-01-26 09:34:38.372 [Connection-1-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSetStatement()
[INFO ] 2022-01-26 09:34:38.372 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: write_ds ::: set client_encoding to 'UNICODE'
[ERROR] 2022-01-26 09:34:38.393 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.postgresql.util.PSQLException: The server's client_encoding parameter was changed to UNICODE. The JDBC driver requires client_encoding to be 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.proxy.backend.communication.jdbc.executor.callback.impl.ProxyStatementExecutorCallback.execute(ProxyStatementExecutorCallback.java:40)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:73)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:66)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:44)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:97)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:82)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:66)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:170)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:127)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:116)
at org.apache.shardingsphere.proxy.backend.communication.ProxyLockEngine.doExecute(ProxyLockEngine.java:103)
at org.apache.shardingsphere.proxy.backend.communication.ProxyLockEngine.execute(ProxyLockEngine.java:81)
at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:99)
at org.apache.shardingsphere.proxy.backend.text.data.impl.BroadcastDatabaseBackendHandler.execute(BroadcastDatabaseBackendHandler.java:54)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.text.PostgreSQLComQueryExecutor.execute(PostgreSQLComQueryExecutor.java:63)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
[INFO ] 2022-01-26 09:34:38.507 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT * FROM ir_translation
WHERE lang='zh_CN' AND type='model_terms' AND name='ir.ui.view,arch_db' AND res_id =197
[INFO ] 2022-01-26 09:34:38.507 [Connection-1-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-01-26 09:34:38.507 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: read_ds_0 ::: SELECT * FROM ir_translation
WHERE lang='zh_CN' AND type='model_terms' AND name='ir.ui.view,arch_db' AND res_id =197
```
### version
5.1.0
### project
ShardingSphere-Proxy
### postgresql version
14.1 (Debian 14.1-1.pgdg110+1)
### docker-compose.yml
```
version: '3.1'
services:
proxy:
image: apache/sharding-proxy:latest
ports:
- "23308:3308"
environment:
- PORT=3308
volumes:
- ./conf:/opt/shardingsphere-proxy/conf
- ./log:/opt/shardingsphere-proxy/logs
```
### server.yaml
```
rules:
- !AUTHORITY
users:
- root@:root
- sharding@:sharding
provider:
type: NATIVE
props:
max-connections-size-per-query: 1
kernel-executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default value is 128.
proxy-transaction-type: LOCAL
proxy-opentracing-enabled: false
proxy-hint-enabled: false
sql-show: true
check-table-metadata-enabled: false
```
### config-readwrite-splitting.yaml
```
schemaName: proxy_db
dataSources:
write_ds:
url: jdbc:postgresql://192.168.10.224:9978/lansil20220119?serverTimezone=UTC&useSSL=false
username: postgres
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
read_ds_0:
url: jdbc:postgresql://192.168.10.241:9978/lansil20220119?serverTimezone=UTC&useSSL=false
username: postgres
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !READWRITE_SPLITTING
dataSources:
pr_ds:
writeDataSourceName: write_ds
readDataSourceNames:
- read_ds_0
```
--
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] TeslaCN commented on issue #15090: postgresql select result did not match.
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #15090:
URL: https://github.com/apache/shardingsphere/issues/15090#issuecomment-1022088304
Could you check if the columns' order of tables are consistent?
--
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] PingFeng233 commented on issue #15090: postgresql select result did not match.
Posted by GitBox <gi...@apache.org>.
PingFeng233 commented on issue #15090:
URL: https://github.com/apache/shardingsphere/issues/15090#issuecomment-1031319855
I tried to use proxy version 4.1.1 and then the result is correct. Just can't connect through Navicat but it's ok to connect through psql.
--
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] TeslaCN commented on issue #15090: postgresql select result did not match.
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #15090:
URL: https://github.com/apache/shardingsphere/issues/15090#issuecomment-1022037894
Hi @PingFeng233
Have you executed DDL to database directly? The Proxy may not be aware of meta data changes in database.
--
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] PingFeng233 closed issue #15090: postgresql select result did not match.
Posted by GitBox <gi...@apache.org>.
PingFeng233 closed issue #15090:
URL: https://github.com/apache/shardingsphere/issues/15090
--
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] TeslaCN edited a comment on issue #15090: postgresql select result did not match.
Posted by GitBox <gi...@apache.org>.
TeslaCN edited a comment on issue #15090:
URL: https://github.com/apache/shardingsphere/issues/15090#issuecomment-1022088304
Could you check if the columns' order of the tables in read and write data sources are consistent?
--
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] PingFeng233 commented on issue #15090: postgresql select result did not match.
Posted by GitBox <gi...@apache.org>.
PingFeng233 commented on issue #15090:
URL: https://github.com/apache/shardingsphere/issues/15090#issuecomment-1022074260
Hi @TeslaCN ，I didn't do any other operations except query and I found it was strange, if I select with specified fields, the result is correct. If I use '*' to select, the result is incorrect.
--
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