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/07/06 09:03:07 UTC
[GitHub] [shardingsphere] sasikanth-th opened a new issue, #18896: Unable to create new table in Postgres with timestamp column
sasikanth-th opened a new issue, #18896:
URL: https://github.com/apache/shardingsphere/issues/18896
## Bug Report
### Which version of ShardingSphere did you use?
5.1.2
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
Able to create postgresql table with timestamp
### Actual behavior
Unable to create table with timestamp
```
Caused by: liquibase.exception.DatabaseException: You have an error in your SQL syntax [Failed SQL: CREATE TABLE public.files (id BIGINT NOT NULL, created_at TIMESTAMP WITHOUT TIME ZONE, updated_at TIMESTAMP WITHOUT TIME ZONE, created_by BIGINT, updated_by BIGINT, name VARCHAR(255), path VARCHAR(255), CONSTRAINT files_pkey PRIMARY KEY (id))]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:397) ~[liquibase-core-4.9.1.jar:na]
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:83) ~[liquibase-core-4.9.1.jar:na]
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:151) ~[liquibase-core-4.9.1.jar:na]
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1279) ~[liquibase-core-4.9.1.jar:na]
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1261) ~[liquibase-core-4.9.1.jar:na]
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:661) ~[liquibase-core-4.9.1.jar:na]
... 56 common frames omitted
Caused by: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax
at org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.twoPhaseParse(SQLParserExecutor.java:65) ~[shardingsphere-sql-parser-engine-5.1.1.jar:5.1.1]
at org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.parse(SQLParserExecutor.java:46) ~[shardingsphere-sql-parser-engine-5.1.1.jar:5.1.1]
at org.apache.shardingsphere.sql.parser.api.SQLParserEngine.parse(SQLParserEngine.java:47) ~[shardingsphere-sql-parser-engine-5.1.1.jar:5.1.1]
at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserExecutor.parse(SQLStatementParserExecutor.java:48) ~[shardingsphere-infra-parser-5.1.1.jar:5.1.1]
at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine.parse(SQLStatementParserEngine.java:47) ~[shardingsphere-infra-parser-5.1.1.jar:5.1.1]
at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:58) ~[shardingsphere-infra-parser-5.1.1.jar:5.1.1]
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.createLogicSQL(ShardingSphereStatement.java:461) ~[shardingsphere-jdbc-core-5.1.1.jar:5.1.1]
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute0(ShardingSphereStatement.java:418) ~[shardingsphere-jdbc-core-5.1.1.jar:5.1.1]
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute(ShardingSphereStatement.java:355) ~[shardingsphere-jdbc-core-5.1.1.jar:5.1.1]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:393) ~[liquibase-core-4.9.1.jar:na]
... 61 common frames omitted
```
### Reason analyze (If you can)
The postgres parser is failing incase of timestamp
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
Configuration
```
logging:
level:
com:
zaxxer:
hikari:
pool:
ProxyConnection: ERROR
spring:
liquibase:
change-log: [secret]
default-schema: public
liquibase-schema: public
enabled: true
shardingsphere:
datasource:
names: write-ds,read-ds
write-ds:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: org.postgresql.Driver
jdbc-url: jdbc:postgresql://localhost:5432/testgres
username: postgres
password: postgres
maxLifetime: 25
minIdle: 5
schema: public
read-ds:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: org.postgresql.Driver
jdbc-url: jdbc:postgresql://localhost:5432/testgres
username: postgres
password: postgres
maxLifetime: 25
minIdle: 5
schema: public
rules:
readwrite-splitting:
data-sources:
readwrite_ds:
type: Static
load-balancer-name: round_robin
props:
write-data-source-name: write-ds
read-data-source-names: read-ds
load-balancers:
round_robin:
type: ROUND_ROBIN
schema:
name: public
```
SQL
```
CREATE TABLE public.files (id BIGINT NOT NULL, created_at TIMESTAMP WITHOUT TIME ZONE, updated_at TIMESTAMP WITHOUT TIME ZONE, created_by BIGINT, updated_by BIGINT, name VARCHAR(255), path VARCHAR(255), CONSTRAINT files_pkey PRIMARY KEY (id))
```
--
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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sasikanth-th commented on issue #18896: Unable to create new table in Postgres
Posted by GitBox <gi...@apache.org>.
sasikanth-th commented on issue #18896:
URL: https://github.com/apache/shardingsphere/issues/18896#issuecomment-1177055374
@tuichenchuxin Thanks for the quick revert. I have updated the issue accordingly to avoid confusion for others.
--
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] strongduanmu closed issue #18896: Unable to create new table in Postgres
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #18896: Unable to create new table in Postgres
URL: https://github.com/apache/shardingsphere/issues/18896
--
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] tuichenchuxin commented on issue #18896: Unable to create new table in Postgres with timestamp column
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #18896:
URL: https://github.com/apache/shardingsphere/issues/18896#issuecomment-1176060430
@sasikanth-th It's not about timestamp but `path`
You can use this sql instead
```
CREATE TABLE public.files (id BIGINT NOT NULL, created_at TIMESTAMP WITHOUT TIME ZONE, updated_at TIMESTAMP WITHOUT TIME ZONE, created_by BIGINT, updated_by BIGINT, name VARCHAR(255), "path" VARCHAR(255), CONSTRAINT files_pkey PRIMARY KEY (id))
```
--
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] tuichenchuxin commented on issue #18896: Unable to create new table in Postgres with timestamp column
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #18896:
URL: https://github.com/apache/shardingsphere/issues/18896#issuecomment-1176057602
sure.
--
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] tuichenchuxin commented on issue #18896: Unable to create new table in Postgres
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #18896:
URL: https://github.com/apache/shardingsphere/issues/18896#issuecomment-1178460015
@sasikanth-th I think it's a good way to improve logging. I have created a issue to improve it. #18948 if you are interested in it, you can try to solve it by a pr.
--
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] sasikanth-th commented on issue #18896: Unable to create new table in Postgres
Posted by GitBox <gi...@apache.org>.
sasikanth-th commented on issue #18896:
URL: https://github.com/apache/shardingsphere/issues/18896#issuecomment-1177057770
@tuichenchuxin Couple of things
1. As I can see from https://www.postgresql.org/docs/current/sql-keywords-appendix.html path is a non-reserved keyword.
2. Is there a place where I can find the list of keywords that create while working with shardingsphere
3. Can we improve the logging to help it easy to debug.
--
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] strongduanmu commented on issue #18896: Unable to create new table in Postgres with timestamp column
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #18896:
URL: https://github.com/apache/shardingsphere/issues/18896#issuecomment-1176056712
@sasikanth-th Thank you for your feedback. @tuichenchuxin Can you help check this issue?
--
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