Bug #75830 Triggers cause statements using LAST_INSERT_ID(X) to return wrong value
Submitted: 9 Feb 2015 20:35 Modified: 9 Feb 2015 21:02
Reporter: Gavin Towey Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.5, 5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: LAST_INSERT_ID, triggers
Triage: Needs Triage: D3 (Medium)

[9 Feb 2015 20:35] Gavin Towey
Description:
When running a statement like the following:
UPDATE `table` SET b=LAST_INSERT_ID(b+1) WHERE a=1;

The client detects that the function LAST_INSERT_ID is used with an argument, and it returns the value as part of the server's response.  Most connectors make this value available via a function call without having to send another query to retrieve the value.

When you add an AFTER UPDATE trigger to the table, then the server stops sending back the correct value.  This breaks applications which depend on the value sent by the server.

The reason is in mysql_update() function in the server code:

The after update trigger is handled by this code:

      if (table->triggers &&
          table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
                                            TRG_ACTION_AFTER, TRUE))
      {
        error= 1;
        break;
      }

Which eventually calls THD::cleanup_after_query, and that resets the flag which tells us that the original client statement used an argument to last_insert_id:

  arg_of_last_insert_id_function= 0;

Later, when the response is sent by the server, this happens:

  /* If LAST_INSERT_ID(X) was used, report X */
  id= thd->arg_of_last_insert_id_function ?
    thd->first_successful_insert_id_in_prev_stmt : 0;

    my_ok(thd, row_count, id, buff);

Since the variable was reset after running the trigger, the client gets a different response than it would expect.

How to repeat:
# create a table and add some data
 CREATE TABLE `changeme` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
);
INSERT INTO `changeme` VALUES (1, 1 );

# test with connection through php/pdo for example:
DOING UPDATE WITH LAST_INSERT_ID()
LAST_INSERT_ID: 2

DOING EXPLICIT SELECT OF LAST_INSERT_ID()
            [LAST_INSERT_ID()] => 2

# now add a trigger:
 CREATE TRIGGER `changeme_upd` AFTER UPDATE ON `test`.`changeme` FOR EACH ROW SET @value = '1';

# rerun the script:
DOING UPDATE WITH LAST_INSERT_ID()
LAST_INSERT_ID: 0

DOING EXPLICIT SELECT OF LAST_INSERT_ID()
            [LAST_INSERT_ID()] => 3

# notice that the second script run causes the first LAST_INSERT_ID value to be 0.  This is because it's calling the $dbh->lastInsertId() method, and the server is sending the wrong value.  You can see the problem also if you strace the mysql client and watch the server responses.

The php code I'm using is as follows:
  1 <?php
  2 
  3 $dsn = "mysql:host=127.0.0.1;port=3306;dbname=test";
  4 $user = "root";
  5 $password = "";
  6 
  7 try {
  8     $dbh = new PDO($dsn, $user, $password);
  9 } catch (PDOException $e) {
 10     echo 'Connection failed: ' . $e->getMessage()."\n";
 11     exit(1);
 12 }
 13 
 14     print "DOING UPDATE WITH LAST_INSERT_ID()\n";
 15     $sth = $dbh->query("update changeme set b=LAST_INSERT_ID(b+1) WHERE a=1");
 16     print "LAST_INSERT_ID: ".$dbh->lastInsertId()."\n\n";
 17 
 18 
 19     print "DOING EXPLICIT SELECT OF LAST_INSERT_ID()\n";
 20     $sth = $dbh->query("SELECT LAST_INSERT_ID()");
 21     print_r($sth->fetchAll());
 22 ?>

Suggested fix:
in mysql_update it looks like certain thread variables should be saved before the trigger is called so the state can be restored afterwards.
[9 Feb 2015 21:01] Sveta Smirnova
C code, demonstrating same issue - this is not PHP fault.

Attachment: bug75830.c (text/x-csrc), 926 bytes.

[9 Feb 2015 21:02] Sveta Smirnova
Thank you for the report.

Verified as described.