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/08/03 02:54:33 UTC
[GitHub] [shardingsphere] xingluan1216 opened a new issue #6579: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
xingluan1216 opened a new issue #6579:
URL: https://github.com/apache/shardingsphere/issues/6579
## Bug Report
How to solve this problem,It seems to be a matter of time resolution,Because it's normal when there's no time。
```
Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
```
### Which version of ShardingSphere did you use?
sharding-proxy-4.1.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
insert a record with time
### Actual behavior
An error occured in sharding-proxy when i insert a record with time
```
java.lang.IllegalArgumentException: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
at com.google.common.base.Preconditions.checkArgument(Preconditions.java:145)
at org.apache.shardingsphere.database.protocol.postgresql.packet.command.query.binary.bind.protocol.PostgreSQLBinaryProtocolValueFactory.getBinaryProtocolValue(PostgreSQLBinaryProtocolValueFactory.java:94)
at org.apache.shardingsphere.database.protocol.postgresql.packet.command.query.binary.bind.PostgreSQLComBindPacket.getParameters(PostgreSQLComBindPacket.java:84)
at org.apache.shardingsphere.database.protocol.postgresql.packet.command.query.binary.bind.PostgreSQLComBindPacket.<init>(PostgreSQLComBindPacket.java:64)
at org.apache.shardingsphere.database.protocol.postgresql.packet.command.PostgreSQLCommandPacketFactory.newInstance(PostgreSQLCommandPacketFactory.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.postgresql.command.PostgreSQLCommandExecuteEngine.getCommandPacket(PostgreSQLCommandExecuteEngine.java:55)
at org.apache.shardingsphere.shardingproxy.frontend.postgresql.command.PostgreSQLCommandExecuteEngine.getCommandPacket(PostgreSQLCommandExecuteEngine.java:46)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:91)
at org.apache.shardingsphere.shardingproxy.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:748)
```
### Reason analyze (If you can)
time parse, it`s ok when i insert a record without time
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
* sharding-proxy-4.1.1(4.1.0、4.0.1 have same error)
* jdbc: postgresql-42.2.5
* jdk 1.8
table
```sql
CREATE TABLE "public"."book"
(
"id" int8 NOT NULL,
"name" varchar(255) COLLATE "default" NOT NULL,
"tenant_no" int4 NOT NULL,
"create_time" timestamp(6) NOT NULL,
CONSTRAINT "book_pkey" PRIMARY KEY ("id")
)
WITH (OIDS= FALSE)
;
```
server.yaml
```yaml
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: sharding-demo
props:
proxy.hint.enabled: true
```
config-demo
```yaml
schemaName: sharding-demo
dataSources:
ds-1:
url: jdbc:postgresql://192.168.159.128:5432/sharding-proxy-1?serverTimezone=UTC&characterEncoding=utf8&useSSL=false
username: postgres
password: postgres
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 10
ds-2:
url: jdbc:postgresql://192.168.159.128:5432/sharding-proxy-2?serverTimezone=UTC&characterEncoding=utf8&useSSL=false
username: postgres
password: postgres
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 10
shardingRule:
defaultDatabaseStrategy:
standard:
shardingColumn: tenant_no
preciseAlgorithmClassName: demo.CustomPreciseShardingAlgorithm
defaultDataSourceName: ds-1
tables:
book:
actualDataNodes: ds-$->{1..2}.book
keyGenerator:
column: id
type: SNOWFLAKE
logicTable: book
```
CustomPreciseShardingAlgorithm.java
```java
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
String result = null;
for (String dbName : availableTargetNames) {
if (("ds-" + shardingValue.getValue()).equals(dbName)) {
result = dbName;
break;
}
}
return result;
}
```
jdbc
```java
String sql = "INSERT INTO book (id,name,tenant_no,create_time) VALUES (?,?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setLong(1,1);
preparedStatement.setString(2,"test_book");
preparedStatement.setInt(3,1);
preparedStatement.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
```
### Example codes for reproduce this issue (such as a github link).
----------------------------------------------------------------
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 edited a comment on issue #6579: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
Posted by GitBox <gi...@apache.org>.
sandynz edited a comment on issue #6579:
URL: https://github.com/apache/shardingsphere/issues/6579#issuecomment-678653755
After fixing `IllegalArgumentException: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value`, there's new exception thrown:
```
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "create_time" is of type timestamp without time zone but expression is of type bytea
```
From PostgreSQL driver source, Date/Time/Timestamp/Distinct/Other will use Oid.UNSPECIFIED, all of them set parameter as string, so read unsepcified parameter as string. but still exception:
```
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "create_time" is of type timestamp without time zone but expression is of type character varying
```
Since `BackendConnection` use `PreparedStatement.setObject(int parameterIndex, Object x)`, Oid is changed as \`x\`'s Java object type, e.g. String. In order to keep Oid.UNSPECIFIED, a new type `TypeUnspecifiedSQLParameter` added.
----------------------------------------------------------------
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 edited a comment on issue #6579: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
Posted by GitBox <gi...@apache.org>.
sandynz edited a comment on issue #6579:
URL: https://github.com/apache/shardingsphere/issues/6579#issuecomment-678653755
After fixing `IllegalArgumentException: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value`, there's new exception thrown:
```
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "create_time" is of type timestamp without time zone but expression is of type bytea
```
From PostgreSQL driver source, Date/Time/Timestamp/Distinct/Other will use Oid.UNSPECIFIED, all of them set parameter as string, so read unsepcified parameter as string. but still exception:
```
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "create_time" is of type timestamp without time zone but expression is of type character varying
```
Since `BackendConnection` use `PreparedStatement.setObject(int parameterIndex, Object x)`, Oid is changed as ``x`'s` Java object type, e.g. String. In order to keep Oid.UNSPECIFIED, a new type `TypeUnspecifiedSQLParameter` added.
----------------------------------------------------------------
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] tuohai666 commented on issue #6579: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6579:
URL: https://github.com/apache/shardingsphere/issues/6579#issuecomment-678073171
Hi @sandynz , the blocked issue has been fixed.
----------------------------------------------------------------
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 #6579: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #6579:
URL: https://github.com/apache/shardingsphere/issues/6579#issuecomment-678265191
>
>
> Hi @sandynz , the blocked issue has been fixed.
Ok, thanks. Then I'll continue
----------------------------------------------------------------
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 #6579: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #6579:
URL: https://github.com/apache/shardingsphere/issues/6579#issuecomment-668013749
Hi, I'd like try to solve it
----------------------------------------------------------------
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] tuohai666 closed issue #6579: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
Posted by GitBox <gi...@apache.org>.
tuohai666 closed issue #6579:
URL: https://github.com/apache/shardingsphere/issues/6579
----------------------------------------------------------------
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 #6579: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #6579:
URL: https://github.com/apache/shardingsphere/issues/6579#issuecomment-671023600
Blocked by #6729 , can't do integration test for now
----------------------------------------------------------------
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] tristaZero commented on issue #6579: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6579:
URL: https://github.com/apache/shardingsphere/issues/6579#issuecomment-667825881
Hi @xingluan1216
Thanks for your detailed issue description!
We will give it a look ASAP.
----------------------------------------------------------------
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 #6579: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #6579:
URL: https://github.com/apache/shardingsphere/issues/6579#issuecomment-678653755
After fixing `IllegalArgumentException: Cannot find PostgreSQL type 'POSTGRESQL_TYPE_UNSPECIFIED' in column type when process binary protocol value`, there's new exception thrown:
```
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "create_time" is of type timestamp without time zone but expression is of type bytea
```
From PostgreSQL driver source, Date/Time/Timestamp/Distinct/Other will use Oid.UNSPECIFIED, all of them set parameter as string, so read unsepcified parameter as string. but still exception:
```
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "create_time" is of type timestamp without time zone but expression is of type character varying
```
Since `BackendConnection` use `PreparedStatement.setObject(int parameterIndex, Object x)`, Oid is changed as `x`'s Java object type, e.g. String. In order to keep Oid.UNSPECIFIED, a new type `TypeUnspecifiedSQLParameter` added.
----------------------------------------------------------------
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