MySQL Bugs: #25111: mysql_insert_id() doesn't work with stored procedures

Bug #25111	mysql_insert_id() doesn't work with stored procedures
Submitted:	16 Dec 2006 20:44	Modified:	6 May 2008 17:29
Reporter:	Nuno Lopes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.26, 5.1 BK	OS:	Linux (linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
mysql_insert_id() returns 0 when the insert statement happens inside a stored procedure.

How to repeat:
create a simple store procedure

CREATE PROCEDURE NEW_AUTHOR(IN nome VARCHAR(255))
BEGIN
	INSERT INTO autor (nome) VALUES(nome);
END

then in php:
<?
$stmt = $pdo->prepare('CALL NEW_AUTHOR(?)');

if ($stmt->execute(array($_POST['nome']))) {
  var_dump($pdo->query('SELECT last_insert_id()')->fetchColumn()); // correct
  var_dump($pdo->lastInsertId()); // always 0
}

?>

Thank you for the report.

Verified using last BK sources and modified test case:

#include <mysql.h>

int main ()
{
    MYSQL       conn;

    mysql_init (&conn);
    mysql_real_connect (
        &conn,
        "localhost",
        "root",
        "",
        "test",
        0,
        "/tmp/mysql_ssmirnova.sock",
        0);

mysql_real_query (&conn, "CALL NEW_AUTHOR('nome');", strlen("CALL
NEW_AUTHOR('nome');"));
printf("%d\n", mysql_insert_id(&conn));
};

This is not a bug.
This seems to be counter-intuitive, so not closing it for now.

The server reply to a stored procedure always contains the status of execution of the stored procedure itself, not the embedded statements.
Effectively, when you call a procedure that contains an SQL statement you execute
two statements:
- the embedded statement
- the CALL statement.
The result of the embedded statement is suppressed, unless the embedded statement is a SELECT.

If you would like to know the status of an embedded statement, you can query it explicitly inside the procedure, and select as a result set.
This not only applies to the last insert id, but also to the number of affected rows.
The corresponding SQL level functions are LAST_INSERT_ID() and ROW_COUNT().

Reclassifying as a 'Documentation' bug.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.