Below is the list of changes that have just been committed into a local
5.1 repository of guilhem. When guilhem does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2006-08-31 21:54:52+02:00, guilhem@stripped +3 -0
New way to fix BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY UPDATE".
This bug report was two problems:
1) LAST_INSERT_ID() returns a value which does not exist in the table
2) the reporter would want it to return the autoinc id of the updated
row. 1) is a real bug, 2) is a feature request.
In July I implemented 2) in 5.1 (which automatically fixes 1).
This has not yet been documented or released, so is changeable.
Precisely, recently Paul and a user found an easy workaround to give
2), which works in 4.1-5.0-5.1. So I can revert my code for 2),
because it's not needed, that's what I do here;
we forget about 2) (we will document the workaround).
But when I revert my code for 2), 1) comes back. We solve 1) by saying
that if INSERT ON DUPLICATE KEY UPDATE updates a row, it's like a
regular UPDATE: LAST_INSERT_ID() should not be affected (instead of
returning a non-existent value).
So note: no behaviour change compared to the last released 5.1; just
a bugfix for 1).
mysql-test/r/auto_increment.result@stripped, 2006-08-31 21:54:49+02:00, guilhem@stripped +26 -4
result update.
mysql-test/t/auto_increment.test@stripped, 2006-08-31 21:54:49+02:00, guilhem@stripped +18 -2
test for the new way to fix BUG#19243: that if INSERT ON DUPLICATE
KEY UPDATE updates a row, SELECT LAST_INSERT_ID() is not affected.
Test of the workaround for people who want SELECT LAST_INSERT_ID()
to return the autoinc id of the updated row.
sql/sql_insert.cc@stripped, 2006-08-31 21:54:49+02:00, guilhem@stripped +7 -10
No need to change LAST_INSERT_ID() if INSERT ON DUPLICATE KEY UPDATE
updates a row, there is a workaround to achieve this without changing
code: just add "autoinc_col=LAST_INSERT_ID(autoinc_col)" to your
ON DUPLICATE KEY UPDATE clause.
Prevent LAST_INSERT_ID() to contain an inexistent value in this case:
if the row is updated it should be like a regular UPDATE: don't
affect LAST_INSERT_ID() (achieved by marking that we didn't generate
an id for this row: insert_id_for_cur_row=0).
Removing a line which I had left in comments.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: guilhem
# Host: gbichot3.local
# Root: /home/mysql_src/mysql-5.1-maint
--- 1.220/sql/sql_insert.cc 2006-08-31 21:54:59 +02:00
+++ 1.221/sql/sql_insert.cc 2006-08-31 21:54:59 +02:00
@@ -1145,16 +1145,15 @@ int write_record(THD *thd, TABLE *table,
}
info->updated++;
/*
- If ON DUP KEY UPDATE updates a row instead of inserting one, and
- there is an auto_increment column, then SELECT LAST_INSERT_ID()
- returns the id of the updated row:
+ If ON DUP KEY UPDATE updates a row instead of inserting one, it's
+ like a regular UPDATE statement: it should not affect the value of a
+ next SELECT LAST_INSERT_ID() or mysql_insert_id().
+ Except if LAST_INSERT_ID(#) was in the INSERT query, which is
+ handled separately by THD::arg_of_last_insert_id_function.
*/
+ insert_id_for_cur_row= table->file->insert_id_for_cur_row= 0;
if (table->next_number_field)
- {
- longlong field_val= table->next_number_field->val_int();
- thd->record_first_successful_insert_id_in_cur_stmt(field_val);
- table->file->adjust_next_insert_id_after_explicit_value(field_val);
- }
+ table->file->adjust_next_insert_id_after_explicit_value(table->next_number_field->val_int());
trg_error= (table->triggers &&
table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
TRG_ACTION_AFTER, TRUE));
@@ -2106,8 +2105,6 @@ bool delayed_insert::handle_inserts(void
thd.start_time=row->start_time;
thd.query_start_used=row->query_start_used;
- /* for the binlog, forget auto_increment ids generated by previous rows */
-// thd.auto_inc_intervals_in_cur_stmt_for_binlog.empty();
thd.first_successful_insert_id_in_prev_stmt=
row->first_successful_insert_id_in_prev_stmt;
thd.stmt_depends_on_first_successful_insert_id_in_prev_stmt=
--- 1.46/mysql-test/r/auto_increment.result 2006-08-31 21:54:59 +02:00
+++ 1.47/mysql-test/r/auto_increment.result 2006-08-31 21:54:59 +02:00
@@ -471,22 +471,44 @@ ifnull( c,
0 ) + 1;
select last_insert_id();
last_insert_id()
-1
+2
+select last_insert_id(0);
+last_insert_id(0)
+0
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+last_insert_id()
+0
select * from t2;
k a c
-1 6 1
+1 6 2
2 7 NULL
insert ignore into t2 values (null,6,1),(10,8,1);
select last_insert_id();
last_insert_id()
-1
+0
insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
select last_insert_id();
last_insert_id()
11
select * from t2;
k a c
-1 6 1
+1 6 2
+2 7 NULL
+10 8 1
+11 15 1
+12 20 1
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1, k=last_insert_id(k);
+select last_insert_id();
+last_insert_id()
+1
+select * from t2;
+k a c
+1 6 3
2 7 NULL
10 8 1
11 15 1
--- 1.28/mysql-test/t/auto_increment.test 2006-08-31 21:54:59 +02:00
+++ 1.29/mysql-test/t/auto_increment.test 2006-08-31 21:54:59 +02:00
@@ -305,8 +305,8 @@ ALTER TABLE t1 CHANGE t1 t1 INT(10) auto
DROP TABLE t1;
# Fix for BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY
-# UPDATE": now LAST_INSERT_ID() will return the id of the updated
-# row.
+# UPDATE": if the row is updated, it's like a regular UPDATE:
+# LAST_INSERT_ID() is not affected.
CREATE TABLE `t2` (
`k` int(11) NOT NULL auto_increment,
`a` int(11) default NULL,
@@ -326,6 +326,12 @@ insert into t2 ( a ) values ( 6 ) on dup
ifnull( c,
0 ) + 1;
select last_insert_id();
+# test again when last_insert_id() is 0 initially
+select last_insert_id(0);
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
select * from t2;
# Test of LAST_INSERT_ID() when autogenerated will fail:
@@ -335,6 +341,16 @@ select last_insert_id();
# First and second autogenerated will fail, last_insert_id() should
# point to third
insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
+select last_insert_id();
+select * from t2;
+
+# Test of the workaround which enables people to know the id of the
+# updated row in INSERT ON DUPLICATE KEY UPDATE, by using
+# LAST_INSERT_ID(autoinc_col) in the UPDATE clause.
+
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1, k=last_insert_id(k);
select last_insert_id();
select * from t2;
| Thread |
|---|
| • bk commit into 5.1 tree (guilhem:1.2291) BUG#19243 | guilhem | 31 Aug |
