MySQL Bugs: #21379: LAST_INSERT_ID() does not return results

Bug #21379	LAST_INSERT_ID() does not return results
Submitted:	1 Aug 2006 3:40	Modified:	19 Sep 2006 1:48
Reporter:	Ronald Bradford	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Linux (Linux FC5)
Assigned to:	Mark Matthews	CPU Architecture:	Any
Tags:	Connector/J, LAST_INSERT_ID

Description:
When retrieving the LAST_INSERT_ID() in a SELECT after an INSERT, the resultset does not correctly identify the column name,.

Under 3.1.13, the resultset contains 1 row, with id=999
Under 5.0.3, the resultset contains 1 row, with [blank]=999

How to repeat:
The following simple .jsp page uses the Sakila Sample Database.
I can reproduce the error by simply swapping out the 5.0.3.jar and replacing latest 3.1.13.jar (and rebooting my webserver)
I am using MySQL Database Server 5.1.11

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>

<sql:setDataSource url="jdbc:mysql://localhost/sakila" driver="com.mysql.jdbc.Driver" user="sakila" password="dolphin" />
Actors:
<sql:query var="q" sql="SELECT first_name,last_name FROM actor ORDER BY actor_id DESC LIMIT 5" />
<c:forEach var="r" items="${q.rows}"> ${r.first_name} ${r.last_name}, </c:forEach>
<br />

<sql:transaction>
  <sql:update>
    INSERT INTO actor(first_name,last_name)
    VALUES ('Ronald', 'Bradford')
  </sql:update>
  <sql:query var="q" sql="SELECT LAST_INSERT_ID() AS id" />
</sql:transaction>
${q.rowCount} Rows<br />
<c:forEach var="r" items="${q.rows[0]}">
  ${r.key} = ${r.value}*<br />
</c:forEach>
Inserted record as id '${q.rows[0].id}'<br />

Note: To use this example you also need to include the standard.jar and jstl.jar with your app.

Ronald, is there an actualy exception thrown? You wouldn't happen to know if JSTL's taglib uses RSMD.getColumnName() or .getColumnLabel() to generate the keyset, would you (I don't use it, myself).

Connector/J 5.0, in a move to be JDBC-compliant, returns the alias name only in RSMD.getColumnLabel() and the original column name for RSMD.getColumnName(), and thus JSTL is getting somewhat confused, since it uses RSMD.getColumnName() to generate the "key" set for your map.

We're going to add a backwards-compatible option to revert to the old (but non-spec-compliant) behavior, since (1) MySQL doesn't always return the original column name (in the case of functions) and (2) some frameworks/libraries don't follow the JDBC spec in using column names and labels.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9869

Thanks for the extremely prompt reply and patch. Will await a nightly build/release.

For reference to your first question Re: getColumeName()

Using source archive at http://people.apache.org/builds/jakarta-taglibs/nightly/src/jakarta-taglibs-src-20060731.t... 

getColumnName is used. See Line 70 of 
jakarta-taglibs/standard-1.0/src/javax/servlet/jsp/jstl/sql/ResultImpl.java  

 64         ResultSetMetaData rsmd = rs.getMetaData();
 65         int noOfColumns = rsmd.getColumnCount();
 66
 67         // Create the column name array
 68         columnNames = new String[noOfColumns];
 69         for (int i = 1; i <= noOfColumns; i++) {
 70             columnNames[i-1] = rsmd.getColumnName(i);
 71         }