MySQL Bugs: #38338: REPLACE causes last_insert_id() to return an incorrect value

Bug #38338	REPLACE causes last_insert_id() to return an incorrect value
Submitted:	24 Jul 2008 9:44	Modified:	14 Jul 2009 13:25
Reporter:	Sergei Golubchik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Storage Engine API	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	V Venkateswaran	CPU Architecture:	Any

Description:
auto_increment.test fails if HA_DUPLICATE_KEY_NOT_IN_ORDER is added to MyISAM, disabling last_uniq_key() optimization.

How to repeat:
.

Suggested fix:
adding HA_DUPLICATE_KEY_NOT_IN_ORDER to any engine should not affect the test suite.

REPLACE causes last_insert_id() to return an incorrect value

last_insert_id() does not return the expected value in the following case after a replace happens

mysql>  create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(null,1,1,1,now());
Query OK, 1 row affected (5.02 sec)

mysql>  insert into t1 values(null,0,0,0,null);
Query OK, 1 row affected (3.73 sec)

mysql>  replace into t1 values(null,1,0,2,null);
Query OK, 3 rows affected (2 min 13.77 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

BUT THE TABLE HAS THIS

mysql> select * from t1;
+---+------+------+------+---------------------+
| a | b    | c    | e    | d                   |
+---+------+------+------+---------------------+
| 3 |    1 |    0 |    2 | 2008-07-30 10:47:59 |
+---+------+------+------+---------------------+
1 row in set (0.00 sec)

The correct value in the above case should have been 3

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/51814

2680 Narayanan V	2008-08-17
      Bug#38338
      
      Fix the write_record function to record auto increment
      values in a consistent way.

approved with minor comments

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/52249

2718 Narayanan V	2008-08-22
      Bug#38338: REPLACE causes last_insert_id() to return an incorrect value
      
      Fix the write_record function to record auto increment
      values in a consistent way.

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/52762

2675 Narayanan V	2008-08-27 [merge]
      Bug#38338: REPLACE causes last_insert_id() to return an incorrect value
            
      Fix the write_record function to record auto increment
      values in a consistent way.

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/52770

2808 Narayanan V	2008-08-27 [merge]
      Bug#38338: REPLACE causes last_insert_id() to return an incorrect value
                  
      Fix the write_record function to record auto increment
      values in a consistent way.

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/52905

2725 Narayanan V	2008-08-29 [merge]
      Bug#38338: REPLACE causes last_insert_id() to return an incorrect value
                  
      Fix the write_record function to record auto increment
      values in a consistent way.

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/53475

2735 Narayanan V	2008-09-08
      Bug#38338: REPLACE causes last_insert_id() to return an incorrect value
                        
      Fix the write_record function to record auto increment
      values in a consistent way.

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/54200

2739 Narayanan V	2008-09-16
      Bug#38338: REPLACE causes last_insert_id() to return an incorrect value
                        
      Fix the write_record function to record auto increment
      values in a consistent way.

Pushed into 5.1.30  (revid:v.narayanan@sun.com-20080916130759-6t6gvoency1igj96) (version source revid:v.narayanan@sun.com-20080916130759-6t6gvoency1igj96) (pib:4)

Pushed into 6.0.8-alpha  (revid:v.narayanan@sun.com-20080916130759-6t6gvoency1igj96) (version source revid:v.narayanan@sun.com-20080916144500-csk5c11lzo7dsl0a) (pib:5)

Pushed into 5.1.29-ndb-6.2.17  (revid:v.narayanan@sun.com-20080916130759-6t6gvoency1igj96) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)

Pushed into 5.1.29-ndb-6.3.19  (revid:v.narayanan@sun.com-20080916130759-6t6gvoency1igj96) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)

Pushed into 5.1.29-ndb-6.4.0  (revid:v.narayanan@sun.com-20080916130759-6t6gvoency1igj96) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)

Is this bugs already fixed?

<a href="http://computersmagazines.info/" title="computers magazines">computers magazines</a>

Internal change only. No changelog entry required.