Bug #41657 Type of "mediumint unsigned" in getGeneratedKeys is Long

• Submitted: 20 Dec 2008 15:17
• Modified: 10 May 2016 15:49
• Reporter: Firstname Lastname
• Status: Closed
• Category: Connector / J
• Severity: S5 (Performance)
• Version: 5.0.45-community-nt
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[20 Dec 2008 15:17] Firstname Lastname

Description:
The document says that "mediumint unsigned" returns java.lang.Integer upon getObject().  But in my code on 5.0.45-community-nt it returns a java.lang.Integer for executeQuery, but a java.lang.Long for getGeneratedKeys() after executeUpdate().

I found http://bugs.mysql.com/bug.php?id=25894, but am not clear whether there is a bug in the documentation or in the code.

How to repeat:
package sample;

import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MediumInt
{
   /*
       create database mytest;
       create table integers (id mediumint unsigned not null auto_increment, medIntUnsigned mediumint unsigned, tinyIntUnsigned tinyint unsigned, primary key (id));
       create user test1 identified by 'test';
       grant all on mytest.* to test1;
   */

   public static void main(String[] args)
   {
      Connection connection = null;
      Statement statement = null;
      ResultSet resultSet = null;
      try
      {
         Class.forName(Driver.class.getName());
         String connectString = "jdbc:mysql://localhost:3306/mytest";

         connection = DriverManager.getConnection(connectString, "test1", "test");
         statement = connection.createStatement();
         statement.executeUpdate("delete from integers");
         statement.executeUpdate("insert into integers (medIntUnsigned, tinyIntUnsigned) values(1,2)");
         resultSet = statement.getGeneratedKeys();
         resultSet.first();
         Object generatedId = resultSet.getObject(1);         
         resultSet = statement.executeQuery("select id, medIntUnsigned, tinyIntUnsigned from integers");
         resultSet.first();
         Object id = resultSet.getObject(1);
         Object medIntUnsigned = resultSet.getObject(2);
         Object tinyIntUnsigned = resultSet.getObject(3);
         System.out.println(generatedId.getClass().getName() + " " + generatedId.toString()); // this is Long, which seems wrong
         System.out.println(id.getClass().getName() + " " + id.toString()); // this is Integer
         System.out.println(medIntUnsigned.getClass().getName() + " " + medIntUnsigned.toString());
         System.out.println(tinyIntUnsigned.getClass().getName() + " " + tinyIntUnsigned.toString());
      }
      catch (ClassNotFoundException e)
      {
         e.printStackTrace();
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
      finally
      {
         try
         {
            if (resultSet != null)
            {
               resultSet.close();
            }
            if (statement != null)
            {
               statement.close();
            }
            if (connection != null)
            {
               connection.close();
            }
         }
         catch (SQLException e)
         {
            e.printStackTrace();
         }
      }
   }
}

[20 Dec 2008 21:44] Mark Matthews

The protocol doesn't inform the driver what type the generated key is, we only know that it can be as large as a BIGINT. I've assigned this to our documentation person, so a note can be added to the manual.

[6 Jan 2009 17:23] Tony Bedford

If possible, can you provide a link to the page that is in error please. Thanks.

[12 Apr 2009 22:15] james gamber

getGeneratedKeys works when executeUpdate("insert ...").
getGeneratedKeys does not work when executeUpdate( "call insertsp(...)")

putting the insert inside the stored procedure breaks getGeneratedKeys.  A valid resultset is returned by getGeneratedKeys with no rows.

Driver 1.5.7
MySQL 5.1.32

work around--
do not use getGeneratedKeys

inside stored proc put:

    insert test (name, address, city, state, zip) values(iname, iaddress, icity, istate, izip);
    if ROW_COUNT() > 0 then
        select LAST_INSERT_ID();
     else
        select -1;
     end if;

use standard result set processes
note: by putting a select after and insert in a stored proc, executeUpdate does not return number of rows updated.  Is that another bug?

[10 May 2016 15:49] Daniel So

Edited the manual to have he following as the returned Java class for MEDIUMINT:

"java.lang.Integer (regardless of whether it is UNSIGNED or not)"