shardingshere-proxy分库分表使用时，主键设置不生效为null的报错处理

坚持是一种态度

已于 2022-03-13 10:52:20 修改

阅读量3.5k

点赞数 1

分类专栏：数据库及存储技术异常报错文章标签： java hibernate postgresql shardingshpere 分库分表

于 2022-03-13 10:46:16 首次发布

本文链接：https://blog.csdn.net/u010882234/article/details/123454796

版权

异常报错同时被 2 个专栏收录

69 篇文章 0 订阅

订阅专栏

数据库及存储技术

50 篇文章 3 订阅

订阅专栏

文章目录

场景

SaaS平台项目，需要根据租户分库，使用ShardingSphere-proxy 版本5.1
项目采用Spring Cloud微服务架构，全部是Spring Boot项目，持久层使用hibernate，版本 5.4
编程语言Java，程序运行，使用jdk 11
数据库PostgresQL 13.5
普通的JPA保存方法，代码没有变动，原本可以正常运行没有报错，使用sharding分库分表后，报错

报错一：分库的表

报错代码

报错的表，是需要分库的表，已经在配置文件里设置好了分库规则和主键规则
使用的就是hibernate普通的保存方法

Long id = this.saveEntity(cmsContentEO);

程序报错

下面是我的应用程序运行报错的部分日志

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259) ~[spring-orm-5.3.3.jar:5.3.3]
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233) ~[spring-orm-5.3.3.jar:5.3.3]
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551) ~[spring-orm-5.3.3.jar:5.3.3]
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.3.3.jar:5.3.3]
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) ~[spring-tx-5.3.3.jar:5.3.3]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152) ~[spring-tx-5.3.3.jar:5.3.3]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.3.jar:5.3.3]
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174) ~[spring-data-jpa-2.4.3.jar:2.4.3]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.3.jar:5.3.3]
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.3.jar:5.3.3]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.3.jar:5.3.3]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.3.jar:5.3.3]
	at com.sun.proxy.$Proxy178.save(Unknown Source) ~[na:na]
	at cn.lonsun.core.base.service.impl.BaseService.saveEntity(BaseService.java:113) ~[ls-framework-core-4.0.5-20220125.094401-2.jar:na]
	at cn.lonsun.digitalvillage.content.content.internal.service.impl.CmsContentServiceImpl.addContent(CmsContentServiceImpl.java:143) ~[classes/:na]
	at cn.lonsun.digitalvillage.content.content.internal.service.impl.CmsContentServiceImpl$$FastClassBySpringCGLIB$$8fb2b787.invoke(<generated>) ~[classes/:na]
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.3.jar:5.3.3]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.3.jar:5.3.3]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.3.jar:5.3.3]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.3.jar:5.3.3]
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.3.jar:5.3.3]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.3.jar:5.3.3]
    ......
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.id.enhanced.SequenceStructure$1.getNextValue(SequenceStructure.java:95) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.id.enhanced.NoopOptimizer.generate(NoopOptimizer.java:40) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.id.enhanced.SequenceStyleGenerator.generate(SequenceStyleGenerator.java:523) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:115) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:185) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:128) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:55) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:720) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:706) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    ......
Caused by: org.postgresql.util.PSQLException: ERROR: relation "hibernate_sequence" does not exist
  位置：17
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552) ~[postgresql-42.2.23.jar:42.2.23]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284) ~[postgresql-42.2.23.jar:42.2.23]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322) ~[postgresql-42.2.23.jar:42.2.23]
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) ~[postgresql-42.2.23.jar:42.2.23]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) ~[postgresql-42.2.23.jar:42.2.23]
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) ~[postgresql-42.2.23.jar:42.2.23]
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114) ~[postgresql-42.2.23.jar:42.2.23]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.4.5.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.5.jar:na]
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
	... 135 common frames omitted

sharding中间件报错

PostgreSQLComParseExecutor :: sql = select nextval ('hibernate_sequence')
[INFO ] 2022-03-03 17:48:42.200 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select nextval ('hibernate_sequence')
[INFO ] 2022-03-03 17:48:42.200 [Connection-1-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-03-03 17:48:42.200 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select nextval ('hibernate_sequence')
[ERROR] 2022-03-03 17:48:42.216 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur: 
org.postgresql.util.PSQLException: ERROR: relation "hibernate_sequence" does not exist
  Position: 17
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyPreparedStatementExecutorCallback.execute(ProxyPreparedStatementExecutorCallback.java:41)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:73)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:66)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:44)
	at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)
	at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)
	at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101)
	at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:87)
	at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:81)
	at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:71)
	at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:185)
	at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:138)
	at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:127)
	at org.apache.shardingsphere.proxy.backend.communication.ProxyLockEngine.doExecute(ProxyLockEngine.java:103)
	at org.apache.shardingsphere.proxy.backend.communication.ProxyLockEngine.execute(ProxyLockEngine.java:81)
	at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:126)
	at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.binary.PostgreSQLPortal.execute(PostgreSQLPortal.java:99)
	at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.binary.bind.PostgreSQLComBindExecutor.execute(PostgreSQLComBindExecutor.java:49)
	at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.binary.execute.PostgreSQLComExecuteExecutor.execute(PostgreSQLComExecuteExecutor.java:56)
	at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:99)
	at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:72)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)

解决

关键报错是ERROR: relation "hibernate_sequence" does not exist，说明程序没有使用我的主键策略
检查了下主键注解，实体类的底层抽象类，主键策略配置的事AUTO
将主键注解改为 IDENTIFIFY 后解决，不报这个错误了
对于一个项目，建议将公共字段作为抽象类或接口，保持统一性，主键全部使用ID字段，这样也方便以后的修改，像这次，只需要修改抽象类即可，不需要改动所有实体类

	/**
	 * 主键
	 */
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "id")
	private Long id;

报错二：不分库的表

报错代码

部分表不需要分库分表，单独在一个库里存储
已经在sharding的配置文件，设置了公共的主键生成策略
考虑到这些表不需要分库分表，就没有在sharding配置文件里对这些表配置
还是普通的hibernate的保存方法

this.saveEntity(customerInfoEO);

程序报错

insert的是测试数据，没有实际意义
会对部分字段（身份证、手机号）进行加密，使用的也是sharding，这里没有配置，没体现出来

2022-03-07 15:20:52.773  WARN 37532 --- [io-10010-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 23502
2022-03-07 15:20:52.773 ERROR 37532 --- [io-10010-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: null value in column "id" of relation "customer_info" violates not-null constraint
  详细：Failing row contains (null, 镜湖社区, 张三二, 17689789097, 2022-03-07 00:00:00, 2022-04-07 00:00:00, 1,M, 0, 108340, 2022-03-07 15:20:52.643, 108340, 2022-03-07 15:20:52.643, null, 0, null, null, null, null, 340000,340200,340202).
2022-03-07 15:20:52.947 ERROR 37532 --- [io-10010-exec-6] c.l.c.b.controller.LSControllerAdvice    : 程序已知异常捕获：could not execute statement; SQL [n/a]; constraint [id" of relation "customer_info]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

cn.lonsun.core.exception.FormatedException: cn.lonsun.core.exception.BaseRunTimeException : null
	at cn.lonsun.digitalvillage.customer.controller.CustomerInfoController.saveCustomerInfo(CustomerInfoController.java:110) ~[classes/:na]
	at cn.lonsun.digitalvillage.customer.controller.CustomerInfoController$$FastClassBySpringCGLIB$$4942ea3a.invoke(<generated>) ~[classes/:na]
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.3.jar:5.3.3]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.3.jar:5.3.3]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.3.jar:5.3.3]
	at cn.lonsun.core.base.logger.LogAspect.logAround(LogAspect.java:138) ~[ls-framework-core-4.0.5-SNAPSHOT.jar:na]
	at cn.lonsun.digitalvillage.customer.controller.CustomerInfoController$$EnhancerBySpringCGLIB$$abb9989b.saveCustomerInfo(<generated>) ~[classes/:na]
	at cn.lonsun.core.base.filter.BaseFilter.doFilter(BaseFilter.java:100) ~[ls-framework-core-4.0.5-SNAPSHOT.jar:na]

sharding中间件报错

[INFO ] 2022-03-07 15:41:11.114 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: insert into customer_info (create_date, create_organ_id, create_user_id, update_date, update_user_id, record_status, collect_date_limit, customer_contacts, customer_end_time, customer_name, customer_phone, customer_start_time, customer_term, customer_type, org_code, org_id, recurrent_collect_limit, remark) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
RETURNING *
[INFO ] 2022-03-07 15:41:11.114 [Connection-1-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLInsertStatement(withSegment=Optional.empty)
[INFO ] 2022-03-07 15:41:11.114 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds ::: insert into customer_info (create_date, create_organ_id, create_user_id, update_date, update_user_id, record_status, collect_date_limit, customer_contacts, customer_end_time, customer_name, customer_phone, customer_start_time, customer_term, customer_type, org_code, org_id, recurrent_collect_limit, remark) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
RETURNING * ::: [2022-03-07 15:41:11.156+08, null, 108340, 2022-03-07 15:41:11.156+08, 108340, 0, null, 张三二, 2022-04-07 00:00:00+08, 镜湖社区, 17689789097, 2022-03-07 00:00:00+08, 1,M, 0, 340000,340200,340202, null, null, null]
[ERROR] 2022-03-07 15:41:11.124 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur: 
org.postgresql.util.PSQLException: ERROR: null value in column "id" of relation "customer_info" violates not-null constraint
  Detail: Failing row contains (null, 镜湖社区, 张三二, 17689789097, 2022-03-07 00:00:00, 2022-04-07 00:00:00, 1,M, 0, 108340, 2022-03-07 15:41:11.156, 108340, 2022-03-07 15:41:11.156, null, 0, null, null, null, null, 340000,340200,340202).
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:155)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyPreparedStatementExecutorCallback.execute(ProxyPreparedStatementExecutorCallback.java:41)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:73)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:66)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:44)
	at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)
	at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)
	at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101)
	at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:87)
	at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:81)
	at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:71)
	at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:155)
	at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:126)
	at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:118)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:127)
	at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.JDBCPortal.bind(JDBCPortal.java:106)
	at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.bind.PostgreSQLComBindExecutor.execute(PostgreSQLComBindExecutor.java:54)
	at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.PostgreSQLAggregatedCommandExecutor.execute(PostgreSQLAggregatedCommandExecutor.java:41)
	at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:96)
	at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:69)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)

解决

主要报错是ERROR: null value in column "id" of relation "customer_info" violates not-null constraint，就是因为没有自动生成主键导致插入报错
我尝试了5.0 和 5.1 两个版本，都不行，于是用我下载的源码本地调试
一开始是使用我的应用程序，去调取sharding进行调试，后面感觉这样好麻烦，有点傻
因为sharding本身就是伪装成一个数据库连接，我使用Navicat对我的sharding建立数据库连接，直接在里面执行插入SQL，效果和程序一样
进行了源码调试，发现在配置文件里配置了的表，默认的主键策略都会生效，没有在配置文件里出现的表，都没有生效 isGenerate fasle
我尝试把不分库的表也配置进去，发现可以了
以前一直以为，不分库的表，不在配置文件里，默认主键策略也会生效的，因为实际上sharding也读取到了这些表的信息。不知道是不是我的配置有问题，现在没在配置文件里的表，主键策略就是没生效
这样我需要把我的所有表，无论是否需要分库，全部配置到配置文件里，果然都可以了
下面是部分配置

rules:
  - !SHARDING
    tables:
      # 需要分库的表，根据租户id分库
      cms_basic_info:
        actualDataNodes: ds_${0..3}.cms_basic_info
      cms_column:
        actualDataNodes: ds_${0..3}.cms_column
      cms_content:
        actualDataNodes: ds_${0..3}.cms_content
      # 不需要分库分表的表，全部存储在 ds 数据源
      auth_cfg_catalog_data_permission:
        actualDataNodes: ds.auth_cfg_catalog_data_permission
        databaseStrategy: 
          none:
      auth_cfg_column_data_permission:
        actualDataNodes: ds.auth_cfg_column_data_permission
        databaseStrategy:
          none:
      auth_cfg_data_permission:
        actualDataNodes: ds.auth_cfg_data_permission
        databaseStrategy: 
          none:
    # 默认分库策略
    defaultDatabaseStrategy:
      standard:
        shardingColumn: customer_id  #分库字段
        shardingAlgorithmName:  customer_id_inline #分库规则:
    defaultTableStrategy:
      none:
    # 默认主键策略
    defaultKeyGenerateStrategy:
      column: id
      keyGeneratorName: snowflake
    # 分片算法
    shardingAlgorithms:
      customer_id_inline:
        type: CLASS_BASED
        props:
          strategy: standard
          algorithmClassName: cn.lonsun.dv.DigitalVillageShardingAlgorithm
    # 主键生成策略
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
        props:
          worker-id: 123

坚持是一种态度

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
shardingshere-proxy分库分表使用时，主键设置不生效为null的报错处理

报错一：分库的表relation "hibernate_sequence" does not exist 报错二：不分库的表null value in column "id" of relation "customer_info"SaaS平台项目，需要根据租户分库，使用ShardingSphere-proxy 版本5.1项目采用Spring Cloud微服务架构，持久层使用hibernate，版本 5.4 数据库Postgres QL 13.5 普通的JPA保存方法，代码没有变动，原本可以正常运行没有报错
复制链接

扫一扫