diff options
51 files changed, 2654 insertions, 711 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/binlog_tests/mix_innodb_myisam_binlog.test b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test index b5052620f91..bdc49573ae5 100644 --- a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test +++ b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test @@ -29,7 +29,7 @@ insert into t1 values(1); insert into t2 select * from t1; commit; ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; @@ -43,7 +43,7 @@ insert into t2 select * from t1; # should say some changes to non-transact1onal tables couldn't be rolled back rollback; ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; @@ -59,7 +59,7 @@ insert into t2 select * from t1; rollback to savepoint my_savepoint; commit; ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; @@ -77,7 +77,7 @@ insert into t1 values(7); commit; select a from t1 order by a; # check that savepoints work :) ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; @@ -99,7 +99,7 @@ connection con2; # so SHOW BINLOG EVENTS may come before con1 does the loggin. To be sure that # logging has been done, we use a user lock. select get_lock("a",10); ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; @@ -111,7 +111,7 @@ reset master; insert into t1 values(9); insert into t2 select * from t1; ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; @@ -124,13 +124,13 @@ reset master; insert into t1 values(10); # first make t1 non-empty begin; insert into t2 select * from t1; ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; insert into t1 values(11); commit; ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; @@ -149,7 +149,7 @@ insert into t1 values(12); insert into t2 select * from t1; commit; ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; @@ -162,7 +162,7 @@ insert into t1 values(13); insert into t2 select * from t1; rollback; ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; @@ -178,7 +178,7 @@ insert into t2 select * from t1; rollback to savepoint my_savepoint; commit; ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; @@ -196,7 +196,7 @@ insert into t1 values(18); commit; select a from t1 order by a; # check that savepoints work :) ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; @@ -257,7 +257,7 @@ insert into t2 values (3); disconnect con2; connection con3; select get_lock("lock1",60); ---replace_column 5 # +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; do release_lock("lock1"); @@ -324,6 +324,7 @@ CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * ROLLBACK; SELECT * from t2; DROP TABLE t1,t2; +--replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\// show binlog events from 102; diff --git a/mysql-test/extra/rpl_tests/rpl_auto_increment.test b/mysql-test/extra/rpl_tests/rpl_auto_increment.test index 42f8fcfc5fb..dbae317e8ab 100644 --- a/mysql-test/extra/rpl_tests/rpl_auto_increment.test +++ b/mysql-test/extra/rpl_tests/rpl_auto_increment.test @@ -104,9 +104,47 @@ select * from t1; sync_slave_with_master; select * from t1; -connection master; +# Test for BUG#20524 "auto_increment_* not observed when inserting +# a too large value". When an autogenerated value was bigger than the +# maximum possible value of the field, it was truncated to that max +# possible value, without being "rounded down" to still honour +# auto_increment_* variables. + +connection master; drop table t1; +create table t1 (a tinyint not null auto_increment primary key) engine=myisam; +insert into t1 values(103); +set auto_increment_increment=11; +set auto_increment_offset=4; +insert into t1 values(null); +insert into t1 values(null); +--error 1062 +insert into t1 values(null); +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a; + +# same but with a larger value +create table t2 (a tinyint unsigned not null auto_increment primary key) engine=myisam; +set auto_increment_increment=10; +set auto_increment_offset=1; +set insert_id=1000; +insert into t2 values(null); +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a; + +# An offset so big that even first value does not fit +create table t3 like t1; +set auto_increment_increment=1000; +set auto_increment_offset=700; +insert into t3 values(null); +select * from t3 order by a; +sync_slave_with_master; +select * from t1 order by a; +select * from t2 order by a; +select * from t3 order by a; + +connection master; + +drop table t1,t2,t3; # End cleanup sync_slave_with_master; diff --git a/mysql-test/extra/rpl_tests/rpl_insert_id.test b/mysql-test/extra/rpl_tests/rpl_insert_id.test index 68e39c54381..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,7 +171,106 @@ 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 + +create table t1 (n int primary key auto_increment not null, +b int, unique(b)); + +# First, test that we do not call restore_auto_increment() too early +# in write_record(): +set sql_log_bin=0; +insert into t1 values(null,100); +replace into t1 values(null,50),(null,100),(null,150); +select * from t1 order by n; +truncate table t1; +set sql_log_bin=1; + +insert into t1 values(null,100); +select * from t1 order by n; +sync_slave_with_master; +# make slave's table autoinc counter bigger +insert into t1 values(null,200),(null,300); +delete from t1 where b <> 100; +# check that slave's table content is identical to master +select * from t1 order by n; +# only the auto_inc counter differs. + +connection master; +replace into t1 values(null,100),(null,350); +select * from t1 order by n; +sync_slave_with_master; +select * from t1 order by n; + +# Same test as for REPLACE, but for ON DUPLICATE KEY UPDATE + +# We first check that if we update a row using a value larger than the +# table's counter, the counter for next row is bigger than the +# after-value of the updated row. +connection master; +insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000; +select * from t1 order by n; +sync_slave_with_master; +select * from t1 order by n; + +# and now test for the bug: +connection master; +drop table t1; +create table t1 (n int primary key auto_increment not null, +b int, unique(b)); +insert into t1 values(null,100); +select * from t1 order by n; +sync_slave_with_master; +insert into t1 values(null,200),(null,300); +delete from t1 where b <> 100; +select * from t1 order by n; + +connection master; +insert into t1 values(null,100),(null,350) on duplicate key update n=2; +select * from t1 order by n; +sync_slave_with_master; +select * from t1 order by n; + +connection master; +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/lib/mtr_cases.pl b/mysql-test/lib/mtr_cases.pl index 448ca90d48d..6f326562649 100644 --- a/mysql-test/lib/mtr_cases.pl +++ b/mysql-test/lib/mtr_cases.pl @@ -37,6 +37,23 @@ sub collect_test_cases ($) { opendir(TESTDIR, $testdir) or mtr_error("Can't open dir \"$testdir\": $!"); + # ---------------------------------------------------------------------- + # Disable some tests listed in disabled.def + # ---------------------------------------------------------------------- + my %disabled; + if ( open(DISABLED, "$testdir/disabled.def" ) ) + { + while ( <DISABLED> ) + { + chomp; + if ( /^\s*(\S+)\s*:\s*(.*?)\s*$/ ) + { + $disabled{$1}= $2; + } + } + close DISABLED; + } + if ( @::opt_cases ) { foreach my $tname ( @::opt_cases ) { # Run in specified order, no sort @@ -100,30 +117,13 @@ sub collect_test_cases ($) { } } - collect_one_test_case($testdir,$resdir,$tname,$elem,$cases,{}, + collect_one_test_case($testdir,$resdir,$tname,$elem,$cases,\%disabled, $component_id); } closedir TESTDIR; } else { - # ---------------------------------------------------------------------- - # Disable some tests listed in disabled.def - # ---------------------------------------------------------------------- - my %disabled; - if ( ! $::opt_ignore_disabled_def and open(DISABLED, "$testdir/disabled.def" ) ) - { - while ( <DISABLED> ) - { - chomp; - if ( /^\s*(\S+)\s*:\s*(.*?)\s*$/ ) - { - $disabled{$1}= $2; - } - } - close DISABLED; - } - foreach my $elem ( sort readdir(TESTDIR) ) { my $component_id= undef; my $tname= undef; @@ -414,20 +414,35 @@ sub collect_one_test_case($$$$$$$) { } # FIXME why this late? + my $marked_as_disabled= 0; if ( $disabled->{$tname} ) { - $tinfo->{'skip'}= 1; - $tinfo->{'disable'}= 1; # Sub type of 'skip' - $tinfo->{'comment'}= $disabled->{$tname} if $disabled->{$tname}; + $marked_as_disabled= 1; + $tinfo->{'comment'}= $disabled->{$tname}; } if ( -f $disabled_file ) { - $tinfo->{'skip'}= 1; - $tinfo->{'disable'}= 1; # Sub type of 'skip' + $marked_as_disabled= 1; $tinfo->{'comment'}= mtr_fromfile($disabled_file); } + # If test was marked as disabled, either opt_enable_disabled is off and then + # we skip this test, or it is on and then we run this test but warn + + if ( $marked_as_disabled ) + { + if ( $::opt_enable_disabled ) + { + $tinfo->{'dont_skip_though_disabled'}= 1; + } + else + { + $tinfo->{'skip'}= 1; + $tinfo->{'disable'}= 1; # Sub type of 'skip' + } + } + if ( $component_id eq 'im' ) { if ( $::glob_use_embedded_server ) diff --git a/mysql-test/lib/mtr_report.pl b/mysql-test/lib/mtr_report.pl index f2da89355f7..6a68e3a68d0 100644 --- a/mysql-test/lib/mtr_report.pl +++ b/mysql-test/lib/mtr_report.pl @@ -10,6 +10,7 @@ sub mtr_report_test_name($); sub mtr_report_test_passed($); sub mtr_report_test_failed($); sub mtr_report_test_skipped($); +sub mtr_report_test_not_skipped_though_disabled($); sub mtr_show_failed_diff ($); sub mtr_report_stats ($); @@ -100,6 +101,23 @@ sub mtr_report_test_skipped ($) { } } +sub mtr_report_tests_not_skipped_though_disabled ($) { + my $tests= shift; + + if ( $::opt_enable_disabled ) + { + my @disabled_tests= grep {$_->{'dont_skip_though_disabled'}} @$tests; + if ( @disabled_tests ) + { + print "\nTest(s) which will be run though they are marked as disabled:\n"; + foreach my $tinfo ( sort {$a->{'name'} cmp $b->{'name'}} @disabled_tests ) + { + printf " %-20s : %s\n", $tinfo->{'name'}, $tinfo->{'comment'}; + } + } + } +} + sub mtr_report_test_passed ($) { my $tinfo= shift; diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index ae3cab3f17c..fb000906e1e 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -216,6 +216,7 @@ our $opt_extern; our $opt_fast; our $opt_force; our $opt_reorder; +our $opt_enable_disabled; our $opt_gcov; our $opt_gcov_err; @@ -660,6 +661,7 @@ sub command_line_setup () { 'netware' => \$opt_netware, 'old-master' => \$opt_old_master, 'reorder' => \$opt_reorder, + 'enable-disabled' => \$opt_enable_disabled, 'script-debug' => \$opt_script_debug, 'sleep=i' => \$opt_sleep, 'socket=s' => \$opt_socket, @@ -1794,12 +1796,12 @@ sub run_suite () { mtr_print_thick_line(); - mtr_report("Finding Tests in the '$suite' suite"); - mtr_timer_start($glob_timers,"suite", 60 * $opt_suite_timeout); mtr_report("Starting Tests in the '$suite' suite"); + mtr_report_tests_not_skipped_though_disabled($tests); + mtr_print_header(); foreach my $tinfo ( @$tests ) @@ -3357,6 +3359,12 @@ sub run_mysqltest ($) { "--port=$master->[0]->{'path_myport'} " . "--socket=$master->[0]->{'path_mysock'}"; + if ( $opt_debug ) + { + $cmdline_mysql_client_test .= + " --debug=d:t:A,$opt_vardir_trace/log/mysql_client_test.trace"; + } + if ( $glob_use_embedded_server ) { $cmdline_mysql_client_test.= 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_row_mix_innodb_myisam.result b/mysql-test/r/binlog_row_mix_innodb_myisam.result index 32c21a01f27..ae66f98739d 100644 --- a/mysql-test/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/r/binlog_row_mix_innodb_myisam.result @@ -8,12 +8,12 @@ insert into t2 select * from t1; commit; show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Query 1 # use `test`; BEGIN -master-bin.000001 170 Table_map 1 # table_id: # (test.t1) -master-bin.000001 209 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 243 Table_map 1 # table_id: # (test.t2) -master-bin.000001 282 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 316 Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Query 1 # use `test`; BEGIN +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.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Xid 1 # COMMIT /* xid= */ delete from t1; delete from t2; reset master; @@ -25,12 +25,12 @@ Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Query 1 # use `test`; BEGIN -master-bin.000001 170 Table_map 1 # table_id: # (test.t1) -master-bin.000001 209 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 243 Table_map 1 # table_id: # (test.t2) -master-bin.000001 282 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 316 Query 1 # use `test`; ROLLBACK +master-bin.000001 # Query 1 # use `test`; BEGIN +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.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `test`; ROLLBACK delete from t1; delete from t2; reset master; @@ -45,16 +45,16 @@ Warning 1196 Some non-transactional changed tables couldn't be rolled back commit; show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Query 1 # use `test`; BEGIN -master-bin.000001 170 Table_map 1 # table_id: # (test.t1) -master-bin.000001 209 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 243 Query 1 # use `test`; savepoint my_savepoint -master-bin.000001 328 Table_map 1 # table_id: # (test.t1) -master-bin.000001 367 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 401 Table_map 1 # table_id: # (test.t2) -master-bin.000001 440 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 479 Query 1 # use `test`; rollback to savepoint my_savepoint -master-bin.000001 576 Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Query 1 # use `test`; BEGIN +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`; savepoint my_savepoint +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.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `test`; rollback to savepoint my_savepoint +master-bin.000001 # Xid 1 # COMMIT /* xid= */ delete from t1; delete from t2; reset master; @@ -74,18 +74,18 @@ a 7 show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Query 1 # use `test`; BEGIN -master-bin.000001 170 Table_map 1 # table_id: # (test.t1) -master-bin.000001 209 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 243 Query 1 # use `test`; savepoint my_savepoint -master-bin.000001 328 Table_map 1 # table_id: # (test.t1) -master-bin.000001 367 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 401 Table_map 1 # table_id: # (test.t2) -master-bin.000001 440 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 479 Query 1 # use `test`; rollback to savepoint my_savepoint -master-bin.000001 576 Table_map 1 # table_id: # (test.t1) -master-bin.000001 615 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 649 Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Query 1 # use `test`; BEGIN +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`; savepoint my_savepoint +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.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `test`; rollback to savepoint my_savepoint +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 # Xid 1 # COMMIT /* xid= */ delete from t1; delete from t2; reset master; @@ -100,12 +100,12 @@ get_lock("a",10) 1 show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Query 1 # use `test`; BEGIN -master-bin.000001 170 Table_map 1 # table_id: # (test.t1) -master-bin.000001 209 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 243 Table_map 1 # table_id: # (test.t2) -master-bin.000001 282 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 316 Query 1 # use `test`; ROLLBACK +master-bin.000001 # Query 1 # use `test`; BEGIN +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.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `test`; ROLLBACK delete from t1; delete from t2; reset master; @@ -113,11 +113,11 @@ insert into t1 values(9); insert into t2 select * from t1; show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Table_map 1 # table_id: # (test.t1) -master-bin.000001 141 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 175 Xid 1 # COMMIT /* xid= */ -master-bin.000001 202 Table_map 1 # table_id: # (test.t2) -master-bin.000001 241 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 # Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Table_map 1 # table_id: # (test.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F delete from t1; delete from t2; reset master; @@ -126,24 +126,24 @@ begin; insert into t2 select * from t1; show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Table_map 1 # table_id: # (test.t1) -master-bin.000001 141 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 175 Xid 1 # COMMIT /* xid= */ -master-bin.000001 202 Table_map 1 # table_id: # (test.t2) -master-bin.000001 241 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 # Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Table_map 1 # table_id: # (test.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F insert into t1 values(11); commit; show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Table_map 1 # table_id: # (test.t1) -master-bin.000001 141 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 175 Xid 1 # COMMIT /* xid= */ -master-bin.000001 202 Table_map 1 # table_id: # (test.t2) -master-bin.000001 241 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 275 Query 1 # use `test`; BEGIN -master-bin.000001 343 Table_map 1 # table_id: # (test.t1) -master-bin.000001 382 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 416 Xid 1 # COMMIT /* xid= */ +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 # Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Table_map 1 # table_id: # (test.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `test`; BEGIN +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 # Xid 1 # COMMIT /* xid= */ alter table t2 engine=INNODB; delete from t1; delete from t2; @@ -154,12 +154,12 @@ insert into t2 select * from t1; commit; show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Query 1 # use `test`; BEGIN -master-bin.000001 170 Table_map 1 # table_id: # (test.t1) -master-bin.000001 209 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 243 Table_map 1 # table_id: # (test.t2) -master-bin.000001 282 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 316 Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Query 1 # use `test`; BEGIN +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.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Xid 1 # COMMIT /* xid= */ delete from t1; delete from t2; reset master; @@ -181,10 +181,10 @@ rollback to savepoint my_savepoint; commit; show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Query 1 # use `test`; BEGIN -master-bin.000001 170 Table_map 1 # table_id: # (test.t1) -master-bin.000001 209 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 243 Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Query 1 # use `test`; BEGIN +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 # Xid 1 # COMMIT /* xid= */ delete from t1; delete from t2; reset master; @@ -202,12 +202,12 @@ a 18 show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Query 1 # use `test`; BEGIN -master-bin.000001 170 Table_map 1 # table_id: # (test.t1) -master-bin.000001 209 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 243 Table_map 1 # table_id: # (test.t1) -master-bin.000001 282 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 316 Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Query 1 # use `test`; BEGIN +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 # Xid 1 # COMMIT /* xid= */ delete from t1; delete from t2; alter table t2 engine=MyISAM; @@ -254,28 +254,28 @@ get_lock("lock1",60) 1 show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Query 1 # use `test`; BEGIN -master-bin.000001 170 Table_map 1 # table_id: # (test.t1) -master-bin.000001 209 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 243 Table_map 1 # table_id: # (test.t1) -master-bin.000001 282 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 316 Xid 1 # COMMIT /* xid= */ -master-bin.000001 343 Table_map 1 # table_id: # (test.t1) -master-bin.000001 382 Delete_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 421 Xid 1 # COMMIT /* xid= */ -master-bin.000001 448 Query 1 # use `test`; alter table t2 engine=MyISAM -master-bin.000001 539 Table_map 1 # table_id: # (test.t1) -master-bin.000001 578 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 612 Xid 1 # COMMIT /* xid= */ -master-bin.000001 639 Table_map 1 # table_id: # (test.t2) -master-bin.000001 678 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 712 Query 1 # use `test`; drop table t1,t2 -master-bin.000001 791 Query 1 # use `test`; create table t0 (n int) -master-bin.000001 877 Table_map 1 # table_id: # (test.t0) -master-bin.000001 916 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 950 Table_map 1 # table_id: # (test.t0) -master-bin.000001 989 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 1023 Query 1 # use `test`; create table t2 (n int) engine=innodb +master-bin.000001 # Query 1 # use `test`; BEGIN +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 # Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Table_map 1 # table_id: # (test.t1) +master-bin.000001 # Delete_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Query 1 # use `test`; alter table t2 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 # Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Table_map 1 # table_id: # (test.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `test`; drop table t1,t2 +master-bin.000001 # Query 1 # use `test`; create table t0 (n int) +master-bin.000001 # Table_map 1 # table_id: # (test.t0) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (test.t0) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `test`; create table t2 (n int) engine=innodb do release_lock("lock1"); drop table t0,t2; set autocommit=0; @@ -357,83 +357,55 @@ a b DROP TABLE t1,t2; show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 102 Table_map 1 142 table_id: # (test.t1) -master-bin.000001 142 Write_rows 1 189 table_id: # flags: STMT_END_F -master-bin.000001 189 Query 1 257 use `test`; BEGIN -master-bin.000001 257 Query 1 182 use `test`; CREATE TABLE `t2` ( +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`; BEGIN +master-bin.000001 # Query 1 # use `test`; CREATE TABLE `t2` ( `a` int(11) NOT NULL DEFAULT '0', `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB -master-bin.000001 439 Table_map 1 222 table_id: # (test.t2) -master-bin.000001 479 Write_rows 1 260 table_id: # flags: STMT_END_F -master-bin.000001 517 Xid 1 544 COMMIT /* xid= */ -master-bin.000001 544 Query 1 630 use `test`; DROP TABLE if exists t2 -master-bin.000001 630 Table_map 1 670 table_id: # (test.t1) -master-bin.000001 670 Write_rows 1 708 table_id: # flags: STMT_END_F -master-bin.000001 708 Query 1 776 use `test`; BEGIN -master-bin.000001 776 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` ( +master-bin.000001 # Table_map 1 # table_id: # (test.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Query 1 # use `test`; DROP TABLE if exists t2 +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 IF EXISTS t2 +master-bin.000001 # Query 1 # use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb +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`; BEGIN +master-bin.000001 # Query 1 # use `test`; CREATE TABLE `t2` ( `a` int(11) NOT NULL DEFAULT '0', `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB -master-bin.000001 968 Query 1 1039 use `test`; ROLLBACK -master-bin.000001 1039 Query 1 1125 use `test`; DROP TABLE IF EXISTS t2 -master-bin.000001 1125 Query 1 1249 use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb -master-bin.000001 1249 Table_map 1 1289 table_id: # (test.t1) -master-bin.000001 1289 Write_rows 1 1327 table_id: # flags: STMT_END_F -master-bin.000001 1327 Query 1 1395 use `test`; BEGIN -master-bin.000001 1395 Query 1 182 use `test`; CREATE TABLE `t2` ( - `a` int(11) NOT NULL DEFAULT '0', - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB -master-bin.000001 1577 Table_map 1 222 table_id: # (test.t2) -master-bin.000001 1617 Write_rows 1 260 table_id: # flags: STMT_END_F -master-bin.000001 1655 Xid 1 1682 COMMIT /* xid= */ -master-bin.000001 1682 Query 1 80 use `test`; TRUNCATE table t2 -master-bin.000001 1762 Xid 1 1789 COMMIT /* xid= */ -master-bin.000001 1789 Table_map 1 1829 table_id: # (test.t1) -master-bin.000001 1829 Write_rows 1 1867 table_id: # flags: STMT_END_F -master-bin.000001 1867 Query 1 1935 use `test`; BEGIN -master-bin.000001 1935 Table_map 1 40 table_id: # (test.t2) -master-bin.000001 1975 Write_rows 1 78 table_id: # flags: STMT_END_F -master-bin.000001 2013 Xid 1 2040 COMMIT /* xid= */ -master-bin.000001 2040 Query 1 2116 use `test`; DROP TABLE t2 -master-bin.000001 2116 Table_map 1 2156 table_id: # (test.t1) -master-bin.000001 2156 Write_rows 1 2194 table_id: # flags: STMT_END_F -master-bin.000001 2194 Table_map 1 2234 table_id: # (test.t1) -master-bin.000001 2234 Write_rows 1 2272 table_id: # flags: STMT_END_F -master-bin.000001 2272 Table_map 1 2312 table_id: # (test.t1) -master-bin.000001 2312 Write_rows 1 2350 table_id: # flags: STMT_END_F -master-bin.000001 2350 Query 1 2418 use `test`; BEGIN -master-bin.000001 2418 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` ( - `a` int(11) NOT NULL DEFAULT '0', - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB -master-bin.000001 2610 Xid 1 2637 COMMIT /* xid= */ -master-bin.000001 2637 Table_map 1 2677 table_id: # (test.t1) -master-bin.000001 2677 Write_rows 1 2715 table_id: # flags: STMT_END_F -master-bin.000001 2715 Query 1 2783 use `test`; BEGIN -master-bin.000001 2783 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` ( - `a` int(11) NOT NULL DEFAULT '0', - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB -master-bin.000001 2975 Query 1 3046 use `test`; ROLLBACK -master-bin.000001 3046 Query 1 80 use `test`; TRUNCATE table t2 -master-bin.000001 3126 Xid 1 3153 COMMIT /* xid= */ -master-bin.000001 3153 Table_map 1 3193 table_id: # (test.t1) -master-bin.000001 3193 Write_rows 1 3231 table_id: # flags: STMT_END_F -master-bin.000001 3231 Query 1 3299 use `test`; BEGIN -master-bin.000001 3299 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` ( - `a` int(11) NOT NULL DEFAULT '0', - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB -master-bin.000001 3491 Xid 1 3518 COMMIT /* xid= */ -master-bin.000001 3518 Query 1 3622 use `test`; DROP TABLE `t1` /* generated by server */ +master-bin.000001 # Table_map 1 # table_id: # (test.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Query 1 # use `test`; TRUNCATE table t2 +master-bin.000001 # Xid 1 # COMMIT /* xid= */ +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`; BEGIN +master-bin.000001 # Table_map 1 # table_id: # (test.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Xid 1 # COMMIT /* xid= */ +master-bin.000001 # Query 1 # use `test`; DROP TABLE t2 +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 +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`; TRUNCATE table t2 +master-bin.000001 # Xid 1 # COMMIT /* xid= */ +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` /* generated by server */ reset master; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; 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_auto_increment.result b/mysql-test/r/rpl_auto_increment.result index 9984ccf51f3..083f3a4e901 100644 --- a/mysql-test/r/rpl_auto_increment.result +++ b/mysql-test/r/rpl_auto_increment.result @@ -183,3 +183,47 @@ a 32 42 drop table t1; +create table t1 (a tinyint not null auto_increment primary key) engine=myisam; +insert into t1 values(103); +set auto_increment_increment=11; +set auto_increment_offset=4; +insert into t1 values(null); +insert into t1 values(null); +insert into t1 values(null); +ERROR 23000: Duplicate entry '125' for key 'PRIMARY' +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a; +a mod(a-@@auto_increment_offset,@@auto_increment_increment) +103 0 +114 0 +125 0 +create table t2 (a tinyint unsigned not null auto_increment primary key) engine=myisam; +set auto_increment_increment=10; +set auto_increment_offset=1; +set insert_id=1000; +insert into t2 values(null); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a; +a mod(a-@@auto_increment_offset,@@auto_increment_increment) +251 0 +create table t3 like t1; +set auto_increment_increment=1000; +set auto_increment_offset=700; +insert into t3 values(null); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t3 order by a; +a +127 +select * from t1 order by a; +a +103 +114 +125 +select * from t2 order by a; +a +251 +select * from t3 order by a; +a +127 +drop table t1,t2,t3; diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index 622b1489f91..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,10 +132,126 @@ 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; +insert into t1 values(null,100); +replace into t1 values(null,50),(null,100),(null,150); +select * from t1 order by n; +n b +2 50 +3 100 +4 150 +truncate table t1; +set sql_log_bin=1; +insert into t1 values(null,100); +select * from t1 order by n; +n b +1 100 +insert into t1 values(null,200),(null,300); +delete from t1 where b <> 100; +select * from t1 order by n; +n b +1 100 +replace into t1 values(null,100),(null,350); +select * from t1 order by n; +n b +2 100 +3 350 +select * from t1 order by n; +n b +2 100 +3 350 +insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000; +select * from t1 order by n; +n b +2 100 +4 400 +1000 350 +1001 600 +select * from t1 order by n; +n b +2 100 +4 400 +1000 350 +1001 600 +drop table t1; +create table t1 (n int primary key auto_increment not null, +b int, unique(b)); +insert into t1 values(null,100); +select * from t1 order by n; +n b +1 100 +insert into t1 values(null,200),(null,300); +delete from t1 where b <> 100; +select * from t1 order by n; +n b +1 100 +insert into t1 values(null,100),(null,350) on duplicate key update n=2; +select * from t1 order by n; +n b +2 100 +3 350 +select * from t1 order by n; +n b +2 100 +3 350 +drop table t1; 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/r/rpl_row_create_table.result b/mysql-test/r/rpl_row_create_table.result index f314aa39b81..1f7dbe13607 100644 --- a/mysql-test/r/rpl_row_create_table.result +++ b/mysql-test/r/rpl_row_create_table.result @@ -178,6 +178,7 @@ CREATE TABLE t8 LIKE t4; CREATE TABLE t9 LIKE tt4; CREATE TEMPORARY TABLE tt5 LIKE t4; CREATE TEMPORARY TABLE tt6 LIKE tt4; +CREATE TEMPORARY TABLE tt7 SELECT 1; **** On Master **** SHOW CREATE TABLE t8; Table t8 diff --git a/mysql-test/r/rpl_switch_stm_row_mixed.result b/mysql-test/r/rpl_switch_stm_row_mixed.result index 313037bb9dc..9e8770ce108 100644 --- a/mysql-test/r/rpl_switch_stm_row_mixed.result +++ b/mysql-test/r/rpl_switch_stm_row_mixed.result @@ -18,18 +18,18 @@ select @@global.binlog_format, @@session.binlog_format; ROW ROW CREATE TABLE t1 (a varchar(100)); prepare stmt1 from 'insert into t1 select concat(UUID(),?)'; -set @string="emergency"; -insert into t1 values("work"); +set @string="emergency_1_"; +insert into t1 values("work_2_"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values(concat(UUID(),"work")); +insert into t1 values(concat(UUID(),"work_3_")); execute stmt1 using @string; deallocate prepare stmt1; -insert into t1 values(concat("for",UUID())); -insert into t1 select "yesterday"; -create temporary table tmp(a char(3)); -insert into tmp values("see"); +insert into t1 values(concat("for_4_",UUID())); +insert into t1 select "yesterday_5_"; +create temporary table tmp(a char(100)); +insert into tmp values("see_6_"); set binlog_format=statement; ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables insert into t1 select * from tmp; @@ -55,16 +55,16 @@ select @@global.binlog_format, @@session.binlog_format; @@global.binlog_format @@session.binlog_format STATEMENT STATEMENT prepare stmt1 from 'insert into t1 select ?'; -set @string="emergency"; -insert into t1 values("work"); +set @string="emergency_7_"; +insert into t1 values("work_8_"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values("work"); +insert into t1 values("work_9_"); execute stmt1 using @string; deallocate prepare stmt1; -insert into t1 values("for"); -insert into t1 select "yesterday"; +insert into t1 values("for_10_"); +insert into t1 select "yesterday_11_"; set binlog_format=default; select @@global.binlog_format, @@session.binlog_format; @@global.binlog_format @@session.binlog_format @@ -75,16 +75,16 @@ select @@global.binlog_format, @@session.binlog_format; @@global.binlog_format @@session.binlog_format STATEMENT STATEMENT prepare stmt1 from 'insert into t1 select ?'; -set @string="emergency"; -insert into t1 values("work"); +set @string="emergency_12_"; +insert into t1 values("work_13_"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values("work"); +insert into t1 values("work_14_"); execute stmt1 using @string; deallocate prepare stmt1; -insert into t1 values("for"); -insert into t1 select "yesterday"; +insert into t1 values("for_15_"); +insert into t1 select "yesterday_16_"; set binlog_format=mixed; select @@global.binlog_format, @@session.binlog_format; @@global.binlog_format @@session.binlog_format @@ -94,40 +94,40 @@ select @@global.binlog_format, @@session.binlog_format; @@global.binlog_format @@session.binlog_format MIXED MIXED prepare stmt1 from 'insert into t1 select concat(UUID(),?)'; -set @string="emergency"; -insert into t1 values("work"); +set @string="emergency_17_"; +insert into t1 values("work_18_"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values(concat(UUID(),"work")); +insert into t1 values(concat(UUID(),"work_19_")); execute stmt1 using @string; deallocate prepare stmt1; -insert into t1 values(concat("for",UUID())); -insert into t1 select "yesterday"; +insert into t1 values(concat("for_20_",UUID())); +insert into t1 select "yesterday_21_"; prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values(concat(UUID(),"work")); +insert into t1 values(concat(UUID(),"work_22_")); execute stmt1 using @string; deallocate prepare stmt1; -insert into t1 values(concat("for",UUID())); -insert into t1 select "yesterday"; -create table t2 select UUID(); +insert into t1 values(concat("for_23_",UUID())); +insert into t1 select "yesterday_24_"; +create table t2 select rpad(UUID(),100,' '); create table t3 select 1 union select UUID(); create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3); create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3); insert into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4); create procedure foo() begin -insert into t1 values("work"); -insert into t1 values(concat("for",UUID())); -insert into t1 select "yesterday"; +insert into t1 values("work_25_"); +insert into t1 values(concat("for_26_",UUID())); +insert into t1 select "yesterday_27_"; end| create procedure foo2() begin -insert into t1 values(concat("emergency",UUID())); -insert into t1 values("work"); -insert into t1 values(concat("for",UUID())); +insert into t1 values(concat("emergency_28_",UUID())); +insert into t1 values("work_29_"); +insert into t1 values(concat("for_30_",UUID())); set session binlog_format=row; # accepted for stored procs -insert into t1 values("more work"); +insert into t1 values("more work_31_"); set session binlog_format=mixed; end| create function foo3() returns bigint unsigned @@ -136,32 +136,228 @@ set session binlog_format=row; # rejected for stored funcs insert into t1 values("alarm"); return 100; end| +create procedure foo4(x varchar(100)) +begin +insert into t1 values(concat("work_250_",x)); +insert into t1 select "yesterday_270_"; +end| call foo(); call foo2(); +call foo4("hello"); +call foo4(UUID()); +call foo4("world"); select foo3(); ERROR HY000: Cannot change the binary logging format inside a stored function or trigger select * from t1 where a="alarm"; a +drop function foo3; +create function foo3() returns bigint unsigned +begin +insert into t1 values("foo3_32_"); +call foo(); +return 100; +end| +insert into t2 select foo3(); +prepare stmt1 from 'insert into t2 select foo3()'; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; +create function foo4() returns bigint unsigned +begin +insert into t2 select foo3(); +return 100; +end| +select foo4(); +foo4() +100 +prepare stmt1 from 'select foo4()'; +execute stmt1; +foo4() +100 +execute stmt1; +foo4() +100 +deallocate prepare stmt1; +create function foo5() returns bigint unsigned +begin +insert into t2 select UUID(); +return 100; +end| +select foo5(); +foo5() +100 +prepare stmt1 from 'select foo5()'; +execute stmt1; +foo5() +100 +execute stmt1; +foo5() +100 +deallocate prepare stmt1; +create function foo6(x varchar(100)) returns bigint unsigned +begin +insert into t2 select x; +return 100; +end| +select foo6("foo6_1_"); +foo6("foo6_1_") +100 +select foo6(concat("foo6_2_",UUID())); +foo6(concat("foo6_2_",UUID())) +100 +prepare stmt1 from 'select foo6(concat("foo6_3_",UUID()))'; +execute stmt1; +foo6(concat("foo6_3_",UUID())) +100 +execute stmt1; +foo6(concat("foo6_3_",UUID())) +100 +deallocate prepare stmt1; +create view v1 as select uuid(); +create table t11 (data varchar(255)); +insert into t11 select * from v1; +insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11'); +prepare stmt1 from "insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11')"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; +create trigger t11_bi before insert on t11 for each row +begin +set NEW.data = concat(NEW.data,UUID()); +end| +insert into t11 values("try_560_"); +insert delayed into t2 values("delay_1_"); +insert delayed into t2 values(concat("delay_2_",UUID())); +insert delayed into t2 values("delay_3_"),(concat("delay_4_",UUID())),("delay_5_"); +insert delayed into t2 values("delay_6_"); +insert delayed into t2 values(rand()); +set @a=2.345; +insert delayed into t2 values(@a); +create table t20 select * from t1; +create table t21 select * from t2; +create table t22 select * from t3; +drop table t1,t2,t3; +create table t1 (a int primary key auto_increment, b varchar(100)); +create table t2 (a int primary key auto_increment, b varchar(100)); +create table t3 (b varchar(100)); +create function f (x varchar(100)) returns int deterministic +begin +insert into t1 values(null,x); +insert into t2 values(null,x); +return 1; +end| +select f("try_41_"); +f("try_41_") +1 +use mysqltest1; +insert into t2 values(2,null),(3,null),(4,null); +delete from t2 where a>=2; +select f("try_42_"); +f("try_42_") +1 +insert into t2 values(3,null),(4,null); +delete from t2 where a>=3; +prepare stmt1 from 'select f(?)'; +set @string="try_43_"; +insert into t1 values(null,"try_44_"); +execute stmt1 using @string; +f(?) +1 +deallocate prepare stmt1; +create table t12 select * from t1; +drop table t1; +create table t1 (a int, b varchar(100), key(a)); +select f("try_45_"); +f("try_45_") +1 +create table t13 select * from t1; +drop table t1; +create table t1 (a int primary key auto_increment, b varchar(100)); +drop function f; +truncate table t2; +create function f1 (x varchar(100)) returns int deterministic +begin +insert into t1 values(null,x); +return 1; +end| +create function f2 (x varchar(100)) returns int deterministic +begin +insert into t2 values(null,x); +return 1; +end| +select f1("try_46_"),f2("try_47_"); +f1("try_46_") f2("try_47_") +1 1 +insert into t2 values(2,null),(3,null),(4,null); +delete from t2 where a>=2; +select f1("try_48_"),f2("try_49_"); +f1("try_48_") f2("try_49_") +1 1 +insert into t3 values(concat("try_50_",f1("try_51_"),f2("try_52_"))); +drop function f2; +create function f2 (x varchar(100)) returns int deterministic +begin +declare y int; +insert into t1 values(null,x); +set y = (select count(*) from t2); +return y; +end| +select f1("try_53_"),f2("try_54_"); +f1("try_53_") f2("try_54_") +1 3 +drop function f2; +create trigger t1_bi before insert on t1 for each row +begin +insert into t2 values(null,"try_55_"); +end| +insert into t1 values(null,"try_56_"); +alter table t1 modify a int, drop primary key; +insert into t1 values(null,"try_57_"); +CREATE TEMPORARY TABLE t15 SELECT UUID(); +create table t16 like t15; +INSERT INTO t16 SELECT * FROM t15; +insert into t16 values("try_65_"); +drop table t15; +insert into t16 values("try_66_"); select count(*) from t1; count(*) -36 +7 select count(*) from t2; count(*) -1 +5 select count(*) from t3; count(*) -2 +1 select count(*) from t4; count(*) 29 select count(*) from t5; count(*) 58 +select count(*) from t11; +count(*) +8 +select count(*) from t20; +count(*) +66 +select count(*) from t21; +count(*) +22 +select count(*) from t22; +count(*) +2 +select count(*) from t12; +count(*) +4 +select count(*) from t13; +count(*) +1 +select count(*) from t16; +count(*) +3 show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # drop database if exists mysqltest1 -master-bin.000001 # Table_map 1 # table_id: # (mysql.proc) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # create database mysqltest1 master-bin.000001 # Query 1 # use `mysqltest1`; CREATE TABLE t1 (a varchar(100)) master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) @@ -178,45 +374,41 @@ master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work") -master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work_8_") +master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E63795F375F COLLATE latin1_swedish_ci master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select @'string' -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work") -master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work_9_") +master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E63795F375F COLLATE latin1_swedish_ci master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select @'string' -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("for") -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday" -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work") -master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("for_10_") +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday_11_" +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work_13_") +master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E63795F31325F COLLATE latin1_swedish_ci master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select @'string' -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work") -master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work_14_") +master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E63795F31325F COLLATE latin1_swedish_ci master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select @'string' -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("for") -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday" -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work") +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("for_15_") +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday_16_" +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work_18_") master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci +master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E63795F31375F COLLATE latin1_swedish_ci master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select @'string' master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday" +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday_21_" master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci +master-bin.000001 # User var 1 # @`string`=_latin1 0x656D657267656E63795F31375F COLLATE latin1_swedish_ci master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select @'string' master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday" +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday_24_" master-bin.000001 # Query 1 # use `mysqltest1`; CREATE TABLE `t2` ( - `UUID()` varchar(36) CHARACTER SET utf8 NOT NULL DEFAULT '' + `rpad(UUID(),100,' ')` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' ) master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F @@ -239,17 +431,17 @@ master-bin.000001 # Write_rows 1 # table_id: # master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo() begin -insert into t1 values("work"); -insert into t1 values(concat("for",UUID())); -insert into t1 select "yesterday"; +insert into t1 values("work_25_"); +insert into t1 values(concat("for_26_",UUID())); +insert into t1 select "yesterday_27_"; end master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo2() begin -insert into t1 values(concat("emergency",UUID())); -insert into t1 values("work"); -insert into t1 values(concat("for",UUID())); +insert into t1 values(concat("emergency_28_",UUID())); +insert into t1 values("work_29_"); +insert into t1 values(concat("for_30_",UUID())); set session binlog_format=row; # accepted for stored procs -insert into t1 values("more work"); +insert into t1 values("more work_31_"); set session binlog_format=mixed; end master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo3() returns bigint unsigned @@ -258,15 +450,210 @@ set session binlog_format=row; # rejected for stored funcs insert into t1 values("alarm"); return 100; end -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work") +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo4(x varchar(100)) +begin +insert into t1 values(concat("work_250_",x)); +insert into t1 select "yesterday_270_"; +end +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work_25_") +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday_27_" +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work_29_") +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values(concat("work_250_", NAME_CONST('x',... +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday_270_" +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values(concat("work_250_", NAME_CONST('x',... +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday_270_" +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values(concat("work_250_", NAME_CONST('x',... +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday_270_" +master-bin.000001 # Query 1 # use `mysqltest1`; drop function foo3 +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo3() returns bigint unsigned +begin +insert into t1 values("foo3_32_"); +call foo(); +return 100; +end +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo4() returns bigint unsigned +begin +insert into t2 select foo3(); +return 100; +end +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 select "yesterday" +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values("work") +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo5() returns bigint unsigned +begin +insert into t2 select UUID(); +return 100; +end +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo6(x varchar(100)) returns bigint unsigned +begin +insert into t2 select x; +return 100; +end +master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `foo6`(_latin1'foo6_1_') +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select uuid() +master-bin.000001 # Query 1 # use `mysqltest1`; create table t11 (data varchar(255)) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t11) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11') +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11') +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11') +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger t11_bi before insert on t11 for each row +begin +set NEW.data = concat(NEW.data,UUID()); +end +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t11) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; create table t20 select * from t1 +master-bin.000001 # Query 1 # use `mysqltest1`; create table t21 select * from t2 +master-bin.000001 # Query 1 # use `mysqltest1`; create table t22 select * from t3 +master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1,t2,t3 +master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int primary key auto_increment, b varchar(100)) +master-bin.000001 # Query 1 # use `mysqltest1`; create table t2 (a int primary key auto_increment, b varchar(100)) +master-bin.000001 # Query 1 # use `mysqltest1`; create table t3 (b varchar(100)) +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f (x varchar(100)) returns int deterministic +begin +insert into t1 values(null,x); +insert into t2 values(null,x); +return 1; +end +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Intvar 1 # INSERT_ID=3 +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values(null,"try_44_") +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; create table t12 select * from t1 +master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1 +master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int, b varchar(100), key(a)) +master-bin.000001 # Intvar 1 # INSERT_ID=4 +master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `f`(_latin1'try_45_') +master-bin.000001 # Query 1 # use `mysqltest1`; create table t13 select * from t1 +master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1 +master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int primary key auto_increment, b varchar(100)) +master-bin.000001 # Query 1 # use `mysqltest1`; drop function f +master-bin.000001 # Query 1 # use `mysqltest1`; truncate table t2 +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f1 (x varchar(100)) returns int deterministic +begin +insert into t1 values(null,x); +return 1; +end +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f2 (x varchar(100)) returns int deterministic +begin +insert into t2 values(null,x); +return 1; +end +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t3) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; drop function f2 +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f2 (x varchar(100)) returns int deterministic +begin +declare y int; +insert into t1 values(null,x); +set y = (select count(*) from t2); +return y; +end +master-bin.000001 # Intvar 1 # INSERT_ID=4 +master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `f1`(_latin1'try_53_') +master-bin.000001 # Intvar 1 # INSERT_ID=5 +master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `f2`(_latin1'try_54_') +master-bin.000001 # Query 1 # use `mysqltest1`; drop function f2 +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1 for each row +begin +insert into t2 values(null,"try_55_"); +end master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t1) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t2) +master-bin.000001 # Write_rows 1 # table_id: # +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; alter table t1 modify a int, drop primary key +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values(null,"try_57_") +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE TABLE `t16` ( + `UUID()` varchar(36) CHARACTER SET utf8 NOT NULL DEFAULT '' +) +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t16) +master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (mysqltest1.t16) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t16 values("try_66_") drop database mysqltest1; 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/disabled.def b/mysql-test/t/disabled.def index 6f26847f8d7..a0b61f21047 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -32,7 +32,6 @@ rpl_ndb_ddl : BUG#18946 result file needs update + test needs to ch rpl_ndb_innodb2ndb : Bug #19710 Cluster replication to partition table fails on DELETE FROM statement #rpl_ndb_log : BUG#18947 2006-03-21 tomas CRBR: order in binlog of create table and insert (on different table) not determ rpl_ndb_myisam2ndb : Bug #19710 Cluster replication to partition table fails on DELETE FROM statement -rpl_switch_stm_row_mixed : BUG#18590 2006-03-28 brian rpl_row_blob_innodb : BUG#18980 2006-04-10 kent Test fails randomly rpl_row_func003 : BUG#19074 2006-13-04 andrei test failed rpl_sp : BUG#16456 2006-02-16 jmiller 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/mysql-test/t/rpl_row_create_table.test b/mysql-test/t/rpl_row_create_table.test index 8a8ea01d688..044dd58ce82 100644 --- a/mysql-test/t/rpl_row_create_table.test +++ b/mysql-test/t/rpl_row_create_table.test @@ -97,6 +97,7 @@ CREATE TABLE t8 LIKE t4; CREATE TABLE t9 LIKE tt4; CREATE TEMPORARY TABLE tt5 LIKE t4; CREATE TEMPORARY TABLE tt6 LIKE tt4; +CREATE TEMPORARY TABLE tt7 SELECT 1; --echo **** On Master **** --query_vertical SHOW CREATE TABLE t8 --query_vertical SHOW CREATE TABLE t9 diff --git a/mysql-test/t/rpl_switch_stm_row_mixed.test b/mysql-test/t/rpl_switch_stm_row_mixed.test index 4a79b3995c4..aef69df914a 100644 --- a/mysql-test/t/rpl_switch_stm_row_mixed.test +++ b/mysql-test/t/rpl_switch_stm_row_mixed.test @@ -15,22 +15,22 @@ select @@global.binlog_format, @@session.binlog_format; CREATE TABLE t1 (a varchar(100)); prepare stmt1 from 'insert into t1 select concat(UUID(),?)'; -set @string="emergency"; -insert into t1 values("work"); +set @string="emergency_1_"; +insert into t1 values("work_2_"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values(concat(UUID(),"work")); +insert into t1 values(concat(UUID(),"work_3_")); execute stmt1 using @string; deallocate prepare stmt1; -insert into t1 values(concat("for",UUID())); -insert into t1 select "yesterday"; +insert into t1 values(concat("for_4_",UUID())); +insert into t1 select "yesterday_5_"; # verify that temp tables prevent a switch to SBR -create temporary table tmp(a char(3)); -insert into tmp values("see"); +create temporary table tmp(a char(100)); +insert into tmp values("see_6_"); --error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR set binlog_format=statement; insert into t1 select * from tmp; @@ -47,18 +47,18 @@ show session variables like "binlog_format%"; select @@global.binlog_format, @@session.binlog_format; prepare stmt1 from 'insert into t1 select ?'; -set @string="emergency"; -insert into t1 values("work"); +set @string="emergency_7_"; +insert into t1 values("work_8_"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values("work"); +insert into t1 values("work_9_"); execute stmt1 using @string; deallocate prepare stmt1; -insert into t1 values("for"); -insert into t1 select "yesterday"; +insert into t1 values("for_10_"); +insert into t1 select "yesterday_11_"; # test SET DEFAULT (=statement at this point of test) set binlog_format=default; @@ -69,18 +69,18 @@ set global binlog_format=default; select @@global.binlog_format, @@session.binlog_format; prepare stmt1 from 'insert into t1 select ?'; -set @string="emergency"; -insert into t1 values("work"); +set @string="emergency_12_"; +insert into t1 values("work_13_"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values("work"); +insert into t1 values("work_14_"); execute stmt1 using @string; deallocate prepare stmt1; -insert into t1 values("for"); -insert into t1 select "yesterday"; +insert into t1 values("for_15_"); +insert into t1 select "yesterday_16_"; # and now the mixed mode @@ -90,53 +90,52 @@ set global binlog_format=mixed; select @@global.binlog_format, @@session.binlog_format; prepare stmt1 from 'insert into t1 select concat(UUID(),?)'; -set @string="emergency"; -insert into t1 values("work"); +set @string="emergency_17_"; +insert into t1 values("work_18_"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values(concat(UUID(),"work")); +insert into t1 values(concat(UUID(),"work_19_")); execute stmt1 using @string; deallocate prepare stmt1; -insert into t1 values(concat("for",UUID())); -insert into t1 select "yesterday"; +insert into t1 values(concat("for_20_",UUID())); +insert into t1 select "yesterday_21_"; prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values(concat(UUID(),"work")); +insert into t1 values(concat(UUID(),"work_22_")); execute stmt1 using @string; deallocate prepare stmt1; -insert into t1 values(concat("for",UUID())); -insert into t1 select "yesterday"; +insert into t1 values(concat("for_23_",UUID())); +insert into t1 select "yesterday_24_"; # Test of CREATE TABLE SELECT -create table t2 select UUID(); +create table t2 select rpad(UUID(),100,' '); create table t3 select 1 union select UUID(); create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3); create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3); # what if UUID() is first: insert into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4); -# inside a stored procedure (inside a function or trigger won't -# work) +# inside a stored procedure delimiter |; create procedure foo() begin -insert into t1 values("work"); -insert into t1 values(concat("for",UUID())); -insert into t1 select "yesterday"; +insert into t1 values("work_25_"); +insert into t1 values(concat("for_26_",UUID())); +insert into t1 select "yesterday_27_"; end| create procedure foo2() begin -insert into t1 values(concat("emergency",UUID())); -insert into t1 values("work"); -insert into t1 values(concat("for",UUID())); +insert into t1 values(concat("emergency_28_",UUID())); +insert into t1 values("work_29_"); +insert into t1 values(concat("for_30_",UUID())); set session binlog_format=row; # accepted for stored procs -insert into t1 values("more work"); +insert into t1 values("more work_31_"); set session binlog_format=mixed; end| create function foo3() returns bigint unsigned @@ -145,15 +144,131 @@ begin insert into t1 values("alarm"); return 100; end| +create procedure foo4(x varchar(100)) +begin +insert into t1 values(concat("work_250_",x)); +insert into t1 select "yesterday_270_"; +end| delimiter ;| call foo(); call foo2(); +call foo4("hello"); +call foo4(UUID()); +call foo4("world"); # test that can't SET in a stored function --error ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT select foo3(); select * from t1 where a="alarm"; +# Tests of stored functions/triggers/views for BUG#20930 "Mixed +# binlogging mode does not work with stored functions, triggers, +# views" + +# Function which calls procedure +drop function foo3; +delimiter |; +create function foo3() returns bigint unsigned +begin + insert into t1 values("foo3_32_"); + call foo(); + return 100; +end| +delimiter ;| +insert into t2 select foo3(); + +prepare stmt1 from 'insert into t2 select foo3()'; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +# Test if stored function calls stored function which calls procedure +# which requires row-based. + +delimiter |; +create function foo4() returns bigint unsigned +begin + insert into t2 select foo3(); + return 100; +end| +delimiter ;| +select foo4(); + +prepare stmt1 from 'select foo4()'; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +# A simple stored function +delimiter |; +create function foo5() returns bigint unsigned +begin + insert into t2 select UUID(); + return 100; +end| +delimiter ;| +select foo5(); + +prepare stmt1 from 'select foo5()'; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +# A simple stored function where UUID() is in the argument +delimiter |; +create function foo6(x varchar(100)) returns bigint unsigned +begin + insert into t2 select x; + return 100; +end| +delimiter ;| +select foo6("foo6_1_"); +select foo6(concat("foo6_2_",UUID())); + +prepare stmt1 from 'select foo6(concat("foo6_3_",UUID()))'; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + + +# Test of views using UUID() + +create view v1 as select uuid(); +create table t11 (data varchar(255)); +insert into t11 select * from v1; +# Test of querying INFORMATION_SCHEMA which parses the view's body, +# to verify that it binlogs statement-based (is not polluted by +# the parsing of the view's body). +insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11'); +prepare stmt1 from "insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11')"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +# Test of triggers with UUID() +delimiter |; +create trigger t11_bi before insert on t11 for each row +begin + set NEW.data = concat(NEW.data,UUID()); +end| +delimiter ;| +insert into t11 values("try_560_"); + +# Test that INSERT DELAYED works in mixed mode (BUG#20649) +insert delayed into t2 values("delay_1_"); +insert delayed into t2 values(concat("delay_2_",UUID())); +insert delayed into t2 values("delay_3_"),(concat("delay_4_",UUID())),("delay_5_"); +insert delayed into t2 values("delay_6_"); + +# Test for BUG#20633 (INSERT DELAYED RAND()/user_variable does not +# replicate fine in statement-based ; we test that in mixed mode it +# works). +insert delayed into t2 values(rand()); +set @a=2.345; +insert delayed into t2 values(@a); + +sleep 4; # time for the delayed inserts to reach disk + # If you want to do manual testing of the mixed mode regarding UDFs (not # testable automatically as quite platform- and compiler-dependent), # you just need to set the variable below to 1, and to @@ -164,30 +279,182 @@ if ($you_want_to_test_UDF) { CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so'; prepare stmt1 from 'insert into t1 select metaphon(?)'; - set @string="emergency"; - insert into t1 values("work"); + set @string="emergency_133_"; + insert into t1 values("work_134_"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; - insert into t1 values(metaphon("work")); + insert into t1 values(metaphon("work_135_")); execute stmt1 using @string; deallocate prepare stmt1; - insert into t1 values(metaphon("for")); - insert into t1 select "yesterday"; - create table t6 select metaphon("for"); - create table t7 select 1 union select metaphon("for"); - create table t8 select * from t1 where 3 in (select 1 union select 2 union select metaphon("for") union select 3); + insert into t1 values(metaphon("for_136_")); + insert into t1 select "yesterday_137_"; + create table t6 select metaphon("for_138_"); + create table t7 select 1 union select metaphon("for_139_"); + create table t8 select * from t1 where 3 in (select 1 union select 2 union select metaphon("for_140_") union select 3); create table t9 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3); } +create table t20 select * from t1; # save for comparing later +create table t21 select * from t2; +create table t22 select * from t3; +drop table t1,t2,t3; + +# This tests the fix to +# BUG#19630 stored function inserting into two auto_increment breaks statement-based binlog +# We verify that under the mixed binlog mode, a stored function +# modifying at least two tables having an auto_increment column, +# is binlogged row-based. Indeed in statement-based binlogging, +# only the auto_increment value generated for the first table +# is recorded in the binlog, the value generated for the 2nd table +# lacking. + +create table t1 (a int primary key auto_increment, b varchar(100)); +create table t2 (a int primary key auto_increment, b varchar(100)); +create table t3 (b varchar(100)); +delimiter |; +create function f (x varchar(100)) returns int deterministic +begin + insert into t1 values(null,x); + insert into t2 values(null,x); + return 1; +end| +delimiter ;| +select f("try_41_"); +# Two operations which compensate each other except that their net +# effect is that they advance the auto_increment counter of t2 on slave: +sync_slave_with_master; +use mysqltest1; +insert into t2 values(2,null),(3,null),(4,null); +delete from t2 where a>=2; + +connection master; +# this is the call which didn't replicate well +select f("try_42_"); +sync_slave_with_master; + +# now use prepared statement and test again, just to see that the RBB +# mode isn't set at PREPARE but at EXECUTE. + +insert into t2 values(3,null),(4,null); +delete from t2 where a>=3; + +connection master; +prepare stmt1 from 'select f(?)'; +set @string="try_43_"; +insert into t1 values(null,"try_44_"); # should be SBB +execute stmt1 using @string; # should be RBB +deallocate prepare stmt1; +sync_slave_with_master; + +# verify that if only one table has auto_inc, it does not trigger RBB +# (we'll check in binlog further below) + +connection master; +create table t12 select * from t1; # save for comparing later +drop table t1; +create table t1 (a int, b varchar(100), key(a)); +select f("try_45_"); + +# restore table's key +create table t13 select * from t1; +drop table t1; +create table t1 (a int primary key auto_increment, b varchar(100)); + +# now test if it's two functions, each of them inserts in one table + +drop function f; +# Manifestation of BUG#20341! re-enable this line after merging fix +# for that bug +#create table t14 select * from t2; +truncate table t2; +delimiter |; +create function f1 (x varchar(100)) returns int deterministic +begin + insert into t1 values(null,x); + return 1; +end| +create function f2 (x varchar(100)) returns int deterministic +begin + insert into t2 values(null,x); + return 1; +end| +delimiter ;| +select f1("try_46_"),f2("try_47_"); + +sync_slave_with_master; +insert into t2 values(2,null),(3,null),(4,null); +delete from t2 where a>=2; + +connection master; +# Test with SELECT and INSERT +select f1("try_48_"),f2("try_49_"); +insert into t3 values(concat("try_50_",f1("try_51_"),f2("try_52_"))); +sync_slave_with_master; + +# verify that if f2 does only read on an auto_inc table, this does not +# switch to RBB +connection master; +drop function f2; +delimiter |; +create function f2 (x varchar(100)) returns int deterministic +begin + declare y int; + insert into t1 values(null,x); + set y = (select count(*) from t2); + return y; +end| +delimiter ;| +select f1("try_53_"),f2("try_54_"); +sync_slave_with_master; + +# And now, a normal statement with a trigger (no stored functions) + +connection master; +drop function f2; +delimiter |; +create trigger t1_bi before insert on t1 for each row +begin + insert into t2 values(null,"try_55_"); +end| +delimiter ;| +insert into t1 values(null,"try_56_"); +# and now remove one auto_increment and verify SBB +alter table t1 modify a int, drop primary key; +insert into t1 values(null,"try_57_"); +sync_slave_with_master; + +# Test for BUG#20499 "mixed mode with temporary table breaks binlog" +# Slave used to have only 2 rows instead of 3. +connection master; +CREATE TEMPORARY TABLE t15 SELECT UUID(); +create table t16 like t15; +INSERT INTO t16 SELECT * FROM t15; +# we'll verify that this one is done RBB +insert into t16 values("try_65_"); +drop table t15; +# we'll verify that this one is done SBB +insert into t16 values("try_66_"); +sync_slave_with_master; + # and now compare: +connection master; + # first check that data on master is sensible select count(*) from t1; select count(*) from t2; select count(*) from t3; select count(*) from t4; select count(*) from t5; +select count(*) from t11; +select count(*) from t20; +select count(*) from t21; +select count(*) from t22; +select count(*) from t12; +select count(*) from t13; +#select count(*) from t14; +select count(*) from t16; if ($you_want_to_test_UDF) { select count(*) from t6; @@ -196,21 +463,48 @@ if ($you_want_to_test_UDF) select count(*) from t9; } ---replace_column 2 # 5 # ---replace_regex /table_id: [0-9]+/table_id: #/ -show binlog events from 102; sync_slave_with_master; # as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql -connection master; -drop database mysqltest1; -sync_slave_with_master; - # Let's compare. Note: If they match test will pass, if they do not match # the test will show that the diff statement failed and not reject file # will be created. You will need to go to the mysql-test dir and diff # the files your self to see what is not matching --exec diff $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql; + +connection master; +# As one stored function's parameter is UUID(), its value ends up in a +# NAME_CONST in the binlog, we must hide it for repeatability +--replace_column 2 # 5 # +--replace_regex /table_id: [0-9]+/table_id: #/ /NAME_CONST\('x',.*/NAME_CONST('x',.../ +show binlog events from 102; + +# Now test that mysqlbinlog works fine on a binlog generated by the +# mixed mode + +# BUG#11312 "DELIMITER is not written to the binary log that causes +# syntax error" makes that mysqlbinlog will fail if we pass it the +# text of queries; this forces us to use --base64-output here. + +# BUG#20929 "BINLOG command causes invalid free plus assertion +# failure" makes mysqld segfault when receiving --base64-output + +# So I can't enable this piece of test +# SIGH + +if ($enable_when_11312_or_20929_fixed) +{ +--exec $MYSQL_BINLOG --base64-output $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_mixed.sql +drop database mysqltest1; +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/mysqlbinlog_mixed.sql +--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql +# the old mysqldump output on slave is the same as what it was on +# master before restoring on master. +--exec diff $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql; +} + +drop database mysqltest1; +sync_slave_with_master; 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 0dfb31fba8c..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); @@ -1546,7 +1551,7 @@ next_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) @@ -1554,25 +1559,31 @@ next_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: + - 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. - - 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. + - 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. - 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. + - 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. - - 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 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 @@ -1583,7 +1594,13 @@ next_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 @@ -1600,7 +1617,8 @@ next_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; @@ -1608,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 @@ -1620,131 +1638,138 @@ 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). */ - if (thd->clear_next_insert_id && nr >= thd->next_insert_id) - { - if (variables->auto_increment_increment != 1) - nr= next_insert_id(nr, variables); - else - nr++; - thd->next_insert_id= nr; - DBUG_PRINT("info",("next_insert_id: %lu", (ulong) 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) + { + nr= forced->minimum(); + nb_reserved_values= forced->values(); + } + else { - /* avoid overflow in formula, with this if() */ - if (nb_already_reserved_intervals <= AUTO_INC_DEFAULT_NB_MAX_BITS) + /* + 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 (!table->next_number_field->store((longlong) nr, TRUE)) - thd->insert_id((ulonglong) nr); - else - thd->insert_id(table->next_number_field->val_int()); - - /* - 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 - */ - if (!table->s->next_number_key_offset) + if (unlikely(table->next_number_field->store((longlong) nr, TRUE))) { /* - 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 + 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). */ - thd->next_insert_id= next_insert_id(nr, variables); + 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); } - else - thd->next_insert_id= 0; - - /* 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. -*/ + /* + 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. + */ + 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)); -void handler::restore_auto_increment() -{ - THD *thd= table->in_use; - if (thd->next_insert_id) - thd->next_insert_id= thd->prev_insert_id; + DBUG_RETURN(result); } @@ -1840,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 */ @@ -3369,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 27223de6111..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) { @@ -954,6 +975,7 @@ public: return TRUE; } int ha_open(TABLE *table, const char *name, int mode, int test_if_locked); + void adjust_next_insert_id_after_explicit_value(ulonglong nr); bool update_auto_increment(); void print_keydup_error(uint key_nr, const char *msg); virtual void print_error(int error, myf errflag); @@ -1247,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_create.cc b/sql/item_create.cc index 6eca6209438..bf4af2232f7 100644 --- a/sql/item_create.cc +++ b/sql/item_create.cc @@ -426,7 +426,9 @@ Item *create_func_unhex(Item* a) Item *create_func_uuid(void) { THD *thd= current_thd; - thd->lex->binlog_row_based_if_mixed= 1; +#ifdef HAVE_ROW_BASED_REPLICATION + thd->lex->binlog_row_based_if_mixed= TRUE; +#endif return new(thd->mem_root) Item_func_uuid(); } 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 bb9ef4d453f..1176a98713d 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -1343,9 +1343,9 @@ bool sys_var_thd_binlog_format::is_readonly() const return 1; } /* - if in a stored function, it's too late to change mode + if in a stored function/trigger, it's too late to change mode */ - if (thd->spcont && thd->prelocked_mode) + if (thd->in_sub_stmt) { my_error(ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT, MYF(0)); return 1; @@ -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/sp.cc b/sql/sp.cc index 93e21170156..eb5bc7ecc14 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -1632,6 +1632,7 @@ sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex, sp->add_used_tables_to_table_list(thd, &lex->query_tables_last, rt->belong_to_view); } + sp->propagate_attributes(lex); } first= FALSE; } @@ -1729,14 +1730,16 @@ sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex, { for (int j= 0; j < (int)TRG_ACTION_MAX; j++) { - if (triggers->bodies[i][j]) + sp_head *trigger_body= triggers->bodies[i][j]; + if (trigger_body) { - (void)triggers->bodies[i][j]-> - add_used_tables_to_table_list(thd, &lex->query_tables_last, - table->belong_to_view); + (void)trigger_body-> + add_used_tables_to_table_list(thd, &lex->query_tables_last, + table->belong_to_view); sp_update_stmt_used_routines(thd, lex, - &triggers->bodies[i][j]->m_sroutines, + &trigger_body->m_sroutines, table->belong_to_view); + trigger_body->propagate_attributes(lex); } } } diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 88460337526..b486aad1be4 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -1675,6 +1675,16 @@ sp_head::restore_lex(THD *thd) oldlex->next_state= sublex->next_state; oldlex->trg_table_fields.push_back(&sublex->trg_table_fields); +#ifdef HAVE_ROW_BASED_REPLICATION + /* + If this substatement needs row-based, the entire routine does too (we + cannot switch from statement-based to row-based only for this + substatement). + */ + if (sublex->binlog_row_based_if_mixed) + m_flags|= BINLOG_ROW_BASED_IF_MIXED; +#endif + /* Add routines which are used by statement to respective set for this routine. diff --git a/sql/sp_head.h b/sql/sp_head.h index 791343f0061..0ad0c496b7e 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -126,7 +126,8 @@ public: /* Is set if a procedure with COMMIT (implicit or explicit) | ROLLBACK */ HAS_COMMIT_OR_ROLLBACK= 128, LOG_SLOW_STATEMENTS= 256, // Used by events - LOG_GENERAL_LOG= 512 // Used by events + LOG_GENERAL_LOG= 512, // Used by events + BINLOG_ROW_BASED_IF_MIXED= 1024 }; /* TYPE_ENUM_FUNCTION, TYPE_ENUM_PROCEDURE or TYPE_ENUM_TRIGGER */ @@ -351,6 +352,25 @@ public: int show_routine_code(THD *thd); #endif + /* + This method is intended for attributes of a routine which need + to propagate upwards to the LEX of the caller (when a property of a + sp_head needs to "taint" the caller). + */ + void propagate_attributes(LEX *lex) + { +#ifdef HAVE_ROW_BASED_REPLICATION + /* + If this routine needs row-based binary logging, the entire top statement + too (we cannot switch from statement-based to row-based only for this + routine, as in statement-based the top-statement may be binlogged and + the substatements not). + */ + if (m_flags & BINLOG_ROW_BASED_IF_MIXED) + lex->binlog_row_based_if_mixed= TRUE; +#endif + } + private: diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 9b016ac5ecb..a938287ece3 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -49,6 +49,8 @@ static bool open_new_frm(THD *thd, TABLE_SHARE *share, const char *alias, static void close_old_data_files(THD *thd, TABLE *table, bool abort_locks, bool send_refresh); static bool reopen_table(TABLE *table); +static bool +has_two_write_locked_tables_with_auto_increment(TABLE_LIST *tables); extern "C" byte *table_cache_key(const byte *record,uint *length, @@ -3314,6 +3316,18 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count, bool *need_reopen) *need_reopen= FALSE; +#ifdef HAVE_ROW_BASED_REPLICATION + /* + CREATE ... SELECT UUID() locks no tables, we have to test here. + Note that we will not do the resetting if inside a stored + function/trigger, because the binlogging of those is decided earlier (by + the caller) and can't be changed afterwards. + */ + thd->reset_current_stmt_binlog_row_based(); + if (thd->lex->binlog_row_based_if_mixed) + thd->set_current_stmt_binlog_row_based_if_mixed(); +#endif /*HAVE_ROW_BASED_REPLICATION*/ + if (!tables) DBUG_RETURN(0); @@ -3344,6 +3358,19 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count, bool *need_reopen) { thd->in_lock_tables=1; thd->options|= OPTION_TABLE_LOCK; +#ifdef HAVE_ROW_BASED_REPLICATION + /* + If we have >= 2 different tables to update with auto_inc columns, + statement-based binlogging won't work. We can solve this problem in + mixed mode by switching to row-based binlogging: + */ + if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED && + has_two_write_locked_tables_with_auto_increment(tables)) + { + thd->lex->binlog_row_based_if_mixed= TRUE; + thd->set_current_stmt_binlog_row_based_if_mixed(); + } +#endif } if (! (thd->lock= mysql_lock_tables(thd, start, (uint) (ptr - start), @@ -6476,3 +6503,46 @@ void mysql_wait_completed_table(ALTER_PARTITION_PARAM_TYPE *lpt, TABLE *my_table DBUG_VOID_RETURN; } + +/* + Tells if two (or more) tables have auto_increment columns and we want to + lock those tables with a write lock. + + SYNOPSIS + has_two_write_locked_tables_with_auto_increment + tables Table list + + NOTES: + Call this function only when you have established the list of all tables + which you'll want to update (including stored functions, triggers, views + inside your statement). + + RETURN + 0 No + 1 Yes +*/ + +static bool +has_two_write_locked_tables_with_auto_increment(TABLE_LIST *tables) +{ + char *first_table_name= NULL, *first_db; + for (TABLE_LIST *table= tables; table; table= table->next_global) + { + /* we must do preliminary checks as table->table may be NULL */ + if (!table->placeholder() && !table->schema_table && + table->table->found_next_number_field && + (table->lock_type >= TL_WRITE_ALLOW_WRITE)) + { + if (first_table_name == NULL) + { + first_table_name= table->table_name; + first_db= table->db; + DBUG_ASSERT(first_db); + } + else if (strcmp(first_db, table->db) || + strcmp(first_table_name, table->table_name)) + return 1; + } + } + return 0; +} 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 5222e75f309..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; @@ -1418,7 +1518,17 @@ public: inline void set_current_stmt_binlog_row_based_if_mixed() { #ifdef HAVE_ROW_BASED_REPLICATION - if (variables.binlog_format == BINLOG_FORMAT_MIXED) + /* + If in a stored/function trigger, the caller should already have done the + change. We test in_sub_stmt to prevent introducing bugs where people + wouldn't ensure that, and would switch to row-based mode in the middle + of executing a stored function/trigger (which is too late, see also + reset_current_stmt_binlog_row_based()); this condition will make their + tests fail and so force them to propagate the + lex->binlog_row_based_if_mixed upwards to the caller. + */ + if ((variables.binlog_format == BINLOG_FORMAT_MIXED) && + (in_sub_stmt == 0)) current_stmt_binlog_row_based= TRUE; #endif } @@ -1437,8 +1547,26 @@ public: inline void reset_current_stmt_binlog_row_based() { #ifdef HAVE_ROW_BASED_REPLICATION - current_stmt_binlog_row_based= - test(variables.binlog_format == BINLOG_FORMAT_ROW); + /* + If there are temporary tables, don't reset back to + statement-based. Indeed it could be that: + CREATE TEMPORARY TABLE t SELECT UUID(); # row-based + # and row-based does not store updates to temp tables + # in the binlog. + INSERT INTO u SELECT * FROM t; # stmt-based + and then the INSERT will fail as data inserted into t was not logged. + So we continue with row-based until the temp table is dropped. + If we are in a stored function or trigger, we mustn't reset in the + middle of its execution (as the binary logging way of a stored function + or trigger is decided when it starts executing, depending for example on + the caller (for a stored function: if caller is SELECT or + INSERT/UPDATE/DELETE...). + */ + if ((temporary_tables == NULL) && (in_sub_stmt == 0)) + { + current_stmt_binlog_row_based= + test(variables.binlog_format == BINLOG_FORMAT_ROW); + } #else current_stmt_binlog_row_based= FALSE; #endif @@ -1589,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; @@ -1637,7 +1765,8 @@ public: virtual bool can_rollback_data() { return 1; } // Needed for access from local class MY_HOOKS in prepare(), since thd is proteted. - THD *get_thd(void) { return thd; } + const THD *get_thd(void) { return thd; } + const HA_CREATE_INFO *get_create_info() { return create_info; }; }; #include <myisam.h> diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index ceeb4f6fdf8..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,10 +970,20 @@ 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; - table->file->restore_auto_increment(); // it's too early here! BUG#20188 is_duplicate_key_error= table->file->is_fatal_error(error, 0); if (!is_duplicate_key_error) { @@ -1008,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) { @@ -1067,27 +1070,33 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) if (res == VIEW_CHECK_ERROR) goto before_trg_err; - if (thd->clear_next_insert_id) - { - /* Reset auto-increment cacheing if we do an update */ - thd->clear_next_insert_id= 0; - thd->next_insert_id= 0; - } if ((error=table->file->ha_update_row(table->record[1], table->record[0]))) { if (info->ignore && !table->file->is_fatal_error(error, HA_CHECK_DUP_KEY)) + { + table->file->restore_auto_increment(); goto ok_or_after_trg_err; + } 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) + { + 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 */ { @@ -1111,16 +1120,11 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH) && (!table->triggers || !table->triggers->has_delete_triggers())) { - if (thd->clear_next_insert_id) - { - /* Reset auto-increment cacheing if we do an update */ - thd->clear_next_insert_id= 0; - thd->next_insert_id= 0; - } if ((error=table->file->ha_update_row(table->record[1], 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. @@ -1149,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. @@ -1162,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)); @@ -1187,6 +1193,7 @@ err: table->file->print_error(error,MYF(0)); before_trg_err: + table->file->restore_auto_increment(); if (key) my_safe_afree(key, table->s->max_unique_length, MAX_KEY_LENGTH); table->column_bitmaps_set(save_read_set, save_write_set); @@ -1249,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; @@ -1293,6 +1301,11 @@ public: thd.command=COM_DELAYED_INSERT; thd.lex->current_select= 0; // for my_message_sql thd.lex->sql_command= SQLCOM_INSERT; // For innodb::store_lock() + /* + Statement-based replication of INSERT DELAYED has problems with RAND() + and user vars, so in mixed mode we go to row-based. + */ + thd.set_current_stmt_binlog_row_based_if_mixed(); bzero((char*) &thd.net, sizeof(thd.net)); // Safety bzero((char*) &table_list, sizeof(table_list)); // Safety @@ -1656,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); @@ -1912,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); @@ -2024,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--; @@ -2039,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; @@ -2067,9 +2084,19 @@ bool delayed_insert::handle_inserts(void) } if (row->log_query && row->query.str != NULL && mysql_bin_log.is_open()) + { + /* + If the query has several rows to insert, only the first row will come + here. In row-based binlogging, this means that the first row will be + written to binlog as one Table_map event and one Rows event (due to an + event flush done in binlog_query()), then all other rows of this query + will be binlogged together as one single Table_map event and one + single Rows event. + */ thd.binlog_query(THD::ROW_QUERY_TYPE, row->query.str, row->query.length, FALSE, FALSE); + } if (table->s->blob_fields) free_delayed_insert_blobs(table); @@ -2223,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)); @@ -2432,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); } @@ -2502,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, @@ -2523,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; @@ -2548,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 @@ -2579,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); } @@ -2745,21 +2780,6 @@ static TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info, } -class MY_HOOKS : public TABLEOP_HOOKS -{ -public: - MY_HOOKS(select_create *x) : ptr(x) { } - virtual void do_prelock(TABLE **tables, uint count) - { - if (ptr->get_thd()->current_stmt_binlog_row_based) - ptr->binlog_show_create_table(tables, count); - } - -private: - select_create *ptr; -}; - - int select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u) { @@ -2772,8 +2792,9 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u) MY_HOOKS(select_create *x) : ptr(x) { } virtual void do_prelock(TABLE **tables, uint count) { - if (ptr->get_thd()->current_stmt_binlog_row_based) - ptr->binlog_show_create_table(tables, count); + if (ptr->get_thd()->current_stmt_binlog_row_based && + !(ptr->get_create_info()->options & HA_LEX_CREATE_TMP_TABLE)) + ptr->binlog_show_create_table(tables, count); } private: diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index f6031a1f2fd..7e24436fc39 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -183,7 +183,6 @@ void lex_start(THD *thd, const uchar *buf, uint length) lex->nest_level=0 ; lex->allow_sum_func= 0; lex->in_sum_func= NULL; - lex->binlog_row_based_if_mixed= 0; DBUG_VOID_RETURN; } @@ -1625,6 +1624,9 @@ void Query_tables_list::reset_query_tables_list(bool init) sroutines_list.empty(); sroutines_list_own_last= sroutines_list.next; sroutines_list_own_elements= 0; +#ifdef HAVE_ROW_BASED_REPLICATION + binlog_row_based_if_mixed= FALSE; +#endif } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index a46aaa0bab7..9d8a918e2a3 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -793,6 +793,16 @@ public: byte **sroutines_list_own_last; uint sroutines_list_own_elements; +#ifdef HAVE_ROW_BASED_REPLICATION + /* + Tells if the parsing stage detected that some items require row-based + binlogging to give a reliable binlog/replication, or if we will use + stored functions or triggers which themselves need require row-based + binlogging. + */ + bool binlog_row_based_if_mixed; +#endif + /* These constructor and destructor serve for creation/destruction of Query_tables_list instances which are used as backup storage. @@ -970,11 +980,7 @@ typedef struct st_lex : public Query_tables_list uint8 create_view_check; bool drop_if_exists, drop_temporary, local_file, one_shot_set; bool in_comment, ignore_space, verbose, no_write_to_binlog; - /* - binlog_row_based_if_mixed tells if the parsing stage detected that some - items require row-based binlogging to give a reliable binlog/replication. - */ - bool tx_chain, tx_release, binlog_row_based_if_mixed; + bool tx_chain, tx_release; /* Special JOIN::prepare mode: changing of query is prohibited. When creating a view, we need to just check its syntax omitting 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 232df095816..3eeb5bedf41 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2503,11 +2503,6 @@ mysql_execute_command(THD *thd) statistic_increment(thd->status_var.com_stat[lex->sql_command], &LOCK_status); -#ifdef HAVE_ROW_BASED_REPLICATION - if (lex->binlog_row_based_if_mixed) - thd->set_current_stmt_binlog_row_based_if_mixed(); -#endif /*HAVE_ROW_BASED_REPLICATION*/ - switch (lex->sql_command) { case SQLCOM_SHOW_EVENTS: if ((res= check_access(thd, EVENT_ACL, thd->lex->select_lex.db, 0, 0, 0, @@ -3348,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: @@ -3401,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: @@ -5166,9 +5162,6 @@ end: */ if (thd->one_shot_set && lex->sql_command != SQLCOM_SET_OPTION) reset_one_shot_variables(thd); -#ifdef HAVE_ROW_BASED_REPLICATION - thd->reset_current_stmt_binlog_row_based(); -#endif /*HAVE_ROW_BASED_REPLICATION*/ /* The return value for ROW_COUNT() is "implementation dependent" if the @@ -5807,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 @@ -5817,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 | @@ -5846,6 +5843,11 @@ void mysql_reset_thd_for_next_command(THD *thd) thd->rand_used= 0; thd->sent_row_count= thd->examined_row_count= 0; } +#ifdef HAVE_ROW_BASED_REPLICATION + /* If in a routine, we reset only at end of top statement. */ + thd->reset_current_stmt_binlog_row_based(); +#endif /*HAVE_ROW_BASED_REPLICATION*/ + DBUG_VOID_RETURN; } 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/sql_view.cc b/sql/sql_view.cc index d1e7ba80ecf..c81c4294e1c 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -997,6 +997,15 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table) table->next_global= view_tables; } +#ifdef HAVE_ROW_BASED_REPLICATION + /* + If the view's body needs row-based binlogging (e.g. the VIEW is created + from SELECT UUID()), the top statement also needs it. + */ + if (lex->binlog_row_based_if_mixed) + old_lex->binlog_row_based_if_mixed= TRUE; +#endif + /* If we are opening this view as part of implicit LOCK TABLES, then this view serves as simple placeholder and we should not continue diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 0632e2298cd..6d989feb4ce 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -6374,7 +6374,7 @@ simple_expr: if (udf->type == UDFTYPE_AGGREGATE) Select->in_sum_expr--; - Lex->binlog_row_based_if_mixed= 1; + Lex->binlog_row_based_if_mixed= TRUE; switch (udf->returns) { case STRING_RESULT: 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 } }; |