Hi Mark.

The Connector/J JDBC driver does not correctly handle returning auto-generated keys on multi row inserts.

I'm not sure if this is a bug because the multi row INSERT syntax in MySQL is MySQL specific therefore the JDBC rules may not apply. On the other hand, the ANSI/ISO SQL standard supports multi row inserts, and since the JDBC API is geared towards the ANSI/ISO SQL standard maybe the JDBC rules do apply and it is indeed a bug.

Let my back up for a moment and clarify in case I lost anyone. Lets pretend we have a database table named 'MY_TABLE' that looks something like this:

CREATE TABLE MY_TABLE( pk BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, misc VARCHAR (255), PRIMARY KEY (pk));

Using Connector/J I have code similar to: <code> String sql = "INSERT INTO MY_TABLE (misc) VALUES ('Test')"; Statement st = connection.createStatement();

st.executeUpdate( sql ); ResultSet rs = st.getGeneratedKeys();

if( rs.next() ) System.out.println( "The auto-generated key is " + rs.getLong( 1 ) ); else throw new IllegalStateException( "No auto-generated key found." ); </code>

For a newly created 'MY_TABLE' table the code snippet above would produce: The auto-generated key is 1

The next snippet of code is where the problem lies. <code> String sql = "INSERT INTO MY_TABLE (misc) VALUES ('Test1'),('Test2')"; Statement st = connection.createStatement();

st.executeUpdate( sql ); ResultSet rs = st.getGeneratedKeys();

while( rs.next() ) System.out.println( "The auto-generated key is " + rs.getLong( 1 ) ); </code>

For a newly created 'MY_TABLE' table the code snippet would produce: The auto-generated key is 1

If you missed it; There is no second line of output!

Mark, please get back to me about this when you can. It's kind of important for a project that I'm working on. I would make recommendations on a fix but I know absolutely zilch about the MySQL network protocol. Thanx.