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/10/17 00:34:17 UTC

[GitHub] [shardingsphere] linghengqian opened a new issue #13063: How do I use distributed primary keys with Mybatis? The source code does not show the combination of Mybatis and key-generators.

linghengqian opened a new issue #13063:
URL: https://github.com/apache/shardingsphere/issues/13063


   ## Question
   
   **For English only**, other languages will not accept.
   
   Before asking a question, make sure you have:
   
   - Googled your question.
   - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
   - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
   
   Please pay attention on issues you submitted, because we maybe need more details. 
   If no response anymore and we cannot reproduce it on current information, we will **close it**.
   
   In my imagination, the use of ShardingSphere's distributed primary key should be similar to that of Mybatis Plus. When inserting a row of data, the primary key is not set when inserting, that is, let it be null. ShardingJDBC should help me fill this as Mybatis Plus does. The attribute of null is the id generated by the snowflake algorithm. But my guess was proved wrong, and the SQL error message was displayed on my Druid monitoring page, "Column'id' cannot be null".
   The sql statement is as follows.
   ```SQL
   INSERT INTO tb_result3_sharding_sphere (id, create_time, update_time, version, deleted
   	, posid, datadate, inputdate, abnormal)
   VALUES (?, ?, ?, ?, ?
   	, ?, ?, ?, ?)
   ```
   The parameters of sql are set to[null,'2021/10/15 18:43:01:946','2021/10/15 18:43:01:946',1,1,2137732648237423,'2021/10/15 18:43:01:946','2021/10/15 18:43:01:946','正常' ].
   
   LastErrorMessage is "Column 'id' cannot be null", LastErrorClass is "java.sql.SQLIntegrityConstraintViolationException".
   LastErrorStackTrace is follows.
   ```shell
   com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) 
   com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) 
   com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) 
   com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370) 
   com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461) 
   com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) 
   com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459) 
   com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167) 
   com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497) 
   org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:307) 
   org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:303) 
   org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85) 
   org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64) 
   org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101) org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:97) 
   org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:82) 
   org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65) 
   org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:49) 
   org.apache.shardingsphere.driver.executor.JDBCLockEngine.doExecute(JDBCLockEngine.java:114) 
   org.apache.shardingsphere.driver.executor.JDBCLockEngine.execute(JDBCLockEngine.java:91) 
   org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.execute(DriverJDBCExecutor.java:122) 
   org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:283) 
   org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) 
   sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
   sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
   sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
   java.lang.reflect.Method.invoke(Method.java:498) org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64) 
   com.sun.proxy.$Proxy229.update(Unknown Source) org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) 
   org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) 
   org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) 
   sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
   sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
   sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
   java.lang.reflect.Method.invoke(Method.java:498) org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) 
   com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:106) 
   org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62) com.sun.proxy.$Proxy228.update(Unknown Source) 
   org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:194) 
   org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:181) 
   sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
   sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
   sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
   java.lang.reflect.Method.invoke(Method.java:498) 
   org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) 
   com.sun.proxy.$Proxy199.insert(Unknown Source) org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272) 
   com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:59) 
   com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148) 
   com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) 
   com.sun.proxy.$Proxy204.insertBatchSomeColumn(Unknown Source) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
   sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
   java.lang.reflect.Method.invoke(Method.java:498) 
   org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) 
   org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) 
   org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) 
   org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) 
   org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) 
   org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) 
   com.sun.proxy.$Proxy205.insertBatchSomeColumn(Unknown Source) 
   com.sg8000.service.impl.TbResult3ShardingServiceImpl.batchInsertingLogs(TbResult3ShardingServiceImpl.java:46) 
   com.sg8000.service.impl.TbResult3ShardingServiceImpl$$FastClassBySpringCGLIB$$a97ebae9.invoke(<generated>) 
   org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) 
   org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) 
   org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) 
   org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) 
   org.springframework.aop.interceptor.AsyncExecutionInterceptor.lambda$invoke$0(AsyncExecutionInterceptor.java:115) 
   java.util.concurrent.FutureTask.run(FutureTask.java:266) 
   java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) 
   java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) java.lang.Thread.run(Thread.java:748)
   ```
   The following is the setting of application.properties.
   ```properties
   spring.shardingsphere.datasource.names=ds0
   spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
   spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
   spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=UTF-8
   spring.shardingsphere.datasource.ds0.username=root
   spring.shardingsphere.datasource.ds0.password=123456
   spring.shardingsphere.datasource.ds0.name=ShardingSphere
   spring.shardingsphere.datasource.ds0.maxActive=100
   spring.shardingsphere.datasource.ds0.initialSize=1
   spring.shardingsphere.datasource.ds0.maxWait=6000
   spring.shardingsphere.datasource.ds0.minIdle=1
   spring.shardingsphere.datasource.ds0.timeBetweenEvictionRunsMillis=60000
   spring.shardingsphere.datasource.ds0.minEvictableIdleTimeMillis=300000
   spring.shardingsphere.datasource.ds0.testWhileIdle=true
   spring.shardingsphere.datasource.ds0.testOnBorrow=false
   spring.shardingsphere.datasource.ds0.testOnReturn=false
   spring.shardingsphere.datasource.ds0.poolPreparedStatements=true
   spring.shardingsphere.datasource.ds0.maxOpenPreparedStatements=20
   spring.shardingsphere.datasource.ds0.asyncInit=true
   spring.shardingsphere.datasource.ds0.filters=stat
   spring.shardingsphere.datasource.ds0.use-global-datasource-stat=true
   spring.shardingsphere.datasource.ds0.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
   spring.shardingsphere.rules.sharding.tables.tb_result3_sharding_sphere.actual-data-nodes=ds0.tb_result3_sharding_sphere_$->{0..200}
   spring.shardingsphere.rules.sharding.tables.tb_result3_sharding_sphere.table-strategy.standard.sharding-column=datadate
   spring.shardingsphere.rules.sharding.tables.tb_result3_sharding_sphere.table-strategy.standard.sharding-algorithm-name=tb-result3-class-based
   spring.shardingsphere.rules.sharding.tables.tb_result3_sharding_sphere.key-generate-strategy.column=id
   spring.shardingsphere.rules.sharding.tables.tb_result3_sharding_sphere.key-generate-strategy.key-generator-name=snowflake
   spring.shardingsphere.rules.sharding.sharding-algorithms.tb-result3-class-based.type=CLASS_BASED
   spring.shardingsphere.rules.sharding.sharding-algorithms.tb-result3-class-based.props.strategy=STANDARD
   spring.shardingsphere.rules.sharding.sharding-algorithms.tb-result3-class-based.props.algorithmClassName=com.sg8000.spi.TbResult3ShardingTestAlgorithm
   spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
   spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=1014
   spring.datasource.druid.stat-view-servlet.enabled=true
   spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
   spring.datasource.druid.stat-view-servlet.login-username=admin
   spring.datasource.druid.stat-view-servlet.login-password=123456
   spring.datasource.druid.filter.stat.enabled=true
   spring.datasource.druid.filter.slf4j.enabled=true
   spring.datasource.druid.filter.slf4j.statement-create-after-log-enabled=false
   spring.datasource.druid.filter.slf4j.statement-close-after-log-enabled=false
   spring.datasource.druid.filter.slf4j.result-set-open-after-log-enabled=false
   spring.datasource.druid.filter.slf4j.result-set-close-after-log-enabled=false
   ```
   The following is the class corresponding to com.sg8000.spi.TbResult3ShardingAlgorithm mentioned in the properties section.
   ```java
   package com.sg8000.spi;
   
   import com.google.common.base.Preconditions;
   import com.google.common.collect.Range;
   import lombok.Getter;
   import lombok.Setter;
   import org.apache.shardingsphere.infra.config.exception.ShardingSphereConfigurationException;
   import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
   import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
   import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
   
   import java.text.DecimalFormat;
   import java.text.ParsePosition;
   import java.time.Duration;
   import java.time.LocalDateTime;
   import java.time.format.DateTimeFormatter;
   import java.time.format.DateTimeParseException;
   import java.util.Collection;
   import java.util.LinkedHashSet;
   import java.util.Properties;
   
   
   @Getter
   public final class TbResult3ShardingTestAlgorithm implements StandardShardingAlgorithm<Comparable<?>> {
       private static final String DATE_TIME_LOWER_KEY = "datetime-lower";
       private static final String DATE_TIME_UPPER_KEY = "datetime-upper";
       private static final String SHARDING_SECONDS_KEY = "sharding-seconds";
   
       private static final DateTimeFormatter DATE_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss[.S]");
       private static final String tbResult3ShardingPrefix = "tb_result3_sharding_sphere_";
       @Setter
       private Properties props = new Properties();
   
       private static final LocalDateTime dateTimeLower = LocalDateTime.parse("2021-01-01 00:00:00", DATE_TIME_FORMAT);
       private static final long shardingSeconds = 604800L;
       private static final int autoTablesAmount = 106;
   
       @Override
       public void init() {
       }
   
       private LocalDateTime getDateTime(final String dateTimeKey) {
           String value = props.getProperty(dateTimeKey);
           Preconditions.checkNotNull(value, "%s cannot be null.", dateTimeKey);
           try {
               return LocalDateTime.parse(value, DATE_TIME_FORMAT);
           } catch (final DateTimeParseException ex) {
               throw new ShardingSphereConfigurationException("Invalid %s, datetime pattern should be `yyyy-MM-dd HH:mm:ss`, value is `%s`", dateTimeKey, value);
           }
       }
   
       private long getShardingSeconds() {
           Preconditions.checkArgument(props.containsKey(SHARDING_SECONDS_KEY), "%s cannot be null.", SHARDING_SECONDS_KEY);
           return Long.parseLong(props.getProperty(SHARDING_SECONDS_KEY));
       }
   
       @Override
       public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Comparable<?>> shardingValue) {
           String tableNameSuffix = String.valueOf(doSharding(parseDate(shardingValue.getValue())));
           for (String each : availableTargetNames) {
               if (each.endsWith(tbResult3ShardingPrefix + tableNameSuffix)) {
                   return each;
               }
           }
           return null;
       }
   
       @Override
       public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<Comparable<?>> shardingValue) {
           Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
           int firstPartition = getFirstPartition(shardingValue.getValueRange());
           int lastPartition = getLastPartition(shardingValue.getValueRange());
           for (int i = firstPartition; i <= lastPartition; i++) {
               for (String each : availableTargetNames) {
                   if (each.endsWith(tbResult3ShardingPrefix + i)) {
                       result.add(each);
                       break;
                   }
                   if (result.size() == availableTargetNames.size()) {
                       return result;
                   }
               }
           }
           return result;
       }
   
       private int doSharding(final long shardingValue) {
           String position = new DecimalFormat("0.00").format((float) shardingValue / shardingSeconds);
           return Math.min(Math.max(0, (int) Math.ceil(Float.parseFloat(position))), autoTablesAmount - 1);
       }
   
       private int getFirstPartition(final Range<Comparable<?>> valueRange) {
           return valueRange.hasLowerBound() ? doSharding(parseDate(valueRange.lowerEndpoint())) : 0;
       }
   
       private int getLastPartition(final Range<Comparable<?>> valueRange) {
           return valueRange.hasUpperBound() ? doSharding(parseDate(valueRange.upperEndpoint())) : autoTablesAmount - 1;
       }
   
       private long parseDate(final Comparable<?> shardingValue) {
           LocalDateTime dateValue = LocalDateTime.from(DATE_TIME_FORMAT.parse(shardingValue.toString(), new ParsePosition(0)));
           return Duration.between(dateTimeLower, dateValue).toMillis() / 1000;
       }
   
       @Override
       public String getType() {
           return null;
       }
   }
   
   ```
   ![image](https://user-images.githubusercontent.com/20187731/137479036-9d21c797-ec0b-482a-bd3e-f4d6335c7ba3.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.

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

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



[GitHub] [shardingsphere] linghengqian commented on issue #13063: How do I use distributed primary keys with Mybatis? The source code does not show the combination of Mybatis and key-generators.

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


   Sorry, this is a problem with my configuration. Since my project uses both Mybatis Plus and ShardingSphere-JDBC, according to the documentation of Mybatis Plus, I mistakenly thought that the entity class @TableId(type = IdType.NONE) and @TableId( type = IdType.INPUT) is the same function. The problem is solved after the annotation of the primary key of the entity class is changed to @TableId(type = IdType.NONE).
   ![image](https://user-images.githubusercontent.com/20187731/137496512-3bea44b2-8df6-41e8-bfe7-ea4d6c695e63.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.

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

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



[GitHub] [shardingsphere] linghengqian closed issue #13063: How do I use distributed primary keys with Mybatis? The source code does not show the combination of Mybatis and key-generators.

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


   


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