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

When using the getGeneratedKeys() method, incorrent data is returned (depeneds on version)

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.0, 1.1.1, 1.1.2
    • Fix Version/s: 1.1.3
    • Component/s: None
    • Labels:
      None
    • Environment:
      Server: MySQL 5.0.26, running on SLES10 SP4
      Client: Windows 7, running Java 1.6.26, MariaDB version 1.1.1 OR 1.1.2

      Description

      The incorrect data is returned when executing a prepared statement (using the Statement.RETURN_GENERATED_KEYS flag) which inserts several new rows into a table with an auto Increment column.

      For version 1.1.0:
      Invoking the getGeneratedKeys() method after the execute() method will yield a result set with the correct number of records, however the values in the returned records are all equal to the first index of the inserted rows. For example, if the statement triggered the creation of 3 records, with indexes 1,2,3, the getGeneratedKeys() method would return a result set with 3 records, each containing the value "1".

      For version 1.1.1/1.1.2:
      When invoking the getGeneratedKeys() method after the execute() method will yield a result set with a single record, which contains the value of the first new index created. For example, if the statement triggered the creation of 3 records, with indexes 1,2,3, the getGeneratedKeys() method would return a result set with 1 record containing the value "1".

      The expected result would be to receive a result set where the number of records is equal to the number of new keys generated, and that the values for these records will be the actual inserted keys.

        Gliffy Diagrams

          Activity

          Hide
          wlad Vladislav Vaintroub added a comment -

          Could you attach a small but complete code example to illustrate your point? Single java class with main() is ok, but JUnit format is the best, since it will also illustrate what you're actually expecting with assertXXX.

          If you're talking about multivalued inserts, MySQL cannot return all autogenerated keys for all values. Because it is not sent with the protocol.

          Show
          wlad Vladislav Vaintroub added a comment - Could you attach a small but complete code example to illustrate your point? Single java class with main() is ok, but JUnit format is the best, since it will also illustrate what you're actually expecting with assertXXX. If you're talking about multivalued inserts, MySQL cannot return all autogenerated keys for all values. Because it is not sent with the protocol.
          Hide
          amir@csl-int.com Amir Glaser added a comment -

          I am not sure I will have time to create an example, but I'll try. The scenario can be very easily reproduced when using the MariaDB Java client to insert multiple rows into a table that has an AutoIncrement index column.

          When you say "multivalued" inserts - What do you mean? In my use-case, the table to which I am inserting only has one column which is defined as autoIncrement and it is also the primary key for the table.

          Show
          amir@csl-int.com Amir Glaser added a comment - I am not sure I will have time to create an example, but I'll try. The scenario can be very easily reproduced when using the MariaDB Java client to insert multiple rows into a table that has an AutoIncrement index column. When you say "multivalued" inserts - What do you mean? In my use-case, the table to which I am inserting only has one column which is defined as autoIncrement and it is also the primary key for the table.
          Hide
          wlad Vladislav Vaintroub added a comment -

          Maybe those are multi-row inserts, I forgot the correct name. single insert-multiple rows

          Like this

          Insert into t values (1),(2),(3)

          Because you're talking about insert that generated multiple autoincrement values, so I was wondering what kind of insert that is. Except the above, there is also insert-select

          insert into t select * from t2

          however this , just like the previous example, won't return all autogenerated ids.

          So, I'm really intrigued by the test case you'll hopefully attach.

          Thanks!

          Show
          wlad Vladislav Vaintroub added a comment - Maybe those are multi-row inserts, I forgot the correct name. single insert-multiple rows Like this Insert into t values (1),(2),(3) Because you're talking about insert that generated multiple autoincrement values, so I was wondering what kind of insert that is. Except the above, there is also insert-select insert into t select * from t2 however this , just like the previous example, won't return all autogenerated ids. So, I'm really intrigued by the test case you'll hopefully attach. Thanks!
          Hide
          amir@csl-int.com Amir Glaser added a comment -

          We recently moved from working the Connector/J to MariaDB. I know that when we were working with Connector/J, we were able to retrieve multiple generated IDs.

          Show
          amir@csl-int.com Amir Glaser added a comment - We recently moved from working the Connector/J to MariaDB. I know that when we were working with Connector/J, we were able to retrieve multiple generated IDs.
          Hide
          wlad Vladislav Vaintroub added a comment -

          Maybe. Anyway, to understand and to check what worked there and does not work here, I would need a code example, because I do not understand the problem.
          Thanks!

          Show
          wlad Vladislav Vaintroub added a comment - Maybe. Anyway, to understand and to check what worked there and does not work here, I would need a code example, because I do not understand the problem. Thanks!
          Hide
          amir@csl-int.com Amir Glaser added a comment -

          import java.sql.Connection;
          import java.sql.PreparedStatement;
          import java.sql.ResultSet;
          import java.sql.Statement;
          import java.util.ArrayList;
          import java.util.List;

          import org.apache.commons.dbcp.BasicDataSource;

          public class GeneratedKeys {

          public static void main(String[] args)

          { String query = "INSERT INTO DEMO (value) VALUES (?),(?),(?)"; List<String> queryParms = new ArrayList<String>(); queryParms.add("1"); queryParms.add("2"); queryParms.add("3"); GeneratedKeys k = new GeneratedKeys(); //Connect and execute with the connector: // List<Integer> keys = k.executeQuery("org.mariadb.jdbc.Driver", query, queryParms); List<Integer> keys = k.executeQuery("org.gjt.mm.mysql.Driver", query, queryParms); System.out.println(""); System.out.println("****** Results ********"); System.out.println("Generated Keys: "+keys.toString()); }

          public List<Integer> executeQuery(String driverName,String query, List<String> queryParms){

          String serverName = "127.0.0.1";
          String dbName = "DEMO";
          String mysqlConnectionParams = "?useJvmCharsetConverters=true"
          + "&autoReconnect=true"
          + "&noDatetimeStringSync=true";

          BasicDataSource DataSource = new BasicDataSource();
          DataSource.setDriverClassName(driverName);
          DataSource.setUsername("waveDB");
          DataSource.setPassword("1179sia");
          DataSource.setUrl("jdbc:mysql://" + serverName + "/" + dbName
          + mysqlConnectionParams);
          try{

          //Connect to the DB
          System.out.println("Connecting to DB using connector: "driverName"...");
          Connection conn = DataSource.getConnection();

          //Execute the query
          System.out.println("Executing Query...");
          PreparedStatement stmt = conn.prepareStatement(query,
          Statement.RETURN_GENERATED_KEYS);
          for (int i = 0; i < queryParms.size(); ++i) {
          if (queryParms.get.getClass().equals(String.class))

          { stmt.setString(i + 1, (String) queryParms.get(i)); }

          }

          stmt.executeUpdate();
          ResultSet result = stmt.getGeneratedKeys();

          List<Integer> generatedKeys = new ArrayList<Integer>();
          while (result.next())

          { generatedKeys.add(result.getInt(1)); }

          conn.close();
          stmt.close();
          result.close();

          return generatedKeys;
          }
          catch(Exception e)

          { e.printStackTrace(); }

          finally{
          try

          { System.out.println("Disconnecting from DB..."); DataSource.close(); }

          catch (Exception ee)

          { ee.printStackTrace(); }

          }

          return new ArrayList<Integer>();
          }
          }

          Show
          amir@csl-int.com Amir Glaser added a comment - import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import org.apache.commons.dbcp.BasicDataSource; public class GeneratedKeys { public static void main(String[] args) { String query = "INSERT INTO DEMO (value) VALUES (?),(?),(?)"; List<String> queryParms = new ArrayList<String>(); queryParms.add("1"); queryParms.add("2"); queryParms.add("3"); GeneratedKeys k = new GeneratedKeys(); //Connect and execute with the connector: // List<Integer> keys = k.executeQuery("org.mariadb.jdbc.Driver", query, queryParms); List<Integer> keys = k.executeQuery("org.gjt.mm.mysql.Driver", query, queryParms); System.out.println(""); System.out.println("****** Results ********"); System.out.println("Generated Keys: "+keys.toString()); } public List<Integer> executeQuery(String driverName,String query, List<String> queryParms){ String serverName = "127.0.0.1"; String dbName = "DEMO"; String mysqlConnectionParams = "?useJvmCharsetConverters=true" + "&autoReconnect=true" + "&noDatetimeStringSync=true"; BasicDataSource DataSource = new BasicDataSource(); DataSource.setDriverClassName(driverName); DataSource.setUsername("waveDB"); DataSource.setPassword("1179sia"); DataSource.setUrl("jdbc:mysql://" + serverName + "/" + dbName + mysqlConnectionParams); try{ //Connect to the DB System.out.println("Connecting to DB using connector: " driverName "..."); Connection conn = DataSource.getConnection(); //Execute the query System.out.println("Executing Query..."); PreparedStatement stmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); for (int i = 0; i < queryParms.size(); ++i) { if (queryParms.get .getClass().equals(String.class)) { stmt.setString(i + 1, (String) queryParms.get(i)); } } stmt.executeUpdate(); ResultSet result = stmt.getGeneratedKeys(); List<Integer> generatedKeys = new ArrayList<Integer>(); while (result.next()) { generatedKeys.add(result.getInt(1)); } conn.close(); stmt.close(); result.close(); return generatedKeys; } catch(Exception e) { e.printStackTrace(); } finally{ try { System.out.println("Disconnecting from DB..."); DataSource.close(); } catch (Exception ee) { ee.printStackTrace(); } } return new ArrayList<Integer>(); } }
          Hide
          amir@csl-int.com Amir Glaser added a comment -

          Just added a source code example.

          In the example, I'm using the BasicDataSource class (from apache commons). The table named "DEMO" which I'm inserting in is defined using the following SQL:

          DROP TABLE IF EXISTS `DEMO`.`DEMO`;
          CREATE TABLE `DEMO`.`DEMO` (
          `index` int(10) unsigned NOT NULL AUTO_INCREMENT,
          `value` varchar(45) NOT NULL,
          PRIMARY KEY (`index`)
          ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1;

          Show
          amir@csl-int.com Amir Glaser added a comment - Just added a source code example. In the example, I'm using the BasicDataSource class (from apache commons). The table named "DEMO" which I'm inserting in is defined using the following SQL: DROP TABLE IF EXISTS `DEMO`.`DEMO`; CREATE TABLE `DEMO`.`DEMO` ( `index` int(10) unsigned NOT NULL AUTO_INCREMENT, `value` varchar(45) NOT NULL, PRIMARY KEY (`index`) ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1;
          Hide
          amir@csl-int.com Amir Glaser added a comment -

          I tried to run this code having the mariaDB JAR in the classpath and using driver name: org.mariadb.jdbc.Driver - The resulting list contains one element (instead of 3)
          I tried to run this code with the ConnectorJ JAR in the classpath and using driver name: org.gjt.mm.mysql.Driver - The resulting list contains 3 elements, with the 3 indexes created.

          By the way, If I switch the MariaDB JAR in the classpath to the 1.1.0 version, the resulting list will have 3 elements, but the values are wrong.

          Show
          amir@csl-int.com Amir Glaser added a comment - I tried to run this code having the mariaDB JAR in the classpath and using driver name: org.mariadb.jdbc.Driver - The resulting list contains one element (instead of 3) I tried to run this code with the ConnectorJ JAR in the classpath and using driver name: org.gjt.mm.mysql.Driver - The resulting list contains 3 elements, with the 3 indexes created. By the way, If I switch the MariaDB JAR in the classpath to the 1.1.0 version, the resulting list will have 3 elements, but the values are wrong.
          Hide
          wlad Vladislav Vaintroub added a comment -

          Indeed, ConnectorJ outsmarted the protocol. so we need to keep up

          Show
          wlad Vladislav Vaintroub added a comment - Indeed, ConnectorJ outsmarted the protocol. so we need to keep up

            People

            • Assignee:
              wlad Vladislav Vaintroub
              Reporter:
              amir@csl-int.com Amir Glaser
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 6 hours
                6h