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

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

1485

Comment From: kazuki43zoo

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

Comment From: 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.

Comment From: 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.

Comment From: 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

Comment From: 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

Comment From: 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.

Comment From: harawata

This is just a heads up.

This particular issue (CONJ-698) will be resolved in MariaDB Connector/J 2.5.0. However, the fix (CONJ-706) may affect getGeneratedKeys() behavior in general.

As a result, with MariaDB Connector/J 2.5.0, useGeneratedKeys will no longer work with a typical multi-row insert statement like the following (i.e. only the first item in the list will have the generated id).

<insert id="insertUpdate" useGeneratedKeys="true" keyProperty="id">
  INSERT INTO Animal (name) VALUES
  <foreach var="animal" items="list" separator=",">
    (#{animal.name})
  </foreach>
</insert>

[EDIT]

The behavior change mentioned above was 'corrected' as a regression in MariaDB Connector/J 2.5.1 (CONJ-735).
And then, it stopped working again in 3.0.x.
And in MariaDB Connector/J 3.3.2, a new connection property returnMultiValuesGeneratedIds was added. See CONJ-1117.
MariaDB Connector/J 3.5.1 (with MariaDB Server version 11.5.1+) now returns only the generated keys. See CONJ-1208. This might allow MyBatis to support the usage properly (with a corner case or two).

Comment From: harawata

MariaDB Java Connector 2.5.0 is released. https://github.com/MariaDB/mariadb-connector-j/releases/tag/2.5.0 And, with this latest driver, @ApplePedlar 's test passed.

Comment From: magicliang

I have a similar issue, which can be reproduced under these conditions:

mysql-connector-java: 5.1.40 mybatis: 3.5.5

<insert id="insertShopInfo" parameterType="com.mycompany.po.ShopInfo"
        useGeneratedKeys="true" keyProperty="id">
    insert into shop_info
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            id,
        </if>
        <if test="poiId != null">
            poi_id,
        </if>
        <if test="bizAccountId != null">
            biz_account_id,
        </if>
        <if test="userType != null">
            user_type,
        </if>
        <if test="pointName != null">
            point_name,
        </if>
        <if test="brandName != null">
            brand_name,
        </if>
        <if test="district != null">
            district,
        </if>
        <if test="brandId != null">
            brand_id,
        </if>
        <if test="typeId != null">
            type_id,
        </if>
        <if test="bareaId != null">
            barea_id,
        </if>
        <if test="locationId != null">
            location_id,
        </if>
        <if test="address != null">
            address,
        </if>
        <if test="phone != null">
            phone,
        </if>
        <if test="mergeId != null">
            merge_id,
        </if>
        <if test="bareaName != null">
            barea_name,
        </if>
        <if test="typeName != null">
            type_name,
        </if>
        <if test="openInfo != null">
            open_info,
        </if>
        <if test="pricePerson != null">
            price_person,
        </if>
        <if test="typeHierarchy != null">
            type_hierarchy,
        </if>
        <if test="isForeign != null">
            is_foreign,
        </if>
        <if test="geo != null">
            geo,
        </if>
        <if test="status != null">
            status,
        </if>
        <if test="createTime != null">
            create_time,
        </if>
        <if test="modifyTime != null">
            modify_time,
        </if>
        <if test="cityLocationId != null">
            city_location_id,
        </if>
        <if test="cityName != null">
            city_name,
        </if>
        <if test="provinceName != null">
            province_name,
        </if>
        <if test="provinceId != null">
            province_id,
        </if>
        <if test="email != null">
            email,
        </if>
        <if test="hasEat != null">
            has_eat,
        </if>
        <if test="dispatchTypes != null">
            dispatch_types,
        </if>
        <if test="poiType != null">
            poi_type,
        </if>
        <if test="locationName != null">
            location_name,
        </if>
        <if test="encryptWay != null">
            encrypt_way,
        </if>
        <if test="phoneToken != null">
            phone_token,
        </if>
        <if test="tokenEncryptedPhone != null">
            token_encrypted_phone,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="id != null">
            #{id,jdbcType=BIGINT},
        </if>
        <if test="poiId != null">
            #{poiId,jdbcType=BIGINT},
        </if>
        <if test="bizAccountId != null">
            #{bizAccountId,jdbcType=BIGINT},
        </if>
        <if test="userType != null">
            #{userType,jdbcType=INTEGER},
        </if>
        <if test="pointName != null">
            #{pointName,jdbcType=VARCHAR},
        </if>
        <if test="brandName != null">
            #{brandName,jdbcType=VARCHAR},
        </if>
        <if test="district != null">
            #{district,jdbcType=VARCHAR},
        </if>
        <if test="brandId != null">
            #{brandId,jdbcType=INTEGER},
        </if>
        <if test="typeId != null">
            #{typeId,jdbcType=INTEGER},
        </if>
        <if test="bareaId != null">
            #{bareaId,jdbcType=INTEGER},
        </if>
        <if test="locationId != null">
            #{locationId,jdbcType=INTEGER},
        </if>
        <if test="address != null">
            #{address,jdbcType=VARCHAR},
        </if>
        <if test="phone != null">
            #{phone,jdbcType=VARCHAR},
        </if>
        <if test="mergeId != null">
            #{mergeId,jdbcType=INTEGER},
        </if>
        <if test="bareaName != null">
            #{bareaName,jdbcType=VARCHAR},
        </if>
        <if test="typeName != null">
            #{typeName,jdbcType=VARCHAR},
        </if>
        <if test="openInfo != null">
            #{openInfo,jdbcType=VARCHAR},
        </if>
        <if test="pricePerson != null">
            #{pricePerson,jdbcType=INTEGER},
        </if>
        <if test="typeHierarchy != null">
            #{typeHierarchy,jdbcType=VARCHAR},
        </if>
        <if test="isForeign != null">
            #{isForeign,jdbcType=INTEGER},
        </if>
        <if test="geo != null">
            #{geo,jdbcType=VARCHAR},
        </if>
        <if test="status != null">
            #{status,jdbcType=INTEGER},
        </if>
        <if test="createTime != null">
            #{createTime,jdbcType=TIMESTAMP},
        </if>
        <if test="modifyTime != null">
            #{modifyTime,jdbcType=TIMESTAMP},
        </if>
        <if test="cityLocationId != null">
            #{cityLocationId,jdbcType=INTEGER},
        </if>
        <if test="cityName != null">
            #{cityName,jdbcType=VARCHAR},
        </if>
        <if test="provinceName != null">
            #{provinceName,jdbcType=VARCHAR},
        </if>
        <if test="provinceId != null">
            #{provinceId,jdbcType=INTEGER},
        </if>
        <if test="email != null">
            #{email,jdbcType=VARCHAR},
        </if>
        <if test="hasEat != null">
            #{hasEat,jdbcType=BIT},
        </if>
        <if test="dispatchTypes != null">
            #{dispatchTypes,jdbcType=VARCHAR},
        </if>
        <if test="poiType != null">
            #{poiType,jdbcType=INTEGER},
        </if>
        <if test="locationName != null">
            #{locationName,jdbcType=VARCHAR},
        </if>
        <if test="encryptWay != null">
            #{encryptWay,jdbcType=INTEGER},
        </if>
        <if test="phoneToken != null">
            #{phoneToken,jdbcType=VARCHAR},
        </if>
        <if test="tokenEncryptedPhone != null">
            #{tokenEncryptedPhone,jdbcType=VARCHAR},
        </if>
    </trim>
    ON DUPLICATE KEY UPDATE
    address = VALUES(address),
    point_name = VALUES(point_name),
    location_id = VALUES(location_id),
    phone = VALUES(phone),
    type_name = VALUES(type_name),
    email =VALUES(email),
    has_eat = VALUES(has_eat),
    dispatch_types = VALUES(dispatch_types),
    modify_time = VALUES(modify_time),
    encrypt_Way = VALUES(encrypt_way),
    phone_token = VALUES(phone_token),
    token_encrypted_phone = VALUES(token_encrypted_phone)
</insert>

shopInfoExtMapper.insertShopInfo(shopInfo)

The error stacktrace is：

Caused by: org.apache.ibatis.executor.ExecutorException: Too many keys are generated. There are only 1 target objects. You either specified a wrong 'keyProperty' or encountered a driver bug like #1523. at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.assignKeysToParam(Jdbc3KeyGenerator.java:121) at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.assignKeys(Jdbc3KeyGenerator.java:104) at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:85) ... 56 more

I know this issue is closed, but this is only lead I can found on Github. I can open a new issue if necessary.

Comment From: harawata

Hello @magicliang , We may need to reproduce the problem. Please create and share a complete project like this one on your GitHub repo. And what is the DB version?

Comment From: magicliang

Hello @magicliang , We may need to reproduce the problem. Please create and share a complete project like this one on your GitHub repo. And what is the DB version?

Thanks for replying.

Our DB version is 5.7.21-21-log(but it is a percona version, not normal community version).

It is very hard to provide a complete demo to reproduce this problem, because we are using a non-open-sourcing technical stack which uses non-standar jdbc middleware.

After some digging, we think the root cause of this case is insertion with "update on duplicate key" creates 2 affected rows, which generates 2 auto_increment ids. I will try to prove it and make a simple demo later. Let's see what happens then.

Comment From: anhao

Hello @magicliang , We may need to reproduce the problem. Please create and share a complete project like this one on your GitHub repo. And what is the DB version?

Thanks for replying.

Our DB version is 5.7.21-21-log(but it is a percona version, not normal community version).

It is very hard to provide a complete demo to reproduce this problem, because we are using a non-open-sourcing technical stack which uses non-standar jdbc middleware.

After some digging, we think the root cause of this case is insertion with "update on duplicate key" creates 2 affected rows, which generates 2 auto_increment ids. I will try to prove it and make a simple demo later. Let's see what happens then.

Hello @magicliang , We may need to reproduce the problem. Please create and share a complete project like this one on your GitHub repo. And what is the DB version?

Thanks for replying.

Our DB version is 5.7.21-21-log(but it is a percona version, not normal community version).

It is very hard to provide a complete demo to reproduce this problem, because we are using a non-open-sourcing technical stack which uses non-standar jdbc middleware.

After some digging, we think the root cause of this case is insertion with "update on duplicate key" creates 2 affected rows, which generates 2 auto_increment ids. I will try to prove it and make a simple demo later. Let's see what happens then.

I have also encountered this problem. Has this problem been solved?

Comment From: harawata

Hello @anhao , It's unclear which issue you are referring to. If the issue is reproducible with the latest version of MyBatis (=3.5.6 at the moment), please create a new issue and fill out the bug report form in the template. Be sure to include 1) the versions of DB, driver and MyBatis you are using and 2) a demo project or steps to reproduce the problem.

Comment From: harawata

I'm going to lock this issue.

Read my earlier comment explaining the flip-flops in MariaDB Connector/J. If you still think there is a bug in MyBatis (and not in the driver), please create a new issue and fill in the bug report form in the issue template.

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

MyBatis version

Database vendor and version

Test case or example project

Steps to reproduce

Expected result

Actual result

Related Issues

1485