Bug #41448 java.sql.SQLException: !Statement.GeneratedKeysNotRequested!
Submitted: 13 Dec 2008 17:22 Modified: 16 Jul 2009 10:34
Reporter: Rico H. Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.1.7 OS:Linux
Assigned to: CPU Architecture:Any

[13 Dec 2008 17:22] Rico H.
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!
[26 Dec 2008 16:59] Eduardo Juan
Same problem here with Connector/J 5.1.7. With version 5.1.6 no problem at all. Any workaround?
[26 Dec 2008 17:06] Eduardo Juan
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!
[26 Dec 2008 17:11] Eduardo Juan
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!
[29 Dec 2008 19:48] Alessandro Polverini
I confirm the problem, it bite me too.
[21 Jan 2009 14:18] Soitsa Intesys
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
[22 Jan 2009 0:39] Jess Balint
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.
[25 Jan 2009 13:59] Eme Oko
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();
[26 Jan 2009 13:42] ANDREY Andrey Radionov
works with 
 stmt=conn.prepareStatement(<stmt>,PreparedStatement.RETURN_GENERATED_KEYS);
 stmt.executeUpdate();
 stmt.getGeneratedKeys();
[26 Jan 2009 14:56] Tonci Grgin
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.
[27 Jan 2009 8:55] Rico H.
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.
[28 Jan 2009 10:03] Eric Herman
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?
[29 Jan 2009 14:00] Tonci Grgin
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.
[2 Feb 2009 8:12] Soitsa Intesys
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();
//...
[2 Feb 2009 9:41] Tonci Grgin
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.
[6 Feb 2009 11:36] Soitsa Intesys
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()
[1 Mar 2009 0:00] Bugs System
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".
[4 May 2009 10:04] Venkoba Rao
remove poolPreparedStatements and maxOpenPreparedStatements from Resource, it works
[14 May 2009 1:17] Hammad Sophie
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.
[28 May 2009 18:29] Mark Matthews
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.
[25 Jun 2009 22:34] J Smith
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
[9 Jul 2009 22:21] Simon Haldon
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.
[9 Jul 2009 22:24] Simon Haldon
I'm using executeUpdate, too. Will try changing to execute.
[9 Jul 2009 22:47] Simon Haldon
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.
[9 Jul 2009 22:49] Simon Haldon
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.
[10 Jul 2009 14:58] Tonci Grgin
This problem is fixed and will be released in version 5.1.8.
[16 Jul 2009 10:34] Tony Bedford
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);
[28 Sep 2009 22:53] Harsh Deshpande
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
[29 Sep 2009 15:05] Mark Matthews
@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.
[9 Aug 2012 17:14] Syd Uch
Seems to still have a problem 

my Mysql versions: 5.1.42  and  5.1.61  

Connector J 5.1.8  - Working
Connector J 5.1.10 - Not Working
Connector J 5.1.18 - Not Working
Connector J 5.1.21 - Not Working

ResultSet rs = ps.getGeneratedKeys(); -->  This throws a java.sql.SQLException: !Statement.GeneratedKeysNotRequested!

the same exemple as the first entry of this post.

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!