MySQL Bugs: #1934: prepareStatement dies silently when encountering Statement.RETURN_GENERATED

Bug #1934	prepareStatement dies silently when encountering Statement.RETURN_GENERATED_KEY
Submitted:	24 Nov 2003 10:28	Modified:	25 Nov 2003 7:39
Reporter:	Ralf Hauser	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1. nightly	OS:	Linux (RH9)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:

	public void initialize()
		throws IOException, SAXException, DatabaseException {
		try {
			log.debug("starting db initialize");
			connRead = rds.getConnection();
			connWrite = wds.getConnection();
//... many other prepared statements
	   log.debug("before Statement.RETURN_GENERATED_KEYS");
           insAttachmentStmt =
	     connWrite.prepareStatement("INSERT INTO " + TBL_ATTACHMENT
	        +" (file_size, "
		+ " file_name, "
		+ " content_type, "
		+ " file_data_blob, "
		+ " create_id, "
		+ " create_date) "
		+ " VALUES (?,?,?,AES_ENCRYPT(?,?),?,?);",
		Statement.RETURN_GENERATED_KEYS);
	  log.debug("after Statement.RETURN_GENERATED_KEYS");
//   ... many other prepared statements
		} catch (SQLException e) {
			log.error(e.getMessage());
			throw new DatabaseException(
				"SQLException: "
					+ e.getMessage()
					+ " SQLState: "
					+ e.getSQLState()
					+ " VendorError: "
					+ e.getErrorCode());
		}
	} // end initialize()

How to repeat:
every time, I get until the "before Statement.RETURN_GENERATED_KEYS" in my catalina.out, but I never see the "after Statement.RETURN_GENERATED_KEYS"

Suggested fix:
at least throw an exception if something is wrong and don't continue as if all were all right.

Or am I doing something wrong?

Seems like you're doing something wrong (you don't state what server version you're using, but it shouldn't really matter). 

I don't see how the code could even 'proceed as if everything is all right', given that _after_ prepareStatement() is called, you are returned to your code.

The following testcase with 3.1 nightly works as expected, printing both 'before' and 'after' the prepareStatement() call:

 public void testBug1934() throws Exception {
    	try {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
    		this.stmt.executeUpdate("CREATE TABLE testBug1934 (field1 INT)");
    		
    		System.out.println("Before prepareStatement()");
    		
    		this.conn.prepareStatement("INSERT INTO testBug1934 VALUES (?)", java.sql.Statement.RETURN_GENERATED_KEYS);
    		
    		System.out.println("After prepareStatement()");
    		
    	} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
    	}
    }

Mark, thanks, I had to adapt your test case as follows:
			Statement stmt =
                 connWrite.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
			java.sql.ResultSet.CONCUR_UPDATABLE);
			try {
			stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
			stmt.executeUpdate(
				"CREATE TABLE testBug1934 (field1 INT)");
			log.debug("Before prepareStatement()");
			connWrite.prepareStatement(
				"INSERT INTO testBug1934 VALUES (?)",
				java.sql.Statement.RETURN_GENERATED_KEYS);
			log.debug("After prepareStatement()");
		} finally {
			stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
		}

Same effect  :(
One error I noticed and fixed was that I imported com.mysql.jdbc.Statement instead of java.sql.Statement, but this unfortunately didn't fix it.

Hmm, so I am giving up on statement-granularity, but
     rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
seems to work, with connection granularity only, though

It sounds like there is something definitely wrong with your environment or your code, because the behavior you are describing shouldn't be able to happen :(

What happens if you step through the code with a debugger?

thx for the moral support ;) 
It may take a few weeks, but I'll not give up and once I master the remote debugging you are teaching me in http://bugs.mysql.com/bug.php?id=1658, I guess I'll be able to get that here solved too!
Therefore, pls keep this bug open for while.

After moving to struts1.1 and simultaneously,
- replacing driverClassName "org.gjt.mm.mysql.Driver" with "com.mysql.jdbc.Driver"
- moving from GenericDataSource to org.apache.commons.dbcp.BasicDataSource
The problem no longer occurs.

and oviously, also here got the nightly jar of early Dec03