MySQL Bugs: #41448: java.sql.SQLException: !Statement.GeneratedKeysNotRequested!

Bug #41448	java.sql.SQLException: !Statement.GeneratedKeysNotRequested!
Submitted:	13 Dec 2008 18:22	Modified:	16 Jul 12:34
Reporter:	Rico H.
Status:	Closed
Category:	Connector/J	Severity:	S1 (Critical)
Version:	5.1.7	OS:	Linux
Assigned to:		Target Version:
Triage:	D2 (Serious)

Description:
Hello,

Consider the following code:

PreparedStatement ps = connection.prepareStatement("INSERT INTO table values(?,?)");
ps.setInt(1,value1);
ps.setInt(2,value2);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys(); // This throws a java.sql.SQLException:
!Statement.GeneratedKeysNotRequested!

Even when creating the PreparedStament in this other way the same exception is thrown:

PreparedStatement ps = connection.prepareStatement("INSERT INTO table
values(?,?)",PreparedStatement.RETURN_GENERATED_KEYS);
ps.setInt(1,value1);
ps.setInt(2,value2);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys(); // This ALSO throws a java.sql.SQLException:
!Statement.GeneratedKeysNotRequested!

According to some posts I read on the Internet, this might be related to Bug #34185, as
it seems was pushed into the current 5.1.7 version and the problem didn't happen before.

I guess the problem just occurs when using PreparedStaments, as with Statements you can
provide the RETURN_GENERATED_KEYS flag when executing the query. However, flags for
PreparedStaments can only be provided when "Preparing the Statement". So the following
code works well:

Statement stmt =
connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.executeUpdate("INSERT INTO table values(1,2)",Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys(); //This works

This is breaking lots of apps, and should be fixed as soon as possible.

Thank you very much!!

How to repeat:
See description!

Same problem here with Connector/J 5.1.7. With version 5.1.6 no problem at all. Any
workaround?

Just adding some more info:

The SQLException occurs when I use PreparedStaments as the form:
ps = conn.prepareStatement(query);

ITOH, if I use like:
ps = conn.prepareStatement(query,PreparedStatement.RETURN_GENERATED_KEYS);

It works!

Sorry again (you should be considering having edit mode ;))
The below example worked if I used PreparedStatements in the form:
ps = conn.prepareStatement(query,PreparedStatement.RETURN_GENERATED_KEYS);

But unlike the Rico H. I used: ps.execute();

So my working case example is:

query = "insert into mytable (f1,f2) values (?,?)";
ps = conn.prepareStatement(query,PreparedStatement.RETURN_GENERATED_KEYS);
ps.setString(1,"1");
ps.setString(2,"2");
         
ps.execute(); //>>>> Rico H. uses ps.executeUpdate() instead <<<<<<
           
ResultSet lastid = ps.getGeneratedKeys(); //This works!

I confirm the problem, it bite me too.

The bug happens both in Statement.executeUpdate and in PreparedStatement.executeUpdate and
doesn't happen neither Statement.execute nor PreparedStatemet.execute. 
By the way, although the bug OS is marked as Linux, in Windows happens too

I'm not able to reproduce this using Statement,PreparedStatement and
execute(),executeUpdate(). Please include a complete test program and the stack trace
from the exception.

Any solution yet for this problem. The problem still exists when I invoke the
stmt.getGeneratedKeys() method (see below).

PreparedStatement stmt=conn.prepareStatement("insert into `test_name` (`name`) values
(?)");
for(int i=0;i<numTestInserts;i++){
     stmt.setString(1,"test " + i);
     stmt.addBatch();
}
int[] insertCounts=stmt.executeBatch();
ResultSet rs=stmt.getGeneratedKeys();

works with 
 stmt=conn.prepareStatement(<stmt>,PreparedStatement.RETURN_GENERATED_KEYS);
 stmt.executeUpdate();
 stmt.getGeneratedKeys();

I don't think it's a bug but rather misuse.

Please see what Eduardo and Andrey wrote and make sure you use
PreparedStatement.RETURN_GENERATED_KEYS

If none proves me, with repeatable test case, this really is the bug, I'll close it next
week.

It's not a misuse. I confirm the problem exists. People that get it working may be using
an older version.

PreparedStatement ps = connection.prepareStatement("INSERT INTO table_test
values(0,?)",PreparedStatement.RETURN_GENERATED_KEYS);
ps.setInt(1,value);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys(); //This throws an exception

The important thing here is that the table "table_test" contains an auto_increment field.

I was not able to re-produce with Connector-j-5-1 and mysql 5.1.30 with the following
test:

    public void testBug41448() throws Exception {
        createTable("testBug41448",
            "(a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, b INTEGER)");

        String sql = "INSERT INTO testBug41448 VALUES (NULL,?)";

        this.pstmt = conn.prepareStatement(sql,
            PreparedStatement.RETURN_GENERATED_KEYS);
        this.pstmt.setInt(1, 23);
        this.pstmt.executeUpdate();
        this.rs = pstmt.getGeneratedKeys();
        assertTrue(this.rs.next());
        assertEquals(1, this.rs.getInt(1));
        assertFalse(this.rs.next());
    }

Perhaps I misunderstood a step?

Rico, Gijs, can you please check Eric's test case and see if it fails on your boxes? If
none responds I will have to close the report as "Can't repeat" being that I can not
overrule Eric.

With J-Connector 5.1.7, I REPEAT, 5.1.7 and MySQL 5.1.30 this code DOES THROW the
exception (the table has a autogenerated key called ID)

String[] columNames = new String[1];
columNames[1] = "ID";
PreparedStatement ps = connection.prepareStatement(query, columnNames);
//no parameters in the preparedStatement
ps.executeUpdate(); //throws !Statement.GeneratedKeysNotRequested!

With J-Connector 5.1.7, I REPEAT, 5.1.7 and MySQL 5.1.30 this code DOES NOT THROW the
exception:

String[] columNames = new String[1];
columNames[1] = "ID";
PreparedStatement ps = connection.prepareStatement(query, columnNames);
//no parameters in the preparedStatement
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
//...

With J-Connector 5.1.7, I REPEAT, 5.1.7 and MySQL 5.1.30 this code DOES THROW the
exception:

Statement st = connection.createStatement();
String[] columNames = new String[1];
columNames[1] = "ID";

st.executeUpdate(query, columnNames); //throws Statement.GeneratedKeysNotRequested!

With J-Connector 5.1.7, I REPEAT, 5.1.7 and MySQL 5.1.30 this code DOES NOT THROW the
exception:

Statement st = connection.createStatement();
String[] columNames = new String[1];
columNames[1] = "ID";

st.execute(query, columnNames); 
ResultSet rs = ps.getGeneratedKeys();
//...

Soitsa, it is not necessary to stress, several times, the c/J version as it's in plain
sight in header of report.
Further more, code snippets are *not* test repeatable case. Please a complete test case
so I can check because I feel there might be a genuine problem here.
Also, we are obliged to check bug reports against latest source code available so not to
waste our time with things fixed.
Finally, if RETURN_GENERATED_KEYS is *not* specified, then newer versions will throw
exception, and I believe that is "not a bug" but proper behavior. Fact that some older
version did not throw it is documenting case, not a bug per-se.

Hi Tonci!
Sorry for the stress of the names!
RETURN_GENERATED_KEYS is not the unique way to request the autogenerated keys,
Connection.prepareStatement(String sql, String columnNames[]) is another way as JAVA API
states:
"Creates a default PreparedStatement object capable of returning the auto-generated keys
designated by the given array.  This array contains the names of the columns in the
target table that contain the auto-generated keys that should be returned"
I'm using this method to get the PreparedStatement capable of returning the autogenerated
keys. 
Once I get the preparedStatement, everything is ok in retrieving the autogenerated keys
because I call execute() over the prepared statement. If I use executeUpdate I get the
exception. 
I think that there is not misuse in my code: 
*Getting the PreparedStatement in the way I get it is as valid as RETURN_GENERATED_KEYS
way 
* The method executeUpdate() would fit better (at least semantically) in an insert than
the method execute()

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

remove poolPreparedStatements and maxOpenPreparedStatements from Resource, it works

I am getting the same problem.
Application code that used to work with Connector/J 3.0.8 on MySQL 4.0.14 isn't working
with Connector/J 5.1.7 and MySQL 4.1.22.

I just tested the same code with 5.0.8 and it works.

We've had a testcase that covers the various scenarios mentioned here for quite some time
(statements, prepared statements, execute and executeUpdate()), and it passes, so we
consider this fixed.

Hi,

I've run into this problem as well, however it only occurs in a linux setup. On windows
all works fine. Perhaps that's why some of you have found it hard to reproduce.

My dev box is windows xp running 5.1.30 server mysql-server-5.0.45-6.fc8. 

My linux box is fedora core 8 with mysql-server-5.0.45-6.fc8.

In both cases I am using mysql-connector-5.1.7.

Thanks,
Fil

I confirm this issue with Connector/J 5.1.7. I have been using a windows environment and
when I upload my app to a Linux server and run it under Tomcat, the
!Statement.GeneratedKeysNotRequested! SQLerror is thrown and -1 returned for the key
value.

I'm using executeUpdate, too. Will try changing to execute.

Apologies for the multiple comments.

Changing pstmt.executeUpdate() to pstmt.execute() did not solve the problem.

5.1.6 does not suffer from this problem - I've reverted to that.

Ignore my statement '-1 is returned for the key value', obviously nothing is returned as
an exception is thrown, that's my own return code.

This problem is fixed and will be released in version 5.1.8.

An entry was added to the 5.1.8 changelog:

The RETURN_GENERATED_KEYS flag was being ignored. For example, in the following code the
RETURN_GENERATED_KEYS flag was ignored:

PreparedStatement ps = connection.prepareStatement("INSERT INTO table
  values(?,?)",PreparedStatement.RETURN_GENERATED_KEYS);

I am seeing this exception in Connector J 5.1.10 jar file.

Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to
Statement.executeUpdate() or Connection.prepareStatement().

Thanks
Harsh

@Harsh

The message means what it says. Something called Statement.getGeneratedKeys(), without
telling the driver that it should actually attempt to return those keys. If this is a
"plain" statement, those flags are passed when the statement is executed. If it's a
prepared statement, those flags are passed when the statement is prepared.