diff options
30 files changed, 1219 insertions, 386 deletions
diff --git a/mysql-test/extra/binlog_tests/binlog.test b/mysql-test/extra/binlog_tests/binlog.test index 6f7990893f0..993b3fbf634 100644 --- a/mysql-test/extra/binlog_tests/binlog.test +++ b/mysql-test/extra/binlog_tests/binlog.test @@ -49,3 +49,35 @@ show binlog events in 'master-bin.000001' from 102; --replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events in 'master-bin.000002' from 102; + +# Test of a too big SET INSERT_ID: see if the truncated value goes +# into binlog (right), or the too big value (wrong); we look at the +# binlog further down with SHOW BINLOG EVENTS. +reset master; +create table t1 (id tinyint auto_increment primary key); +set insert_id=128; +insert into t1 values(null); +select * from t1; +drop table t1; + +# Test of binlogging of INSERT_ID with INSERT DELAYED +create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; +# First, avoid BUG#20627: +set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1; +# Verify that only one INSERT_ID event is binlogged. +insert delayed into t1 values (207); + +# We use sleeps between statements, that's the only way to get a +# repeatable binlog in a normal test run and under Valgrind. +# It may be that the "binlog missing rows" of BUG#20821 shows up +# here. +sleep 2; +insert delayed into t1 values (null); +sleep 2; +insert delayed into t1 values (300); +sleep 2; # time for the delayed queries to reach disk +select * from t1; +--replace_column 2 # 5 # +--replace_regex /table_id: [0-9]+/table_id: #/ +show binlog events from 102; +drop table t1; diff --git a/mysql-test/extra/rpl_tests/rpl_insert_id.test b/mysql-test/extra/rpl_tests/rpl_insert_id.test index 03e8f00caae..29a07df2d3c 100644 --- a/mysql-test/extra/rpl_tests/rpl_insert_id.test +++ b/mysql-test/extra/rpl_tests/rpl_insert_id.test @@ -144,6 +144,23 @@ insert into t1 (last_id) values (bug15728()); # This should be exactly one greater than in the previous call. select last_insert_id(); +# BUG#20339 - stored procedure using LAST_INSERT_ID() does not +# replicate statement-based +--disable_warnings +drop procedure if exists foo; +--enable_warnings +delimiter |; +create procedure foo() +begin + declare res int; + insert into t2 (last_id) values (bug15728()); + insert into t1 (last_id) values (bug15728()); +end| +delimiter ;| +call foo(); + +select * from t1; +select * from t2; save_master_pos; connection slave; sync_with_master; @@ -154,6 +171,7 @@ connection master; drop function bug15728; drop function bug15728_insert; drop table t1, t2; +drop procedure foo; # test of BUG#20188 REPLACE or ON DUPLICATE KEY UPDATE in # auto_increment breaks binlog @@ -220,4 +238,39 @@ drop table t1; # End of 5.0 tests +# Test for BUG#20341 "stored function inserting into one +# auto_increment puts bad data in slave" + +truncate table t2; +create table t1 (id tinyint primary key); # no auto_increment + +delimiter |; +create function insid() returns int +begin + insert into t2 (last_id) values (0); + return 0; +end| +delimiter ;| +set sql_log_bin=0; +insert into t2 (id) values(1),(2),(3); +delete from t2; +set sql_log_bin=1; +#inside SELECT, then inside INSERT +select insid(); +set sql_log_bin=0; +insert into t2 (id) values(5),(6),(7); +delete from t2 where id>=5; +set sql_log_bin=1; +insert into t1 select insid(); +select * from t1; +select * from t2; + +sync_slave_with_master; +select * from t1; +select * from t2; + +connection master; +drop table t1, t2; +drop function insid; + sync_slave_with_master; diff --git a/mysql-test/extra/rpl_tests/rpl_loaddata.test b/mysql-test/extra/rpl_tests/rpl_loaddata.test index 5d7c69bd959..61f58ff5279 100644 --- a/mysql-test/extra/rpl_tests/rpl_loaddata.test +++ b/mysql-test/extra/rpl_tests/rpl_loaddata.test @@ -20,8 +20,11 @@ connection slave; reset master; connection master; +select last_insert_id(); create table t1(a int not null auto_increment, b int, primary key(a) ); load data infile '../std_data_ln/rpl_loaddata.dat' into table t1; +# verify that LAST_INSERT_ID() is set by LOAD DATA INFILE +select last_insert_id(); create temporary table t2 (day date,id int(9),category enum('a','b','c'),name varchar(60)); load data infile '../std_data_ln/rpl_loaddata2.dat' into table t2 fields terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by '\n##\n' starting by '>' ignore 1 lines; diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 10f26c40553..985d4d2e1f4 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -153,7 +153,7 @@ insert into t1 set i = null; ERROR 23000: Duplicate entry '255' for key 'PRIMARY' select last_insert_id(); last_insert_id() -0 +255 drop table t1; create table t1 (i tinyint unsigned not null auto_increment, key (i)); insert into t1 set i = 254; @@ -181,7 +181,7 @@ insert into t1 values (NULL, 10); ERROR 23000: Duplicate entry '10' for key 'b' select last_insert_id(); last_insert_id() -0 +2 drop table t1; create table t1(a int auto_increment,b int null,primary key(a)); SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; @@ -446,3 +446,57 @@ INSERT INTO t1 VALUES(1, 1); ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment; ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY' DROP TABLE t1; +CREATE TABLE `t2` ( +`k` int(11) NOT NULL auto_increment, +`a` int(11) default NULL, +`c` int(11) default NULL, +PRIMARY KEY (`k`), +UNIQUE KEY `idx_1` (`a`) +) ENGINE=InnoDB; +insert into t2 ( a ) values ( 6 ) on duplicate key update c = +ifnull( c, +0 ) + 1; +insert into t2 ( a ) values ( 7 ) on duplicate key update c = +ifnull( c, +0 ) + 1; +select last_insert_id(); +last_insert_id() +2 +select * from t2; +k a c +1 6 NULL +2 7 NULL +insert into t2 ( a ) values ( 6 ) on duplicate key update c = +ifnull( c, +0 ) + 1; +select last_insert_id(); +last_insert_id() +1 +select * from t2; +k a c +1 6 1 +2 7 NULL +insert ignore into t2 values (null,6,1),(10,8,1); +select last_insert_id(); +last_insert_id() +1 +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 +2 7 NULL +10 8 1 +11 15 1 +12 20 1 +drop table t2; +create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c)); +insert into t1 values(null,1,1,now()); +insert into t1 values(null,0,0,null); +replace into t1 values(null,1,0,null); +select last_insert_id(); +last_insert_id() +3 +drop table t1; diff --git a/mysql-test/r/binlog_row_binlog.result b/mysql-test/r/binlog_row_binlog.result index 6cb086109b4..17c1d171b5c 100644 --- a/mysql-test/r/binlog_row_binlog.result +++ b/mysql-test/r/binlog_row_binlog.result @@ -235,3 +235,37 @@ master-bin.000001 # Rotate 1 # master-bin.000002;pos=4 show binlog events in 'master-bin.000002' from 102; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000002 # Query 1 # use `test`; drop table t1 +reset master; +create table t1 (id tinyint auto_increment primary key); +set insert_id=128; +insert into t1 values(null); +Warnings: +Warning 1264 Out of range value for column 'id' at row 1 +select * from t1; +id +127 +drop table t1; +create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; +set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1; +insert delayed into t1 values (207); +insert delayed into t1 values (null); +insert delayed into t1 values (300); +select * from t1; +a +207 +208 +300 +show binlog events from 102; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; create table t1 (id tinyint auto_increment primary key) +master-bin.000001 # Table_map 1 # table_id: # (test.t1) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `test`; drop table t1 +master-bin.000001 # Query 1 # use `test`; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam +master-bin.000001 # Table_map 1 # table_id: # (test.t1) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (test.t1) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (test.t1) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +drop table t1; diff --git a/mysql-test/r/binlog_stm_binlog.result b/mysql-test/r/binlog_stm_binlog.result index f9180b69ab6..4e23db4828f 100644 --- a/mysql-test/r/binlog_stm_binlog.result +++ b/mysql-test/r/binlog_stm_binlog.result @@ -145,3 +145,35 @@ master-bin.000001 # Rotate 1 # master-bin.000002;pos=4 show binlog events in 'master-bin.000002' from 102; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000002 # Query 1 # use `test`; drop table t1 +reset master; +create table t1 (id tinyint auto_increment primary key); +set insert_id=128; +insert into t1 values(null); +Warnings: +Warning 1264 Out of range value for column 'id' at row 1 +select * from t1; +id +127 +drop table t1; +create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; +set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1; +insert delayed into t1 values (207); +insert delayed into t1 values (null); +insert delayed into t1 values (300); +select * from t1; +a +207 +208 +300 +show binlog events from 102; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; create table t1 (id tinyint auto_increment primary key) +master-bin.000001 # Intvar 1 # INSERT_ID=127 +master-bin.000001 # Query 1 # use `test`; insert into t1 values(null) +master-bin.000001 # Query 1 # use `test`; drop table t1 +master-bin.000001 # Query 1 # use `test`; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam +master-bin.000001 # Query 1 # use `test`; insert delayed into t1 values (207) +master-bin.000001 # Intvar 1 # INSERT_ID=208 +master-bin.000001 # Query 1 # use `test`; insert delayed into t1 values (null) +master-bin.000001 # Query 1 # use `test`; insert delayed into t1 values (300) +drop table t1; diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 235c3f61fe9..b090f0f52c0 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -353,3 +353,18 @@ select row_count(); row_count() 1 drop table t1; +create table t1 (id int primary key auto_increment, data int, unique(data)); +insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120); +insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90); +insert ignore into t1 values(NULL,130),(NULL,140),(500,110),(550,120),(450,100),(NULL,150); +select * from t1 order by id; +id data +1 100 +2 110 +3 120 +4 10 +5 20 +6 90 +7 130 +8 140 +9 150 diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index f3e14ef9d72..56e31b87c89 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -117,6 +117,14 @@ insert into t1 (last_id) values (bug15728()); select last_insert_id(); last_insert_id() 5 +drop procedure if exists foo; +create procedure foo() +begin +declare res int; +insert into t2 (last_id) values (bug15728()); +insert into t1 (last_id) values (bug15728()); +end| +call foo(); select * from t1; id last_id 1 0 @@ -124,13 +132,64 @@ id last_id 3 2 4 1 5 4 +6 3 select * from t2; id last_id 1 3 2 4 +3 5 +select * from t1; +id last_id +1 0 +2 1 +3 2 +4 1 +5 4 +6 3 +select * from t2; +id last_id +1 3 +2 4 +3 5 drop function bug15728; drop function bug15728_insert; +drop procedure foo; +drop table t1; +truncate table t2; +create table t1 (id tinyint primary key); +create function insid() returns int +begin +insert into t2 (last_id) values (0); +return 0; +end| +set sql_log_bin=0; +insert into t2 (id) values(1),(2),(3); +delete from t2; +set sql_log_bin=1; +select insid(); +insid() +0 +set sql_log_bin=0; +insert into t2 (id) values(5),(6),(7); +delete from t2 where id>=5; +set sql_log_bin=1; +insert into t1 select insid(); +select * from t1; +id +0 +select * from t2; +id last_id +4 0 +8 0 +select * from t1; +id +0 +select * from t2; +id last_id +4 0 +8 0 drop table t1, t2; +drop function insid; create table t1 (n int primary key auto_increment not null, b int, unique(b)); set sql_log_bin=0; diff --git a/mysql-test/r/rpl_loaddata.result b/mysql-test/r/rpl_loaddata.result index 47e056429ce..4ffa65c2c82 100644 --- a/mysql-test/r/rpl_loaddata.result +++ b/mysql-test/r/rpl_loaddata.result @@ -5,8 +5,14 @@ reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; reset master; +select last_insert_id(); +last_insert_id() +0 create table t1(a int not null auto_increment, b int, primary key(a) ); load data infile '../std_data_ln/rpl_loaddata.dat' into table t1; +select last_insert_id(); +last_insert_id() +1 create temporary table t2 (day date,id int(9),category enum('a','b','c'),name varchar(60)); load data infile '../std_data_ln/rpl_loaddata2.dat' into table t2 fields terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by '\n##\n' starting by '>' ignore 1 lines; create table t3 (day date,id int(9),category enum('a','b','c'),name varchar(60)); diff --git a/mysql-test/r/rpl_ndb_auto_inc.result b/mysql-test/r/rpl_ndb_auto_inc.result index 71217442698..dd4cc90a75f 100644 --- a/mysql-test/r/rpl_ndb_auto_inc.result +++ b/mysql-test/r/rpl_ndb_auto_inc.result @@ -71,8 +71,8 @@ a 250 251 400 +401 1000 -1001 ******* Select from Slave ************* select * from t1 ORDER BY a; @@ -83,8 +83,8 @@ a 250 251 400 +401 1000 -1001 drop table t1; create table t1 (a int not null auto_increment, primary key (a)) engine=NDB; insert into t1 values (NULL),(5),(NULL),(NULL); @@ -120,8 +120,6 @@ a 502 503 600 -603 -604 610 611 ******* Select from Slave ************* @@ -137,8 +135,6 @@ a 502 503 600 -603 -604 610 611 drop table t1; diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test index 2674639d0ac..7cef1bad784 100644 --- a/mysql-test/t/auto_increment.test +++ b/mysql-test/t/auto_increment.test @@ -303,3 +303,50 @@ INSERT INTO t1 VALUES(1, 1); --error ER_DUP_ENTRY ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment; 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. +CREATE TABLE `t2` ( + `k` int(11) NOT NULL auto_increment, + `a` int(11) default NULL, + `c` int(11) default NULL, + PRIMARY KEY (`k`), + UNIQUE KEY `idx_1` (`a`) +) ENGINE=InnoDB; + insert into t2 ( a ) values ( 6 ) on duplicate key update c = +ifnull( c, +0 ) + 1; +insert into t2 ( a ) values ( 7 ) on duplicate key update c = +ifnull( c, +0 ) + 1; +select last_insert_id(); +select * from t2; +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: +# last_insert_id() should not change +insert ignore into t2 values (null,6,1),(10,8,1); +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; + +drop table t2; + +# Test of REPLACE when it does INSERT+DELETE and not UPDATE: +# see if it sets LAST_INSERT_ID() ok +create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c)); +insert into t1 values(null,1,1,now()); +insert into t1 values(null,0,0,null); +# this will delete two rows +replace into t1 values(null,1,0,null); +select last_insert_id(); + +drop table t1; diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 3711e2986ed..0cc25469705 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -234,3 +234,10 @@ select row_count(); insert into t1 values (5, 5) on duplicate key update data= data + 10; select row_count(); drop table t1; + +# Test of INSERT IGNORE and re-using auto_increment values +create table t1 (id int primary key auto_increment, data int, unique(data)); +insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120); +insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90); +insert ignore into t1 values(NULL,130),(NULL,140),(500,110),(550,120),(450,100),(NULL,150); +select * from t1 order by id; diff --git a/sql/ha_federated.cc b/sql/ha_federated.cc index be923591cac..9c144faadc9 100644 --- a/sql/ha_federated.cc +++ b/sql/ha_federated.cc @@ -1709,14 +1709,15 @@ int ha_federated::write_row(byte *buf) This method ensures that last_insert_id() works properly. What it simply does is calls last_insert_id() on the foreign database immediately after insert (if the table has an auto_increment field) and sets the insert id via - thd->insert_id(ID) (as well as storing thd->prev_insert_id) + thd->insert_id(ID)). */ void ha_federated::update_auto_increment(void) { THD *thd= current_thd; DBUG_ENTER("ha_federated::update_auto_increment"); - thd->insert_id(mysql->last_used_con->insert_id); + thd->first_successful_insert_id_in_cur_stmt= + mysql->last_used_con->insert_id; DBUG_PRINT("info",("last_insert_id %d", stats.auto_increment_value)); DBUG_VOID_RETURN; diff --git a/sql/ha_ndbcluster.cc b/sql/ha_ndbcluster.cc index bbeea2ca1ba..d09907781e1 100644 --- a/sql/ha_ndbcluster.cc +++ b/sql/ha_ndbcluster.cc @@ -2473,9 +2473,7 @@ int ha_ndbcluster::write_row(byte *record) m_skip_auto_increment= FALSE; update_auto_increment(); - /* Ensure that handler is always called for auto_increment values */ - thd->next_insert_id= 0; - m_skip_auto_increment= !auto_increment_column_changed; + m_skip_auto_increment= (insert_id_for_cur_row == 0); } } diff --git a/sql/handler.cc b/sql/handler.cc index 3f5c05c39c7..51b95030929 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -1514,7 +1514,10 @@ int handler::read_first_row(byte * buf, uint primary_key) } /* - Generate the next auto-increment number based on increment and offset + Generate the next auto-increment number based on increment and offset: + computes the lowest number + - strictly greater than "nr" + - of the form: auto_increment_offset + N * auto_increment_increment In most cases increment= offset= 1, in which case we get: 1,2,3,4,5,... @@ -1523,8 +1526,10 @@ int handler::read_first_row(byte * buf, uint primary_key) */ inline ulonglong -next_insert_id(ulonglong nr,struct system_variables *variables) +compute_next_insert_id(ulonglong nr,struct system_variables *variables) { + if (variables->auto_increment_increment == 1) + return (nr+1); // optimization of the formula below nr= (((nr+ variables->auto_increment_increment - variables->auto_increment_offset)) / (ulonglong) variables->auto_increment_increment); @@ -1533,66 +1538,6 @@ next_insert_id(ulonglong nr,struct system_variables *variables) } -void handler::adjust_next_insert_id_after_explicit_value(ulonglong nr) -{ - /* - If we have set THD::next_insert_id previously and plan to insert an - explicitely-specified value larger than this, we need to increase - THD::next_insert_id to be greater than the explicit value. - */ - THD *thd= table->in_use; - if (thd->clear_next_insert_id && (nr >= thd->next_insert_id)) - { - if (thd->variables.auto_increment_increment != 1) - nr= next_insert_id(nr, &thd->variables); - else - nr++; - thd->next_insert_id= nr; - DBUG_PRINT("info",("next_insert_id: %lu", (ulong) nr)); - } -} - - -/* - Computes the largest number X: - - smaller than or equal to "nr" - - of the form: auto_increment_offset + N * auto_increment_increment - where N>=0. - - SYNOPSIS - prev_insert_id - nr Number to "round down" - variables variables struct containing auto_increment_increment and - auto_increment_offset - - RETURN - The number X if it exists, "nr" otherwise. -*/ - -inline ulonglong -prev_insert_id(ulonglong nr, struct system_variables *variables) -{ - if (unlikely(nr < variables->auto_increment_offset)) - { - /* - There's nothing good we can do here. That is a pathological case, where - the offset is larger than the column's max possible value, i.e. not even - the first sequence value may be inserted. User will receive warning. - */ - DBUG_PRINT("info",("auto_increment: nr: %lu cannot honour " - "auto_increment_offset: %lu", - nr, variables->auto_increment_offset)); - return nr; - } - if (variables->auto_increment_increment == 1) - return nr; // optimization of the formula below - nr= (((nr - variables->auto_increment_offset)) / - (ulonglong) variables->auto_increment_increment); - return (nr * (ulonglong) variables->auto_increment_increment + - variables->auto_increment_offset); -} - - /* Update the auto_increment field if necessary @@ -1606,7 +1551,7 @@ prev_insert_id(ulonglong nr, struct system_variables *variables) IMPLEMENTATION - Updates columns with type NEXT_NUMBER if: + Updates the record's Field of type NEXT_NUMBER if: - If column value is set to NULL (in which case auto_increment_field_not_null is 0) @@ -1614,25 +1559,31 @@ prev_insert_id(ulonglong nr, struct system_variables *variables) set. In the future we will only set NEXT_NUMBER fields if one sets them to NULL (or they are not included in the insert list). + In those cases, we check if the currently reserved interval still has + values we have not used. If yes, we pick the smallest one and use it. + Otherwise: - There are two different cases when the above is true: - - - thd->next_insert_id == 0 (This is the normal case) - In this case we set the set the column for the first row to the value - next_insert_id(get_auto_increment(column))) which is normally - max-used-column-value +1. + - If a list of intervals has been provided to the statement via SET + INSERT_ID or via an Intvar_log_event (in a replication slave), we pick the + first unused interval from this list, consider it as reserved. - We call get_auto_increment() only for the first row in a multi-row - statement. For the following rows we generate new numbers based on the - last used number. + - Otherwise we set the column for the first row to the value + next_insert_id(get_auto_increment(column))) which is usually + max-used-column-value+1. + We call get_auto_increment() for the first row in a multi-row + statement. get_auto_increment() will tell us the interval of values it + reserved for us. - - thd->next_insert_id != 0. This happens when we have read an Intvar event - of type INSERT_ID_EVENT from the binary log or when one has used SET - INSERT_ID=#. + - In both cases, for the following rows we use those reserved values without + calling the handler again (we just progress in the interval, computing + each new value from the previous one). Until we have exhausted them, then + we either take the next provided interval or call get_auto_increment() + again to reserve a new interval. - In this case we will set the column to the value of next_insert_id. - The next row will be given the id - next_insert_id(next_insert_id) + - In both cases, the reserved intervals are remembered in + thd->auto_inc_intervals_in_cur_stmt_for_binlog if statement-based + binlogging; the last reserved interval is remembered in + auto_inc_interval_for_cur_row. The idea is that generated auto_increment values are predictable and independent of the column values in the table. This is needed to be @@ -1643,7 +1594,13 @@ prev_insert_id(ulonglong nr, struct system_variables *variables) inserts a column with a higher value than the last used one, we will start counting from the inserted value. - thd->next_insert_id is cleared after it's been used for a statement. + This function's "outputs" are: the table's auto_increment field is filled + with a value, thd->next_insert_id is filled with the value to use for the + next row, if a value was autogenerated for the current row it is stored in + thd->insert_id_for_cur_row, if get_auto_increment() was called + thd->auto_inc_interval_for_cur_row is modified, if that interval is not + present in thd->auto_inc_intervals_in_cur_stmt_for_binlog it is added to + this list. TODO @@ -1660,7 +1617,8 @@ prev_insert_id(ulonglong nr, struct system_variables *variables) bool handler::update_auto_increment() { - ulonglong nr; + ulonglong nr, nb_reserved_values; + bool append= FALSE; THD *thd= table->in_use; struct system_variables *variables= &thd->variables; bool auto_increment_field_not_null; @@ -1668,10 +1626,10 @@ bool handler::update_auto_increment() DBUG_ENTER("handler::update_auto_increment"); /* - We must save the previous value to be able to restore it if the - row was not inserted + next_insert_id is a "cursor" into the reserved interval, it may go greater + than the interval, but not smaller. */ - thd->prev_insert_id= thd->next_insert_id; + DBUG_ASSERT(next_insert_id >= auto_inc_interval_for_cur_row.minimum()); auto_increment_field_not_null= table->auto_increment_field_not_null; table->auto_increment_field_not_null= FALSE; // to reset for next row @@ -1680,133 +1638,140 @@ bool handler::update_auto_increment() thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO) { /* - The user did specify a value for the auto_inc column, we don't generate - a new value, write it down. - */ - auto_increment_column_changed=0; - /* Update next_insert_id if we had already generated a value in this statement (case of INSERT VALUES(null),(3763),(null): the last NULL needs to insert 3764, not the value of the first NULL plus 1). */ - adjust_next_insert_id_after_explicit_value(nr); + if ((next_insert_id > 0) && (nr >= next_insert_id)) + set_next_insert_id(compute_next_insert_id(nr, variables)); + insert_id_for_cur_row= 0; // didn't generate anything DBUG_RETURN(0); } - if (!(nr= thd->next_insert_id)) + + if ((nr= next_insert_id) >= auto_inc_interval_for_cur_row.maximum()) { - ulonglong nb_desired_values= 1, nb_reserved_values; -#ifdef TO_BE_ENABLED_SOON - /* - Reserved intervals will be stored in "THD::auto_inc_intervals". - handler::estimation_rows_to_insert will be the argument passed by - handler::ha_start_bulk_insert(). - */ - uint estimation_known= test(estimation_rows_to_insert > 0); - uint nb_already_reserved_intervals= thd->auto_inc_intervals.nb_elements(); - /* - If an estimation was given to the engine: - - use it. - - if we already reserved numbers, it means the estimation was - not accurate, then we'll reserve 2*AUTO_INC_DEFAULT_NB_VALUES the 2nd - time, twice that the 3rd time etc. - If no estimation was given, use those increasing defaults from the - start, starting from AUTO_INC_DEFAULT_NB_VALUES. - Don't go beyond a max to not reserve "way too much" (because reservation - means potentially losing unused values). - */ - if (nb_already_reserved_intervals == 0 && estimation_known) - nb_desired_values= estimation_rows_to_insert; - else /* go with the increasing defaults */ + /* next_insert_id is beyond what is reserved, so we reserve more. */ + const Discrete_interval *forced= + thd->auto_inc_intervals_forced.get_next(); + if (forced != NULL) { - /* avoid overflow in formula, with this if() */ - if (nb_already_reserved_intervals <= AUTO_INC_DEFAULT_NB_MAX_BITS) + nr= forced->minimum(); + nb_reserved_values= forced->values(); + } + else + { + /* + handler::estimation_rows_to_insert was set by + handler::ha_start_bulk_insert(); if 0 it means "unknown". + */ + uint nb_already_reserved_intervals= + thd->auto_inc_intervals_in_cur_stmt_for_binlog.nb_elements(); + ulonglong nb_desired_values; + /* + If an estimation was given to the engine: + - use it. + - if we already reserved numbers, it means the estimation was + not accurate, then we'll reserve 2*AUTO_INC_DEFAULT_NB_ROWS the 2nd + time, twice that the 3rd time etc. + If no estimation was given, use those increasing defaults from the + start, starting from AUTO_INC_DEFAULT_NB_ROWS. + Don't go beyond a max to not reserve "way too much" (because + reservation means potentially losing unused values). + */ + if (nb_already_reserved_intervals == 0 && + (estimation_rows_to_insert > 0)) + nb_desired_values= estimation_rows_to_insert; + else /* go with the increasing defaults */ { - nb_desired_values= AUTO_INC_DEFAULT_NB_VALUES * - (1 << nb_already_reserved_intervals); - set_if_smaller(nb_desired_values, AUTO_INC_DEFAULT_NB_MAX); + /* avoid overflow in formula, with this if() */ + if (nb_already_reserved_intervals <= AUTO_INC_DEFAULT_NB_MAX_BITS) + { + nb_desired_values= AUTO_INC_DEFAULT_NB_ROWS * + (1 << nb_already_reserved_intervals); + set_if_smaller(nb_desired_values, AUTO_INC_DEFAULT_NB_MAX); + } + else + nb_desired_values= AUTO_INC_DEFAULT_NB_MAX; } - else - nb_desired_values= AUTO_INC_DEFAULT_NB_MAX; + /* This call ignores all its parameters but nr, currently */ + get_auto_increment(variables->auto_increment_offset, + variables->auto_increment_increment, + nb_desired_values, &nr, + &nb_reserved_values); + if (nr == ~(ulonglong) 0) + result= 1; // Mark failure + + /* + That rounding below should not be needed when all engines actually + respect offset and increment in get_auto_increment(). But they don't + so we still do it. Wonder if for the not-first-in-index we should do + it. Hope that this rounding didn't push us out of the interval; even + if it did we cannot do anything about it (calling the engine again + will not help as we inserted no row). + */ + nr= compute_next_insert_id(nr-1, variables); + } + + if (table->s->next_number_key_offset == 0) + { + /* We must defer the appending until "nr" has been possibly truncated */ + append= TRUE; + } + else + { + /* + For such auto_increment there is no notion of interval, just a + singleton. The interval is not even stored in + thd->auto_inc_interval_for_cur_row, so we are sure to call the engine + for next row. + */ + DBUG_PRINT("info",("auto_increment: special not-first-in-index")); } -#endif - /* This call ignores all its parameters but nr, currently */ - get_auto_increment(variables->auto_increment_offset, - variables->auto_increment_increment, - nb_desired_values, &nr, - &nb_reserved_values); - if (nr == ~(ulonglong) 0) - result= 1; // Mark failure - - /* - That should not be needed when engines actually use offset and increment - above. - */ - if (variables->auto_increment_increment != 1) - nr= next_insert_id(nr-1, variables); - /* - Update next row based on the found value. This way we don't have to - call the handler for every generated auto-increment value on a - multi-row statement - */ - thd->next_insert_id= nr; } DBUG_PRINT("info",("auto_increment: %lu", (ulong) nr)); - /* Mark that we should clear next_insert_id before next stmt */ - thd->clear_next_insert_id= 1; - - if (likely(!table->next_number_field->store((longlong) nr, TRUE))) - thd->insert_id((ulonglong) nr); - else + if (unlikely(table->next_number_field->store((longlong) nr, TRUE))) { /* - overflow of the field; we'll use the max value, however we try to - decrease it to honour auto_increment_* variables: + field refused this value (overflow) and truncated it, use the result of + the truncation (which is going to be inserted). + That will shift the left bound of the reserved interval, we don't + bother shifting the right bound (anyway any other value from this + interval will cause a duplicate key). */ - nr= prev_insert_id(table->next_number_field->val_int(), variables); - thd->insert_id(nr); - if (unlikely(table->next_number_field->store((longlong) nr, TRUE))) - thd->insert_id(nr= table->next_number_field->val_int()); + nr= table->next_number_field->val_int(); + } + if (append) + { + auto_inc_interval_for_cur_row.replace(nr, nb_reserved_values, + variables->auto_increment_increment); + /* Row-based replication does not need to store intervals in binlog */ + if (!thd->current_stmt_binlog_row_based) + result= result || + thd->auto_inc_intervals_in_cur_stmt_for_binlog.append(auto_inc_interval_for_cur_row.minimum(), + auto_inc_interval_for_cur_row.values(), + variables->auto_increment_increment); } /* - We can't set next_insert_id if the auto-increment key is not the - first key part, as there is no guarantee that the first parts will be in - sequence + Record this autogenerated value. If the caller then + succeeds to insert this value, it will call + record_first_successful_insert_id_in_cur_stmt() + which will set first_successful_insert_id_in_cur_stmt if it's not + already set. */ - if (!table->s->next_number_key_offset) - { - /* - Set next insert id to point to next auto-increment value to be able to - handle multi-row statements - This works even if auto_increment_increment > 1 - */ - thd->next_insert_id= next_insert_id(nr, variables); - } - else - thd->next_insert_id= 0; + insert_id_for_cur_row= nr; + /* + Set next insert id to point to next auto-increment value to be able to + handle multi-row statements. + */ + set_next_insert_id(compute_next_insert_id(nr, variables)); - /* Mark that we generated a new value */ - auto_increment_column_changed=1; DBUG_RETURN(result); } -/* - restore_auto_increment - - In case of error on write, we restore the last used next_insert_id value - because the previous value was not used. -*/ - -void handler::restore_auto_increment() -{ - THD *thd= table->in_use; - if (thd->next_insert_id) - thd->next_insert_id= thd->prev_insert_id; -} - /* MySQL signal that it changed the column bitmap @@ -1900,6 +1865,23 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, } +void handler::ha_release_auto_increment() +{ + release_auto_increment(); + insert_id_for_cur_row= 0; + auto_inc_interval_for_cur_row.replace(0, 0, 0); + if (next_insert_id > 0) + { + next_insert_id= 0; + /* + this statement used forced auto_increment values if there were some, + wipe them away for other statements. + */ + table->in_use->auto_inc_intervals_forced.empty(); + } +} + + void handler::print_keydup_error(uint key_nr, const char *msg) { /* Write the duplicated key in the error message */ @@ -3429,10 +3411,13 @@ namespace int handler::ha_external_lock(THD *thd, int lock_type) { DBUG_ENTER("handler::ha_external_lock"); - int error; - if (unlikely(error= external_lock(thd, lock_type))) - DBUG_RETURN(error); - DBUG_RETURN(0); + /* + Whether this is lock or unlock, this should be true, and is to verify that + if get_auto_increment() was called (thus may have reserved intervals or + taken a table lock), ha_release_auto_increment() was too. + */ + DBUG_ASSERT(next_insert_id == 0); + DBUG_RETURN(external_lock(thd, lock_type)); } diff --git a/sql/handler.h b/sql/handler.h index 6def26327be..3c090b887a3 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -906,16 +906,37 @@ public: uint ref_length; FT_INFO *ft_handler; enum {NONE=0, INDEX, RND} inited; - bool auto_increment_column_changed; bool implicit_emptied; /* Can be !=0 only if HEAP */ const COND *pushed_cond; + /* + next_insert_id is the next value which should be inserted into the + auto_increment column: in a inserting-multi-row statement (like INSERT + SELECT), for the first row where the autoinc value is not specified by the + statement, get_auto_increment() called and asked to generate a value, + next_insert_id is set to the next value, then for all other rows + next_insert_id is used (and increased each time) without calling + get_auto_increment(). + */ + ulonglong next_insert_id; + /* + insert id for the current row (*autogenerated*; if not + autogenerated, it's 0). + At first successful insertion, this variable is stored into + THD::first_successful_insert_id_in_cur_stmt. + */ + ulonglong insert_id_for_cur_row; + /* + Interval returned by get_auto_increment() and being consumed by the + inserter. + */ + Discrete_interval auto_inc_interval_for_cur_row; handler(const handlerton *ht_arg, TABLE_SHARE *share_arg) :table_share(share_arg), estimation_rows_to_insert(0), ht(ht_arg), ref(0), key_used_on_scan(MAX_KEY), active_index(MAX_KEY), ref_length(sizeof(my_off_t)), ft_handler(0), inited(NONE), implicit_emptied(0), - pushed_cond(NULL) + pushed_cond(NULL), next_insert_id(0), insert_id_for_cur_row(0) {} virtual ~handler(void) { @@ -1248,9 +1269,30 @@ public: ulonglong nb_desired_values, ulonglong *first_value, ulonglong *nb_reserved_values); +private: virtual void release_auto_increment() { return; }; - virtual void restore_auto_increment(); - +public: + void ha_release_auto_increment(); + void set_next_insert_id(ulonglong id) + { + DBUG_PRINT("info",("auto_increment: next value %lu", (ulong)id)); + next_insert_id= id; + } + void restore_auto_increment(ulonglong prev_insert_id) + { + /* + Insertion of a row failed, re-use the lastly generated auto_increment + id, for the next row. This is achieved by resetting next_insert_id to + what it was before the failed insertion (that old value is provided by + the caller). If that value was 0, it was the first row of the INSERT; + then if insert_id_for_cur_row contains 0 it means no id was generated + for this first row, so no id was generated since the INSERT started, so + we should set next_insert_id to 0; if insert_id_for_cur_row is not 0, it + is the generated id of the first and failed row, so we use it. + */ + next_insert_id= (prev_insert_id > 0) ? prev_insert_id : + insert_id_for_cur_row; + } /* Reset the auto-increment counter to the given value, i.e. the next row inserted will get the given value. This is called e.g. after TRUNCATE diff --git a/sql/item_func.cc b/sql/item_func.cc index 8139ba81777..b24f61d9011 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -3283,12 +3283,20 @@ longlong Item_func_last_insert_id::val_int() if (arg_count) { longlong value= args[0]->val_int(); - thd->insert_id(value); null_value= args[0]->null_value; - return value; // Avoid side effect of insert_id() + /* + LAST_INSERT_ID(X) must affect the client's mysql_insert_id() as + documented in the manual. We don't want to touch + first_successful_insert_id_in_cur_stmt because it would make + LAST_INSERT_ID(X) take precedence over an generated auto_increment + value for this row. + */ + thd->arg_of_last_insert_id_function= TRUE; + thd->first_successful_insert_id_in_prev_stmt= value; + return value; } thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT); - return thd->last_insert_id_used ? thd->current_insert_id : thd->insert_id(); + return thd->read_first_successful_insert_id_in_prev_stmt(); } /* This function is just used to test speed of different functions */ diff --git a/sql/log.cc b/sql/log.cc index ec73400ea3c..c5f5743c51a 100644 --- a/sql/log.cc +++ b/sql/log.cc @@ -430,16 +430,23 @@ bool Log_to_csv_event_handler:: table->field[6]->set_notnull(); } - if (thd->last_insert_id_used) + if (thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt) { - table->field[7]->store((longlong) thd->current_insert_id, TRUE); + table->field[7]->store((longlong) + thd->first_successful_insert_id_in_prev_stmt_for_binlog, TRUE); table->field[7]->set_notnull(); } - /* set value if we do an insert on autoincrement column */ - if (thd->insert_id_used) + /* + Set value if we do an insert on autoincrement column. Note that for + some engines (those for which get_auto_increment() does not leave a + table lock until the statement ends), this is just the first value and + the next ones used may not be contiguous to it. + */ + if (thd->auto_inc_intervals_in_cur_stmt_for_binlog.nb_elements() > 0) { - table->field[8]->store((longlong) thd->last_insert_id, TRUE); + table->field[8]->store((longlong) + thd->auto_inc_intervals_in_cur_stmt_for_binlog.minimum(), TRUE); table->field[8]->set_notnull(); } @@ -729,7 +736,6 @@ bool LOGGER::slow_log_print(THD *thd, const char *query, uint query_length, Security_context *sctx= thd->security_ctx; uint message_buff_len= 0, user_host_len= 0; longlong query_time= 0, lock_time= 0; - longlong last_insert_id= 0, insert_id= 0; /* Print the message to the buffer if we have slow log enabled @@ -764,13 +770,6 @@ bool LOGGER::slow_log_print(THD *thd, const char *query, uint query_length, lock_time= (longlong) (thd->time_after_lock - query_start_arg); } - if (thd->last_insert_id_used) - last_insert_id= (longlong) thd->current_insert_id; - - /* set value if we do an insert on autoincrement column */ - if (thd->insert_id_used) - insert_id= (longlong) thd->last_insert_id; - if (!query) { is_command= TRUE; @@ -1922,18 +1921,22 @@ bool MYSQL_QUERY_LOG::write(THD *thd, time_t current_time, tmp_errno= errno; strmov(db,thd->db); } - if (thd->last_insert_id_used) + if (thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt) { end=strmov(end, ",last_insert_id="); - end=longlong10_to_str((longlong) thd->current_insert_id, end, -10); + end=longlong10_to_str((longlong) + thd->first_successful_insert_id_in_prev_stmt_for_binlog, + end, -10); } // Save value if we do an insert. - if (thd->insert_id_used) + if (thd->auto_inc_intervals_in_cur_stmt_for_binlog.nb_elements() > 0) { if (!(specialflag & SPECIAL_SHORT_LOG_FORMAT)) { end=strmov(end,",insert_id="); - end=longlong10_to_str((longlong) thd->last_insert_id, end, -10); + end=longlong10_to_str((longlong) + thd->auto_inc_intervals_in_cur_stmt_for_binlog.minimum(), + end, -10); } } @@ -3354,21 +3357,24 @@ bool MYSQL_BIN_LOG::write(Log_event *event_info) { if (!thd->current_stmt_binlog_row_based) { - if (thd->last_insert_id_used) + if (thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt) { Intvar_log_event e(thd,(uchar) LAST_INSERT_ID_EVENT, - thd->current_insert_id); + thd->first_successful_insert_id_in_prev_stmt_for_binlog); if (e.write(file)) goto err; } - if (thd->insert_id_used) + if (thd->auto_inc_intervals_in_cur_stmt_for_binlog.nb_elements() > 0) { + DBUG_PRINT("info",("number of auto_inc intervals: %lu", + thd->auto_inc_intervals_in_cur_stmt_for_binlog.nb_elements())); /* If the auto_increment was second in a table's index (possible with MyISAM or BDB) (table->next_number_key_offset != 0), such event is in fact not necessary. We could avoid logging it. */ - Intvar_log_event e(thd,(uchar) INSERT_ID_EVENT,thd->last_insert_id); + Intvar_log_event e(thd,(uchar) INSERT_ID_EVENT, + thd->auto_inc_intervals_in_cur_stmt_for_binlog.minimum()); if (e.write(file)) goto err; } diff --git a/sql/log_event.cc b/sql/log_event.cc index 02bf22429f2..b61f71a4dcf 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -1921,6 +1921,16 @@ end: thd->query_length= thd->db_length =0; VOID(pthread_mutex_unlock(&LOCK_thread_count)); close_thread_tables(thd); + /* + As a disk space optimization, future masters will not log an event for + LAST_INSERT_ID() if that function returned 0 (and thus they will be able + to replace the THD::stmt_depends_on_first_successful_insert_id_in_prev_stmt + variable by (THD->first_successful_insert_id_in_prev_stmt > 0) ; with the + resetting below we are ready to support that. + */ + thd->first_successful_insert_id_in_prev_stmt_for_binlog= 0; + thd->first_successful_insert_id_in_prev_stmt= 0; + thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt= 0; free_root(thd->mem_root,MYF(MY_KEEP_PREALLOC)); /* If there was an error we stop. Otherwise we increment positions. Note that @@ -3396,11 +3406,11 @@ int Intvar_log_event::exec_event(struct st_relay_log_info* rli) { switch (type) { case LAST_INSERT_ID_EVENT: - thd->last_insert_id_used = 1; - thd->last_insert_id = val; + thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt= 1; + thd->first_successful_insert_id_in_prev_stmt= val; break; case INSERT_ID_EVENT: - thd->next_insert_id = val; + thd->force_one_auto_inc_interval(val); break; } rli->inc_event_relay_log_pos(); diff --git a/sql/set_var.cc b/sql/set_var.cc index da94a332cbc..1176a98713d 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -2794,7 +2794,8 @@ byte *sys_var_timestamp::value_ptr(THD *thd, enum_var_type type, bool sys_var_last_insert_id::update(THD *thd, set_var *var) { - thd->insert_id(var->save_result.ulonglong_value); + thd->first_successful_insert_id_in_prev_stmt= + var->save_result.ulonglong_value; return 0; } @@ -2802,14 +2803,19 @@ bool sys_var_last_insert_id::update(THD *thd, set_var *var) 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; + /* + this tmp var makes it robust againt change of type of + read_first_successful_insert_id_in_prev_stmt(). + */ + thd->sys_var_tmp.ulonglong_value= + thd->read_first_successful_insert_id_in_prev_stmt(); + return (byte*) &thd->sys_var_tmp.ulonglong_value; } bool sys_var_insert_id::update(THD *thd, set_var *var) { - thd->next_insert_id= var->save_result.ulonglong_value; + thd->force_one_auto_inc_interval(var->save_result.ulonglong_value); return 0; } @@ -2817,7 +2823,9 @@ bool sys_var_insert_id::update(THD *thd, set_var *var) byte *sys_var_insert_id::value_ptr(THD *thd, enum_var_type type, LEX_STRING *base) { - return (byte*) &thd->current_insert_id; + thd->sys_var_tmp.ulonglong_value= + thd->auto_inc_intervals_forced.minimum(); + return (byte*) &thd->sys_var_tmp.ulonglong_value; } diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 285a1e72f6f..634e873fb00 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -208,8 +208,12 @@ THD::THD() #endif /*HAVE_ROW_BASED_REPLICATION*/ global_read_lock(0), is_fatal_error(0), rand_used(0), time_zone_used(0), - last_insert_id_used(0), insert_id_used(0), clear_next_insert_id(0), + arg_of_last_insert_id_function(FALSE), + first_successful_insert_id_in_prev_stmt(0), + first_successful_insert_id_in_prev_stmt_for_binlog(0), + first_successful_insert_id_in_cur_stmt(0), in_lock_tables(0), bootstrap(0), derived_tables_processing(FALSE), + stmt_depends_on_first_successful_insert_id_in_prev_stmt(FALSE), spcont(NULL) { stmt_arena= this; @@ -224,7 +228,6 @@ THD::THD() killed= NOT_KILLED; db_length= col_access=0; query_error= tmp_table_used= 0; - next_insert_id=last_insert_id=0; hash_clear(&handler_tables_hash); tmp_table=0; used_tables=0; @@ -628,11 +631,26 @@ bool THD::store_globals() void THD::cleanup_after_query() { - if (clear_next_insert_id) + /* + If in stored function or trigger, where statement-based binlogging logs + only the caller, the insert_id/last_insert_id stored in binlog must + describe their first values inside the routine or caller (the values when + they were first set). Otherwise (e.g. stored procedure) it must describe + their values for the current substatement. + */ + if (!prelocked_mode) + { + stmt_depends_on_first_successful_insert_id_in_prev_stmt= 0; + auto_inc_intervals_in_cur_stmt_for_binlog.empty(); + } + if (first_successful_insert_id_in_cur_stmt > 0) { - clear_next_insert_id= 0; - next_insert_id= 0; + /* set what LAST_INSERT_ID() will return */ + first_successful_insert_id_in_prev_stmt= + first_successful_insert_id_in_cur_stmt; + first_successful_insert_id_in_cur_stmt= 0; } + arg_of_last_insert_id_function= 0; /* Free Items that were created during this execution */ free_items(); /* Reset where. */ @@ -2139,18 +2157,16 @@ void THD::reset_sub_statement_state(Sub_statement_state *backup, backup->in_sub_stmt= in_sub_stmt; backup->no_send_ok= net.no_send_ok; backup->enable_slow_log= enable_slow_log; - backup->last_insert_id= last_insert_id; - backup->next_insert_id= next_insert_id; - backup->current_insert_id= current_insert_id; - backup->insert_id_used= insert_id_used; - backup->last_insert_id_used= last_insert_id_used; - backup->clear_next_insert_id= clear_next_insert_id; backup->limit_found_rows= limit_found_rows; backup->examined_row_count= examined_row_count; backup->sent_row_count= sent_row_count; backup->cuted_fields= cuted_fields; backup->client_capabilities= client_capabilities; backup->savepoints= transaction.savepoints; + backup->first_successful_insert_id_in_prev_stmt= + first_successful_insert_id_in_prev_stmt; + backup->first_successful_insert_id_in_cur_stmt= + first_successful_insert_id_in_cur_stmt; if ((!lex->requires_prelocking() || is_update_query(lex->sql_command)) && !current_stmt_binlog_row_based) @@ -2160,12 +2176,11 @@ void THD::reset_sub_statement_state(Sub_statement_state *backup, /* Disable result sets */ client_capabilities &= ~CLIENT_MULTI_RESULTS; in_sub_stmt|= new_state; - next_insert_id= 0; - insert_id_used= 0; examined_row_count= 0; sent_row_count= 0; cuted_fields= 0; transaction.savepoints= 0; + first_successful_insert_id_in_cur_stmt= 0; /* Surpress OK packets in case if we will execute statements */ net.no_send_ok= TRUE; @@ -2193,12 +2208,10 @@ void THD::restore_sub_statement_state(Sub_statement_state *backup) in_sub_stmt= backup->in_sub_stmt; net.no_send_ok= backup->no_send_ok; enable_slow_log= backup->enable_slow_log; - last_insert_id= backup->last_insert_id; - next_insert_id= backup->next_insert_id; - current_insert_id= backup->current_insert_id; - insert_id_used= backup->insert_id_used; - last_insert_id_used= backup->last_insert_id_used; - clear_next_insert_id= backup->clear_next_insert_id; + first_successful_insert_id_in_prev_stmt= + backup->first_successful_insert_id_in_prev_stmt; + first_successful_insert_id_in_cur_stmt= + backup->first_successful_insert_id_in_cur_stmt; limit_found_rows= backup->limit_found_rows; sent_row_count= backup->sent_row_count; client_capabilities= backup->client_capabilities; @@ -2780,4 +2793,26 @@ int THD::binlog_query(THD::enum_binlog_query_type qtype, DBUG_RETURN(0); } +bool Discrete_intervals_list::append(ulonglong start, ulonglong val, + ulonglong incr) +{ + DBUG_ENTER("Discrete_intervals_list::append"); + /* first, see if this can be merged with previous */ + if ((head == NULL) || tail->merge_if_contiguous(start, val, incr)) + { + /* it cannot, so need to add a new interval */ + Discrete_interval *new_interval= new Discrete_interval(start, val, incr); + if (unlikely(new_interval == NULL)) // out of memory + DBUG_RETURN(1); + DBUG_PRINT("info",("adding new auto_increment interval")); + if (head == NULL) + head= current= new_interval; + else + tail->next= new_interval; + tail= new_interval; + elements++; + } + DBUG_RETURN(0); +} + #endif /* !defined(MYSQL_CLIENT) */ diff --git a/sql/sql_class.h b/sql/sql_class.h index c3b06bb053d..fa1205976f4 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -770,12 +770,14 @@ class Sub_statement_state { public: ulonglong options; - ulonglong last_insert_id, next_insert_id, current_insert_id; + ulonglong first_successful_insert_id_in_prev_stmt; + ulonglong first_successful_insert_id_in_cur_stmt, insert_id_for_cur_row; + Discrete_interval auto_inc_interval_for_cur_row; ulonglong limit_found_rows; ha_rows cuted_fields, sent_row_count, examined_row_count; ulong client_capabilities; uint in_sub_stmt; - bool enable_slow_log, insert_id_used, clear_next_insert_id; + bool enable_slow_log; bool last_insert_id_used; my_bool no_send_ok; SAVEPOINT *savepoints; @@ -1071,24 +1073,136 @@ public: Note: in the parser, stmt_arena == thd, even for PS/SP. */ Query_arena *stmt_arena; + /* Tells if LAST_INSERT_ID(#) was called for the current statement */ + bool arg_of_last_insert_id_function; /* - next_insert_id is set on SET INSERT_ID= #. This is used as the next - generated auto_increment value in handler.cc + ALL OVER THIS FILE, "insert_id" means "*automatically generated* value for + insertion into an auto_increment column". */ - ulonglong next_insert_id; - /* Remember last next_insert_id to reset it if something went wrong */ - ulonglong prev_insert_id; /* - The insert_id used for the last statement or set by SET LAST_INSERT_ID=# - or SELECT LAST_INSERT_ID(#). Used for binary log and returned by - LAST_INSERT_ID() + This is the first autogenerated insert id which was *successfully* + inserted by the previous statement (exactly, if the previous statement + didn't successfully insert an autogenerated insert id, then it's the one + of the statement before, etc). + It can also be set by SET LAST_INSERT_ID=# or SELECT LAST_INSERT_ID(#). + It is returned by LAST_INSERT_ID(). */ - ulonglong last_insert_id; + ulonglong first_successful_insert_id_in_prev_stmt; /* - Set to the first value that LAST_INSERT_ID() returned for the last - statement. When this is set, last_insert_id_used is set to true. + Variant of the above, used for storing in statement-based binlog. The + difference is that the one above can change as the execution of a stored + function progresses, while the one below is set once and then does not + change (which is the value which statement-based binlog needs). */ - ulonglong current_insert_id; + ulonglong first_successful_insert_id_in_prev_stmt_for_binlog; + /* + This is the first autogenerated insert id which was *successfully* + inserted by the current statement. It is maintained only to set + first_successful_insert_id_in_prev_stmt when statement ends. + */ + ulonglong first_successful_insert_id_in_cur_stmt; + /* + We follow this logic: + - when stmt starts, first_successful_insert_id_in_prev_stmt contains the + first insert id successfully inserted by the previous stmt. + - as stmt makes progress, handler::insert_id_for_cur_row changes; every + time get_auto_increment() is called, auto_inc_intervals_for_binlog is + augmented with the reserved interval (if statement-based binlogging). + - at first successful insertion of an autogenerated value, + first_successful_insert_id_in_cur_stmt is set to + handler::insert_id_for_cur_row. + - when stmt goes to binlog, auto_inc_intervals_for_binlog is + binlogged if non-empty. + - when stmt ends, first_successful_insert_id_in_prev_stmt is set to + first_successful_insert_id_in_cur_stmt. + */ + /* + stmt_depends_on_first_successful_insert_id_in_prev_stmt is set when + LAST_INSERT_ID() is used by a statement. + If it is set, first_successful_insert_id_in_prev_stmt_for_binlog will be + stored in the statement-based binlog. + This variable is CUMULATIVE along the execution of a stored function or + trigger: if one substatement sets it to 1 it will stay 1 until the + function/trigger ends, thus making sure that + first_successful_insert_id_in_prev_stmt_for_binlog does not change anymore + and is propagated to the caller for binlogging. + */ + bool stmt_depends_on_first_successful_insert_id_in_prev_stmt; + /* + List of auto_increment intervals reserved by the thread so far, for + storage in the statement-based binlog. + Note that its minimum is not first_successful_insert_id_in_cur_stmt: + assuming a table with an autoinc column, and this happens: + INSERT INTO ... VALUES(3); + SET INSERT_ID=3; INSERT IGNORE ... VALUES (NULL); + then the latter INSERT will insert no rows + (first_successful_insert_id_in_cur_stmt == 0), but storing "INSERT_ID=3" + in the binlog is still needed; the list's minimum will contain 3. + */ + Discrete_intervals_list auto_inc_intervals_in_cur_stmt_for_binlog; + /* Used by replication and SET INSERT_ID */ + Discrete_intervals_list auto_inc_intervals_forced; + /* + There is BUG#19630 where statement-based replication of stored + functions/triggers with two auto_increment columns breaks. + We however ensure that it works when there is 0 or 1 auto_increment + column; our rules are + a) on master, while executing a top statement involving substatements, + first top- or sub- statement to generate auto_increment values wins the + exclusive right to write them to binlog (so the losers won't write their + values to binlog). + b) on slave, while replicating a top statement involving substatements, + first top- or sub- statement to need to read auto_increment values from + the master's binlog wins the exclusive right to read them (so the losers + won't read their values from binlog but instead generate on their own). + a) implies that we mustn't backup/restore + auto_inc_intervals_in_cur_stmt_for_binlog. + b) implies that we mustn't backup/restore auto_inc_intervals_forced. + + If there are more than 1 auto_increment columns, then intervals for + different columns may mix into the + auto_inc_intervals_in_cur_stmt_for_binlog list, which is logically wrong, + but there is no point in preventing this mixing by preventing intervals + from the secondly inserted column to come into the list, as such + prevention would be wrong too. + What will happen in the case of + INSERT INTO t1 (auto_inc) VALUES(NULL); + where t1 has a trigger which inserts into an auto_inc column of t2, is + that in binlog we'll store the interval of t1 and the interval of t2 (when + we store intervals, soon), then in slave, t1 will use both intervals, t2 + will use none; if t1 inserts the same number of rows as on master, + normally the 2nd interval will not be used by t1, which is fine. t2's + values will be wrong if t2's internal auto_increment counter is different + from what it was on master (which is likely). In 5.1, in mixed binlogging + mode, row-based binlogging is used for such cases where two + auto_increment columns are inserted. + */ + inline void record_first_successful_insert_id_in_cur_stmt(ulonglong id) + { + if (first_successful_insert_id_in_cur_stmt == 0) + first_successful_insert_id_in_cur_stmt= id; + } + inline ulonglong read_first_successful_insert_id_in_prev_stmt(void) + { + if (!stmt_depends_on_first_successful_insert_id_in_prev_stmt) + { + /* It's the first time we read it */ + first_successful_insert_id_in_prev_stmt_for_binlog= + first_successful_insert_id_in_prev_stmt; + stmt_depends_on_first_successful_insert_id_in_prev_stmt= 1; + } + return first_successful_insert_id_in_prev_stmt; + } + /* + Used by Intvar_log_event::exec_event() and by "SET INSERT_ID=#" + (mysqlbinlog). We'll soon add a variant which can take many intervals in + argument. + */ + inline void force_one_auto_inc_interval(ulonglong next_id) + { + auto_inc_intervals_forced.append(next_id, ULONGLONG_MAX, 0); + } + ulonglong limit_found_rows; ulonglong options; /* Bitmap of states */ longlong row_count_func; /* For the ROW_COUNT() function */ @@ -1157,7 +1271,6 @@ public: bool last_cuted_field; bool no_errors, password, is_fatal_error; bool query_start_used, rand_used, time_zone_used; - bool last_insert_id_used,insert_id_used, clear_next_insert_id; bool in_lock_tables; bool query_error, bootstrap, cleanup_done; bool tmp_table_used; @@ -1185,9 +1298,10 @@ public: /* Used by the sys_var class to store temporary values */ union { - my_bool my_bool_value; - long long_value; - ulong ulong_value; + my_bool my_bool_value; + long long_value; + ulong ulong_value; + ulonglong ulonglong_value; } sys_var_tmp; struct { @@ -1288,20 +1402,6 @@ public: inline void end_time() { time(&start_time); } inline void set_time(time_t t) { time_after_lock=start_time=user_time=t; } inline void lock_time() { time(&time_after_lock); } - inline void insert_id(ulonglong id_arg) - { - last_insert_id= id_arg; - insert_id_used=1; - } - 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; - } inline ulonglong found_rows(void) { return limit_found_rows; @@ -1617,7 +1717,7 @@ class select_insert :public select_result_interceptor { TABLE_LIST *table_list; TABLE *table; List<Item> *fields; - ulonglong last_insert_id; + ulonglong autoinc_value_of_last_inserted_row; // autogenerated or not COPY_INFO info; bool insert_into_view; diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 6183157377c..1648c6ee198 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -411,7 +411,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, table->next_number_field=table->found_next_number_field; error=0; - id=0; thd->proc_info="update"; if (duplic != DUP_ERROR || ignore) table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); @@ -518,16 +517,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, else #endif error=write_record(thd, table ,&info); - /* - If auto_increment values are used, save the first one - for LAST_INSERT_ID() and for the update log. - We can't use insert_id() as we don't want to touch the - last_insert_id_used flag. - */ - if (! id && thd->insert_id_used) - { // Get auto increment value - id= thd->last_insert_id; - } if (error) break; thd->row_count++; @@ -535,6 +524,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, free_underlaid_joins(thd, &thd->lex->select_lex); joins_freed= TRUE; + table->file->ha_release_auto_increment(); /* Now all rows are inserted. Time to update logs and sends response to @@ -545,7 +535,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, { if (!error) { - id=0; // No auto_increment id info.copied=values_list.elements; end_delayed_insert(thd); } @@ -559,11 +548,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, table->file->print_error(my_errno,MYF(0)); error=1; } - if (id && values_list.elements != 1) - thd->insert_id(id); // For update log - else if (table->next_number_field && info.copied) - id=table->next_number_field->val_int(); // Return auto_increment value - transactional_table= table->file->has_transactions(); if ((changed= (info.copied || info.deleted || info.updated))) @@ -612,18 +596,27 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, } } thd->proc_info="end"; + /* + We'll report to the client this id: + - if the table contains an autoincrement column and we successfully + inserted an autogenerated value, the autogenerated value. + - if the table contains no autoincrement column and LAST_INSERT_ID(X) was + called, X. + - if the table contains an autoincrement column, and some rows were + inserted, the id of the last "inserted" row (if IGNORE, that value may not + have been really inserted but ignored). + */ + id= (thd->first_successful_insert_id_in_cur_stmt > 0) ? + thd->first_successful_insert_id_in_cur_stmt : + (thd->arg_of_last_insert_id_function ? + thd->first_successful_insert_id_in_prev_stmt : + ((table->next_number_field && info.copied) ? + table->next_number_field->val_int() : 0)); table->next_number_field=0; thd->count_cuted_fields= CHECK_FIELD_IGNORE; - thd->next_insert_id=0; // Reset this if wrongly used if (duplic != DUP_ERROR || ignore) table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); - /* Reset value of LAST_INSERT_ID if no rows where inserted */ - if (!info.copied && thd->insert_id_used) - { - thd->insert_id(0); - id=0; - } if (error) goto abort; if (values_list.elements == 1 && (!(thd->options & OPTION_WARNINGS) || @@ -645,8 +638,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, thd->row_count_func= info.copied+info.deleted+info.updated; ::send_ok(thd, (ulong) thd->row_count_func, id, buff); } - if (table != NULL) - table->file->release_auto_increment(); thd->abort_on_warning= 0; DBUG_RETURN(FALSE); @@ -656,7 +647,7 @@ abort: end_delayed_insert(thd); #endif if (table != NULL) - table->file->release_auto_increment(); + table->file->ha_release_auto_increment(); if (!joins_freed) free_underlaid_joins(thd, &thd->lex->select_lex); thd->abort_on_warning= 0; @@ -965,6 +956,8 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) int error, trg_error= 0; char *key=0; MY_BITMAP *save_read_set, *save_write_set; + ulonglong prev_insert_id= table->file->next_insert_id; + ulonglong insert_id_for_cur_row= 0; DBUG_ENTER("write_record"); info->records++; @@ -977,6 +970,17 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) while ((error=table->file->ha_write_row(table->record[0]))) { uint key_nr; + /* + If we do more than one iteration of this loop, from the second one the + row will have an explicit value in the autoinc field, which was set at + the first call of handler::update_auto_increment(). So we must save + the autogenerated value to avoid thd->insert_id_for_cur_row to become + 0. + */ + if (table->file->insert_id_for_cur_row > 0) + insert_id_for_cur_row= table->file->insert_id_for_cur_row; + else + table->file->insert_id_for_cur_row= insert_id_for_cur_row; bool is_duplicate_key_error; if (table->file->is_fatal_error(error, HA_CHECK_DUP)) goto err; @@ -1007,7 +1011,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) if (info->handle_duplicates == DUP_REPLACE && table->next_number_field && key_nr == table->s->next_number_index && - table->file->auto_increment_column_changed) + (insert_id_for_cur_row > 0)) goto err; if (table->file->ha_table_flags() & HA_DUPLICATE_POS) { @@ -1078,15 +1082,21 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) goto err; } 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 (table->next_number_field) - table->file->adjust_next_insert_id_after_explicit_value(table->next_number_field->val_int()); - + { + 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); + } trg_error= (table->triggers && table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, TRUE)); - info->copied++; - goto ok_or_after_trg_err; + info->copiedgoto ok_or_after_trg_err; } else /* DUP_REPLACE */ { @@ -1114,6 +1124,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) table->record[0]))) goto err; info->deleted++; + thd->record_first_successful_insert_id_in_cur_stmt(table->file->insert_id_for_cur_row); /* Since we pretend that we have done insert we should call its after triggers. @@ -1142,6 +1153,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) } } } + thd->record_first_successful_insert_id_in_cur_stmt(table->file->insert_id_for_cur_row); /* Restore column maps if they where replaced during an duplicate key problem. @@ -1155,12 +1167,13 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) if (!info->ignore || table->file->is_fatal_error(error, HA_CHECK_DUP)) goto err; - table->file->restore_auto_increment(); + table->file->restore_auto_increment(prev_insert_id); goto ok_or_after_trg_err; } after_trg_n_copied_inc: info->copied++; + thd->record_first_successful_insert_id_in_cur_stmt(table->file->insert_id_for_cur_row); trg_error= (table->triggers && table->triggers->process_triggers(thd, TRG_EVENT_INSERT, TRG_ACTION_AFTER, TRUE)); @@ -1243,8 +1256,9 @@ public: char *record; enum_duplicates dup; time_t start_time; - bool query_start_used,last_insert_id_used,insert_id_used, ignore, log_query; - ulonglong last_insert_id; + bool query_start_used, ignore, log_query; + bool stmt_depends_on_first_successful_insert_id_in_prev_stmt; + ulonglong first_successful_insert_id_in_prev_stmt; timestamp_auto_set_type timestamp_field_type; LEX_STRING query; @@ -1655,9 +1669,16 @@ write_delayed(THD *thd,TABLE *table, enum_duplicates duplic, memcpy(row->record, table->record[0], table->s->reclength); row->start_time= thd->start_time; row->query_start_used= thd->query_start_used; - row->last_insert_id_used= thd->last_insert_id_used; - row->insert_id_used= thd->insert_id_used; - row->last_insert_id= thd->last_insert_id; + /* + those are for the binlog: LAST_INSERT_ID() has been evaluated at this + time, so record does not need it, but statement-based binlogging of the + INSERT will need when the row is actually inserted. + As for SET INSERT_ID, DELAYED does not honour it (BUG#20830). + */ + row->stmt_depends_on_first_successful_insert_id_in_prev_stmt= + thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt; + row->first_successful_insert_id_in_prev_stmt= + thd->first_successful_insert_id_in_prev_stmt; row->timestamp_field_type= table->timestamp_field_type; di->rows.push_back(row); @@ -1911,6 +1932,7 @@ pthread_handler_t handle_delayed_insert(void *arg) MYSQL_LOCK *lock=thd->lock; thd->lock=0; pthread_mutex_unlock(&di->mutex); + di->table->file->ha_release_auto_increment(); mysql_unlock_tables(thd, lock); di->group_count=0; pthread_mutex_lock(&di->mutex); @@ -2023,13 +2045,6 @@ bool delayed_insert::handle_inserts(void) table->file->extra(HA_EXTRA_WRITE_CACHE); pthread_mutex_lock(&mutex); - /* Reset auto-increment cacheing */ - if (thd.clear_next_insert_id) - { - thd.next_insert_id= 0; - thd.clear_next_insert_id= 0; - } - while ((row=rows.get())) { stacked_inserts--; @@ -2038,9 +2053,12 @@ bool delayed_insert::handle_inserts(void) thd.start_time=row->start_time; thd.query_start_used=row->query_start_used; - thd.last_insert_id=row->last_insert_id; - thd.last_insert_id_used=row->last_insert_id_used; - thd.insert_id_used=row->insert_id_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= + row->stmt_depends_on_first_successful_insert_id_in_prev_stmt; table->timestamp_field_type= row->timestamp_field_type; info.ignore= row->ignore; @@ -2232,7 +2250,7 @@ select_insert::select_insert(TABLE_LIST *table_list_par, TABLE *table_par, enum_duplicates duplic, bool ignore_check_option_errors) :table_list(table_list_par), table(table_par), fields(fields_par), - last_insert_id(0), + autoinc_value_of_last_inserted_row(0), insert_into_view(table_list_par && table_list_par->view != 0) { bzero((char*) &info,sizeof(info)); @@ -2441,15 +2459,20 @@ bool select_insert::send_data(List<Item> &values) if (table->next_number_field) { /* + If no value has been autogenerated so far, we need to remember the + value we just saw, we may need to send it to client in the end. + */ + if (thd->first_successful_insert_id_in_cur_stmt == 0) // optimization + autoinc_value_of_last_inserted_row= + table->next_number_field->val_int(); + /* Clear auto-increment field for the next record, if triggers are used we will clear it twice, but this should be cheap. */ table->next_number_field->reset(); - if (!last_insert_id && thd->insert_id_used) - last_insert_id= thd->insert_id(); } } - table->file->release_auto_increment(); + table->file->ha_release_auto_increment(); DBUG_RETURN(error); } @@ -2511,8 +2534,6 @@ void select_insert::send_error(uint errcode,const char *err) { if (!table->file->has_transactions()) { - if (last_insert_id) - thd->insert_id(last_insert_id); // For binary log if (mysql_bin_log.is_open()) { thd->binlog_query(THD::ROW_QUERY_TYPE, thd->query, thd->query_length, @@ -2532,6 +2553,7 @@ void select_insert::send_error(uint errcode,const char *err) bool select_insert::send_eof() { int error,error2; + ulonglong id; DBUG_ENTER("select_insert::send_eof"); error= (!thd->prelocked_mode) ? table->file->ha_end_bulk_insert():0; @@ -2557,8 +2579,6 @@ bool select_insert::send_eof() thd->options|= OPTION_STATUS_NO_TRANS_UPDATE; } - if (last_insert_id) - thd->insert_id(last_insert_id); // For binary log /* Write to binlog before commiting transaction. No statement will be written by the binlog_query() below in RBR mode. All the @@ -2588,7 +2608,13 @@ bool select_insert::send_eof() sprintf(buff, ER(ER_INSERT_INFO), (ulong) info.records, (ulong) (info.deleted+info.updated), (ulong) thd->cuted_fields); thd->row_count_func= info.copied+info.deleted+info.updated; - ::send_ok(thd, (ulong) thd->row_count_func, last_insert_id, buff); + + id= (thd->first_successful_insert_id_in_cur_stmt > 0) ? + thd->first_successful_insert_id_in_cur_stmt : + (thd->arg_of_last_insert_id_function ? + thd->first_successful_insert_id_in_prev_stmt : + (info.copied ? autoinc_value_of_last_inserted_row : 0)); + ::send_ok(thd, (ulong) thd->row_count_func, id, buff); DBUG_RETURN(0); } diff --git a/sql/sql_load.cc b/sql/sql_load.cc index f8debbedc62..215ea303f0a 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -497,13 +497,12 @@ bool mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, error=ha_autocommit_or_rollback(thd,error); err: + table->file->ha_release_auto_increment(); if (thd->lock) { mysql_unlock_tables(thd, thd->lock); thd->lock=0; } - if (table != NULL) - table->file->release_auto_increment(); thd->abort_on_warning= 0; DBUG_RETURN(error); } @@ -639,14 +638,6 @@ read_fixed_length(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, thd->no_trans_update= no_trans_update; /* - If auto_increment values are used, save the first one - for LAST_INSERT_ID() and for the binary/update log. - We can't use insert_id() as we don't want to touch the - last_insert_id_used flag. - */ - if (!id && thd->insert_id_used) - id= thd->last_insert_id; - /* We don't need to reset auto-increment field since we are restoring its default value at the beginning of each loop iteration. */ @@ -662,8 +653,6 @@ read_fixed_length(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, thd->row_count++; continue_loop:; } - if (id && !read_info.error) - thd->insert_id(id); // For binary/update log DBUG_RETURN(test(read_info.error)); } @@ -807,14 +796,6 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, if (write_record(thd, table, &info)) DBUG_RETURN(1); /* - If auto_increment values are used, save the first one - for LAST_INSERT_ID() and for the binary/update log. - We can't use insert_id() as we don't want to touch the - last_insert_id_used flag. - */ - if (!id && thd->insert_id_used) - id= thd->last_insert_id; - /* We don't need to reset auto-increment field since we are restoring its default value at the beginning of each loop iteration. */ @@ -833,8 +814,6 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, thd->row_count++; continue_loop:; } - if (id && !read_info.error) - thd->insert_id(id); // For binary/update log DBUG_RETURN(test(read_info.error)); } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 1e89e9466ad..3eeb5bedf41 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3343,8 +3343,9 @@ end_with_restore_list: res= mysql_insert(thd, all_tables, lex->field_list, lex->many_values, lex->update_list, lex->value_list, lex->duplicates, lex->ignore); + /* do not show last insert ID if VIEW does not have auto_inc */ if (first_table->view && !first_table->contain_auto_increment) - thd->last_insert_id= 0; // do not show last insert ID if VIEW have not it + thd->first_successful_insert_id_in_cur_stmt= 0; break; } case SQLCOM_REPLACE_SELECT: @@ -3396,9 +3397,9 @@ end_with_restore_list: /* revert changes for SP */ select_lex->table_list.first= (byte*) first_table; } - + /* do not show last insert ID if VIEW does not have auto_inc */ if (first_table->view && !first_table->contain_auto_increment) - thd->last_insert_id= 0; // do not show last insert ID if VIEW have not it + thd->first_successful_insert_id_in_cur_stmt= 0; break; } case SQLCOM_TRUNCATE: @@ -5799,6 +5800,7 @@ mysql_init_query(THD *thd, uchar *buf, uint length) DESCRIPTION This needs to be called before execution of every statement (prepared or conventional). + It is not called by substatements of routines. TODO Make it a method of THD and align its name with the rest of @@ -5809,9 +5811,12 @@ mysql_init_query(THD *thd, uchar *buf, uint length) void mysql_reset_thd_for_next_command(THD *thd) { DBUG_ENTER("mysql_reset_thd_for_next_command"); + DBUG_ASSERT(!thd->spcont); /* not for substatements of routines */ thd->free_list= 0; thd->select_number= 1; - thd->last_insert_id_used= thd->query_start_used= thd->insert_id_used=0; + thd->auto_inc_intervals_in_cur_stmt_for_binlog.empty(); + thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt= + thd->query_start_used= 0; thd->is_fatal_error= thd->time_zone_used= 0; thd->server_status&= ~ (SERVER_MORE_RESULTS_EXISTS | SERVER_QUERY_NO_INDEX_USED | diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d34ff070eb1..72e868d6f8b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7895,7 +7895,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) Field *field=((Item_field*) args[0])->field; if (field->flags & AUTO_INCREMENT_FLAG && !field->table->maybe_null && (thd->options & OPTION_AUTO_IS_NULL) && - thd->insert_id()) + (thd->first_successful_insert_id_in_prev_stmt > 0)) { #ifdef HAVE_QUERY_CACHE query_cache_abort(&thd->net); @@ -7903,7 +7903,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) COND *new_cond; if ((new_cond= new Item_func_eq(args[0], new Item_int("last_insert_id()", - thd->insert_id(), + thd->read_first_successful_insert_id_in_prev_stmt(), 21)))) { cond=new_cond; @@ -7914,7 +7914,11 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) */ cond->fix_fields(thd, &cond); } - thd->insert_id(0); // Clear for next request + /* + IS NULL should be mapped to LAST_INSERT_ID only for first row, so + clear for next row + */ + thd->first_successful_insert_id_in_prev_stmt= 0; } /* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */ else if (((field->type() == FIELD_TYPE_DATE) || diff --git a/sql/sql_table.cc b/sql/sql_table.cc index d68de63e3b8..a08c61e9014 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4953,7 +4953,6 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name, char path[FN_REFLEN]; char reg_path[FN_REFLEN+1]; ha_rows copied,deleted; - ulonglong next_insert_id; uint db_create_options, used_fields; handlerton *old_db_type, *new_db_type; HA_CREATE_INFO *create_info; @@ -5773,7 +5772,6 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name, thd->count_cuted_fields= CHECK_FIELD_WARN; // calc cuted fields thd->cuted_fields=0L; thd->proc_info="copy to tmp table"; - next_insert_id=thd->next_insert_id; // Remember for logging copied=deleted=0; if (new_table && !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER)) { @@ -5784,7 +5782,6 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name, handle_duplicates, ignore, order_num, order, &copied, &deleted); } - thd->last_insert_id=next_insert_id; // Needed for correct log thd->count_cuted_fields= CHECK_FIELD_IGNORE; /* If we did not need to copy, we might still need to add/drop indexes. */ @@ -6214,6 +6211,7 @@ copy_data_between_tables(TABLE *from,TABLE *to, ha_rows examined_rows; bool auto_increment_field_copied= 0; ulong save_sql_mode; + ulonglong prev_insert_id; DBUG_ENTER("copy_data_between_tables"); /* @@ -6320,6 +6318,7 @@ copy_data_between_tables(TABLE *from,TABLE *to, { copy_ptr->do_copy(copy_ptr); } + prev_insert_id= to->file->next_insert_id; if ((error=to->file->ha_write_row((byte*) to->record[0]))) { if (!ignore || handle_duplicates != DUP_ERROR || @@ -6343,7 +6342,7 @@ copy_data_between_tables(TABLE *from,TABLE *to, to->file->print_error(error,MYF(0)); break; } - to->file->restore_auto_increment(); + to->file->restore_auto_increment(prev_insert_id); delete_count++; } else @@ -6377,6 +6376,7 @@ copy_data_between_tables(TABLE *from,TABLE *to, free_io_cache(from); *copied= found_count; *deleted=delete_count; + to->file->ha_release_auto_increment(); if (to->file->ha_external_lock(thd,F_UNLCK)) error=1; DBUG_RETURN(error > 0 ? -1 : 0); diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 2164da01c4c..881d4ba1357 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -135,7 +135,8 @@ int mysql_update(THD *thd, SQL_SELECT *select; READ_RECORD info; SELECT_LEX *select_lex= &thd->lex->select_lex; - bool need_reopen; + bool need_reopen; + ulonglong id; DBUG_ENTER("mysql_update"); for ( ; ; ) @@ -676,6 +677,10 @@ int mysql_update(THD *thd, thd->lock=0; } + /* 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; + if (error < 0) { char buff[STRING_BUFFER_USUAL_SIZE]; @@ -683,8 +688,7 @@ int mysql_update(THD *thd, (ulong) thd->cuted_fields); thd->row_count_func= (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated; - send_ok(thd, (ulong) thd->row_count_func, - thd->insert_id_used ? thd->insert_id() : 0L,buff); + send_ok(thd, (ulong) thd->row_count_func, id, buff); DBUG_PRINT("info",("%d records updated",updated)); } thd->count_cuted_fields= CHECK_FIELD_IGNORE; /* calc cuted fields */ @@ -1634,6 +1638,7 @@ err2: bool multi_update::send_eof() { char buff[STRING_BUFFER_USUAL_SIZE]; + ulonglong id; thd->proc_info="updating reference tables"; /* Does updates for the last n - 1 tables, returns 0 if ok */ @@ -1686,12 +1691,12 @@ bool multi_update::send_eof() return TRUE; } - + id= thd->arg_of_last_insert_id_function ? + thd->first_successful_insert_id_in_prev_stmt : 0; sprintf(buff, ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated, (ulong) thd->cuted_fields); thd->row_count_func= (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated; - ::send_ok(thd, (ulong) thd->row_count_func, - thd->insert_id_used ? thd->insert_id() : 0L,buff); + ::send_ok(thd, (ulong) thd->row_count_func, id, buff); return FALSE; } diff --git a/sql/structs.h b/sql/structs.h index 38bb441fc03..83ae6cac032 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -250,3 +250,99 @@ typedef struct user_conn { #define STATUS_UPDATED 16 /* Record is updated by formula */ #define STATUS_NULL_ROW 32 /* table->null_row is set */ #define STATUS_DELETED 64 + +/* + Such interval is "discrete": it is the set of + { auto_inc_interval_min + k * increment, + 0 <= k <= (auto_inc_interval_values-1) } + Where "increment" is maintained separately by the user of this class (and is + currently only thd->variables.auto_increment_increment). + It mustn't derive from Sql_alloc, because SET INSERT_ID needs to + allocate memory which must stay allocated for use by the next statement. +*/ +class Discrete_interval { +private: + ulonglong interval_min; + ulonglong interval_values; + ulonglong interval_max; // excluded bound. Redundant. +public: + Discrete_interval *next; // used when linked into Discrete_intervals_list + void replace(ulonglong start, ulonglong val, ulonglong incr) + { + interval_min= start; + interval_values= val; + interval_max= (val == ULONGLONG_MAX) ? val : start + val * incr; + } + Discrete_interval(ulonglong start, ulonglong val, ulonglong incr) : + next(NULL) { replace(start, val, incr); }; + Discrete_interval() : next(NULL) { replace(0, 0, 0); }; + ulonglong minimum() const { return interval_min; }; + ulonglong values() const { return interval_values; }; + ulonglong maximum() const { return interval_max; }; + /* + If appending [3,5] to [1,2], we merge both in [1,5] (they should have the + same increment for that, user of the class has to ensure that). That is + just a space optimization. Returns 0 if merge succeeded. + */ + bool merge_if_contiguous(ulonglong start, ulonglong val, ulonglong incr) + { + if (interval_max == start) + { + if (val == ULONGLONG_MAX) + { + interval_values= interval_max= val; + } + else + { + interval_values+= val; + interval_max= start + val * incr; + } + return 0; + } + return 1; + }; +}; + +/* List of Discrete_interval objects */ +class Discrete_intervals_list { +private: + Discrete_interval *head; + Discrete_interval *tail; + /* + When many intervals are provided at the beginning of the execution of a + statement (in a replication slave or SET INSERT_ID), "current" points to + the interval being consumed by the thread now (so "current" goes from + "head" to "tail" then to NULL). + */ + Discrete_interval *current; + uint elements; // number of elements +public: + Discrete_intervals_list() : head(NULL), current(NULL), elements(0) {}; + void empty_no_free() + { + head= current= NULL; + elements= 0; + } + void empty() + { + for (Discrete_interval *i= head; i;) + { + Discrete_interval *next= i->next; + delete i; + i= next; + } + empty_no_free(); + } + const Discrete_interval* get_next() + { + Discrete_interval *tmp= current; + if (current != NULL) + current= current->next; + return tmp; + } + ~Discrete_intervals_list() { empty(); }; + bool append(ulonglong start, ulonglong val, ulonglong incr); + ulonglong minimum() const { return (head ? head->minimum() : 0); }; + ulonglong maximum() const { return (head ? tail->maximum() : 0); }; + uint nb_elements() const { return elements; } +}; diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index e3159cfa5e5..3173b896075 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -14947,7 +14947,193 @@ static void test_bug14169() rc= mysql_query(mysql, "drop table t1"); myquery(rc); -}/* +} + + +/* + Test that mysql_insert_id() behaves as documented in our manual +*/ +static void test_mysql_insert_id() +{ + my_ulonglong res; + int rc; + + myheader("test_mysql_insert_id"); + + rc= mysql_query(mysql, "drop table if exists t1"); + myquery(rc); + /* table without auto_increment column */ + rc= mysql_query(mysql, "create table t1 (f1 int, f2 varchar(255), key(f1))"); + myquery(rc); + rc= mysql_query(mysql, "insert into t1 values (1,'a')"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 0); + rc= mysql_query(mysql, "insert into t1 values (null,'b')"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 0); + rc= mysql_query(mysql, "insert into t1 select 5,'c'"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 0); + rc= mysql_query(mysql, "insert into t1 select null,'d'"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 0); + rc= mysql_query(mysql, "insert into t1 values (null,last_insert_id(300))"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 300); + rc= mysql_query(mysql, "insert into t1 select null,last_insert_id(400)"); + myquery(rc); + res= mysql_insert_id(mysql); + /* + Behaviour change: old code used to return 0; but 400 is consistent + with INSERT VALUES, and the manual's section of mysql_insert_id() does not + say INSERT SELECT should be different. + */ + DIE_UNLESS(res == 400); + + /* table with auto_increment column */ + rc= mysql_query(mysql, "create table t2 (f1 int not null primary key auto_increment, f2 varchar(255))"); + myquery(rc); + rc= mysql_query(mysql, "insert into t2 values (1,'a')"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 1); + /* this should not influence next INSERT if it doesn't have auto_inc */ + rc= mysql_query(mysql, "insert into t1 values (10,'e')"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 0); + + rc= mysql_query(mysql, "insert into t2 values (null,'b')"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 2); + rc= mysql_query(mysql, "insert into t2 select 5,'c'"); + myquery(rc); + res= mysql_insert_id(mysql); + /* + Manual says that for multirow insert this should have been 5, but does not + say for INSERT SELECT. This is a behaviour change: old code used to return + 0. We try to be consistent with INSERT VALUES. + */ + DIE_UNLESS(res == 5); + rc= mysql_query(mysql, "insert into t2 select null,'d'"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 6); + /* with more than one row */ + rc= mysql_query(mysql, "insert into t2 values (10,'a'),(11,'b')"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 11); + rc= mysql_query(mysql, "insert into t2 select 12,'a' union select 13,'b'"); + myquery(rc); + res= mysql_insert_id(mysql); + /* + Manual says that for multirow insert this should have been 13, but does + not say for INSERT SELECT. This is a behaviour change: old code used to + return 0. We try to be consistent with INSERT VALUES. + */ + DIE_UNLESS(res == 13); + rc= mysql_query(mysql, "insert into t2 values (null,'a'),(null,'b')"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 14); + rc= mysql_query(mysql, "insert into t2 select null,'a' union select null,'b'"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 16); + rc= mysql_query(mysql, "insert into t2 select 12,'a' union select 13,'b'"); + myquery_r(rc); + rc= mysql_query(mysql, "insert ignore into t2 select 12,'a' union select 13,'b'"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 0); + rc= mysql_query(mysql, "insert into t2 values (12,'a'),(13,'b')"); + myquery_r(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 0); + rc= mysql_query(mysql, "insert ignore into t2 values (12,'a'),(13,'b')"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 0); + /* mixing autogenerated and explicit values */ + rc= mysql_query(mysql, "insert into t2 values (null,'e'),(12,'a'),(13,'b')"); + myquery_r(rc); + rc= mysql_query(mysql, "insert into t2 values (null,'e'),(12,'a'),(13,'b'),(25,'g')"); + myquery_r(rc); + rc= mysql_query(mysql, "insert into t2 values (null,last_insert_id(300))"); + myquery(rc); + res= mysql_insert_id(mysql); + /* + according to the manual, this might be 20 or 300, but it looks like + auto_increment column takes priority over last_insert_id(). + */ + DIE_UNLESS(res == 20); + /* If first autogenerated number fails and 2nd works: */ + rc= mysql_query(mysql, "drop table t2"); + myquery(rc); + rc= mysql_query(mysql, "create table t2 (f1 int not null primary key " + "auto_increment, f2 varchar(255), unique (f2))"); + myquery(rc); + rc= mysql_query(mysql, "insert into t2 values (null,'e')"); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 1); + rc= mysql_query(mysql, "insert ignore into t2 values (null,'e'),(null,'a'),(null,'e')"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 2); + /* If autogenerated fails and explicit works: */ + rc= mysql_query(mysql, "insert ignore into t2 values (null,'e'),(12,'c'),(null,'d')"); + myquery(rc); + res= mysql_insert_id(mysql); + /* + Behaviour change: old code returned 3 (first autogenerated, even if it + fails); we now return first successful autogenerated. + */ + DIE_UNLESS(res == 13); + /* UPDATE may update mysql_insert_id() if it uses LAST_INSERT_ID(#) */ + rc= mysql_query(mysql, "update t2 set f1=14 where f1=12"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 0); + rc= mysql_query(mysql, "update t2 set f1=NULL where f1=14"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 0); + rc= mysql_query(mysql, "update t2 set f2=last_insert_id(372) where f1=0"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 372); + /* check that LAST_INSERT_ID() does not update mysql_insert_id(): */ + rc= mysql_query(mysql, "insert into t2 values (null,'g')"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 15); + rc= mysql_query(mysql, "update t2 set f2=(@li:=last_insert_id()) where f1=15"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 0); + /* + Behaviour change: now if ON DUPLICATE KEY UPDATE updates a row, + mysql_insert_id() returns the id of the row, instead of not being + affected. + */ + rc= mysql_query(mysql, "insert into t2 values (null,@li) on duplicate key " + "update f2=concat('we updated ',f2)"); + myquery(rc); + res= mysql_insert_id(mysql); + DIE_UNLESS(res == 15); + + rc= mysql_query(mysql, "drop table t1,t2"); + myquery(rc); +} + +/* Read and parse arguments and MySQL options from my.cnf */ @@ -15214,6 +15400,7 @@ static struct my_tests_st my_tests[]= { { "test_bug15613", test_bug15613 }, { "test_bug14169", test_bug14169 }, { "test_bug17667", test_bug17667 }, + { "test_mysql_insert_id", test_mysql_insert_id }, { 0, 0 } }; |