Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-1208

permit bulk for INSERT ON DUPLICATE KEY UPDATE commands

Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 3.5.1
    • batch
    • None

    Description

      in order to return correct affected rows, INSERT ON DUPLICATE KEY UPDATE commands are parsed to avoid using BULK commands.

      Since MDEV-30366, server can return appropriate detailed answer. If server support MDEV-30366, Bulk must be used for INSERT ON DUPLICATE KEY UPDATE commands

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. CONJ-1216 Performance degradation on latest version

          • Major - Major loss of function.
          • Closed

          Activity

            harawata Iwao AVE! added a comment -

            Hello Diego,

            Just to clarify, in 3.5.1, returnMultiValuesGeneratedIds is no longer applied to batch insertion with ON DUPLICATE KEY UPDATE.
            Is it intentional?

            Basically, the following test passes with 3.5.0, but fails with 3.5.1.

            // NOTE: returnMultiValuesGeneratedIds is enabled
            		 
            try (Statement stmt = con.createStatement()) {
            		 
              stmt.execute("drop table if exists test");
            		 
              stmt.execute("create table test (id int auto_increment primary key, name varchar(16))");
            		 
              stmt.execute("insert into test (id, name) values (1,'name1'), (2, 'name2'), (3, 'three')");
            		 
              con.commit();
            		 
            }
            		 
            try (PreparedStatement stmt = con.prepareStatement(
            		 
                "insert into test (id, name) values (?,?) on duplicate key update name = values(name)",
            		 
                Statement.RETURN_GENERATED_KEYS)) {
            		 
              stmt.setInt(1, 1);
            		 
              stmt.setString(2, "one");
            		 
              stmt.addBatch();
            		 
             
            		 
              stmt.setNull(1, Types.INTEGER);
            		 
              stmt.setString(2, "foo");
            		 
              stmt.addBatch();
            		 
             
            		 
              int[] batchResult = stmt.executeBatch();
            		 
              assertEquals(2, batchResult.length);
            		 
              assertEquals(2, batchResult[0]);
            		 
              assertEquals(1, batchResult[1]);
            		 
             
            		 
              try (ResultSet rs = stmt.getGeneratedKeys()) {
            		 
                assertTrue(rs.next());
            		 
                assertEquals(1, rs.getInt(1));
            		 
                assertTrue(rs.next());
            		 
                assertEquals(4, rs.getInt(1));
            		 
                assertFalse(rs.next());
            		 
              }
            		 
            }

            harawata Iwao AVE! added a comment - Hello Diego, Just to clarify, in 3.5.1, returnMultiValuesGeneratedIds is no longer applied to batch insertion with ON DUPLICATE KEY UPDATE. Is it intentional? Basically, the following test passes with 3.5.0, but fails with 3.5.1. // NOTE: returnMultiValuesGeneratedIds is enabled try (Statement stmt = con.createStatement()) { stmt.execute( "drop table if exists test" ); stmt.execute( "create table test (id int auto_increment primary key, name varchar(16))" ); stmt.execute( "insert into test (id, name) values (1,'name1'), (2, 'name2'), (3, 'three')" ); con.commit(); } try (PreparedStatement stmt = con.prepareStatement( "insert into test (id, name) values (?,?) on duplicate key update name = values(name)" , Statement.RETURN_GENERATED_KEYS)) { stmt.setInt( 1 , 1 ); stmt.setString( 2 , "one" ); stmt.addBatch();   stmt.setNull( 1 , Types.INTEGER); stmt.setString( 2 , "foo" ); stmt.addBatch();   int [] batchResult = stmt.executeBatch(); assertEquals( 2 , batchResult.length); assertEquals( 2 , batchResult[ 0 ]); assertEquals( 1 , batchResult[ 1 ]);   try (ResultSet rs = stmt.getGeneratedKeys()) { assertTrue(rs.next()); assertEquals( 1 , rs.getInt( 1 )); assertTrue(rs.next()); assertEquals( 4 , rs.getInt( 1 )); assertFalse(rs.next()); } }

            People

              diego dupin Diego Dupin
              diego dupin Diego Dupin
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.