Error occurred when I try to "INSERT INTO ~ ON DUPLICATE KEY UPDATE" with useGeneratedKeys.

[ApplePedlar]

MyBatis version

3.5.1

Database vendor and version

MySQL 5.6

Test case or example project

Table scheme

CREATE TABLE `Animal` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

model (sample.Animal)

@lombok.Data
@lombok.NoArgsConstructor
@lombok.AllArgsConstructor
public class Animal {
    private long id;
    private String name;
}

mapper interface (sample.AnimalMapper)

public interface AnimalMapper {
    Animal get(long id);
    int insertUpdate(Animal animal);
}

mapper xml (sample/AnimalMapper.xml)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="sample.AnimalMapper">
  <select id="get" resultType="Animal">
    SELECT * FROM Animal WHERE id = #{id}
  </select>

  <insert id="insertUpdate" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO Animal
    SET
    id = #{id},
    name = #{name}
    ON DUPLICATE KEY UPDATE
    name = #{name}
  </insert>
</mapper>

test case (sample.AnimalMapperTest)

public class AnimalMapperTest extends AbstractTestBase {
    @Test
    public void test() {
        Animal animal = new Animal(0, "wolf");
        AnimalMapper mapper = inject(AnimalMapper.class);
        mapper.insertUpdate(animal);
        assertThat(animal.getId(), not(is(0L)));
        Animal animalFromDb = mapper.get(animal.getId());
        assertThat(animalFromDb.getId(), is(animal.getId()));
        assertThat(animalFromDb.getName(), is(animal.getName()));

        animal.setName("dog");
        mapper.insertUpdate(animal);
        animalFromDb = mapper.get(animal.getId());
        assertThat(animalFromDb.getId(), is(animal.getId()));
        assertThat(animalFromDb.getName(), is(animal.getName()));
    }
}

Steps to reproduce

run test.

Expected result

Name changes from wolf to dog.

Actual result

Error occurred.

org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: java.util.NoSuchElementException
### The error may exist in sample/AnimalMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: INSERT INTO Animal     SET     id = ?,     name = ?     ON DUPLICATE KEY UPDATE     name = ?
### Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: java.util.NoSuchElementException

	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
	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.apache.ibatis.session.SqlSessionManager$SqlSessionInterceptor.invoke(SqlSessionManager.java:357)
	at com.sun.proxy.$Proxy54.insert(Unknown Source)
	at org.apache.ibatis.session.SqlSessionManager.insert(SqlSessionManager.java:236)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
	at com.sun.proxy.$Proxy65.insertUpdate(Unknown Source)
	at sample.AnimalMapperTest.test(AnimalMapperTest.java:23)
	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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at org.junit.rules.RunRules.evaluate(RunRules.java:20)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at org.junit.rules.RunRules.evaluate(RunRules.java:20)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: java.util.NoSuchElementException
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:81)
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:64)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:51)
	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 sun.reflect.GeneratedMethodAccessor45.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
	... 46 more
Caused by: java.util.NoSuchElementException
	at java.util.AbstractList$Itr.next(AbstractList.java:364)
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.assignKeysToParam(Jdbc3KeyGenerator.java:113)
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.assignKeys(Jdbc3KeyGenerator.java:97)
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:78)
	... 60 more

---

Version 3.4.6 and 3.5.0 work fine.

Related Issues

• MyBatis 3.5.0 inconsistent handling of keyProperty #1485

[kazuki43zoo]

Hi @ApplePedlar , I cannot reproduce this. Could you provide a small reproduce executable project via GitHub?

[harawata]

Me neither (tested with MySQL 5.6.43 and Connector/J 8.0.15).
There might be some non-default config option that triggers the issue.

Here's the project I used (docker required).
https://github.com/harawata/mybatis-issues/tree/master/gh-1523
Try copying relevant settings from your my.cnf to mysql_conf/my.cnf and see if the issue reproduces.

[harawata]

The description matches this Connector/J bug.
https://bugs.mysql.com/bug.php?id=90688
I'm not sure why the MyBatis test does not reproduce the problem.

UPDATE:
The issue with MySQL Connector/J reproduces only when PreparedStatement#executeUpdate() is used.
MyBatis uses PreparedStatement#execute() and the driver returns only one generated key in this case.

[ApplePedlar]

Thank you for creating a test project.

The bug was reproduced using mariadb-connector.
https://github.com/ApplePedlar/mybatis-issues/tree/gh-1523-reproduced

---

09:55:29.068 [main] DEBUG test.Mapper.insertUpdate - ==>  Preparing: INSERT INTO Animal SET id = ?, name = ? ON DUPLICATE KEY UPDATE name = ? 
09:55:29.134 [main] DEBUG test.Mapper.insertUpdate - ==> Parameters: 0(Long), wolf(String), wolf(String)
09:55:29.137 [main] DEBUG test.Mapper.insertUpdate - <==    Updates: 1
09:55:29.149 [main] DEBUG test.Mapper.get - ==>  Preparing: SELECT * FROM Animal WHERE id = ? 
09:55:29.150 [main] DEBUG test.Mapper.get - ==> Parameters: 5(Long)
09:55:29.220 [main] TRACE test.Mapper.get - <==    Columns: id, name
09:55:29.220 [main] TRACE test.Mapper.get - <==        Row: 5, wolf
09:55:29.222 [main] DEBUG test.Mapper.get - <==      Total: 1
09:55:29.227 [main] DEBUG test.Mapper.insertUpdate - ==>  Preparing: INSERT INTO Animal SET id = ?, name = ? ON DUPLICATE KEY UPDATE name = ? 
09:55:29.228 [main] DEBUG test.Mapper.insertUpdate - ==> Parameters: 5(Long), dog(String), dog(String)
09:55:29.229 [main] DEBUG test.Mapper.insertUpdate - <==    Updates: 2

org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: java.util.NoSuchElementException
### The error may exist in test/Mapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: INSERT INTO Animal     SET     id = ?,     name = ?     ON DUPLICATE KEY UPDATE     name = ?
### Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: java.util.NoSuchElementException

	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
	at com.sun.proxy.$Proxy10.insertUpdate(Unknown Source)
	at test.SimpleTest.test(SimpleTest.java:59)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:564)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:628)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:117)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:184)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:180)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:127)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:68)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1378)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1378)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)
	at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)
	at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:128)
	at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:69)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: java.util.NoSuchElementException
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:81)
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:64)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:51)
	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:197)
	... 56 more
Caused by: java.util.NoSuchElementException
	at java.base/java.util.Arrays$ArrayItr.next(Arrays.java:4430)
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.assignKeysToParam(Jdbc3KeyGenerator.java:113)
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.assignKeys(Jdbc3KeyGenerator.java:97)
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:78)
	... 63 more


Process finished with exit code 255

[harawata]

Thank you for the update, @ApplePedlar !

I could verify the problem.
The error occurs because java.sql.Statement#getGeneratedKeys() returns two rows even though the statement updated one row.
Although it was silently ignored in previous versions, throwing an exception may be the right behavior in this case. I'll submit a PR that improves the error message later.

As it's the driver's bug, I reported it to MariaDB JIRA.
https://jira.mariadb.org/browse/CONJ-698

[harawata]

Hi @ApplePedlar ,
I have committed the error handling improvement.
Please try the snapshot and let us know if there is any problem.
Thanks again!

p.s.
Looking at the driver's code, it might need a major change to fix the bug.