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 2019/12/31 09:37:17 UTC

[GitHub] [incubator-shardingsphere] xiyelife opened a new issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

xiyelife opened a new issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842
 
 
   ### Which version of ShardingSphere did you use?
   4.0.0-RC3
   ### Which project did you use? Sharding-JDBC or Sharding-Proxy?
   sharding-jdbc
   ### Expected behavior
   Insert successfully when inserted sql columns contains “system”
   ### Actual behavior
   sharding parse SQL error: 
   line 2:7 no viable alternative at input '(system'
   ### Reason analyze (If you can)
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   
   SQL:
   `insert into template (system, code, name ) values ( ?, ?, ? ) `
   ### 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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] SteNicholas edited a comment on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

SteNicholas edited a comment on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-570059977
 
 
   > Not only one SQL，there are many applications whose used the mybatis generator tools auto created.
   
   @xiyelife Please upgrade the version of MySQL to 8.x, and check the `SYSTEM` keyword. Currently, MySQLParser is based on 8.x version of MySQL. About adapter to different version of MySQL, it doesn't support. If you indeed need, you could add `SYSTEM` keyword to `unreservedWord_ ` and compile a version by yourself to use. If you have no any question, please close this.

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] xiyelife commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

xiyelife commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-570051809
 
 
   Not only one SQL，there are many applications whose used the mybatis generator auto created.

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] terrymanu closed issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

terrymanu closed issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842
 
 
   

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] xiyelife edited a comment on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

xiyelife edited a comment on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-569917765
 
 
   @terrymanu 
   ```
   Preparing: insert into user_info (cust_id, unique_id, name, ident, mobile, password, reg_time, modify_time, reg_source, reg_app_channel, reg_device_info, status, is_mobile_authed, complate_cust_info, time_inst, time_upd, has_checked, system) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now() , ?, ?, ?) 
   
   Parameters: null, a86b78670b214c1c90daf12c764fae86(String), 樊前(String), 152137197406208194(String), 13007897819(String), jpaxsdsewrwe123123f(String), null, 111111(String), 11(String), null, 测试地址(String), null, 1(String), null, 2019-12-31 19:56:16.785(Timestamp), null, 111(String)
   
   line 6:29 no viable alternative at input '(cust_id,unique_id,name,ident,mobile,password,reg_time,modify_time,reg_source,reg_app_channel,reg_device_info,status,is_mobile_authed,complate_cust_info,time_inst,time_upd,has_checked,system'
   
   org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
   ### Error updating database.  Cause: java.lang.IllegalStateException
   ### The error may involve transparent.mybatis.mapper.UserInfoMapper.insert-Inline
   ### The error occurred while setting parameters
   ### SQL: insert into user_info (cust_id, unique_id, name,        ident, mobile, password,        reg_time, modify_time, reg_source,        reg_app_channel, reg_device_info, status,        is_mobile_authed, complate_cust_info, time_inst,        time_upd, has_checked, system)     values (?, ?, ?,        ?, ?, ?,        ?, ?, ?,        ?, ?, ?,        ?, ?,  now() ,        ?, ?, ?)
   ### Cause: java.lang.IllegalStateException
   	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
   	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
   	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
   	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   	at java.lang.reflect.Method.invoke(Method.java:497)
   	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
   	... 13 common frames omitted
   Caused by: java.lang.IllegalStateException: null
   	at com.google.common.base.Preconditions.checkState(Preconditions.java:485)
   	at org.apache.shardingsphere.core.rewrite.feature.encrypt.token.generator.impl.EncryptForUseDefaultInsertColumnsTokenGenerator.generateNewSQLToken(EncryptForUseDefaultInsertColumnsTokenGenerator.java:84)
   	at org.apache.shardingsphere.core.rewrite.feature.encrypt.token.generator.impl.EncryptForUseDefaultInsertColumnsTokenGenerator.generateSQLToken(EncryptForUseDefaultInsertColumnsTokenGenerator.java:62)
   	at org.apache.shardingsphere.core.rewrite.feature.encrypt.token.generator.impl.EncryptForUseDefaultInsertColumnsTokenGenerator.generateSQLToken(EncryptForUseDefaultInsertColumnsTokenGenerator.java:44)
   	at org.apache.shardingsphere.core.rewrite.sql.token.generator.SQLTokenGenerators.generateSQLTokens(SQLTokenGenerators.java:78)
   	at org.apache.shardingsphere.core.rewrite.context.SQLRewriteContext.generateSQLTokens(SQLRewriteContext.java:85)
   	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement.getSQLUnit(EncryptPreparedStatement.java:158)
   	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement.execute(EncryptPreparedStatement.java:127)
   	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   	at java.lang.reflect.Method.invoke(Method.java:497)
   	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
   	at com.sun.proxy.$Proxy120.execute(Unknown Source)
   	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
   	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
   	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
   	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
   	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
   	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
   	... 19 common frames omitted
   ```

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] xiyelife commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

xiyelife commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-569908372
 
 
   @SteNicholas  but sharding parse the insert sql error.   The sql is generated by mybatis automi and it's used in many scenes.   I think it need to fix .

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] SteNicholas commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

SteNicholas commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-570033914
 
 
   > @SteNicholas
   > 
   > > @xiyelife In [MySQL reserved keywords](https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-S), `SYSTEM` is regarded as reserved keyword. Therefore, you couldn't use `SYSTEM` in InsertStatement.
   > 
   > Reserverd keywords can be used in the sql's columns for mysql, such as id/name and so on.
   
   `MySQLKeyword.g4` includes `SYSTEM` keyword, but `unreservedWord_` doesn't include `SYSTEM`. Do you see the  [MySQL reserved keywords](https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-S)? In MySQL 8.0, `SYSTEM` is reserved keyword. Therefore, the `SYSTEM` doesn't put into `unreservedWord_`. I have already questioned @terrymanu yesterday for this keyword. Out decision is that `SYSTEM` isn't put into `unreservedWord_`. Thanks.

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] xiyelife commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

xiyelife commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-569920708
 
 
   @terrymanu 
   Here is the parse exception, I think that we need to print the parse exception to the logger rather than system out.
   
   `line 6:29 no viable alternative at input '(cust_id,unique_id,name,ident,mobile,password,reg_time,modify_time,reg_source,reg_app_channel,reg_device_info,status,is_mobile_authed,complate_cust_info,time_inst,time_upd,has_checked,system'
   `
   ![image](https://user-images.githubusercontent.com/8772975/71621547-e6b2e400-2c0a-11ea-88e1-ec6e4135d853.png)
   

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] xiyelife edited a comment on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

xiyelife edited a comment on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-569947679
 
 
   @terrymanu 
   
   > The keyword `SYSTEM `is not defined in the lexer file `Keyword.g4`, I don't think it can be worked fine if you just add it into `BaseRule.g4`
   
   `SYSTEM `  has bean defined in the lexer file `MySQLKeyword.g4`(in line 1395), so it works fine.

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] xiyelife edited a comment on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

xiyelife edited a comment on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-569908372
 
 
   @SteNicholas  but sharding parse the insert sql error.   The sql is generated by mybatis automatic and it's used in many scenes.   I think it need to fix .

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] SteNicholas edited a comment on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

SteNicholas edited a comment on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-569898833
 
 
   @xiyelife In (MySQL reserved keywords)[https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-S], `SYSTEM` is regarded as reserved keyword. Therefore, you couldn't use `SYSTEM` in InsertStatement.

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] SteNicholas commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

SteNicholas commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-570059977
 
 
   > Not only one SQL，there are many applications whose used the mybatis generator tools auto created.
   
   Please upgrade the version of MySQL to 8.x, and check the `SYSTEM` keyword. Currently, MySQLParser is based on 8.x version of MySQL. About adapter to different version of MySQL, it doesn't support. If you indeed need, you could add `SYSTEM` keyword to `unreservedWord_ ` and compile a version by yourself to use.

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] xiyelife commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

xiyelife commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-570043880
 
 
   So，how can we do with this issue？ The most popular mysql’s version is 5.x and I have many busi  tables has been used this column，you known that change is hard to the busi technical units.

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


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)

Posted by GitBox <gi...@apache.org>.

terrymanu commented on issue #3842: Insert failed when inserted sql columns contains “system” (missing prepared paramters)
URL: https://github.com/apache/incubator-shardingsphere/issues/3842#issuecomment-569923504
 
 
   The keyword `SYSTEM `is not defined in the lexer file `Keyword.g4`, I don't think it can be worked fine if you just add it into `BaseRule.g4`

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


With regards,
Apache Git Services