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/09/10 08:49:48 UTC
[GitHub] [shardingsphere] Andrew-Yang999 opened a new issue #7381: Column 'ID' specified twice
Andrew-Yang999 opened a new issue #7381:
URL: https://github.com/apache/shardingsphere/issues/7381
**Thank you check my questions.
I have a question. It's " Column 'ID' specified twice". When I use ShardingJdbc+mybatis+mysql save some data, Would I use ShardingJdbc's function: built-in Snowflake, Here is my properties:**
`
spring.profiles=sit
#当遇到同样名字的时候，是否允许覆盖注册
spring.main.allow-bean-definition-overriding=true
#数据分片配置
spring.shardingsphere.datasource.names=ds0,ds1
#ds0
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
#ds1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
#默认分片规则
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=age
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{age % 2}
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds$->{0..1}.t_user$->{0..1}
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=ds$->{age % 2}
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=score
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user$->{score % 2}
spring.shardingsphere.props.sql.low=true
spring.shardingsphere.sharding.tables.t_user.key-generator.column=ID
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
`
**I wrote something about ID, meanwhile, I implement "ShardingKeyGenerator" class, here is my class:**
`
@Data
public class SimpleShardingKeyGenerator implements ShardingKeyGenerator {
private AtomicLong atomic = new AtomicLong(0);
private Properties properties = new Properties();
@Override
public Comparable<?> generateKey() {
// return atomic.incrementAndGet();
SnowflakeIdUtil snowflakeIdUtil = new SnowflakeIdUtil();
return snowflakeIdUtil.nextId();
}
@Override
public String getType() {
//声明类型
return "SIMPLE";
}
}
`
**After then I save some data , here is my datas:**
`
userList.add(new UserEntity("大大", 4, 8));
userList.add(new UserEntity("小小", 3, 3));
userList.add(new UserEntity("爸爸", 31, 1));
userList.add(new UserEntity("妈妈", 28, 0));
userList.add(new UserEntity("爷爷", 63, 2));
userList.add(new UserEntity("奶奶", 62, 7));
`
**And my SQL:**
`
CREATE TABLE IF NOT EXISTS `T_USER` (
`ID` BIGINT NOT NULL,
`USER_NAME` VARCHAR(20) NOT NULL,
`AGE` int NOT NULL DEFAULT 0,
`SCORE` int NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
`
**But, I got a bug, here is the bug:**
`
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@426c5120]
[ ERROR] [2020-09-10 16:26:50.055] [] [http-nio-8087-exec-3] o.a.catalina.core.ContainerBase.[Tomcat].[localhost].[/sharding-sphere-demo].[dispatcherServlet] [175] - Servlet.service() for servlet [dispatcherServlet] in context with path [/sharding-sphere-demo] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: Column 'ID' specified twice
### The error may exist in file [D:\myCode\dream\sharding-sphere-demo\target\classes\mapper\t\UserMapper.xml]
### The error may involve com.example.shardingspheredemo.model.t.mapper.UserMapper.insertForeach-Inline
### The error occurred while setting parameters
### SQL: insert into t_user (id,user_name,age,score) values (?, ?,?,?) , (?, ?,?,?) , (?, ?,?,?) , (?, ?,?,?) , (?, ?,?,?) , (?, ?,?,?)
### Cause: java.sql.SQLSyntaxErrorException: Column 'ID' specified twice
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Column 'ID' specified twice] with root cause
java.sql.SQLSyntaxErrorException: Column 'ID' specified twice
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:65)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:61)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:69)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)
at org.apache.shardingsphere.core.execute.ShardingExecuteEngine.syncGroupExecute(ShardingExecuteEngine.java:126)
at org.apache.shardingsphere.core.execute.ShardingExecuteEngine.parallelExecute(ShardingExecuteEngine.java:103)
at org.apache.shardingsphere.core.execute.ShardingExecuteEngine.groupExecute(ShardingExecuteEngine.java:83)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:73)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:56)
at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:128)
at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:156)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:172)
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:498)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy83.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
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.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
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:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
at com.sun.proxy.$Proxy67.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271)
at com.baomidou.mybatisplus.core.override.PageMapperMethod.execute(PageMapperMethod.java:68)
at com.baomidou.mybatisplus.core.override.PageMapperProxy.invoke(PageMapperProxy.java:64)
at com.sun.proxy.$Proxy68.insertForeach(Unknown Source)
at com.example.shardingspheredemo.model.t.service.impl.UserServiceImpl.insertForeach(UserServiceImpl.java:30)
at com.example.shardingspheredemo.model.t.service.impl.UserServiceImpl$$FastClassBySpringCGLIB$$bfa7d93d.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687)
at com.example.shardingspheredemo.model.t.service.impl.UserServiceImpl$$EnhancerBySpringCGLIB$$f0b96ca3.insertForeach(<generated>)
at com.example.shardingspheredemo.model.t.controller.UserController.saveUser(UserController.java:51)
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:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1589)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
`
**Please tell me my fault, Thank you.**
@tristaZero
----------------------------------------------------------------
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 #7381: Column 'ID' specified twice
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7381:
URL: https://github.com/apache/shardingsphere/issues/7381#issuecomment-690854174
----------------------------------------------------------------
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] Andrew-Yang999 commented on issue #7381: Column 'ID' specified twice
Posted by GitBox <gi...@apache.org>.
Andrew-Yang999 commented on issue #7381:
URL: https://github.com/apache/shardingsphere/issues/7381#issuecomment-690125225
@xbkaishui OK, I know, I can update my issues.
----------------------------------------------------------------
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] Andrew-Yang999 closed issue #7381: Column 'ID' specified twice
Posted by GitBox <gi...@apache.org>.
Andrew-Yang999 closed issue #7381:
URL: https://github.com/apache/shardingsphere/issues/7381
----------------------------------------------------------------
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] kimmking commented on issue #7381: Column 'ID' specified twice
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7381:
URL: https://github.com/apache/shardingsphere/issues/7381#issuecomment-690127144
remove id in your insert sql.
----------------------------------------------------------------
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] xbkaishui commented on issue #7381: Column 'ID' specified twice
Posted by GitBox <gi...@apache.org>.
xbkaishui commented on issue #7381:
URL: https://github.com/apache/shardingsphere/issues/7381#issuecomment-690106128
you should submit the issue using issue template, you are not specify which version to use. no one will know what's happened ..
----------------------------------------------------------------
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 #7381: Column 'ID' specified twice
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7381:
URL: https://github.com/apache/shardingsphere/issues/7381#issuecomment-690854174
> you should submit the issue using issue template, you are not specify which version to use. no one will know what's happened ..
Great explanation. : )
----------------------------------------------------------------
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 #7381: Column 'ID' specified twice
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7381:
URL: https://github.com/apache/shardingsphere/issues/7381#issuecomment-690854174
> you should submit the issue using issue template, you are not specify which version to use. no one will know what's happened ..
Great explanation. : )
----------------------------------------------------------------
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