From: Date: September 22 2006 2:33pm Subject: bk commit into 5.0 tree (kroki:1.2264) BUG#21726 List-Archive: http://lists.mysql.com/commits/12394 X-Bug: 21726 Message-Id: <200609221233.k8MCXQLA022269@moonlight.intranet> Below is the list of changes that have just been committed into a local 5.0 repository of tomash. When tomash 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-09-22 16:33:20+04:00, kroki@stripped +10 -0 BUG#21726: Incorrect result with multiple invocations of LAST_INSERT_ID Non-upper-level INSERTs (the ones in the body of stored procedure, stored function, or trigger) into a table that have AUTO_INCREMENT column didn't affected the result of LAST_INSERT_ID() on this level. The problem was introduced with the fix of bug 6880, where current insert_id value that was remembered on the first call to LAST_INSERT_ID() was returned from that function until it was reset before the next _upper-level_ statement. The very procedure of remembering current insert_id had another defects that made the fix for bug 6880 only a partial solution. The fix for bug#21726 employs the following protocol: - remember insert_id value after the statement execution (which is at that point equals to the first insert_id value generated by the statement or the result of its explicit setting). - on statements "LAST_INSERT_ID(expr)", "SET LAST_INSERT_ID= expr", and on binary log command "LAST_INSERT_ID= constant" update remembered insert_id value. Thus, the value returned by LAST_INSERT_ID() is not affected by values generated by current statement, but affected by its explicit update. This fix is for 5.0 only, as in 5.1 WL 3146 solves the same problems in a more generic way. This patch tries to produce the same results as 5.1. mysql-test/r/rpl_insert_id.result@stripped, 2006-09-22 16:33:15+04:00, kroki@stripped +127 -0 Add results for bug#21726: Incorrect result with multiple invocations of LAST_INSERT_ID, and bug#20339: stored procedure using LAST_INSERT_ID() does not replicate statement-based. mysql-test/t/rpl_insert_id.test@stripped, 2006-09-22 16:33:15+04:00, kroki@stripped +112 -1 Add test cases for bug#21726: Incorrect result with multiple invocations of LAST_INSERT_ID, and bug#20339: stored procedure using LAST_INSERT_ID() does not replicate statement-based. sql/item_func.cc@stripped, 2006-09-22 16:33:15+04:00, kroki@stripped +7 -2 On call to LAST_INSERT_ID(expr) set THD::current_insert_id together with THD::last_insert_id. Return THD::current_insert_id (via call to THD::insert_id()). sql/log_event.cc@stripped, 2006-09-22 16:33:15+04:00, kroki@stripped +5 -0 For binary log command "LAST_INSERT_ID= expr" set THD::current_insert_id together with THD::last_insert_id. sql/set_var.cc@stripped, 2006-09-22 16:33:16+04:00, kroki@stripped +6 -2 For statement "SET LAST_INSERT_ID= expr" set THD::current_insert_id together with THD::last_insert_id. Return THD::current_insert_id for "@@LAST_INSERT_ID". sql/sql_class.cc@stripped, 2006-09-22 16:33:16+04:00, kroki@stripped +2 -0 Remember generated value for insert_id in THD::current_insert_id at the end of the statement, which then will be returned by THD::insert_id(). sql/sql_class.h@stripped, 2006-09-22 16:33:16+04:00, kroki@stripped +2 -6 Rather then remember current insert_id value on first invocation of THD::insert_id(), remember it in THD::current_insert_id at statement end, and return from THD::insert_id(). sql/sql_insert.cc@stripped, 2006-09-22 16:33:16+04:00, kroki@stripped +1 -1 Since THD::insert_id() now returns THD::current_insert_id that we have remembered from the previous statement, here we remember current value of THD::last_insert_id (and that is actually how the code worked before the fix for bug#6880). sql/sql_parse.cc@stripped, 2006-09-22 16:33:16+04:00, kroki@stripped +1 -1 No need to reset THD::last_insert_id_used here, it will be reset after each statement. tests/mysql_client_test.c@stripped, 2006-09-22 16:33:16+04:00, kroki@stripped +39 -1 Add test case for bug#21726: Incorrect result with multiple invocations of LAST_INSERT_ID. # 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: kroki # Host: moonlight.intranet # Root: /home/tomash/src/mysql_ab/mysql-5.0-bug21726 --- 1.300/sql/item_func.cc 2006-09-22 16:33:28 +04:00 +++ 1.301/sql/item_func.cc 2006-09-22 16:33:28 +04:00 @@ -3305,11 +3305,16 @@ longlong Item_func_last_insert_id::val_i { longlong value= args[0]->val_int(); thd->insert_id(value); + /* + Update THD::current_insert_id, which is returned by + THD::insert_id(). + */ + thd->current_insert_id= value; null_value= args[0]->null_value; - return value; // Avoid side effect of insert_id() + return value; } thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT); - return thd->last_insert_id_used ? thd->current_insert_id : thd->insert_id(); + return thd->insert_id(); } /* This function is just used to test speed of different functions */ --- 1.208/sql/log_event.cc 2006-09-22 16:33:28 +04:00 +++ 1.209/sql/log_event.cc 2006-09-22 16:33:28 +04:00 @@ -3367,6 +3367,11 @@ int Intvar_log_event::exec_event(struct case LAST_INSERT_ID_EVENT: thd->last_insert_id_used = 1; thd->last_insert_id = val; + /* + Update THD::current_insert_id, which is returned by + THD::insert_id(). + */ + thd->current_insert_id= val; break; case INSERT_ID_EVENT: thd->next_insert_id = val; --- 1.247/sql/sql_class.cc 2006-09-22 16:33:28 +04:00 +++ 1.248/sql/sql_class.cc 2006-09-22 16:33:28 +04:00 @@ -557,6 +557,8 @@ bool THD::store_globals() void THD::cleanup_after_query() { + last_insert_id_used= 0; + current_insert_id= last_insert_id; if (clear_next_insert_id) { clear_next_insert_id= 0; --- 1.295/sql/sql_class.h 2006-09-22 16:33:28 +04:00 +++ 1.296/sql/sql_class.h 2006-09-22 16:33:28 +04:00 @@ -1463,12 +1463,8 @@ public: } inline ulonglong insert_id(void) { - if (!last_insert_id_used) - { - last_insert_id_used=1; - current_insert_id=last_insert_id; - } - return last_insert_id; + last_insert_id_used= 1; + return current_insert_id; } inline ulonglong found_rows(void) { --- 1.199/sql/sql_insert.cc 2006-09-22 16:33:28 +04:00 +++ 1.200/sql/sql_insert.cc 2006-09-22 16:33:28 +04:00 @@ -2438,7 +2438,7 @@ bool select_insert::send_data(List */ table->next_number_field->reset(); if (!last_insert_id && thd->insert_id_used) - last_insert_id= thd->insert_id(); + last_insert_id= thd->last_insert_id; } } DBUG_RETURN(error); --- 1.572/sql/sql_parse.cc 2006-09-22 16:33:28 +04:00 +++ 1.573/sql/sql_parse.cc 2006-09-22 16:33:28 +04:00 @@ -5625,7 +5625,7 @@ void mysql_reset_thd_for_next_command(TH DBUG_ENTER("mysql_reset_thd_for_next_command"); thd->free_list= 0; thd->select_number= 1; - thd->last_insert_id_used= thd->query_start_used= thd->insert_id_used=0; + thd->query_start_used= thd->insert_id_used=0; thd->is_fatal_error= thd->time_zone_used= 0; thd->server_status&= ~ (SERVER_MORE_RESULTS_EXISTS | SERVER_QUERY_NO_INDEX_USED | --- 1.16/mysql-test/r/rpl_insert_id.result 2006-09-22 16:33:28 +04:00 +++ 1.17/mysql-test/r/rpl_insert_id.result 2006-09-22 16:33:28 +04:00 @@ -210,3 +210,130 @@ n b 2 100 3 350 drop table t1; +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1, t2; +SELECT LAST_INSERT_ID(0); +LAST_INSERT_ID(0) +0 +CREATE TABLE t1 ( +id INT NOT NULL DEFAULT 0, +last_id INT, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id INT NOT NULL AUTO_INCREMENT, +last_id INT, +PRIMARY KEY (id) +); +CREATE PROCEDURE p1() +BEGIN +INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID()); +INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID()); +END| +CALL p1(); +SELECT * FROM t1; +id last_id +0 1 +SELECT * FROM t2; +id last_id +1 0 +SELECT * FROM t1; +id last_id +0 1 +SELECT * FROM t2; +id last_id +1 0 +DROP PROCEDURE p1; +DROP TABLE t1, t2; +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 ( +i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, +j INT DEFAULT 0 +); +CREATE TABLE t2 (i INT); +CREATE PROCEDURE p1() +BEGIN +INSERT INTO t1 (i) VALUES (NULL); +INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); +INSERT INTO t1 (i) VALUES (NULL), (NULL); +INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); +END | +CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA +BEGIN +INSERT INTO t1 (i) VALUES (NULL); +INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); +INSERT INTO t1 (i) VALUES (NULL), (NULL); +INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); +RETURN 0; +END | +CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC +RETURN LAST_INSERT_ID() | +INSERT INTO t1 VALUES (NULL, -1); +CALL p1(); +SELECT f1(); +f1() +0 +INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()), +(NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2()); +INSERT INTO t1 VALUES (NULL, f2()); +INSERT INTO t1 VALUES (NULL, LAST_INSERT_ID()), (NULL, @@LAST_INSERT_ID); +INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID()); +UPDATE t1 SET j= -1 WHERE i IS NULL; +SELECT * FROM t1; +i j +1 -1 +2 0 +3 0 +4 0 +5 0 +6 0 +7 0 +8 3 +9 3 +10 3 +11 3 +12 3 +13 8 +14 13 +15 13 +16 -1 +17 14 +SELECT * FROM t2; +i +2 +3 +5 +6 +SELECT * FROM t1; +i j +1 -1 +2 0 +3 0 +4 0 +5 0 +6 0 +7 0 +8 3 +9 3 +10 3 +11 3 +12 3 +13 8 +14 13 +15 13 +16 -1 +17 14 +SELECT * FROM t2; +i +2 +3 +5 +6 +DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1, t2; +End of 5.0 tests. --- 1.18/mysql-test/t/rpl_insert_id.test 2006-09-22 16:33:28 +04:00 +++ 1.19/mysql-test/t/rpl_insert_id.test 2006-09-22 16:33:28 +04:00 @@ -228,6 +228,117 @@ select * from t1 order by n; connection master; drop table t1; -# End of 5.0 tests + +# +# BUG#20339: stored procedure using LAST_INSERT_ID() does not +# replicate statement-based +# +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +# Reset result of LAST_INSERT_ID(). +SELECT LAST_INSERT_ID(0); + +CREATE TABLE t1 ( + id INT NOT NULL DEFAULT 0, + last_id INT, + PRIMARY KEY (id) +); + +CREATE TABLE t2 ( + id INT NOT NULL AUTO_INCREMENT, + last_id INT, + PRIMARY KEY (id) +); + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID()); + INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID()); +END| +delimiter ;| + +CALL p1(); +SELECT * FROM t1; +SELECT * FROM t2; + +sync_slave_with_master; +SELECT * FROM t1; +SELECT * FROM t2; + +connection master; + +DROP PROCEDURE p1; +DROP TABLE t1, t2; + + +# +# BUG#21726: Incorrect result with multiple invocations of +# LAST_INSERT_ID +# +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 ( + i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + j INT DEFAULT 0 +); +CREATE TABLE t2 (i INT); + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + INSERT INTO t1 (i) VALUES (NULL); + INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); + INSERT INTO t1 (i) VALUES (NULL), (NULL); + INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); +END | + +CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA +BEGIN + INSERT INTO t1 (i) VALUES (NULL); + INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); + INSERT INTO t1 (i) VALUES (NULL), (NULL); + INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); + RETURN 0; +END | + +CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC + RETURN LAST_INSERT_ID() | +delimiter ;| + +INSERT INTO t1 VALUES (NULL, -1); +CALL p1(); +SELECT f1(); +INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()), + (NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2()); +INSERT INTO t1 VALUES (NULL, f2()); +INSERT INTO t1 VALUES (NULL, LAST_INSERT_ID()), (NULL, @@LAST_INSERT_ID); +# Test replication of substitution "IS NULL" -> "= LAST_INSERT_ID". +INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID()); +UPDATE t1 SET j= -1 WHERE i IS NULL; + +SELECT * FROM t1; +SELECT * FROM t2; + +sync_slave_with_master; +SELECT * FROM t1; +SELECT * FROM t2; + +connection master; +DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1, t2; + + +--echo End of 5.0 tests. sync_slave_with_master; --- 1.163/sql/set_var.cc 2006-09-22 16:33:28 +04:00 +++ 1.164/sql/set_var.cc 2006-09-22 16:33:28 +04:00 @@ -2564,6 +2564,11 @@ byte *sys_var_timestamp::value_ptr(THD * bool sys_var_last_insert_id::update(THD *thd, set_var *var) { thd->insert_id(var->save_result.ulonglong_value); + /* + Update THD::current_insert_id, which is returned by + THD::insert_id(). + */ + thd->current_insert_id= var->save_result.ulonglong_value; return 0; } @@ -2571,8 +2576,7 @@ bool sys_var_last_insert_id::update(THD byte *sys_var_last_insert_id::value_ptr(THD *thd, enum_var_type type, LEX_STRING *base) { - thd->sys_var_tmp.long_value= (long) thd->insert_id(); - return (byte*) &thd->last_insert_id; + return (byte*) &thd->current_insert_id; } --- 1.207/tests/mysql_client_test.c 2006-09-22 16:33:28 +04:00 +++ 1.208/tests/mysql_client_test.c 2006-09-22 16:33:28 +04:00 @@ -15237,6 +15237,43 @@ static void test_bug21206() DBUG_VOID_RETURN; } +/* + Bug#21726: Incorrect result with multiple invocations of + LAST_INSERT_ID + + Test that client gets updated value of insert_id on UPDATE that uses + LAST_INSERT_ID(expr). +*/ +static void test_bug21726() +{ + const char *create_table[]= + { + "DROP TABLE IF EXISTS t1", + "CREATE TABLE t1 (i INT)", + "INSERT INTO t1 VALUES (1)", + }; + const char *update_query= "UPDATE t1 SET i= LAST_INSERT_ID(i + 1)"; + int rc; + my_ulonglong insert_id; + + DBUG_ENTER("test_bug21726"); + myheader("test_bug21726"); + + fill_tables(create_table, sizeof(create_table) / sizeof(*create_table)); + + rc= mysql_query(mysql, update_query); + myquery(rc); + insert_id= mysql_insert_id(mysql); + DIE_UNLESS(insert_id == 2); + + rc= mysql_query(mysql, update_query); + myquery(rc); + insert_id= mysql_insert_id(mysql); + DIE_UNLESS(insert_id == 3); + + DBUG_VOID_RETURN; +} + /* Read and parse arguments and MySQL options from my.cnf @@ -15511,7 +15548,8 @@ static struct my_tests_st my_tests[]= { { "test_bug17667", test_bug17667 }, { "test_bug19671", test_bug19671 }, { "test_bug15752", test_bug15752 }, - { "test_bug21206", test_bug21206}, + { "test_bug21206", test_bug21206 }, + { "test_bug21726", test_bug21726 }, { 0, 0 } };