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 2021/12/31 07:42:43 UTC

[GitHub] [shardingsphere] ziliangzeng opened a new issue #14464: jdbcTemplate update sql Report an error:You have an error in your SQL syntax ,but in navicate is right

ziliangzeng opened a new issue #14464:
URL: https://github.com/apache/shardingsphere/issues/14464


   ## Bug Report
   
   **For English only**, other languages will not accept.
   
   
   ### Which version of ShardingSphere did you use?
          5.0.0
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
       ShardingSphere-JDBC 
   ### Expected behavior
       update success
   ### Actual behavior
      org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax] with root cause
   ### Reason analyze (If you can)
      i thank sql is worse , bu i try many time in navicate , it is correct,so it may be a bug? or my sql  is worse.
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   sql   
   `       Date now = new Date();
           String sql = " UPDATE package_service_test_instance p SET p.IS_PASS = ? , p.DESC = ? , p.UPDATE_DATE = ? WHERE p.ID = ? ";
           return confJdbcTemplate.update(sql, new Object[]{3, '3', now, 8871000});`
   create table 
   `  DROP TABLE IF EXISTS `package_service_test_instance`;
   CREATE TABLE `package_service_test_instance`  (
     `ID` bigint NOT NULL COMMENT '主键ID',
     `CUST_ORDER_CODE` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '客户订单编码/测试单号',
     `SCENE_ID` bigint NOT NULL COMMENT '关联业务测试场景ID',
     `CUST_ORDER_MESSAGE` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '客户订单报文',
     `CREATE_DATE` datetime NOT NULL COMMENT '创建时间',
     `UPDATE_DATE` datetime NOT NULL COMMENT '更新时间',
     `ACC_NBR` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '业务号码',
     `IS_PASS` tinyint NOT NULL COMMENT '是否通过',
     `DESC` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '结果描述',
     `INFORM_ID` bigint NOT NULL COMMENT '关联业务包编码',
     `OPERUSER` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '操作用户',
     PRIMARY KEY (`ID`) USING BTREE,
     INDEX `IDX_PSTI_INFORMID`(`INFORM_ID`) USING BTREE
   ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '业务测试单实例表,单片表' ROW_FORMAT = Dynamic;
     `
   exception:
   `   
   context with path [] threw exception [Request processing failed; nested exception is com.google.common.util.concurrent.UncheckedExecutionException: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax] with root cause
   
   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:68) ~[shardingsphere-sql-parser-engine-5.0.0.jar:5.0.0]
   	at org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.parse(SQLParserExecutor.java:49) ~[shardingsphere-sql-parser-engine-5.0.0.jar:5.0.0]
   	at org.apache.shardingsphere.sql.parser.api.SQLParserEngine.parse(SQLParserEngine.java:51) ~[shardingsphere-sql-parser-engine-5.0.0.jar:5.0.0]
   	at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserExecutor.parse(SQLStatementParserExecutor.java:47) ~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
   	at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:40) ~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
   	at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:29) ~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
   	at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529) ~[guava-29.0-jre.jar:na]
   	at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278) ~[guava-29.0-jre.jar:na]
   	at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155) ~[guava-29.0-jre.jar:na]
   	at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045) ~[guava-29.0-jre.jar:na]
   	at com.google.common.cache.LocalCache.get(LocalCache.java:3951) ~[guava-29.0-jre.jar:na]
   	at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974) ~[guava-29.0-jre.jar:na]
   	at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958) ~[guava-29.0-jre.jar:na]
   	at com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4964) ~[guava-29.0-jre.jar:na]
   	at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine.parse(SQLStatementParserEngine.java:48) ~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
   	at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse0(ShardingSphereSQLParserEngine.java:71) ~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
   	at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:60) ~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
   	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:157) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
   	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:128) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
   	at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:78) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
   	at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1527) ~[spring-jdbc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615) ~[spring-jdbc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862) ~[spring-jdbc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:917) ~[spring-jdbc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:927) ~[spring-jdbc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at com.example.shardingspheredemo.controller.Test.test4(Test.java:31) ~[classes/:na]
   	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_261]
   	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_261]
   	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_261]
   	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_261]
   	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:215) ~[spring-web-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:142) ~[spring-web-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) ~[spring-webmvc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800) ~[spring-webmvc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038) ~[spring-webmvc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942) ~[spring-webmvc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:998) ~[spring-webmvc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:890) ~[spring-webmvc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:875) ~[spring-webmvc-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92) ~[spring-web-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) ~[spring-web-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.2.RELEASE.jar:5.1.2.RELEASE]
   	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) ~[tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:770) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1415) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_261]
   	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_261]
   	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.12.jar:9.0.12]
   	at java.lang.Thread.run(Thread.java:748) [na:1.8.0_261]
   
   
   `
   
   ### 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.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] NOLouser commented on issue #14464: jdbcTemplate update sql Report an error:You have an error in your SQL syntax ,but in navicate is right

Posted by GitBox <gi...@apache.org>.
NOLouser commented on issue #14464:
URL: https://github.com/apache/shardingsphere/issues/14464#issuecomment-1003881822


   @ziliangzeng 
   Hi, this seems to be a conflict between 'p.DESC' and mysql keyword DESC, and caused sql parsing exception.


-- 
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] ziliangzeng commented on issue #14464: jdbcTemplate update sql Report an error:You have an error in your SQL syntax ,but in navicate is right

Posted by GitBox <gi...@apache.org>.
ziliangzeng commented on issue #14464:
URL: https://github.com/apache/shardingsphere/issues/14464#issuecomment-1004487357


   @NOLouser  you are right. when i found it is right in navicate. i didnot realize that the problem is mysql keyword. so low-level i am... ths.


-- 
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] ziliangzeng closed issue #14464: jdbcTemplate update sql Report an error:You have an error in your SQL syntax ,but in navicate is right

Posted by GitBox <gi...@apache.org>.
ziliangzeng closed issue #14464:
URL: https://github.com/apache/shardingsphere/issues/14464


   


-- 
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