Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

insert with generated keys,when the last column value is null,cause NullPointerException #2252

Closed
zhangfenghuang opened this issue Apr 23, 2019 · 9 comments · Fixed by #2322

Comments

@zhangfenghuang
Copy link

zhangfenghuang commented Apr 23, 2019

sharding-jdbc 4.0.0-RC1

<sharding:key-generator id="xxxxxx" type="SNOWFLAKE" column="id" />
useGeneratedKeys,when insert sql the last column value is null,cause NullPointerException
when update the last column,success!
exception:

Caused by: java.lang.NullPointerException
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.isQualifiedInsertOptimizeResult(StandardRoutingEngine.java:221)
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.reviseInsertOptimizeResult(StandardRoutingEngine.java:211)
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.routeByShardingConditionsWithCondition(StandardRoutingEngine.java:110)
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.routeByShardingConditions(StandardRoutingEngine.java:102)
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.getDataNodes(StandardRoutingEngine.java:87)
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.route(StandardRoutingEngine.java:69)
at org.apache.shardingsphere.core.route.router.sharding.ParsingSQLRouter.route(ParsingSQLRouter.java:106)
at org.apache.shardingsphere.core.route.PreparedStatementRoutingEngine.route(PreparedStatementRoutingEngine.java:66)
at org.apache.shardingsphere.core.PreparedQueryShardingEngine.route(PreparedQueryShardingEngine.java:60)
at org.apache.shardingsphere.core.BaseShardingEngine.shard(BaseShardingEngine.java:64)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.shard(ShardingPreparedStatement.java:224)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:170)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:45)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
at cn.uce.core.db.interceptor.BatchUpdateExecutorInterceptor.intercept(BatchUpdateExecutorInterceptor.java:79)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy40.update(Unknown Source)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy40.update(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy40.update(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170)

@zhangfenghuang zhangfenghuang changed the title insert generatedKeys 插入,使用分布式主键,当最后一列值刚好为null时,报错NullPointerException Apr 23, 2019
@zhangfenghuang zhangfenghuang changed the title 插入,使用分布式主键,当最后一列值刚好为null时,报错NullPointerException insert with generated keys,when the last column value is null,cause NullPointerException Apr 23, 2019
@tristaZero
Copy link
Contributor

tristaZero commented Apr 24, 2019

Can you give me the INSERT SQL?

useGeneratedKeys,when insert sql the last column value is null,cause NullPointerException

Is the last column generated Key column?And is its value null?
@zhangfenghuang

@zhangfenghuang
Copy link
Author

like this:
insert into xxxxx (xxxxx, xxxxx,
xxxx, xxxx, xxxx,
xxxx, xxx, xxxx,
xxx, delete_flag, create_emp,
create_org, create_time, update_time
)
values (#{xxxx,jdbcType=VARCHAR}, #{xxxx,jdbcType=VARCHAR},
#{xxxx,jdbcType=VARCHAR}, #{xxxx,jdbcType=VARCHAR}, #{xxxx,jdbcType=VARCHAR},
#{xxx,jdbcType=VARCHAR}, #{xxxx,jdbcType=INTEGER}, #{xxx,jdbcType=SMALLINT},
#{xxx,jdbcType=VARCHAR}, #{deleteFlag,jdbcType=BIT}, #{createEmp,jdbcType=VARCHAR},
#{createOrg,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP}
)

column "updateTime" is null,cause NullPointerException
remove "updateTime“,inserted

@zhangfenghuang
Copy link
Author

Can you give me the INSERT SQL?

useGeneratedKeys,when insert sql the last column value is null,cause NullPointerException

Is the last column generated Key column?And is its value null?
@zhangfenghuang

No,generated column is ”id“, insert sql not contains ”id“

@tristaZero
Copy link
Contributor

I did the following test, but no exception happened.
ShardingRule Configuration

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id

Table structure

CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SQL

insert into t_order values(1,null), (2,2);

Do you think this example fits your scenario?

By the way, can you set the props sql.show=true? So that we can view log to check the rewritten SQLs are correct or not.

@tristaZero
Copy link
Contributor

@zhangfenghuang

@zhangfenghuang
Copy link
Author

spring-datasource.xml:
<sharding:inline-strategy id="databaseStrategy" sharding-column="id" algorithm-expression="dataSource${id % 2}" />
<sharding:inline-strategy id="fileTableStrategy" sharding-column="id" algorithm-expression="file${id % 2}" />

<sharding:key-generator id="fileKeyGenerator" type="SNOWFLAKE" column="id" />
				

<sharding:data-source id="dataSource">
	<sharding:sharding-rule
		data-source-names="dataSource0, dataSource1" default-data-source-name="dataSource0" >
		<sharding:table-rules>
			<sharding:table-rule logic-table="file"
				actual-data-nodes="dataSource${0..1}.file${0..1}"
				database-strategy-ref="databaseStrategy"
				key-generator-ref="fileKeyGenerator"
				table-strategy-ref="fileTableStrategy" />
		</sharding:table-rules>
	</sharding:sharding-rule>
	<sharding:props>
		<prop key="sql.show">true</prop>
	</sharding:props>
</sharding:data-source>

fileMapper.xml:

insert into file (orig_file_name, group_name,
save_path, biz_key_id, biz_type,
file_type, file_size, encrypt_version,
save_type, delete_flag, create_emp,
create_org, create_time,update_time
)
values (#{origFileName,jdbcType=VARCHAR}, #{groupName,jdbcType=VARCHAR},
#{savePath,jdbcType=VARCHAR}, #{bizKeyId,jdbcType=VARCHAR}, #{bizType,jdbcType=VARCHAR},
#{fileType,jdbcType=VARCHAR}, #{fileSize,jdbcType=INTEGER}, #{encryptVersion,jdbcType=SMALLINT},
#{saveType,jdbcType=VARCHAR}, #{deleteFlag,jdbcType=BIT}, #{createEmp,jdbcType=VARCHAR},
#{createOrg,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP}
)

log:
java.lang.RuntimeException: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: java.lang.NullPointerException

The error may involve com.demo.file.common.dao.IFileDao.insert-Inline

The error occurred while setting parameters

SQL: insert into file (orig_file_name, group_name, save_path, biz_key_id, biz_type, file_type, file_size, encrypt_version, save_type, delete_flag, create_emp, create_org, create_time,update_time ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

Cause: java.lang.NullPointerException

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: java.lang.NullPointerException

The error may involve com.demo.file.common.dao.IFileDao.insert-Inline

The error occurred while setting parameters

SQL: insert into file (orig_file_name, group_name, save_path, biz_key_id, biz_type, file_type, file_size, encrypt_version, save_type, delete_flag, create_emp, create_org, create_time,update_time ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

Cause: java.lang.NullPointerException

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:76)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:421)
at com.sun.proxy.$Proxy28.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:254)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:52)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
at com.sun.proxy.$Proxy31.insert(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at com.demo.core.db.interceptor.aop.QueryPaginationAspect.invoke(QueryPaginationAspect.java:72)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy32.insert(Unknown Source)
at com.demo.file.common.biz.FileBiz.saveFile(FileBiz.java:77)
at com.alibaba.dubbo.common.bytecode.Wrapper2.invokeMethod(Wrapper2.java)
at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46)
at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72)
at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53)
at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:64)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:42)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:70)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:113)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:84)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:170)
at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52)
at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:82)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)

Caused by: org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: java.lang.NullPointerException

The error may involve com.demo.file.common.dao.IFileDao.insert-Inline

The error occurred while setting parameters

SQL: insert into file (orig_file_name, group_name, save_path, biz_key_id, biz_type, file_type, file_size, encrypt_version, save_type, delete_flag, create_emp, create_org, create_time,update_time ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

Cause: java.lang.NullPointerException

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:172)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:157)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:408)
... 47 more

Caused by: java.lang.NullPointerException
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.isQualifiedInsertOptimizeResult(StandardRoutingEngine.java:221)
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.reviseInsertOptimizeResult(StandardRoutingEngine.java:211)
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.routeByShardingConditionsWithCondition(StandardRoutingEngine.java:110)
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.routeByShardingConditions(StandardRoutingEngine.java:102)
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.getDataNodes(StandardRoutingEngine.java:87)
at org.apache.shardingsphere.core.route.type.standard.StandardRoutingEngine.route(StandardRoutingEngine.java:69)
at org.apache.shardingsphere.core.route.router.sharding.ParsingSQLRouter.route(ParsingSQLRouter.java:106)
at org.apache.shardingsphere.core.route.PreparedStatementRoutingEngine.route(PreparedStatementRoutingEngine.java:66)
at org.apache.shardingsphere.core.PreparedQueryShardingEngine.route(PreparedQueryShardingEngine.java:60)
at org.apache.shardingsphere.core.BaseShardingEngine.shard(BaseShardingEngine.java:64)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.shard(ShardingPreparedStatement.java:224)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:170)
at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy45.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:45)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)

@tristaZero
Copy link
Contributor

Thanks for your reply, can you provide your CREATE TABLE SQL ?
@zhangfenghuang

@tristaZero
Copy link
Contributor

@zhangfenghuang Hi, can you use SpringNameSpace + Mybatis + ShardingSphere examples to try to make your exception appeared?
I try to run the examples above, but i can not get exception.

Or maybe you can provide your demo git url, so i can run your example in my computer.

Waiting for your reply.

@tristaZero
Copy link
Contributor

@zhangfenghuang Hi, waiting, waiting, waiting...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants