diff options
Diffstat (limited to 'mysql-test')
80 files changed, 4263 insertions, 131 deletions
diff --git a/mysql-test/collections/default.experimental b/mysql-test/collections/default.experimental index 6d5655ba499..41d5282a6c9 100644 --- a/mysql-test/collections/default.experimental +++ b/mysql-test/collections/default.experimental @@ -9,7 +9,7 @@ innodb.innodb_information_schema # Bug#47449 2009-09-19 alik main.inform main.ctype_gbk_binlog @solaris # Bug#46010: main.ctype_gbk_binlog fails sporadically : Table 't2' already exists main.information_schema # Bug#47449 2009-09-19 alik main.information_schema and innodb.innodb_information_schema fail sporadically -main.innodb-autoinc # Bug#44030 2009-09-24 alik Marking innodb-autoinc experimental while waiting for the patch to be merged +main.innodb-autoinc* # Bug#47809 2009-10-04 joro innodb-autoinc.test fails with valgrind errors with the innodb plugin main.lock_multi_bug38499 # Bug#47448 2009-09-19 alik main.lock_multi_bug38499 times out sporadically main.lock_multi_bug38691 @solaris # Bug#47792 2009-10-02 alik main.lock_multi_bug38691 times out sporadically on Solaris 10 main.log_tables # Bug#47924 2009-10-08 alik main.log_tables times out sporadically diff --git a/mysql-test/extra/binlog_tests/drop_temp_table.test b/mysql-test/extra/binlog_tests/drop_temp_table.test index 7d37fca2bef..5616fb4a643 100644 --- a/mysql-test/extra/binlog_tests/drop_temp_table.test +++ b/mysql-test/extra/binlog_tests/drop_temp_table.test @@ -1,27 +1,62 @@ --disable_warnings -drop database if exists `drop-temp+table-test`; +DROP DATABASE IF EXISTS `drop-temp+table-test`; --enable_warnings connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; -reset master; -create database `drop-temp+table-test`; -use `drop-temp+table-test`; -create temporary table shortn1 (a int); -create temporary table `table:name` (a int); -create temporary table shortn2 (a int); -select get_lock("a",10); +RESET MASTER; +CREATE DATABASE `drop-temp+table-test`; +USE `drop-temp+table-test`; +CREATE TEMPORARY TABLE shortn1 (a INT); +CREATE TEMPORARY TABLE `table:name` (a INT); +CREATE TEMPORARY TABLE shortn2 (a INT); + +############################################################################## +# BUG#46572 DROP TEMPORARY table IF EXISTS does not have a consistent behavior +# in ROW mode +# +# In RBR, 'DROP TEMPORARY TABLE ...' statement should never be binlogged no +# matter if the tables exist or not. In contrast, both in SBR and MBR, the +# statement should be always binlogged no matter if the tables exist or not. +############################################################################## +CREATE TEMPORARY TABLE tmp(c1 int); +CREATE TEMPORARY TABLE tmp1(c1 int); +CREATE TEMPORARY TABLE tmp2(c1 int); +CREATE TEMPORARY TABLE tmp3(c1 int); +CREATE TABLE t(c1 int); + +DROP TEMPORARY TABLE IF EXISTS tmp; + +--disable_warnings +# Before fixing BUG#46572, 'DROP TEMPORARY TABLE IF EXISTS...' statement was +# binlogged when the table did not exist in RBR. +DROP TEMPORARY TABLE IF EXISTS tmp; + +# In RBR, 'DROP TEMPORARY TABLE ...' statement is never binlogged no matter if +# the tables exist or not. +DROP TEMPORARY TABLE IF EXISTS tmp, tmp1; +DROP TEMPORARY TABLE tmp3; + +#In RBR, tmp2 will NOT be binlogged, because it is a temporary table. +DROP TABLE IF EXISTS tmp2, t; + +#In RBR, tmp2 will be binlogged, because it does not exist and master do not know +# whether it is a temporary table or not. +DROP TABLE IF EXISTS tmp2, t; +--enable_warnings + +SELECT GET_LOCK("a",10); disconnect con1; connection con2; # We want to SHOW BINLOG EVENTS, to know what was logged. But there is no # guarantee that logging of the terminated con1 has been done yet. # To be sure that logging has been done, we use a user lock. -select get_lock("a",10); -let $VERSION=`select version()`; +SELECT GET_LOCK("a",10); +let $VERSION=`SELECT VERSION()`; source include/show_binlog_events.inc; -drop database `drop-temp+table-test`; +DROP DATABASE `drop-temp+table-test`; # End of 4.1 tests diff --git a/mysql-test/extra/rpl_tests/rpl_auto_increment_insert_view.test b/mysql-test/extra/rpl_tests/rpl_auto_increment_insert_view.test new file mode 100644 index 00000000000..0bfa46de113 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_auto_increment_insert_view.test @@ -0,0 +1,44 @@ +# +# This test verifies if inserting data into view that invokes a +# trigger will make the autoinc values become inconsistent on +# master and slave. +# +connection master; +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +eval create trigger tr16 $insert_action on t1 for each row insert into t3(a) values(new.c1); +eval create trigger tr17 $insert_action on t2 for each row insert into t3(a) values(new.c2); +begin; +INSERT INTO t1(c1) VALUES (11), (12); +INSERT INTO t2(c2) VALUES (13), (14); + +CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2; + +INSERT INTO v16(c1) VALUES (15),(16); +INSERT INTO v16(c2) VALUES (17),(18); + +connection master1; +INSERT INTO v16(c1) VALUES (19),(20); +INSERT INTO v16(c2) VALUES (21),(22); + +connection master; +INSERT INTO v16(c1) VALUES (23), (24); +INSERT INTO v16(c1) VALUES (25), (26); +commit; +sync_slave_with_master; +--echo #Test if the results are consistent on master and slave +--echo #for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS' +let $diff_table_1=master:test.t3; +let $diff_table_2=slave:test.t3; +source include/diff_tables.inc; + +connection master; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP VIEW v16; +sync_slave_with_master; + + + diff --git a/mysql-test/extra/rpl_tests/rpl_auto_increment_invoke_trigger.test b/mysql-test/extra/rpl_tests/rpl_auto_increment_invoke_trigger.test new file mode 100644 index 00000000000..614d79d9c2d --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_auto_increment_invoke_trigger.test @@ -0,0 +1,82 @@ +# +# This test verifies if concurrent transactions that invoke a +# trigger that inserts more than one values into one or more +# tables with an auto_increment column will make the autoinc +# values become inconsistent on master and slave. +# + +connection master; +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +eval create trigger tr1 $trigger_action on t1 for each row insert into t2(a) values(6); + +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +delimiter |; +eval create trigger tr2 $trigger_action on t3 for each row begin + insert into t4(a) values(f1_insert_triggered()); + insert into t4(a) values(f1_insert_triggered()); + insert into t5(a) values(8); +end | +delimiter ;| + +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +delimiter //; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN + INSERT INTO t6(a) values(2),(3); + RETURN 1; +END// +delimiter ;// + +begin; +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; + +connection master1; +#The default autocommit is set to 1, so the statement is auto committed +insert into t2(a) values(3); +insert into t4(a) values(3); + +connection master; +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +--echo # To verify if insert/update in an autoinc column causes statement to be logged in row format +source include/show_binlog_events.inc; +commit; + +connection master; +sync_slave_with_master; +--echo #Test if the results are consistent on master and slave +--echo #for 'INVOKES A TRIGGER with $trigger_action action' +let $diff_table_1=master:test.t2; +let $diff_table_2=slave:test.t2; +source include/diff_tables.inc; +let $diff_table_1=master:test.t4; +let $diff_table_2=slave:test.t4; +source include/diff_tables.inc; +let $diff_table_1=master:test.t6; +let $diff_table_2=slave:test.t6; +source include/diff_tables.inc; + +connection master; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +sync_slave_with_master; + diff --git a/mysql-test/extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test b/mysql-test/extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test new file mode 100644 index 00000000000..fece19b397d --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test @@ -0,0 +1,57 @@ +# +# This test verifies if concurrent transactions that call a +# function which invokes a 'after/before insert action' trigger +# that inserts more than one values into a table with autoinc +# column will make the autoinc values become inconsistent on +# master and slave. +# + +connection master; +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +delimiter |; +CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER +BEGIN + INSERT INTO t2(a) values(2),(3); + INSERT INTO t2(a) values(2),(3); + RETURN 1; +END | +eval create trigger tr11 $insert_action on t2 for each row begin + insert into t3(a) values(new.a); + insert into t3(a) values(new.a); +end | +delimiter ;| +begin; +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +insert into t1(a) values(f1_two_inserts_trigger()); + +connection master1; +#The default autocommit is set to 1, so the statement is auto committed +insert into t2(a) values(4),(5); + +connection master; +commit; +insert into t1(a) values(f1_two_inserts_trigger()); +--echo # To verify if insert/update in an autoinc column causes statement to be logged in row format +source include/show_binlog_events.inc; +commit; + +connection master; +sync_slave_with_master; +--echo #Test if the results are consistent on master and slave +--echo #for 'CALLS A FUNCTION which INVOKES A TRIGGER with $insert_action action' +let $diff_table_1=master:test.t2; +let $diff_table_2=slave:test.t2; +source include/diff_tables.inc; +let $diff_table_1=master:test.t3; +let $diff_table_2=slave:test.t3; +source include/diff_tables.inc; + +connection master; +drop table t1; +drop table t2; +drop table t3; +drop function f1_two_inserts_trigger; +sync_slave_with_master; + diff --git a/mysql-test/extra/rpl_tests/rpl_get_master_version_and_clock.test b/mysql-test/extra/rpl_tests/rpl_get_master_version_and_clock.test index c79ccdd044f..89c136d8893 100644 --- a/mysql-test/extra/rpl_tests/rpl_get_master_version_and_clock.test +++ b/mysql-test/extra/rpl_tests/rpl_get_master_version_and_clock.test @@ -41,7 +41,17 @@ eval SELECT RELEASE_LOCK($debug_lock); connection slave; source include/wait_for_slave_io_error.inc; let $last_io_errno= query_get_value("show slave status", Last_IO_Errno, 1); -echo Slave_IO_Errno= $last_io_errno; +--echo Check network error happened here +if (`SELECT '$last_io_errno' = '2013' || # CR_SERVER_LOST + '$last_io_errno' = '2003' || # CR_CONN_HOST_ERROR + '$last_io_errno' = '2002' || # CR_CONNECTION_ERROR + '$last_io_errno' = '2006' || # CR_SERVER_GONE_ERROR + '$last_io_errno' = '1040' || # ER_CON_COUNT_ERROR + '$last_io_errno' = '1053' # ER_SERVER_SHUTDOWN + `) +{ + --echo NETWORK ERROR +} # Write file to make mysql-test-run.pl start up the server again --append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect diff --git a/mysql-test/include/concurrent.inc b/mysql-test/include/concurrent.inc index 66f8a65a102..0b7299a3c34 100644 --- a/mysql-test/include/concurrent.inc +++ b/mysql-test/include/concurrent.inc @@ -25,8 +25,6 @@ # new wrapper t/concurrent_innodb_safelog.test # ---source include/not_embedded.inc - connection default; # # Show prerequisites for this test. diff --git a/mysql-test/include/have_debug_sync.inc b/mysql-test/include/have_debug_sync.inc new file mode 100644 index 00000000000..7aa5baf3342 --- /dev/null +++ b/mysql-test/include/have_debug_sync.inc @@ -0,0 +1,5 @@ +--require r/have_debug_sync.require +disable_query_log; +let $value= query_get_value(SHOW VARIABLES LIKE 'debug_sync', Value, 1); +eval SELECT ('$value' LIKE 'ON %') AS debug_sync; +enable_query_log; diff --git a/mysql-test/include/have_mysql_upgrade.inc b/mysql-test/include/have_mysql_upgrade.inc new file mode 100644 index 00000000000..8f486176018 --- /dev/null +++ b/mysql-test/include/have_mysql_upgrade.inc @@ -0,0 +1,4 @@ +--require r/have_mysql_upgrade.result +--disable_query_log +select LENGTH("$MYSQL_UPGRADE")>0 as have_mysql_upgrade; +--enable_query_log diff --git a/mysql-test/include/have_not_innodb_plugin.inc b/mysql-test/include/have_not_innodb_plugin.inc new file mode 100644 index 00000000000..aaefbaf661c --- /dev/null +++ b/mysql-test/include/have_not_innodb_plugin.inc @@ -0,0 +1,4 @@ +disable_query_log; +--require r/not_true.require +select (PLUGIN_LIBRARY LIKE 'ha_innodb_plugin%') as `TRUE` from information_schema.plugins where PLUGIN_NAME='InnoDB'; +enable_query_log; diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index 6dabe4864a9..194d9e41108 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -442,6 +442,8 @@ INSERT INTO t1(id, dept, age, name) VALUES EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; DELETE FROM t1; +--echo # Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746). +--replace_column 9 # EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; diff --git a/mysql-test/include/mtr_warnings.sql b/mysql-test/include/mtr_warnings.sql index 52bf4cd51ef..28d4dacfde0 100644 --- a/mysql-test/include/mtr_warnings.sql +++ b/mysql-test/include/mtr_warnings.sql @@ -173,6 +173,7 @@ INSERT INTO global_suppressions VALUES this error message. */ ("Can't find file: '.\\\\test\\\\\\?{8}.frm'"), + ("Slave: Unknown table 't1' Error_code: 1051"), /* Added 2009-08-XX after fixing Bug #42408 */ diff --git a/mysql-test/mysql-stress-test.pl b/mysql-test/mysql-stress-test.pl index 3061506da51..c80706d9f25 100755 --- a/mysql-test/mysql-stress-test.pl +++ b/mysql-test/mysql-stress-test.pl @@ -14,17 +14,16 @@ # # Design of stress script should allow one: # -# - To stress test the mysqltest binary test engine. -# - To stress test the regular test suite and any additional test suites -# (such as mysql-test-extra-5.0). -# - To specify files with lists of tests both for initialization of -# stress db and for further testing itself. -# - To define the number of threads to be concurrently used in testing. -# - To define limitations for the test run. such as the number of tests or -# loops for execution or duration of testing, delay between test -# executions, and so forth. -# - To get a readable log file that can be used for identification of -# errors that occur during testing. +# - to use for stress testing mysqltest binary as test engine +# - to use for stress testing both regular test suite and any +# additional test suites (e.g. mysql-test-extra-5.0) +# - to specify files with lists of tests both for initialization of +# stress db and for further testing itself +# - to define number of threads that will be concurrently used in testing +# - to define limitations for test run. e.g. number of tests or loops +# for execution or duration of testing, delay between test executions, etc. +# - to get readable log file which can be used for identification of +# errors arose during testing # # Basic scenarios: # @@ -58,6 +57,8 @@ # to reproduce and debug errors that was found in continued stress # testing # +# 2009-01-28 OBN Additions and modifications per WL#4685 +# ######################################################################## use Config; @@ -114,13 +115,15 @@ $opt_stress_mode="random"; $opt_loop_count=0; $opt_test_count=0; $opt_test_duration=0; -$opt_abort_on_error=0; +# OBN: Changing abort-on-error default to -1 (for WL-4626/4685): -1 means no abort +$opt_abort_on_error=-1; $opt_sleep_time = 0; $opt_threads=1; $pid_file="mysql_stress_test.pid"; $opt_mysqltest= ($^O =~ /mswin32/i) ? "mysqltest.exe" : "mysqltest"; $opt_check_tests_file=""; -@mysqltest_args=("--silent", "-v", "--skip-safemalloc"); +# OBM adding a setting for 'max-connect-retries=7' the default of 500 is to high +@mysqltest_args=("--silent", "-v", "--skip-safemalloc", "--max-connect-retries=7"); # Client ip address $client_ip=inet_ntoa((gethostbyname(hostname()))[4]); @@ -133,24 +136,31 @@ $client_ip=~ s/\.//g; # # S1 - Critical errors - cause immediately abort of testing. These errors # could be caused by server crash or impossibility -# of test execution +# of test execution. # # S2 - Serious errors - these errors are bugs for sure as it knowns that # they shouldn't appear during stress testing # -# S3 - Non-seriuos errros - these errors could be caused by fact that +# S3 - Unknown errors - Errors were returned but we don't know what they are +# so script can't determine if they are OK or not +# +# S4 - Non-seriuos errros - these errors could be caused by fact that # we execute simultaneously statements that # affect tests executed by other threads %error_strings = ( 'Failed in mysql_real_connect()' => S1, + 'Can\'t connect' => S1, 'not found (Errcode: 2)' => S1 ); %error_codes = ( 1012 => S2, 1015 => S2, 1021 => S2, 1027 => S2, 1037 => S2, 1038 => S2, 1039 => S2, 1040 => S2, 1046 => S2, - 1180 => S2, 1181 => S2, 1203 => S2, - 1205 => S2, 1206 => S2, 1207 => S2, - 1223 => S2, 2013 => S1); + 1053 => S2, 1180 => S2, 1181 => S2, + 1203 => S2, 1205 => S4, 1206 => S2, + 1207 => S2, 1213 => S4, 1223 => S2, + 2002 => S1, 2003 => S1, 2006 => S1, + 2013 => S1 + ); share(%test_counters); %test_counters=( loop_count => 0, test_count=>0); @@ -158,6 +168,35 @@ share(%test_counters); share($exiting); $exiting=0; +# OBN Code and 'set_exit_code' function added by ES to set an exit code based on the error category returned +# in combination with the --abort-on-error value see WL#4685) +use constant ABORT_MAKEWEIGHT => 20; +share($gExitCode); +$gExitCode = 0; # global exit code +sub set_exit_code { + my $severity = shift; + my $code = 0; + if ( $severity =~ /^S(\d+)/ ) { + $severity = $1; + $code = 11 - $severity; # S1=10, S2=9, ... -- as per WL + } + else { + # we know how we call the sub: severity should be S<num>; so, we should never be here... + print STDERR "Unknown severity format: $severity; setting to S1\n"; + $severity = 1; + } + $abort = 0; + if ( $severity <= $opt_abort_on_error ) { + # the test finished with a failure severe enough to abort. We are adding the 'abort flag' to the exit code + $code += ABORT_MAKEWEIGHT; + # but are not exiting just yet -- we need to update global exit code first + $abort = 1; + } + lock $gExitCode; # we can use lock here because the script uses threads anyway + $gExitCode = $code if $code > $gExitCode; + kill INT, $$ if $abort; # this is just a way to call sig_INT_handler: it will set exiting flag, which should do the rest +} + share($test_counters_lock); $test_counters_lock=0; share($log_file_lock); @@ -176,7 +215,8 @@ GetOptions("server-host=s", "server-logs-dir=s", "server-port=s", "threads=s", "sleep-time=s", "loop-count=i", "test-count=i", "test-duration=i", "test-suffix=s", "check-tests-file", "verbose", "log-error-details", "cleanup", "mysqltest=s", - "abort-on-error", "help") || usage(); + # OBN: (changing 'abort-on-error' to numberic for WL-4626/4685) + "abort-on-error=i" => \$opt_abort_on_error, "help") || usage(); usage() if ($opt_help); @@ -563,7 +603,15 @@ EOF if ($opt_test_duration) { - sleep($opt_test_duration); + # OBN - At this point we need to wait for the duration of the test, hoever + # we need to be able to quit if an 'abort-on-error' condition has happend + # with one of the children (WL#4685). Using solution by ES and replacing + # the 'sleep' command with a loop checking the abort condition every second + + foreach ( 1..$opt_test_duration ) { + last if $exiting; + sleep 1; + } kill INT, $$; #Interrupt child threads } @@ -580,6 +628,8 @@ EOF print "EXIT\n"; } +exit $gExitCode; # ES WL#4685: script should return a meaningful exit code + sub test_init { my ($env)=@_; @@ -681,7 +731,9 @@ sub test_execute { if (!exists($error_codes{$err_code})) { - $severity="S3"; + # OBN Changing severity level to S4 from S3 as S3 now reserved + # for the case where the error is unknown (for WL#4626/4685 + $severity="S4"; $err_code=0; } else @@ -734,6 +786,7 @@ sub test_execute { push @{$env->{test_status}}, "Severity $severity: $total"; $env->{errors}->{total}=+$total; + set_exit_code($severity); } } @@ -748,18 +801,20 @@ sub test_execute log_session_errors($env, $test_file); - if (!$exiting && ($signal_num == 2 || $signal_num == 15 || - ($opt_abort_on_error && $env->{errors}->{S1} > 0))) + #OBN Removing the case of S1 and abort-on-error as that is now set + # inside the set_exit_code function (for WL#4626/4685) + #if (!$exiting && ($signal_num == 2 || $signal_num == 15 || + # ($opt_abort_on_error && $env->{errors}->{S1} > 0))) + if (!$exiting && ($signal_num == 2 || $signal_num == 15)) { - #mysqltest was interrupted with INT or TERM signals or test was - #ran with --abort-on-error option and we got errors with severity S1 + #mysqltest was interrupted with INT or TERM signals #so we assume that we should cancel testing and exit $exiting=1; + # OBN - Adjusted text to exclude case of S1 and abort-on-error that + # was mentioned (for WL#4626/4685) print STDERR<<EOF; WARNING: - mysqltest was interrupted with INT or TERM signals or test was - ran with --abort-on-error option and we got errors with severity S1 - (test cann't connect to the server or server crashed) so we assume that + mysqltest was interrupted with INT or TERM signals so we assume that we should cancel testing and exit. Please check log file for this thread in $stress_log_file or inspect below output of the last test case executed with mysqltest to @@ -840,12 +895,23 @@ LOOP: $client_env{test_count}."]:". " TID ".$client_env{thread_id}. " test: '$test_name' ". - " Errors: ".join(" ",@{$client_env{test_status}}),"\n"; - print "\n"; + " Errors: ".join(" ",@{$client_env{test_status}}). + ( $exiting ? " (thread aborting)" : "" )."\n"; } - sleep($opt_sleep_time) if($opt_sleep_time); - + # OBN - At this point we need to wait until the 'wait' time between test + # executions passes (in case it is specifed) passes, hoever we need + # to be able to quit and break out of the test if an 'abort-on-error' + # condition has happend with one of the other children (WL#4685). + # Using solution by ES and replacing the 'sleep' command with a loop + # checking the abort condition every second + + if ( $opt_sleep_time ) { + foreach ( 1..$opt_sleep_time ) { + last if $exiting; + sleep 1; + } + } } } @@ -1119,6 +1185,9 @@ mysql-stress-test.pl --stress-basedir=<dir> --stress-suite-basedir=<dir> --serve --cleanup Force to clean up working directory (specified with --stress-basedir) +--abort-on-error=<number> + Causes the script to abort if an error with severity <= number was encounterd + --log-error-details Enable errors details in the global error log file. (Default: off) diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 83364db0eeb..5697bced053 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -226,6 +226,7 @@ my @default_valgrind_args= ("--show-reachable=yes"); my @valgrind_args; my $opt_valgrind_path; my $opt_callgrind; +my $opt_debug_sync_timeout= 300; # Default timeout for WAIT_FOR actions. our $opt_warnings= 1; @@ -867,6 +868,7 @@ sub command_line_setup { 'valgrind-option=s' => \@valgrind_args, 'valgrind-path=s' => \$opt_valgrind_path, 'callgrind' => \$opt_callgrind, + 'debug-sync-timeout=i' => \$opt_debug_sync_timeout, # Directories 'tmpdir=s' => \$opt_tmpdir, @@ -1812,7 +1814,7 @@ sub environment_setup { ($lib_example_plugin ? dirname($lib_example_plugin) : ""); $ENV{'HA_EXAMPLE_SO'}="'".$plugin_filename."'"; - $ENV{'EXAMPLE_PLUGIN_LOAD'}="--plugin_load=;EXAMPLE=".$plugin_filename.";"; + $ENV{'EXAMPLE_PLUGIN_LOAD'}="--plugin_load=EXAMPLE=".$plugin_filename; } else { @@ -4178,6 +4180,11 @@ sub mysqld_arguments ($$$) { mtr_add_arg($args, "%s", "--core-file"); } + # Enable the debug sync facility, set default wait timeout. + # Facility stays disabled if timeout value is zero. + mtr_add_arg($args, "--loose-debug-sync-timeout=%s", + $opt_debug_sync_timeout); + return $args; } @@ -5288,6 +5295,8 @@ Misc options to turn off. sleep=SECONDS Passed to mysqltest, will be used as fixed sleep time + debug-sync-timeout=NUM Set default timeout for WAIT_FOR debug sync + actions. Disable facility with NUM=0. gcov Collect coverage information after the test. The result is a gcov file per source and header file. experimental=<file> Refer to list of tests considered experimental; diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 5a115e9ea99..db7173d0b47 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1268,4 +1268,66 @@ a b 4 b 5 a DROP TABLE t1; +# +# Bug#45567: Fast ALTER TABLE broken for enum and set +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a ENUM('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +# No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# No copy: Add new enumeration to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Add new enumeration +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# No copy: Add new enumerations to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +DROP TABLE t1; +CREATE TABLE t1 (a SET('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +# No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Add new member +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Copy: Numerical incrase (pack lenght) +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/bug46760.result b/mysql-test/r/bug46760.result new file mode 100644 index 00000000000..413df050b10 --- /dev/null +++ b/mysql-test/r/bug46760.result @@ -0,0 +1,43 @@ +# +# Bug#46760: Fast ALTER TABLE no longer works for InnoDB +# +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +# By using --enable_info and verifying that number of affected +# rows is 0 we check that this ALTER TABLE is really carried +# out as "fast/online" operation, i.e. without full-blown data +# copying. +# +# I.e. info for the below statement should normally look like: +# +# affected rows: 0 +# info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 10; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT '10' +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# MySQL Bug#39200: optimize table does not recognize +# ROW_FORMAT=COMPRESSED +# +CREATE TABLE t1 (a INT) ROW_FORMAT=compressed; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status Table is already up to date +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/r/debug_sync.result b/mysql-test/r/debug_sync.result new file mode 100644 index 00000000000..47e968f79cf --- /dev/null +++ b/mysql-test/r/debug_sync.result @@ -0,0 +1,277 @@ +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: '' +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 EXECUTE 2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 SIGNAL s1 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 EXECUTE 2'; +SET DEBUG_SYNC='p0 SIGNAL s1 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 EXECUTE 2'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 CLEAR'; +SET DEBUG_SYNC='p0 TEST'; +SET DEBUG_SYNC='RESET'; +set debug_sync='p0 signal s1 wait_for s2 timeout 6 execute 2 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2 timeout 6 execute 2'; +set debug_sync='p0 signal s1 wait_for s2 timeout 6 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2 timeout 6'; +set debug_sync='p0 signal s1 wait_for s2 execute 2 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2 execute 2'; +set debug_sync='p0 signal s1 wait_for s2 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2'; +set debug_sync='p0 signal s1 execute 2 hit_limit 3'; +set debug_sync='p0 signal s1 execute 2'; +set debug_sync='p0 signal s1 hit_limit 3'; +set debug_sync='p0 signal s1'; +set debug_sync='p0 wait_for s2 timeout 6 execute 2 hit_limit 3'; +set debug_sync='p0 wait_for s2 timeout 6 execute 2'; +set debug_sync='p0 wait_for s2 timeout 6 hit_limit 3'; +set debug_sync='p0 wait_for s2 timeout 6'; +set debug_sync='p0 wait_for s2 execute 2 hit_limit 3'; +set debug_sync='p0 wait_for s2 execute 2'; +set debug_sync='p0 wait_for s2 hit_limit 3'; +set debug_sync='p0 wait_for s2'; +set debug_sync='p0 hit_limit 3'; +set debug_sync='p0 clear'; +set debug_sync='p0 test'; +set debug_sync='reset'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 + EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC=' p0 SIGNAL s1 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 '; +SET DEBUG_SYNC=' p0 SIGNAL s1 WAIT_FOR s2 '; +SET DEBUG_SYNC=' p0 SIGNAL s1 WAIT_FOR s2 '; +SET DEBUG_SYNC=''; +ERROR 42000: Missing synchronization point name +SET DEBUG_SYNC=' '; +ERROR 42000: Missing synchronization point name +SET DEBUG_SYNC='p0'; +ERROR 42000: Missing action after synchronization point name 'p0' +SET DEBUG_SYNC='p0 EXECUTE 2'; +ERROR 42000: Missing action before EXECUTE +SET DEBUG_SYNC='p0 TIMEOUT 6 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 TIMEOUT 6'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 TIMEOUT 6 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 TIMEOUT 6'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 SIGNAL s1 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 SIGNAL s1'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 TIMEOUT 6 WAIT_FOR s2 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 TIMEOUT 6 WAIT_FOR s2'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 SIGNAL s1 TIMEOUT 6 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 SIGNAL s1 TIMEOUT 6'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 EXECUTE 2 SIGNAL s1 TIMEOUT 6'; +ERROR 42000: Missing action before EXECUTE +SET DEBUG_SYNC='p0 TIMEOUT 6 SIGNAL s1'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 EXECUTE 2 TIMEOUT 6 SIGNAL s1'; +ERROR 42000: Missing action before EXECUTE +SET DEBUG_SYNC='p0 CLEAR HIT_LIMIT 3'; +ERROR 42000: Nothing must follow action CLEAR +SET DEBUG_SYNC='CLEAR'; +ERROR 42000: Missing action after synchronization point name 'CLEAR' +SET DEBUG_SYNC='p0 CLEAR p0'; +ERROR 42000: Nothing must follow action CLEAR +SET DEBUG_SYNC='TEST'; +ERROR 42000: Missing action after synchronization point name 'TEST' +SET DEBUG_SYNC='p0 TEST p0'; +ERROR 42000: Nothing must follow action TEST +SET DEBUG_SYNC='p0 RESET'; +ERROR 42000: Illegal or out of order stuff: 'RESET' +SET DEBUG_SYNC='RESET p0'; +ERROR 42000: Illegal or out of order stuff: 'p0' +SET DEBUG_SYNC='p0 RESET p0'; +ERROR 42000: Illegal or out of order stuff: 'RESET' +SET DEBUG_SYNC='p0 SIGNAL '; +ERROR 42000: Missing signal name after action SIGNAL +SET DEBUG_SYNC='p0 WAIT_FOR '; +ERROR 42000: Missing signal name after action WAIT_FOR +SET DEBUG_SYNC='p0 SIGNAL s1 EXECUTE '; +ERROR 42000: Missing valid number after EXECUTE +SET DEBUG_SYNCx='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +ERROR HY000: Unknown system variable 'DEBUG_SYNCx' +SET DEBUG_SYNC='p0 SIGNAx s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAx' +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOx s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +ERROR 42000: Illegal or out of order stuff: 'WAIT_FOx' +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUx 0 EXECUTE 2 HIT_LIMIT 3'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUx' +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTx 2 HIT_LIMIT 3'; +ERROR 42000: Illegal or out of order stuff: 'EXECUTx' +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIx 3'; +ERROR 42000: Illegal or out of order stuff: 'HIT_LIMIx' +SET DEBUG_SYNC='p0 CLEARx'; +ERROR 42000: Illegal or out of order stuff: 'CLEARx' +SET DEBUG_SYNC='p0 TESTx'; +ERROR 42000: Illegal or out of order stuff: 'TESTx' +SET DEBUG_SYNC='RESETx'; +ERROR 42000: Missing action after synchronization point name 'RESETx' +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 0x6 EXECUTE 2 HIT_LIMIT 3'; +ERROR 42000: Missing valid number after TIMEOUT +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 EXECUTE 0x2 HIT_LIMIT 3'; +ERROR 42000: Missing valid number after EXECUTE +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 7 EXECUTE 2 HIT_LIMIT 0x3'; +ERROR 42000: Missing valid number after HIT_LIMIT +SET DEBUG_SYNC= 7; +ERROR 42000: Incorrect argument type to variable 'debug_sync' +SET GLOBAL DEBUG_SYNC= 'p0 CLEAR'; +ERROR HY000: Variable 'debug_sync' is a SESSION variable and can't be used with SET GLOBAL +SET @myvar= 'now SIGNAL from_myvar'; +SET DEBUG_SYNC= @myvar; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 'from_myvar' +SET DEBUG_SYNC= LEFT('now SIGNAL from_function_cut_here', 24); +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 'from_function' +SET DEBUG_SYNC= 'now SIGNAL something'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 'something' +SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0'; +Warnings: +Warning #### debug sync point wait timed out +SET DEBUG_SYNC= 'now SIGNAL nothing'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 'nothing' +SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0'; +SET DEBUG_SYNC= 'now SIGNAL something EXECUTE 0'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 'nothing' +SET DEBUG_SYNC= 'now WAIT_FOR anotherthing TIMEOUT 0 EXECUTE 0'; +SET DEBUG_SYNC= 'now HIT_LIMIT 1'; +ERROR HY000: debug sync point hit limit reached +SET DEBUG_SYNC= 'RESET'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: '' +SET DEBUG_SYNC= 'p1abcd SIGNAL s1 EXECUTE 2'; +SET DEBUG_SYNC= 'p2abc SIGNAL s2 EXECUTE 2'; +SET DEBUG_SYNC= 'p9abcdef SIGNAL s9 EXECUTE 2'; +SET DEBUG_SYNC= 'p4a SIGNAL s4 EXECUTE 2'; +SET DEBUG_SYNC= 'p5abcde SIGNAL s5 EXECUTE 2'; +SET DEBUG_SYNC= 'p6ab SIGNAL s6 EXECUTE 2'; +SET DEBUG_SYNC= 'p7 SIGNAL s7 EXECUTE 2'; +SET DEBUG_SYNC= 'p8abcdef SIGNAL s8 EXECUTE 2'; +SET DEBUG_SYNC= 'p3abcdef SIGNAL s3 EXECUTE 2'; +SET DEBUG_SYNC= 'p4a TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's4' +SET DEBUG_SYNC= 'p1abcd TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's1' +SET DEBUG_SYNC= 'p7 TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's7' +SET DEBUG_SYNC= 'p9abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's9' +SET DEBUG_SYNC= 'p3abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's3' +SET DEBUG_SYNC= 'p1abcd CLEAR'; +SET DEBUG_SYNC= 'p2abc CLEAR'; +SET DEBUG_SYNC= 'p5abcde CLEAR'; +SET DEBUG_SYNC= 'p6ab CLEAR'; +SET DEBUG_SYNC= 'p8abcdef CLEAR'; +SET DEBUG_SYNC= 'p9abcdef CLEAR'; +SET DEBUG_SYNC= 'p3abcdef CLEAR'; +SET DEBUG_SYNC= 'p4a CLEAR'; +SET DEBUG_SYNC= 'p7 CLEAR'; +SET DEBUG_SYNC= 'p1abcd TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's3' +SET DEBUG_SYNC= 'p7 TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's3' +SET DEBUG_SYNC= 'p9abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's3' +SET DEBUG_SYNC= 'RESET'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: '' +CREATE USER mysqltest_1@localhost; +GRANT SUPER ON *.* TO mysqltest_1@localhost; +connection con1, mysqltest_1 +SET DEBUG_SYNC= 'RESET'; +connection default +DROP USER mysqltest_1@localhost; +CREATE USER mysqltest_2@localhost; +GRANT ALL ON *.* TO mysqltest_2@localhost; +REVOKE SUPER ON *.* FROM mysqltest_2@localhost; +connection con1, mysqltest_2 +SET DEBUG_SYNC= 'RESET'; +ERROR 42000: Access denied; you need the SUPER privilege for this operation +connection default +DROP USER mysqltest_2@localhost; +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT); +connection con1 +SET DEBUG_SYNC= 'before_lock_tables_takes_lock + SIGNAL opened WAIT_FOR flushed'; +INSERT INTO t1 VALUES(1); +connection default +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +SET DEBUG_SYNC= 'after_flush_unlock SIGNAL flushed'; +FLUSH TABLE t1; +connection con1 +connection default +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT); +LOCK TABLE t1 WRITE; +connection con1 +SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2'; +INSERT INTO t1 VALUES (1); +connection default +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +UNLOCK TABLES; +connection con1 +retrieve INSERT result. +connection default +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index eb93c69d960..0124a7da35a 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -279,3 +279,48 @@ ERROR 42000: Incorrect number of arguments for FUNCTION test.f1; expected 0, got DROP TABLE t1; DROP FUNCTION f1; End of 5.0 tests +# +# Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, +# merge table +# +CREATE TABLE t1 ( a INT ); +CREATE TABLE t2 ( a INT ); +CREATE TABLE t3 ( a INT ); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1), (2); +INSERT INTO t3 VALUES (1), (2); +CREATE TRIGGER tr1 BEFORE DELETE ON t2 +FOR EACH ROW INSERT INTO no_such_table VALUES (1); +DELETE t1, t2, t3 FROM t1, t2, t3; +ERROR 42S02: Table 'test.no_such_table' doesn't exist +SELECT * FROM t1; +a +SELECT * FROM t2; +a +1 +2 +SELECT * FROM t3; +a +1 +2 +DROP TABLE t1, t2, t3; +CREATE TABLE t1 ( a INT ); +CREATE TABLE t2 ( a INT ); +CREATE TABLE t3 ( a INT ); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1), (2); +INSERT INTO t3 VALUES (1), (2); +CREATE TRIGGER tr1 AFTER DELETE ON t2 +FOR EACH ROW INSERT INTO no_such_table VALUES (1); +DELETE t1, t2, t3 FROM t1, t2, t3; +ERROR 42S02: Table 'test.no_such_table' doesn't exist +SELECT * FROM t1; +a +SELECT * FROM t2; +a +2 +SELECT * FROM t3; +a +1 +2 +DROP TABLE t1, t2, t3; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 88a822a2fa6..ffdacc43735 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -608,4 +608,146 @@ SELECT SUM( DISTINCT e ) FROM t1 GROUP BY b,c,d HAVING (b,c,d) IN ((AVG( 1 ), 1 + c, 1 + d), (AVG( 1 ), 2 + c, 2 + d)); SUM( DISTINCT e ) DROP TABLE t1; +# +# Bug #44139: Table scan when NULL appears in IN clause +# +CREATE TABLE t1 ( +c_int INT NOT NULL, +c_decimal DECIMAL(5,2) NOT NULL, +c_float FLOAT(5, 2) NOT NULL, +c_bit BIT(10) NOT NULL, +c_date DATE NOT NULL, +c_datetime DATETIME NOT NULL, +c_timestamp TIMESTAMP NOT NULL, +c_time TIME NOT NULL, +c_year YEAR NOT NULL, +c_char CHAR(10) NOT NULL, +INDEX(c_int), INDEX(c_decimal), INDEX(c_float), INDEX(c_bit), INDEX(c_date), +INDEX(c_datetime), INDEX(c_timestamp), INDEX(c_time), INDEX(c_year), +INDEX(c_char)); +INSERT INTO t1 (c_int) VALUES (1), (2), (3), (4), (5); +INSERT INTO t1 (c_int) SELECT 0 FROM t1; +INSERT INTO t1 (c_int) SELECT 0 FROM t1; +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, 1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, NULL, 2, NULL, 3, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, 1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_float IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_float c_float 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, 1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_float c_float 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, 1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_date +IN ('2009-09-01', '2009-09-02', '2009-09-03'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_date c_date 3 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_date +IN (NULL, '2009-09-01', '2009-09-02', '2009-09-03'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_date c_date 3 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_datetime +IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_datetime +IN (NULL, '2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_timestamp +IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_timestamp +IN (NULL, '2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_year IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_year c_year 1 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, 1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_year c_year 1 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_char IN ('1', '2', '3'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_char c_char 10 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, '1', '2', '3'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_char c_char 10 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +DROP TABLE t1; +# End of 5.1 tests diff --git a/mysql-test/r/have_debug_sync.require b/mysql-test/r/have_debug_sync.require new file mode 100644 index 00000000000..c2090bc5657 --- /dev/null +++ b/mysql-test/r/have_debug_sync.require @@ -0,0 +1,2 @@ +debug_sync +1 diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 756c010843a..5fa38f93993 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -139,7 +139,7 @@ show create view testdb_1.v7; View Create View character_set_client collation_connection v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist show fields from testdb_1.v7; Field Type Null Key Default Extra f1 char(4) YES NULL @@ -169,7 +169,7 @@ show create view testdb_1.v7; View Create View character_set_client collation_connection v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist revoke insert(f1) on v3 from testdb_2@localhost; revoke show view on v5 from testdb_2@localhost; use testdb_1; @@ -187,7 +187,8 @@ ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7' show create view testdb_1.v7; ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7' show create view v4; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +View Create View character_set_client collation_connection +v4 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `v3`.`f1` AS `f1`,`v3`.`f2` AS `f2` from `testdb_1`.`v3` latin1 latin1_swedish_ci show fields from v4; Field Type Null Key Default Extra f1 char(4) YES NULL diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 3ff5f04b6c6..b112bde4f27 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -385,9 +385,10 @@ name dept rs5 cs10 rs5 cs9 DELETE FROM t1; +# Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746). EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range name name 44 NULL 2 Using where; Using index for group-by +1 SIMPLE t1 range name name 44 NULL # Using where; Using index for group-by SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; name dept DROP TABLE t1; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 736ecf1d90e..77f73532474 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1064,3 +1064,13 @@ a b c d 128 NULL 128 NULL DROP TABLE IF EXISTS t1,t2; End of 5.0 tests. +CREATE TABLE t1 (f1 int); +CREATE TABLE t2 (f1 int); +INSERT INTO t2 VALUES (1); +CREATE VIEW v1 AS SELECT * FROM t2; +PREPARE stmt FROM 'UPDATE t2 AS A NATURAL JOIN v1 B SET B.f1 = 1'; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +DROP VIEW v1; +DROP TABLE t1, t2; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 732b1b260f8..58e2e451a0d 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -2252,4 +2252,23 @@ h+0 d + 0 e g + 0 1 1 3 0 1 1 4 0 DROP TABLE t1; +# +# Test of BUG#35570 CHECKSUM TABLE unreliable if LINESTRING field +# (same content / differen checksum) +# +CREATE TABLE t1 (line LINESTRING NOT NULL) engine=myisam; +INSERT INTO t1 VALUES (GeomFromText("POINT(0 0)")); +checksum table t1; +Table Checksum +test.t1 326284887 +CREATE TABLE t2 (line LINESTRING NOT NULL) engine=myisam; +INSERT INTO t2 VALUES (GeomFromText("POINT(0 0)")); +checksum table t2; +Table Checksum +test.t2 326284887 +CREATE TABLE t3 select * from t1; +checksum table t3; +Table Checksum +test.t3 326284887 +drop table t1,t2,t3; End of 5.1 tests diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 295a2f41d40..5f32561798b 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -44,16 +44,16 @@ SET TIMESTAMP=1000000000/*!*/; insert into t2 values () /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; DELIMITER ; # End of log file @@ -144,16 +144,16 @@ SET TIMESTAMP=1000000000/*!*/; insert into t2 values () /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; DELIMITER ; # End of log file @@ -359,29 +359,29 @@ SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a varchar(64) character set utf8) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=7/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=DEFAULT/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=7/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-a-0' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-a-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=DEFAULT/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-b-0' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-b-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-c-0' INTO table t1 character set koi8r +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-c-0' INTO TABLE `t1` CHARACTER SET koi8r FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; drop table t1 @@ -473,5 +473,94 @@ IS NOT NULL SET @@global.server_id= 1; RESET MASTER; FLUSH LOGS; +RESET MASTER; +FLUSH LOGS; +# +# Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is exist +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +ROLLBACK/*!*/; +use test/*!*/; +SET TIMESTAMP=1253783037/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +create table t1(a int) engine= innodb +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +insert into t1 (a) values (1) +/*!*/; +COMMIT/*!*/; +SET TIMESTAMP=1253783037/*!*/; +create table t3(a int) engine= innodb +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +insert into t3 (a) values (2) +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +ROLLBACK +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +create table t5(a int) engine= NDB +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +insert into t5 (a) values (3) +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +COMMIT +/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; +# +# Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is not exist +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +ROLLBACK/*!*/; +SET TIMESTAMP=1253783037/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +BEGIN +/*!*/; +COMMIT/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +ROLLBACK +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +COMMIT +/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; End of 5.0 tests End of 5.1 tests diff --git a/mysql-test/r/not_true.require b/mysql-test/r/not_true.require new file mode 100644 index 00000000000..0032832f3d1 --- /dev/null +++ b/mysql-test/r/not_true.require @@ -0,0 +1,2 @@ +TRUE +NULL diff --git a/mysql-test/r/partition_innodb_builtin.result b/mysql-test/r/partition_innodb_builtin.result new file mode 100644 index 00000000000..384ce0790a4 --- /dev/null +++ b/mysql-test/r/partition_innodb_builtin.result @@ -0,0 +1,39 @@ +SET NAMES utf8; +CREATE TABLE `t``\""e` (a INT, PRIMARY KEY (a)) +ENGINE=InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION `p0``\""e` VALUES LESS THAN (100) +(SUBPARTITION `sp0``\""e`, +SUBPARTITION `sp1``\""e`), +PARTITION `p1``\""e` VALUES LESS THAN (MAXVALUE) +(SUBPARTITION `sp2``\""e`, +SUBPARTITION `sp3``\""e`)); +INSERT INTO `t``\""e` VALUES (0), (2), (6), (10), (14), (18), (22); +START TRANSACTION; +# con1 +SET NAMES utf8; +START TRANSACTION; +# default connection +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +# con1 +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +# default connection +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# First table reported in 'SHOW ENGINE InnoDB STATUS' +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @@sql_mode = @old_sql_mode; +# con1 +ROLLBACK; +# default connection +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; diff --git a/mysql-test/r/partition_innodb_plugin.result b/mysql-test/r/partition_innodb_plugin.result new file mode 100644 index 00000000000..dd91eee316a --- /dev/null +++ b/mysql-test/r/partition_innodb_plugin.result @@ -0,0 +1,50 @@ +SET NAMES utf8; +CREATE TABLE `t``\""e` (a INT, PRIMARY KEY (a)) +ENGINE=InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION `p0``\""e` VALUES LESS THAN (100) +(SUBPARTITION `sp0``\""e`, +SUBPARTITION `sp1``\""e`), +PARTITION `p1``\""e` VALUES LESS THAN (MAXVALUE) +(SUBPARTITION `sp2``\""e`, +SUBPARTITION `sp3``\""e`)); +INSERT INTO `t``\""e` VALUES (0), (2), (6), (10), (14), (18), (22); +START TRANSACTION; +# con1 +SET NAMES utf8; +START TRANSACTION; +# default connection +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +# con1 +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +# default connection +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +lock_table COUNT(*) +`test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ 2 +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +lock_table COUNT(*) +"test"."t`\""""e" /* Partition "p0`\""""e", Subpartition "sp0`\""""e" */ 2 +set @@sql_mode = @old_sql_mode; +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# First table reported in 'SHOW ENGINE InnoDB STATUS' +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @@sql_mode = @old_sql_mode; +# con1 +ROLLBACK; +# default connection +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 68577cb2a4c..482e0045840 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -27,4 +27,35 @@ SELECT 1; 1 1 DROP TABLE t1,t2,t3; +# +# Bug #47106: Crash / segfault on adding EXPLAIN to a non-crashing +# query +# +CREATE TABLE t1 ( +a INT, +b INT, +PRIMARY KEY (a), +KEY b (b) +); +INSERT INTO t1 VALUES (1, 1), (2, 1); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 SELECT * FROM t1; +CREATE TABLE t3 LIKE t1; +INSERT INTO t3 SELECT * FROM t1; +# Should not crash. +# Should have 1 impossible where and 2 dependent subqs. +EXPLAIN +SELECT +(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +FROM t3 WHERE 1 = 0 GROUP BY 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index +2 DEPENDENT SUBQUERY t2 index b b 5 NULL 2 Using where; Using index; Using join buffer +# should return 0 rows +SELECT +(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +FROM t3 WHERE 1 = 0 GROUP BY 1; +(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +DROP TABLE t1,t2,t3; End of 5.0 tests. diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 7e280fa2fe5..4e43f52d8d7 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -606,7 +606,7 @@ SHOW CREATE VIEW v; View Create View character_set_client collation_connection v CREATE ALGORITHM=UNDEFINED DEFINER=`no-such-user`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `test`.`t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no-such-user'@'localhost') does not exist SELECT * FROM v; ERROR HY000: The user specified as a definer ('no-such-user'@'localhost') does not exist DROP VIEW v; @@ -963,7 +963,7 @@ SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; Warnings: Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist @@ -971,7 +971,7 @@ SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=MERGE DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; Warnings: Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist @@ -979,7 +979,7 @@ SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`no_such`@`user_2` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist DROP VIEW v1; DROP TABLE t1; CREATE USER mysqluser1@localhost; @@ -1044,3 +1044,177 @@ DROP DATABASE mysqltest1; DROP VIEW test.v3; DROP USER mysqluser1@localhost; USE test; +# +# Bug#35996: SELECT + SHOW VIEW should be enough to display view +# definition +# +CREATE USER mysqluser1@localhost; +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +GRANT USAGE, SELECT, CREATE VIEW, SHOW VIEW +ON mysqltest2.* TO mysqluser1@localhost; +USE mysqltest1; +CREATE TABLE t1( a INT ); +CREATE TABLE t2( a INT, b INT ); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE VIEW v1 AS SELECT 1 AS a; +CREATE VIEW v2 AS SELECT 1 AS a, 2 AS b; +GRANT SELECT ON TABLE t1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE t2 TO mysqluser1@localhost; +GRANT EXECUTE ON FUNCTION f1 TO mysqluser1@localhost; +GRANT SELECT ON TABLE v1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE v2 TO mysqluser1@localhost; +CREATE VIEW v_t1 AS SELECT * FROM t1; +CREATE VIEW v_t2 AS SELECT * FROM t2; +CREATE VIEW v_f1 AS SELECT f1() AS a; +CREATE VIEW v_v1 AS SELECT * FROM v1; +CREATE VIEW v_v2 AS SELECT * FROM v2; +GRANT SELECT, SHOW VIEW ON v_t1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_t2 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_f1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v2 TO mysqluser1@localhost; +CREATE VIEW v_mysqluser1_t1 AS SELECT * FROM mysqltest1.t1; +CREATE VIEW v_mysqluser1_t2 AS SELECT * FROM mysqltest1.t2; +CREATE VIEW v_mysqluser1_f1 AS SELECT mysqltest1.f1() AS a; +CREATE VIEW v_mysqluser1_v1 AS SELECT * FROM mysqltest1.v1; +CREATE VIEW v_mysqluser1_v2 AS SELECT * FROM mysqltest1.v2; +SHOW CREATE VIEW mysqltest1.v_t1; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_t2; +View Create View character_set_client collation_connection +v_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v2; +View Create View character_set_client collation_connection +v_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t2; +View Create View character_set_client collation_connection +v_mysqluser1_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v2; +View Create View character_set_client collation_connection +v_mysqluser1_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +REVOKE SELECT ON TABLE t1 FROM mysqluser1@localhost; +REVOKE SELECT (a) ON TABLE t2 FROM mysqluser1@localhost; +REVOKE EXECUTE ON FUNCTION f1 FROM mysqluser1@localhost; +REVOKE SELECT ON TABLE v1 FROM mysqluser1@localhost; +SHOW CREATE VIEW mysqltest1.v_t1; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_t2; +View Create View character_set_client collation_connection +v_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v2; +View Create View character_set_client collation_connection +v_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t2; +View Create View character_set_client collation_connection +v_mysqluser1_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v2; +View Create View character_set_client collation_connection +v_mysqluser1_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +# Testing the case when the views reference missing objects. +# Obviously, there are no privileges to check for, so we +# need only each object type once. +DROP TABLE t1; +DROP FUNCTION f1; +DROP VIEW v1; +SHOW CREATE VIEW mysqltest1.v_t1; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +REVOKE SHOW VIEW ON v_t1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_f1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_v1 FROM mysqluser1@localhost; +SHOW CREATE VIEW mysqltest1.v_t1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_t1' +SHOW CREATE VIEW mysqltest1.v_f1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_f1' +SHOW CREATE VIEW mysqltest1.v_v1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_v1' +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DROP USER mysqluser1@localhost; +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +USE test; +CREATE TABLE t1( a INT ); +CREATE DEFINER = no_such_user@no_such_host VIEW v1 AS SELECT * FROM t1; +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +DROP TABLE t1; +DROP VIEW v1; diff --git a/mysql-test/r/windows.result b/mysql-test/r/windows.result index 4e0d73ea0eb..d0cdd858d4a 100644 --- a/mysql-test/r/windows.result +++ b/mysql-test/r/windows.result @@ -53,3 +53,10 @@ ERROR HY000: No paths allowed for shared library execute abc; ERROR HY000: No paths allowed for shared library deallocate prepare abc; +# +# Bug#45498: Socket variable not available on Windows +# +SELECT VARIABLE_NAME FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME = 'socket'; +VARIABLE_NAME +SOCKET diff --git a/mysql-test/std_data/binlog_transaction.000001 b/mysql-test/std_data/binlog_transaction.000001 Binary files differnew file mode 100644 index 00000000000..c1d0745d57c --- /dev/null +++ b/mysql-test/std_data/binlog_transaction.000001 diff --git a/mysql-test/suite/binlog/r/binlog_killed_simulate.result b/mysql-test/suite/binlog/r/binlog_killed_simulate.result index 634d3f62814..df04f5129cf 100644 --- a/mysql-test/suite/binlog/r/binlog_killed_simulate.result +++ b/mysql-test/suite/binlog/r/binlog_killed_simulate.result @@ -19,7 +19,7 @@ ERROR 70100: Query execution was interrupted show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/rpl_loaddata.dat' into table t2 /* will be "killed" in the middle */ ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t2` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a, b) ;file_id=# select (@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) is not null; diff --git a/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result b/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result index 503076d66d9..0a6ff1d4400 100644 --- a/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result +++ b/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result @@ -1,17 +1,31 @@ -drop database if exists `drop-temp+table-test`; -reset master; -create database `drop-temp+table-test`; -use `drop-temp+table-test`; -create temporary table shortn1 (a int); -create temporary table `table:name` (a int); -create temporary table shortn2 (a int); -select get_lock("a",10); -get_lock("a",10) +DROP DATABASE IF EXISTS `drop-temp+table-test`; +RESET MASTER; +CREATE DATABASE `drop-temp+table-test`; +USE `drop-temp+table-test`; +CREATE TEMPORARY TABLE shortn1 (a INT); +CREATE TEMPORARY TABLE `table:name` (a INT); +CREATE TEMPORARY TABLE shortn2 (a INT); +CREATE TEMPORARY TABLE tmp(c1 int); +CREATE TEMPORARY TABLE tmp1(c1 int); +CREATE TEMPORARY TABLE tmp2(c1 int); +CREATE TEMPORARY TABLE tmp3(c1 int); +CREATE TABLE t(c1 int); +DROP TEMPORARY TABLE IF EXISTS tmp; +DROP TEMPORARY TABLE IF EXISTS tmp; +DROP TEMPORARY TABLE IF EXISTS tmp, tmp1; +DROP TEMPORARY TABLE tmp3; +DROP TABLE IF EXISTS tmp2, t; +DROP TABLE IF EXISTS tmp2, t; +SELECT GET_LOCK("a",10); +GET_LOCK("a",10) 1 -select get_lock("a",10); -get_lock("a",10) +SELECT GET_LOCK("a",10); +GET_LOCK("a",10) 1 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # create database `drop-temp+table-test` -drop database `drop-temp+table-test`; +master-bin.000001 # Query # # CREATE DATABASE `drop-temp+table-test` +master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TABLE t(c1 int) +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS `t` /* generated by server */ +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS tmp2, t +DROP DATABASE `drop-temp+table-test`; diff --git a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result index 9ae5121f618..2d11fec5787 100644 --- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result @@ -917,7 +917,7 @@ master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2) ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a, @b) SET b=((@b) + `bug27417`(2)) ;file_id=# master-bin.000001 # Query # # ROLLBACK drop trigger trg_del_t2; drop table t1,t2,t3,t4,t5; diff --git a/mysql-test/suite/binlog/r/binlog_stm_blackhole.result b/mysql-test/suite/binlog/r/binlog_stm_blackhole.result index f3a01f66fc2..434c1b0896f 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_blackhole.result +++ b/mysql-test/suite/binlog/r/binlog_stm_blackhole.result @@ -127,7 +127,7 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; create table t2 (a varchar(200)) engine=blackhole master-bin.000001 # Query # # BEGIN master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=581 -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/words.dat' into table t2 ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE `t2` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) ;file_id=# master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; alter table t1 add b int master-bin.000001 # Query # # use `test`; alter table t1 drop b diff --git a/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result b/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result index 4d24b2409b9..8bbf1bccc63 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result +++ b/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result @@ -1,21 +1,43 @@ -drop database if exists `drop-temp+table-test`; -reset master; -create database `drop-temp+table-test`; -use `drop-temp+table-test`; -create temporary table shortn1 (a int); -create temporary table `table:name` (a int); -create temporary table shortn2 (a int); -select get_lock("a",10); -get_lock("a",10) +DROP DATABASE IF EXISTS `drop-temp+table-test`; +RESET MASTER; +CREATE DATABASE `drop-temp+table-test`; +USE `drop-temp+table-test`; +CREATE TEMPORARY TABLE shortn1 (a INT); +CREATE TEMPORARY TABLE `table:name` (a INT); +CREATE TEMPORARY TABLE shortn2 (a INT); +CREATE TEMPORARY TABLE tmp(c1 int); +CREATE TEMPORARY TABLE tmp1(c1 int); +CREATE TEMPORARY TABLE tmp2(c1 int); +CREATE TEMPORARY TABLE tmp3(c1 int); +CREATE TABLE t(c1 int); +DROP TEMPORARY TABLE IF EXISTS tmp; +DROP TEMPORARY TABLE IF EXISTS tmp; +DROP TEMPORARY TABLE IF EXISTS tmp, tmp1; +DROP TEMPORARY TABLE tmp3; +DROP TABLE IF EXISTS tmp2, t; +DROP TABLE IF EXISTS tmp2, t; +SELECT GET_LOCK("a",10); +GET_LOCK("a",10) 1 -select get_lock("a",10); -get_lock("a",10) +SELECT GET_LOCK("a",10); +GET_LOCK("a",10) 1 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # create database `drop-temp+table-test` -master-bin.000001 # Query # # use `drop-temp+table-test`; create temporary table shortn1 (a int) -master-bin.000001 # Query # # use `drop-temp+table-test`; create temporary table `table:name` (a int) -master-bin.000001 # Query # # use `drop-temp+table-test`; create temporary table shortn2 (a int) +master-bin.000001 # Query # # CREATE DATABASE `drop-temp+table-test` +master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE shortn1 (a INT) +master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE `table:name` (a INT) +master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE shortn2 (a INT) +master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE tmp(c1 int) +master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE tmp1(c1 int) +master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE tmp2(c1 int) +master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE tmp3(c1 int) +master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TABLE t(c1 int) +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS tmp +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS tmp +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS tmp, tmp1 +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE tmp3 +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS tmp2, t +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS tmp2, t master-bin.000001 # Query # # use `drop-temp+table-test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `shortn2`,`table:name`,`shortn1` -drop database `drop-temp+table-test`; +DROP DATABASE `drop-temp+table-test`; diff --git a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result index c15374dc1c6..06c57fba2e7 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result @@ -625,7 +625,7 @@ master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Intvar # # INSERT_ID=10 -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2) ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a, @b) SET b=((@b) + `bug27417`(2)) ;file_id=# master-bin.000001 # Query # # ROLLBACK /* the output must denote there is the query */; drop trigger trg_del_t2; @@ -863,7 +863,7 @@ master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2) ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a, @b) SET b=((@b) + `bug27417`(2)) ;file_id=# master-bin.000001 # Query # # ROLLBACK drop trigger trg_del_t2; drop table t1,t2,t3,t4,t5; diff --git a/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_db_filter.test b/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_db_filter.test index 0422c204270..6682d84d9c9 100644 --- a/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_db_filter.test +++ b/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_db_filter.test @@ -112,17 +112,22 @@ while($i) # remove unecessary files -- remove_file $outfile.1 -- remove_file $outfile.2 - + + # + # The two tests are canceled since we introduced the patch of bug#46998, + # which will make mydsqlbinlog output the 'BEGIN', 'COMMIT' and 'ROLLBACK' + # in regardless of database filtering + # # assertion: events for database test are filtered - if (`SELECT INSTR(@b42941_output.1, 'test')`) - { - -- echo **** ERROR **** Database name 'test' FOUND in mysqlbinlog output ($flags $outfile.1). - } - - if (`SELECT INSTR(@b42941_output.2, 'test')`) - { - -- echo **** ERROR **** Database name 'test' FOUND in mysqlbinlog output ($flags $outfile.2). - } + #if (`SELECT INSTR(@b42941_output.1, 'test')`) + #{ + #-- echo **** ERROR **** Database name 'test' FOUND in mysqlbinlog output ($flags $outfile.1). + #} + + #if (`SELECT INSTR(@b42941_output.2, 'test')`) + #{ + #-- echo **** ERROR **** Database name 'test' FOUND in mysqlbinlog output ($flags $outfile.2). + #} # assertion: events for database b42941 are not filtered if (!`SELECT INSTR(@b42941_output.1, 'b42941')`) diff --git a/mysql-test/suite/federated/federated_debug-master.opt b/mysql-test/suite/federated/federated_debug-master.opt new file mode 100644 index 00000000000..ad9ba4795af --- /dev/null +++ b/mysql-test/suite/federated/federated_debug-master.opt @@ -0,0 +1 @@ +--loose-debug=d,simulate_detached_thread_refresh diff --git a/mysql-test/suite/federated/federated_debug.result b/mysql-test/suite/federated/federated_debug.result new file mode 100644 index 00000000000..81a2558d425 --- /dev/null +++ b/mysql-test/suite/federated/federated_debug.result @@ -0,0 +1,28 @@ +CREATE DATABASE federated; +CREATE DATABASE federated; +# +# Bug#47525: MySQL crashed (Federated) +# +# Switch to slave +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +# Switch to master +CREATE TABLE t1(a INT) ENGINE=FEDERATED +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/test/t1'; +SELECT * FROM t1; +a +1 +# Start a asynchronous reload +# Wait for tables to be closed +# Ensure that the server didn't crash +SELECT * FROM t1; +a +1 +# Drop tables on master and slave +DROP TABLE t1; +DROP TABLE t1; +# Federated cleanup +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE federated; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE federated; diff --git a/mysql-test/suite/federated/federated_debug.test b/mysql-test/suite/federated/federated_debug.test new file mode 100644 index 00000000000..4152d2975b3 --- /dev/null +++ b/mysql-test/suite/federated/federated_debug.test @@ -0,0 +1,39 @@ +--source include/have_debug.inc +--source federated.inc + +--echo # +--echo # Bug#47525: MySQL crashed (Federated) +--echo # + +connection slave; +--echo # Switch to slave +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); + +connection master; +--echo # Switch to master +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE t1(a INT) ENGINE=FEDERATED + CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/t1'; + +SELECT * FROM t1; + +--echo # Start a asynchronous reload +--exec $MYSQLADMIN --no-defaults -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= refresh 2>&1 + +--echo # Wait for tables to be closed +let $show_statement= SHOW STATUS LIKE 'Open_tables'; +let $field= Value; +let $condition= = '0'; +--source include/wait_show_condition.inc + +--echo # Ensure that the server didn't crash +SELECT * FROM t1; +--echo # Drop tables on master and slave +DROP TABLE t1; +connection slave; +DROP TABLE t1; + +connection default; +--echo # Federated cleanup +source federated_cleanup.inc; diff --git a/mysql-test/suite/federated/my.cnf b/mysql-test/suite/federated/my.cnf index 3e84845b945..82600949712 100644 --- a/mysql-test/suite/federated/my.cnf +++ b/mysql-test/suite/federated/my.cnf @@ -9,4 +9,7 @@ log-bin= master-bin [ENV] MASTER_MYPORT= @mysqld.1.port +MASTER_MYSOCK= @mysqld.1.socket + SLAVE_MYPORT= @mysqld.2.port +SLAVE_MYSOCK= @mysqld.2.socket diff --git a/mysql-test/suite/innodb/t/disabled.def b/mysql-test/suite/innodb/t/disabled.def index 195fd1c0758..9a188ac8a48 100644 --- a/mysql-test/suite/innodb/t/disabled.def +++ b/mysql-test/suite/innodb/t/disabled.def @@ -1 +1,2 @@ -innodb-index : Bug#47563 2009-06-11 svoj InnoDB: Error: table `test`.`t1#1` already exists in InnoDB internal +innodb-index : Bug#47563 2009-06-11 svoj InnoDB: Error: table `test`.`t1#1` already exists in InnoDB internal +innodb_information_schema : Bug#47808 joro : innodb_information_schema.test fails when run under valgrind diff --git a/mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result b/mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result new file mode 100644 index 00000000000..b2cc92491c3 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result @@ -0,0 +1,1041 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +# Test case1: INVOKES A TRIGGER with after insert action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 after insert on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 after insert on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with after insert action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case2: INVOKES A TRIGGER with before insert action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 before insert on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 before insert on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with before insert action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case3: INVOKES A TRIGGER with after update action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 after update on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 after update on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with after update action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case4: INVOKES A TRIGGER with before update action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 before update on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 before update on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with before update action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case5: INVOKES A TRIGGER with after delete action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 after delete on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 after delete on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with after delete action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case6: INVOKES A TRIGGER with before delete action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 before delete on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 before delete on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with before delete action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with after insert action +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER +BEGIN +INSERT INTO t2(a) values(2),(3); +INSERT INTO t2(a) values(2),(3); +RETURN 1; +END | +create trigger tr11 after insert on t2 for each row begin +insert into t3(a) values(new.a); +insert into t3(a) values(new.a); +end | +begin; +insert into t1(a) values(f1_two_inserts_trigger()); +insert into t2(a) values(4),(5); +commit; +insert into t1(a) values(f1_two_inserts_trigger()); +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'CALLS A FUNCTION which INVOKES A TRIGGER with after insert action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t3 and slave:test.t3 +drop table t1; +drop table t2; +drop table t3; +drop function f1_two_inserts_trigger; +# Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with before insert action +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER +BEGIN +INSERT INTO t2(a) values(2),(3); +INSERT INTO t2(a) values(2),(3); +RETURN 1; +END | +create trigger tr11 before insert on t2 for each row begin +insert into t3(a) values(new.a); +insert into t3(a) values(new.a); +end | +begin; +insert into t1(a) values(f1_two_inserts_trigger()); +insert into t2(a) values(4),(5); +commit; +insert into t1(a) values(f1_two_inserts_trigger()); +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'CALLS A FUNCTION which INVOKES A TRIGGER with before insert action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t3 and slave:test.t3 +drop table t1; +drop table t2; +drop table t3; +drop function f1_two_inserts_trigger; +# Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with after insert action +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr16 after insert on t1 for each row insert into t3(a) values(new.c1); +create trigger tr17 after insert on t2 for each row insert into t3(a) values(new.c2); +begin; +INSERT INTO t1(c1) VALUES (11), (12); +INSERT INTO t2(c2) VALUES (13), (14); +CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2; +INSERT INTO v16(c1) VALUES (15),(16); +INSERT INTO v16(c2) VALUES (17),(18); +INSERT INTO v16(c1) VALUES (19),(20); +INSERT INTO v16(c2) VALUES (21),(22); +INSERT INTO v16(c1) VALUES (23), (24); +INSERT INTO v16(c1) VALUES (25), (26); +commit; +#Test if the results are consistent on master and slave +#for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS' +Comparing tables master:test.t3 and slave:test.t3 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP VIEW v16; +# Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with before insert action +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr16 before insert on t1 for each row insert into t3(a) values(new.c1); +create trigger tr17 before insert on t2 for each row insert into t3(a) values(new.c2); +begin; +INSERT INTO t1(c1) VALUES (11), (12); +INSERT INTO t2(c2) VALUES (13), (14); +CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2; +INSERT INTO v16(c1) VALUES (15),(16); +INSERT INTO v16(c2) VALUES (17),(18); +INSERT INTO v16(c1) VALUES (19),(20); +INSERT INTO v16(c2) VALUES (21),(22); +INSERT INTO v16(c1) VALUES (23), (24); +INSERT INTO v16(c1) VALUES (25), (26); +commit; +#Test if the results are consistent on master and slave +#for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS' +Comparing tables master:test.t3 and slave:test.t3 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP VIEW v16; +# Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_two_inserts() RETURNS INTEGER +BEGIN +INSERT INTO t2(a) values(2); +INSERT INTO t2(a) values(2); +RETURN 1; +END// +begin; +insert into t1(a) values(f1_two_inserts()); +insert into t2(a) values(4),(5); +commit; +insert into t1(a) values(f1_two_inserts()); +commit; +#Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master +select * from t2 ORDER BY i1; +i1 a +1 2 +2 2 +3 4 +4 5 +5 2 +6 2 +#Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave +select * from t2 ORDER BY i1; +i1 a +1 2 +2 2 +3 4 +4 5 +5 2 +6 2 +drop table t1; +drop table t2; +drop function f1_two_inserts; +# Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_two_updates() RETURNS INTEGER +BEGIN +update t2 set a = a + 5 where b = 1; +update t2 set a = a + 5 where b = 2; +update t2 set a = a + 5 where b = 3; +update t2 set a = a + 5 where b = 4; +RETURN 1; +END// +insert into t2(a,b) values(1,1); +insert into t2(a,b) values(2,2); +insert into t2(a,b) values(3,3); +insert into t2(a,b) values(4,4); +insert into t1(a) values(f1_two_updates()); +begin; +insert into t1(a) values(f1_two_updates()); +commit; +#Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master +select * from t2 ORDER BY i1; +i1 a b +1 11 1 +2 12 2 +3 13 3 +4 14 4 +#Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave +select * from t2 ORDER BY i1; +i1 a b +1 11 1 +2 12 2 +3 13 3 +4 14 4 +drop table t1; +drop table t2; +drop function f1_two_updates; +# Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT +create table t1(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +begin; +insert into t1(a,b) values(1,1),(2,2); +insert into t2(a,b) values(1,1),(2,2); +update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b; +insert into t1(a,b) values(3,3); +insert into t2(a,b) values(3,3); +commit; +# To verify if it works fine when these statements are not be marked as unsafe +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=1 +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,2) +master-bin.000001 # Intvar # # INSERT_ID=1 +master-bin.000001 # Query # # use `test`; insert into t2(a,b) values(1,1),(2,2) +master-bin.000001 # Query # # use `test`; update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(3,3) +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a,b) values(3,3) +master-bin.000001 # Xid # # COMMIT /* XID */ +#Test if the results are consistent on master and slave +#for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT' +Comparing tables master:test.t1 and slave:test.t1 +Comparing tables master:test.t2 and slave:test.t2 +drop table t1; +drop table t2; +# Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +begin; +INSERT INTO t1(c1) VALUES (11), (12); +INSERT INTO t2(c2) VALUES (13), (14); +CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2; +INSERT INTO v15(c1) VALUES (15),(16); +INSERT INTO v15(c2) VALUES (17),(18); +INSERT INTO v15(c1) VALUES (19),(20); +INSERT INTO v15(c2) VALUES (21),(22); +INSERT INTO v15(c1) VALUES (23), (24); +INSERT INTO v15(c2) VALUES (25), (26); +commit; +# To verify if it works fine when these statements are not be marked as unsafe +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=1 +master-bin.000001 # Query # # use `test`; INSERT INTO t1(c1) VALUES (11), (12) +master-bin.000001 # Intvar # # INSERT_ID=1 +master-bin.000001 # Query # # use `test`; INSERT INTO t2(c2) VALUES (13), (14) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v15` AS SELECT c1, c2 FROM t1, t2 +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (15),(16) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (17),(18) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (19),(20) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (21),(22) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=7 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (23), (24) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=7 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (25), (26) +master-bin.000001 # Xid # # COMMIT /* XID */ +#Test if the results are consistent on master and slave +#for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES' +Comparing tables master:test.t1 and slave:test.t1 +Comparing tables master:test.t2 and slave:test.t2 +drop table t1; +drop table t2; +drop view v15; diff --git a/mysql-test/suite/rpl/r/rpl_get_master_version_and_clock.result b/mysql-test/suite/rpl/r/rpl_get_master_version_and_clock.result index 99a0fd21f66..ae3554a5420 100644 --- a/mysql-test/suite/rpl/r/rpl_get_master_version_and_clock.result +++ b/mysql-test/suite/rpl/r/rpl_get_master_version_and_clock.result @@ -18,7 +18,8 @@ start slave; SELECT RELEASE_LOCK("debug_lock.before_get_UNIX_TIMESTAMP"); RELEASE_LOCK("debug_lock.before_get_UNIX_TIMESTAMP") 1 -Slave_IO_Errno= 2013 +Check network error happened here +NETWORK ERROR SELECT IS_FREE_LOCK("debug_lock.before_get_SERVER_ID"); IS_FREE_LOCK("debug_lock.before_get_SERVER_ID") 1 @@ -31,7 +32,8 @@ start slave; SELECT RELEASE_LOCK("debug_lock.before_get_SERVER_ID"); RELEASE_LOCK("debug_lock.before_get_SERVER_ID") 1 -Slave_IO_Errno= 2013 +Check network error happened here +NETWORK ERROR set global debug= ''; reset master; include/stop_slave.inc diff --git a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result index 81c486cb43c..3a1c2b68b01 100644 --- a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result +++ b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result @@ -885,7 +885,7 @@ master-bin.000001 # Query 1 # use `test_rpl`; DELETE FROM t2 master-bin.000001 # Xid 1 # # master-bin.000001 # Query 1 # BEGIN master-bin.000001 # Begin_load_query 1 # ;file_id=#;block_len=# -master-bin.000001 # Execute_load_query 1 # use `test_rpl`; LOAD DATA INFILE 'MYSQLTEST_VARDIR/std_data/rpl_mixed.dat' INTO TABLE t1 FIELDS TERMINATED BY '|' ;file_id=# +master-bin.000001 # Execute_load_query 1 # use `test_rpl`; LOAD DATA INFILE 'MYSQLTEST_VARDIR/std_data/rpl_mixed.dat' INTO TABLE `t1` FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a, b) ;file_id=# master-bin.000001 # Xid 1 # # master-bin.000001 # Query 1 # BEGIN master-bin.000001 # Query 1 # use `test_rpl`; DELETE FROM t1 diff --git a/mysql-test/suite/rpl/r/rpl_loaddata.result b/mysql-test/suite/rpl/r/rpl_loaddata.result index d7a02bc84a5..0653936f0ec 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddata.result +++ b/mysql-test/suite/rpl/r/rpl_loaddata.result @@ -36,7 +36,7 @@ set global sql_slave_skip_counter=1; start slave; show slave status; Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error -# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 1797 # # master-bin.000001 Yes Yes # 0 0 1797 # None 0 No # No 0 0 +# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 2009 # # master-bin.000001 Yes Yes # 0 0 2009 # None 0 No # No 0 0 set sql_log_bin=0; delete from t1; set sql_log_bin=1; @@ -46,7 +46,7 @@ change master to master_user='test'; change master to master_user='root'; show slave status; Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error -# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 1832 # # master-bin.000001 No No # 0 0 1832 # None 0 No # No 0 0 +# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 2044 # # master-bin.000001 No No # 0 0 2044 # None 0 No # No 0 0 set global sql_slave_skip_counter=1; start slave; set sql_log_bin=0; diff --git a/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result b/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result index 27fb8623e85..35696615b5a 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result +++ b/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result @@ -53,7 +53,7 @@ Master_User root Master_Port MASTER_PORT Connect_Retry 1 Master_Log_File master-bin.000001 -Read_Master_Log_Pos 465 +Read_Master_Log_Pos 556 Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 diff --git a/mysql-test/suite/rpl/r/rpl_loaddata_map.result b/mysql-test/suite/rpl/r/rpl_loaddata_map.result index 901f3352b44..006f84043a4 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddata_map.result +++ b/mysql-test/suite/rpl/r/rpl_loaddata_map.result @@ -20,7 +20,7 @@ master-bin.000001 # Query # # use `test`; create table t2 (id int not null prima master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Append_block # # ;file_id=#;block_len=# master-bin.000001 # Append_block # # ;file_id=#;block_len=# -master-bin.000001 # Execute_load_query # # use `test`; load data infile 'MYSQLTEST_VARDIR/tmp/bug30435_5k.txt' into table t2 ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug30435_5k.txt' INTO TABLE `t2` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (id) ;file_id=# ==== Verify results on slave ==== [on slave] select count(*) from t2 /* 5 000 */; diff --git a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result b/mysql-test/suite/rpl/r/rpl_loaddatalocal.result index 93ef33f3fc0..6dccaa3d74c 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result +++ b/mysql-test/suite/rpl/r/rpl_loaddatalocal.result @@ -54,3 +54,31 @@ a [on master] DROP TABLE t1; [on slave] + +Bug #43746: +"return wrong query string when parse 'load data infile' sql statement" + +[master] +SELECT @@SESSION.sql_mode INTO @old_mode; +SET sql_mode='ignore_space'; +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (1), (2), (3), (4); +SELECT * INTO OUTFILE 'MYSQLD_DATADIR/bug43746.sql' FROM t1; +TRUNCATE TABLE t1; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; +LOAD/* look mum, with comments in weird places! */DATA/* oh hai */LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql'/* we are */INTO/* from the internets */TABLE t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO */ TABLE t1; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO TABLE */ t1; +LOAD DATA /*!10000 LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE */ t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql'/*!10000 INTO*/TABLE t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql'/* empty */INTO TABLE t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO/* empty */TABLE t1; +LOAD/*!99999 special comments that do not expand */DATA/*!99999 code from the future */LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql'/*!99999 have flux capacitor */INTO/*!99999 will travel */TABLE t1; +SET sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER'; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; +[slave] +[master] +DROP TABLE t1; +SET SESSION sql_mode=@old_mode; +[slave] diff --git a/mysql-test/suite/rpl/r/rpl_log_pos.result b/mysql-test/suite/rpl/r/rpl_log_pos.result index 7b3ebf62959..85fa4c10eac 100644 --- a/mysql-test/suite/rpl/r/rpl_log_pos.result +++ b/mysql-test/suite/rpl/r/rpl_log_pos.result @@ -4,6 +4,7 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; +call mtr.add_suppression ("Slave I/O: Got fatal error 1236 from master when reading data from binary"); show master status; File Position Binlog_Do_DB Binlog_Ignore_DB master-bin.000001 # <Binlog_Do_DB> <Binlog_Ignore_DB> diff --git a/mysql-test/suite/rpl/r/rpl_mysql_upgrade.result b/mysql-test/suite/rpl/r/rpl_mysql_upgrade.result new file mode 100644 index 00000000000..09a9121d22c --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_mysql_upgrade.result @@ -0,0 +1,13 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +DROP DATABASE IF EXISTS `#mysql50#mysqltest-1`; +CREATE DATABASE `#mysql50#mysqltest-1`; +Master position is not changed +STOP SLAVE SQL_THREAD; +Master position has been changed +DROP DATABASE `mysqltest-1`; +DROP DATABASE `#mysql50#mysqltest-1`; diff --git a/mysql-test/suite/rpl/r/rpl_packet.result b/mysql-test/suite/rpl/r/rpl_packet.result index 4c64054e348..0a9495751fe 100644 --- a/mysql-test/suite/rpl/r/rpl_packet.result +++ b/mysql-test/suite/rpl/r/rpl_packet.result @@ -4,6 +4,8 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; +call mtr.add_suppression("Slave I/O: Got a packet bigger than 'max_allowed_packet' bytes, Error_code: 1153"); +call mtr.add_suppression("Slave I/O: Got fatal error 1236 from master when reading data from binary log:"); drop database if exists DB_NAME_OF_MAX_LENGTH_AKA_NAME_LEN_64_BYTES_____________________; create database DB_NAME_OF_MAX_LENGTH_AKA_NAME_LEN_64_BYTES_____________________; SET @@global.max_allowed_packet=1024; @@ -32,6 +34,21 @@ include/start_slave.inc CREATE TABLE `t1` (`f1` LONGTEXT) ENGINE=MyISAM; INSERT INTO `t1`(`f1`) VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2048'); Slave_IO_Running = No (expect No) +SELECT "Got a packet bigger than 'max_allowed_packet' bytes" AS Last_IO_Error; +Last_IO_Error +Got a packet bigger than 'max_allowed_packet' bytes +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +CREATE TABLE t1 (f1 int PRIMARY KEY, f2 LONGTEXT, f3 LONGTEXT) ENGINE=MyISAM; +INSERT INTO t1(f1, f2, f3) VALUES(1, REPEAT('a', @@global.max_allowed_packet), REPEAT('b', @@global.max_allowed_packet)); +Slave_IO_Running = No (expect No) +SELECT "Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'" AS Last_IO_Error; +Last_IO_Error +Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' ==== clean up ==== DROP TABLE t1; SET @@global.max_allowed_packet= 1024; diff --git a/mysql-test/suite/rpl/r/rpl_row_disabled_slave_key.result b/mysql-test/suite/rpl/r/rpl_row_disabled_slave_key.result new file mode 100644 index 00000000000..01e3dfd6508 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_disabled_slave_key.result @@ -0,0 +1,26 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +SET SQL_LOG_BIN=0; +CREATE TABLE t (a int, b int, c int, key(b)); +SET SQL_LOG_BIN=1; +CREATE TABLE t (a int, b int, c int); +INSERT INTO t VALUES (1,2,4); +INSERT INTO t VALUES (4,3,4); +DELETE FROM t; +DROP TABLE t; +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +CREATE TABLE t (a int, b int, c int, key(b)); +ALTER TABLE t DISABLE KEYS; +INSERT INTO t VALUES (1,2,4); +INSERT INTO t VALUES (4,3,4); +DELETE FROM t; +DROP TABLE t; diff --git a/mysql-test/suite/rpl/r/rpl_stm_log.result b/mysql-test/suite/rpl/r/rpl_stm_log.result index bcefc6f9d3d..d73b8990041 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_log.result +++ b/mysql-test/suite/rpl/r/rpl_stm_log.result @@ -25,7 +25,7 @@ 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 (word char(20) not null)ENGINE=MyISAM master-bin.000001 # Begin_load_query 1 # ;file_id=1;block_len=581 -master-bin.000001 # Execute_load_query 1 # use `test`; load data infile '../../std_data/words.dat' into table t1 ignore 1 lines ;file_id=1 +master-bin.000001 # Execute_load_query 1 # use `test`; LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (word) ;file_id=1 show binlog events from 106 limit 1; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # use `test`; create table t1(n int not null auto_increment primary key)ENGINE=MyISAM @@ -193,7 +193,7 @@ master-bin.000001 # Query # # use `test`; insert into t1 values (NULL) master-bin.000001 # Query # # use `test`; drop table t1 master-bin.000001 # Query # # use `test`; create table t1 (word char(20) not null)ENGINE=MyISAM master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/words.dat' into table t1 ignore 1 lines ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (word) ;file_id=# master-bin.000001 # Rotate # # master-bin.000002;pos=4 show binlog events in 'master-bin.000002'; Log_name Pos Event_type Server_id End_log_pos Info @@ -218,7 +218,7 @@ slave-bin.000001 # Query 1 # use `test`; insert into t1 values (NULL) slave-bin.000001 # Query 1 # use `test`; drop table t1 slave-bin.000001 # Query 1 # use `test`; create table t1 (word char(20) not null)ENGINE=MyISAM slave-bin.000001 # Begin_load_query 1 # ;file_id=1;block_len=581 -slave-bin.000001 # Execute_load_query 1 # use `test`; load data INFILE '../../tmp/SQL_LOAD-2-1-1.data' INTO table t1 ignore 1 lines ;file_id=1 +slave-bin.000001 # Execute_load_query 1 # use `test`; LOAD DATA INFILE '../../tmp/SQL_LOAD-2-1-1.data' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (word) ;file_id=1 slave-bin.000001 # Query 1 # use `test`; create table t3 (a int)ENGINE=MyISAM slave-bin.000001 # Rotate 2 # slave-bin.000002;pos=4 show binlog events in 'slave-bin.000002' from 4; diff --git a/mysql-test/suite/rpl/t/disabled.def b/mysql-test/suite/rpl/t/disabled.def index 85747264b6f..8cae44a3607 100644 --- a/mysql-test/suite/rpl/t/disabled.def +++ b/mysql-test/suite/rpl/t/disabled.def @@ -10,4 +10,3 @@ # ############################################################################## -rpl_cross_version : Bug#43913 2009-03-27 joro rpl_cross_version can't pass on conflicts complainig clash with --slave-load-tm diff --git a/mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test b/mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test new file mode 100644 index 00000000000..f38d2151ab3 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test @@ -0,0 +1,214 @@ +# +# Bug45677 +# This test verifies the following two properties: +# P1) insert/update in an autoinc column causes statement to +# be logged in row format if binlog_format=mixed. +# P2) if binlog_format=mixed, and a trigger or function contains +# two or more inserts/updates in a table that has an autoinc +# column, then the slave should not go out of sync, even if +# there are concurrent transactions. +# +# Property (P1) is tested by executing an insert and an update on +# a table that has an autoinc column, and verifying that these +# statements result in row events in the binlog. +# Property (P2) is tested by setting up the test scenario and +# verifying that the tables are identical on master and slave. +# + +source include/have_binlog_format_mixed.inc; +source include/have_innodb.inc; +source include/master-slave.inc; + +--echo # Test case1: INVOKES A TRIGGER with after insert action +let $trigger_action = after insert; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case2: INVOKES A TRIGGER with before insert action +let $trigger_action = before insert; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case3: INVOKES A TRIGGER with after update action +let $trigger_action = after update; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case4: INVOKES A TRIGGER with before update action +let $trigger_action = before update; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case5: INVOKES A TRIGGER with after delete action +let $trigger_action = after delete; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case6: INVOKES A TRIGGER with before delete action +let $trigger_action = before delete; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with after insert action +let $insert_action = after insert; +source extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test; + +--echo # Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with before insert action +let $insert_action = before insert; +source extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test; + +--echo # Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with after insert action +let $insert_action = after insert; +source extra/rpl_tests/rpl_auto_increment_insert_view.test; + +--echo # Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with before insert action +let $insert_action = before insert; +source extra/rpl_tests/rpl_auto_increment_insert_view.test; + +--echo # Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN +connection master; +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +delimiter //; +CREATE FUNCTION f1_two_inserts() RETURNS INTEGER +BEGIN + INSERT INTO t2(a) values(2); + INSERT INTO t2(a) values(2); + RETURN 1; +END// +delimiter ;// +begin; +insert into t1(a) values(f1_two_inserts()); + +connection master1; +#The default autocommit is set to 1, so the statement is auto committed +insert into t2(a) values(4),(5); + +connection master; +commit; +insert into t1(a) values(f1_two_inserts()); +commit; + +connection master; +--echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master +select * from t2 ORDER BY i1; + +sync_slave_with_master; +connection slave; +--echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave +select * from t2 ORDER BY i1; + +connection master; +drop table t1; +drop table t2; +drop function f1_two_inserts; +sync_slave_with_master; + +--echo # Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN +connection master; +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +delimiter //; +CREATE FUNCTION f1_two_updates() RETURNS INTEGER +BEGIN + update t2 set a = a + 5 where b = 1; + update t2 set a = a + 5 where b = 2; + update t2 set a = a + 5 where b = 3; + update t2 set a = a + 5 where b = 4; + RETURN 1; +END// +delimiter ;// + +connection master1; +#The default autocommit is set to 1, so the statement is auto committed +insert into t2(a,b) values(1,1); +insert into t2(a,b) values(2,2); +insert into t2(a,b) values(3,3); +insert into t2(a,b) values(4,4); +insert into t1(a) values(f1_two_updates()); + +connection master; +begin; +insert into t1(a) values(f1_two_updates()); +commit; + +connection master; +--echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master +select * from t2 ORDER BY i1; + +sync_slave_with_master; +connection slave; +--echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave +select * from t2 ORDER BY i1; + +connection master; +drop table t1; +drop table t2; +drop function f1_two_updates; +sync_slave_with_master; + +--echo # Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT +connection master; +create table t1(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +begin; +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +insert into t1(a,b) values(1,1),(2,2); +insert into t2(a,b) values(1,1),(2,2); +update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b; +insert into t1(a,b) values(3,3); +insert into t2(a,b) values(3,3); +commit; +--echo # To verify if it works fine when these statements are not be marked as unsafe +source include/show_binlog_events.inc; + +sync_slave_with_master; +--echo #Test if the results are consistent on master and slave +--echo #for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT' +let $diff_table_1=master:test.t1; +let $diff_table_2=slave:test.t1; +source include/diff_tables.inc; +let $diff_table_1=master:test.t2; +let $diff_table_2=slave:test.t2; +source include/diff_tables.inc; + +connection master; +drop table t1; +drop table t2; +sync_slave_with_master; + +--echo # Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES +connection master; +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +begin; +INSERT INTO t1(c1) VALUES (11), (12); +INSERT INTO t2(c2) VALUES (13), (14); + +CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2; + +INSERT INTO v15(c1) VALUES (15),(16); +INSERT INTO v15(c2) VALUES (17),(18); + +connection master1; +INSERT INTO v15(c1) VALUES (19),(20); +INSERT INTO v15(c2) VALUES (21),(22); + +connection master; +INSERT INTO v15(c1) VALUES (23), (24); +INSERT INTO v15(c2) VALUES (25), (26); +commit; +--echo # To verify if it works fine when these statements are not be marked as unsafe +source include/show_binlog_events.inc; + +sync_slave_with_master; +--echo #Test if the results are consistent on master and slave +--echo #for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES' +let $diff_table_1=master:test.t1; +let $diff_table_2=slave:test.t1; +source include/diff_tables.inc; +let $diff_table_1=master:test.t2; +let $diff_table_2=slave:test.t2; +source include/diff_tables.inc; + +connection master; +drop table t1; +drop table t2; +drop view v15; +sync_slave_with_master; + diff --git a/mysql-test/suite/rpl/t/rpl_loaddatalocal.test b/mysql-test/suite/rpl/t/rpl_loaddatalocal.test index 23c802ab3de..a93a82d6d9f 100644 --- a/mysql-test/suite/rpl/t/rpl_loaddatalocal.test +++ b/mysql-test/suite/rpl/t/rpl_loaddatalocal.test @@ -98,3 +98,73 @@ DROP TABLE t1; --echo [on slave] sync_slave_with_master; +--echo +--echo Bug #43746: +--echo "return wrong query string when parse 'load data infile' sql statement" +--echo + +--echo [master] +connection master; +let $MYSQLD_DATADIR= `select @@datadir`; +SELECT @@SESSION.sql_mode INTO @old_mode; + +SET sql_mode='ignore_space'; + +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (1), (2), (3), (4); + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval SELECT * INTO OUTFILE '$MYSQLD_DATADIR/bug43746.sql' FROM t1; +TRUNCATE TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD/* look mum, with comments in weird places! */DATA/* oh hai */LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql'/* we are */INTO/* from the internets */TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO */ TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO TABLE */ t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA /*!10000 LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE */ t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql'/*!10000 INTO*/TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql'/* empty */INTO TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO/* empty */TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD/*!99999 special comments that do not expand */DATA/*!99999 code from the future */LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql'/*!99999 have flux capacitor */INTO/*!99999 will travel */TABLE t1; + +SET sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER'; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; + +--echo [slave] +sync_slave_with_master; + +# cleanup + +--remove_file $MYSQLD_DATADIR/bug43746.sql + +--echo [master] +connection master; +DROP TABLE t1; +SET SESSION sql_mode=@old_mode; + +--echo [slave] +sync_slave_with_master; + +connection master; diff --git a/mysql-test/suite/rpl/t/rpl_log_pos.test b/mysql-test/suite/rpl/t/rpl_log_pos.test index 5e8390f97ed..48effa00b64 100644 --- a/mysql-test/suite/rpl/t/rpl_log_pos.test +++ b/mysql-test/suite/rpl/t/rpl_log_pos.test @@ -11,6 +11,7 @@ # Passes with rbr no problem, removed statement include [jbm] source include/master-slave.inc; +call mtr.add_suppression ("Slave I/O: Got fatal error 1236 from master when reading data from binary"); source include/show_master_status.inc; sync_slave_with_master; source include/stop_slave.inc; diff --git a/mysql-test/suite/rpl/t/rpl_mysql_upgrade.test b/mysql-test/suite/rpl/t/rpl_mysql_upgrade.test new file mode 100644 index 00000000000..bf5c6d2b921 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mysql_upgrade.test @@ -0,0 +1,56 @@ +############################################################################# +# BUG#43579 mysql_upgrade tries to alter log tables on replicated database +# Master and slave should be upgraded separately. All statements executed by +# mysql_upgrade will not be binlogged. --write-binlog and --skip-write-binlog +# options are added into mysql_upgrade. These options control whether sql +# statements are binlogged or not. +############################################################################# +--source include/master-slave.inc + +# Only run test if "mysql_upgrade" is found +--source include/have_mysql_upgrade.inc + +connection master; +--disable_warnings +DROP DATABASE IF EXISTS `#mysql50#mysqltest-1`; +CREATE DATABASE `#mysql50#mysqltest-1`; +--enable_warnings +sync_slave_with_master; + +connection master; +let $before_position= query_get_value(SHOW MASTER STATUS, Position, 1); + +#With '--force' option, mysql_upgrade always executes all sql statements for upgrading. +#--skip-write-binlog option disables binlog. +--exec $MYSQL_UPGRADE --skip-write-binlog --skip-verbose --force --user=root > $MYSQLTEST_VARDIR/log/mysql_upgrade.log 2>&1 +sync_slave_with_master; + +connection master; +let $after_position= query_get_value(SHOW MASTER STATUS, Position, 1); + +if (`SELECT '$before_position'='$after_position'`) +{ + echo Master position is not changed; +} + +#Some log events of the mysql_upgrade's will cause errors on slave. +connection slave; +STOP SLAVE SQL_THREAD; +source include/wait_for_slave_sql_to_stop.inc; + +connection master; +#With '--force' option, mysql_upgrade always executes all sql statements for upgrading. +--exec $MYSQL_UPGRADE --skip-verbose --force --user=root > $MYSQLTEST_VARDIR/log/mysql_upgrade.log 2>&1 + +connection master; +let $after_file= query_get_value(SHOW MASTER STATUS, File, 1); +let $after_position= query_get_value(SHOW MASTER STATUS, Position, 1); + +if (!`SELECT '$before_position'='$after_position'`) +{ + echo Master position has been changed; +} + +DROP DATABASE `mysqltest-1`; +connection slave; +DROP DATABASE `#mysql50#mysqltest-1`; diff --git a/mysql-test/suite/rpl/t/rpl_packet.test b/mysql-test/suite/rpl/t/rpl_packet.test index 79cb2d9d735..bfc144c759b 100644 --- a/mysql-test/suite/rpl/t/rpl_packet.test +++ b/mysql-test/suite/rpl/t/rpl_packet.test @@ -5,6 +5,9 @@ # max-out size db name source include/master-slave.inc; +source include/have_binlog_format_row.inc; +call mtr.add_suppression("Slave I/O: Got a packet bigger than 'max_allowed_packet' bytes, Error_code: 1153"); +call mtr.add_suppression("Slave I/O: Got fatal error 1236 from master when reading data from binary log:"); let $db= DB_NAME_OF_MAX_LENGTH_AKA_NAME_LEN_64_BYTES_____________________; disable_warnings; @@ -86,6 +89,35 @@ connection slave; --source include/wait_for_slave_io_to_stop.inc let $slave_io_running= query_get_value(SHOW SLAVE STATUS, Slave_IO_Running, 1); --echo Slave_IO_Running = $slave_io_running (expect No) +# +# Bug#42914: The slave I/O thread must stop after trying to read the above +# event, However there is no Last_IO_Error report. +# +let $last_io_error= query_get_value(SHOW SLAVE STATUS, Last_IO_Error, 1); +eval SELECT "$last_io_error" AS Last_IO_Error; + +# +# Bug#42914: On the master, if a binary log event is larger than +# max_allowed_packet, the error message ER_MASTER_FATAL_ERROR_READING_BINLOG +# is sent to a slave when it requests a dump from the master, thus leading the +# I/O thread to stop. However, there is no Last_IO_Error reported. +# +source include/master-slave-reset.inc; +connection master; +CREATE TABLE t1 (f1 int PRIMARY KEY, f2 LONGTEXT, f3 LONGTEXT) ENGINE=MyISAM; +sync_slave_with_master; + +connection master; +INSERT INTO t1(f1, f2, f3) VALUES(1, REPEAT('a', @@global.max_allowed_packet), REPEAT('b', @@global.max_allowed_packet)); + +connection slave; +# The slave I/O thread must stop after receiving +# ER_MASTER_FATAL_ERROR_READING_BINLOG error message from master. +--source include/wait_for_slave_io_to_stop.inc +let $slave_io_running= query_get_value(SHOW SLAVE STATUS, Slave_IO_Running, 1); +--echo Slave_IO_Running = $slave_io_running (expect No) +let $last_io_error= query_get_value(SHOW SLAVE STATUS, Last_IO_Error, 1); +eval SELECT "$last_io_error" AS Last_IO_Error; --echo ==== clean up ==== connection master; diff --git a/mysql-test/suite/rpl/t/rpl_row_disabled_slave_key.test b/mysql-test/suite/rpl/t/rpl_row_disabled_slave_key.test new file mode 100644 index 00000000000..1d7e134f4f4 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_disabled_slave_key.test @@ -0,0 +1,73 @@ +# BUG#47312: RBR: Disabling key on slave breaks replication: +# HA_ERR_WRONG_INDEX +# +# Description +# =========== +# +# This test case checks whether disabling a key on a slave breaks +# replication or not. +# +# Case #1, shows that while not using ALTER TABLE... DISABLE KEYS and +# the slave has no key defined while the master has one, replication +# won't break. +# +# Case #2, shows that before patch for BUG#47312, if defining key on +# slave table, and later disable it, replication would break. This +# has been fixed. +# + +-- source include/master-slave.inc +-- source include/have_binlog_format_row.inc + +# +# Case #1: master has key, but slave has not. +# Replication does not break. +# + +SET SQL_LOG_BIN=0; +CREATE TABLE t (a int, b int, c int, key(b)); +SET SQL_LOG_BIN=1; + +-- connection slave + +CREATE TABLE t (a int, b int, c int); + +-- connection master + +INSERT INTO t VALUES (1,2,4); +INSERT INTO t VALUES (4,3,4); +DELETE FROM t; + +-- sync_slave_with_master + +-- connection master +DROP TABLE t; + +-- sync_slave_with_master + +# +# Case #2: master has key, slave also has one, +# but it gets disabled sometime. +# Replication does not break anymore. +# +-- source include/master-slave-reset.inc +-- connection master + +CREATE TABLE t (a int, b int, c int, key(b)); + +-- sync_slave_with_master + +ALTER TABLE t DISABLE KEYS; + +-- connection master + +INSERT INTO t VALUES (1,2,4); +INSERT INTO t VALUES (4,3,4); +DELETE FROM t; + +-- sync_slave_with_master + +-- connection master +DROP TABLE t; + +-- sync_slave_with_master diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index ae48d5a8736..17549745203 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1000,4 +1000,50 @@ ALTER TABLE t1 MODIFY b ENUM('a', 'z', 'b', 'c') NOT NULL; SELECT * FROM t1; DROP TABLE t1; +--echo # +--echo # Bug#45567: Fast ALTER TABLE broken for enum and set +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a ENUM('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +--enable_info +--echo # No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2'); +--echo # No copy: Add new enumeration to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3'); +--echo # Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5'); +--echo # Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx'); +--echo # Copy: Add new enumeration +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx'); +--echo # No copy: Add new enumerations to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6'); +--disable_info +DROP TABLE t1; + +CREATE TABLE t1 (a SET('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +--enable_info +--echo # No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2'); +--echo # No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3'); +--echo # Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5'); +--echo # Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx'); +--echo # Copy: Add new member +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx'); +--echo # No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6'); +--echo # Copy: Numerical incrase (pack lenght) +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10'); +--disable_info +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/bug46760-master.opt b/mysql-test/t/bug46760-master.opt new file mode 100644 index 00000000000..f830d135149 --- /dev/null +++ b/mysql-test/t/bug46760-master.opt @@ -0,0 +1,2 @@ +--innodb-lock-wait-timeout=2 +--innodb-file-per-table diff --git a/mysql-test/t/bug46760.test b/mysql-test/t/bug46760.test new file mode 100644 index 00000000000..f55edbbfa42 --- /dev/null +++ b/mysql-test/t/bug46760.test @@ -0,0 +1,38 @@ +-- source include/have_innodb.inc + +--echo # +--echo # Bug#46760: Fast ALTER TABLE no longer works for InnoDB +--echo # + +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); + +--echo # By using --enable_info and verifying that number of affected +--echo # rows is 0 we check that this ALTER TABLE is really carried +--echo # out as "fast/online" operation, i.e. without full-blown data +--echo # copying. +--echo # +--echo # I.e. info for the below statement should normally look like: +--echo # +--echo # affected rows: 0 +--echo # info: Records: 0 Duplicates: 0 Warnings: 0 + +--enable_info +ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 10; +--disable_info +SHOW CREATE TABLE t1; + +DROP TABLE t1; + +--echo # +--echo # MySQL Bug#39200: optimize table does not recognize +--echo # ROW_FORMAT=COMPRESSED +--echo # + +CREATE TABLE t1 (a INT) ROW_FORMAT=compressed; +SHOW CREATE TABLE t1; +OPTIMIZE TABLE t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/debug_sync.test b/mysql-test/t/debug_sync.test new file mode 100644 index 00000000000..514e471b603 --- /dev/null +++ b/mysql-test/t/debug_sync.test @@ -0,0 +1,420 @@ +###################### t/debug_sync.test ############################### +# # +# Testing of the Debug Sync Facility. # +# # +# There is important documentation within sql/debug_sync.cc # +# # +# Used objects in this test case: # +# p0 - synchronization point 0. Non-existent dummy sync point. # +# s1 - signal 1. # +# s2 - signal 2. # +# # +# Creation: # +# 2008-02-18 istruewing # +# # +######################################################################## + +# +# We need the Debug Sync Facility. +# +--source include/have_debug_sync.inc + +# +# We are checking privileges, which the embedded server cannot do. +# +--source include/not_embedded.inc + +# +# Preparative cleanup. +# +--disable_warnings +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Show the special system variable. +# It shows ON or OFF depending on the command line option --debug-sync. +# The test case assumes it is ON (command line option present). +# +SHOW VARIABLES LIKE 'DEBUG_SYNC'; + +# +# Syntax. Valid forms. +# +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 EXECUTE 2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 SIGNAL s1 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 EXECUTE 2'; +SET DEBUG_SYNC='p0 SIGNAL s1 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 EXECUTE 2'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 CLEAR'; +SET DEBUG_SYNC='p0 TEST'; +SET DEBUG_SYNC='RESET'; + +# +# Syntax. Valid forms. Lower case. +# +set debug_sync='p0 signal s1 wait_for s2 timeout 6 execute 2 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2 timeout 6 execute 2'; +set debug_sync='p0 signal s1 wait_for s2 timeout 6 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2 timeout 6'; +set debug_sync='p0 signal s1 wait_for s2 execute 2 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2 execute 2'; +set debug_sync='p0 signal s1 wait_for s2 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2'; +set debug_sync='p0 signal s1 execute 2 hit_limit 3'; +set debug_sync='p0 signal s1 execute 2'; +set debug_sync='p0 signal s1 hit_limit 3'; +set debug_sync='p0 signal s1'; +set debug_sync='p0 wait_for s2 timeout 6 execute 2 hit_limit 3'; +set debug_sync='p0 wait_for s2 timeout 6 execute 2'; +set debug_sync='p0 wait_for s2 timeout 6 hit_limit 3'; +set debug_sync='p0 wait_for s2 timeout 6'; +set debug_sync='p0 wait_for s2 execute 2 hit_limit 3'; +set debug_sync='p0 wait_for s2 execute 2'; +set debug_sync='p0 wait_for s2 hit_limit 3'; +set debug_sync='p0 wait_for s2'; +set debug_sync='p0 hit_limit 3'; +set debug_sync='p0 clear'; +set debug_sync='p0 test'; +set debug_sync='reset'; + +# +# Syntax. Valid forms. Line wrap, leading, mid, trailing space. +# +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 + EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC=' p0 SIGNAL s1 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 '; +SET DEBUG_SYNC=' p0 SIGNAL s1 WAIT_FOR s2 '; +SET DEBUG_SYNC=' p0 SIGNAL s1 WAIT_FOR s2 '; + +# +# Syntax. Invalid forms. +# +--error ER_PARSE_ERROR +SET DEBUG_SYNC=''; +--error ER_PARSE_ERROR +SET DEBUG_SYNC=' '; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TIMEOUT 6 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TIMEOUT 6'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 TIMEOUT 6 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 TIMEOUT 6'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 SIGNAL s1 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 SIGNAL s1'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TIMEOUT 6 WAIT_FOR s2 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TIMEOUT 6 WAIT_FOR s2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 TIMEOUT 6 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 TIMEOUT 6'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 EXECUTE 2 SIGNAL s1 TIMEOUT 6'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TIMEOUT 6 SIGNAL s1'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 EXECUTE 2 TIMEOUT 6 SIGNAL s1'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 CLEAR HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='CLEAR'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 CLEAR p0'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='TEST'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TEST p0'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 RESET'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='RESET p0'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 RESET p0'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL '; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR '; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 EXECUTE '; + +# +# Syntax. Invalid keywords used. +# +--error ER_UNKNOWN_SYSTEM_VARIABLE +SET DEBUG_SYNCx='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAx s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOx s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUx 0 EXECUTE 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTx 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIx 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 CLEARx'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TESTx'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='RESETx'; + +# +# Syntax. Invalid numbers. Decimal only. +# +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 0x6 EXECUTE 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 EXECUTE 0x2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 7 EXECUTE 2 HIT_LIMIT 0x3'; + +# +# Syntax. Invalid value type. +# +--error ER_WRONG_TYPE_FOR_VAR +SET DEBUG_SYNC= 7; + +# +# Syntax. DEBUG_SYNC is a SESSION-only variable. +# +--error ER_LOCAL_VARIABLE +SET GLOBAL DEBUG_SYNC= 'p0 CLEAR'; + +# +# Syntax. The variable value does not need to be a string literal. +# +SET @myvar= 'now SIGNAL from_myvar'; +SET DEBUG_SYNC= @myvar; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +# +SET DEBUG_SYNC= LEFT('now SIGNAL from_function_cut_here', 24); +SHOW VARIABLES LIKE 'DEBUG_SYNC'; + +# +# Functional tests. +# +# NOTE: There is the special synchronization point 'now'. It is placed +# immediately after setting of the DEBUG_SYNC variable. +# So it is executed before the SET statement ends. +# +# NOTE: There is only one global signal (say "signal post" or "flag mast"). +# A SIGNAL action writes its signal into it ("sets a flag"). +# The signal persists until explicitly overwritten. +# To avoid confusion for later tests, it is recommended to clear +# the signal by signalling "empty" ("setting the 'empty' flag"): +# SET DEBUG_SYNC= 'now SIGNAL empty'; +# Preferably you can reset the whole facility with: +# SET DEBUG_SYNC= 'RESET'; +# The signal is then '' (really empty) which connot be done otherwise. +# + +# +# Time out immediately. This gives just a warning. +# +SET DEBUG_SYNC= 'now SIGNAL something'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +# Suppress warning number +--replace_column 2 #### +SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0'; +# +# If signal is present already, TIMEOUT 0 does not give a warning. +# +SET DEBUG_SYNC= 'now SIGNAL nothing'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0'; + +# +# EXECUTE 0 is effectively a no-op. +# +SET DEBUG_SYNC= 'now SIGNAL something EXECUTE 0'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'now WAIT_FOR anotherthing TIMEOUT 0 EXECUTE 0'; + +# +# Run into HIT_LIMIT. This gives an error. +# +--error ER_DEBUG_SYNC_HIT_LIMIT +SET DEBUG_SYNC= 'now HIT_LIMIT 1'; + +# +# Many actions. Watch the array growing and shrinking in the debug trace: +# egrep 'query:|debug_sync_action:' mysql-test/var/log/master.trace +# +SET DEBUG_SYNC= 'RESET'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p1abcd SIGNAL s1 EXECUTE 2'; +SET DEBUG_SYNC= 'p2abc SIGNAL s2 EXECUTE 2'; +SET DEBUG_SYNC= 'p9abcdef SIGNAL s9 EXECUTE 2'; +SET DEBUG_SYNC= 'p4a SIGNAL s4 EXECUTE 2'; +SET DEBUG_SYNC= 'p5abcde SIGNAL s5 EXECUTE 2'; +SET DEBUG_SYNC= 'p6ab SIGNAL s6 EXECUTE 2'; +SET DEBUG_SYNC= 'p7 SIGNAL s7 EXECUTE 2'; +SET DEBUG_SYNC= 'p8abcdef SIGNAL s8 EXECUTE 2'; +SET DEBUG_SYNC= 'p3abcdef SIGNAL s3 EXECUTE 2'; +# +# Execute some actions to show they exist. Each sets a distinct signal. +# +SET DEBUG_SYNC= 'p4a TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p1abcd TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p7 TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p9abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p3abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +# +# Clear the actions. +# +SET DEBUG_SYNC= 'p1abcd CLEAR'; +SET DEBUG_SYNC= 'p2abc CLEAR'; +SET DEBUG_SYNC= 'p5abcde CLEAR'; +SET DEBUG_SYNC= 'p6ab CLEAR'; +SET DEBUG_SYNC= 'p8abcdef CLEAR'; +SET DEBUG_SYNC= 'p9abcdef CLEAR'; +SET DEBUG_SYNC= 'p3abcdef CLEAR'; +SET DEBUG_SYNC= 'p4a CLEAR'; +SET DEBUG_SYNC= 'p7 CLEAR'; +# +# Execute some actions to show they have gone. +# +SET DEBUG_SYNC= 'p1abcd TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p7 TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p9abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +# +# Now cleanup. Actions are clear already, but signal needs to be cleared. +# +SET DEBUG_SYNC= 'RESET'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; + +# +# Facility requires SUPER privilege. +# +CREATE USER mysqltest_1@localhost; +GRANT SUPER ON *.* TO mysqltest_1@localhost; +--echo connection con1, mysqltest_1 +connect (con1,localhost,mysqltest_1,,); +SET DEBUG_SYNC= 'RESET'; +disconnect con1; +--echo connection default +connection default; +DROP USER mysqltest_1@localhost; +# +CREATE USER mysqltest_2@localhost; +GRANT ALL ON *.* TO mysqltest_2@localhost; +REVOKE SUPER ON *.* FROM mysqltest_2@localhost; +--echo connection con1, mysqltest_2 +connect (con1,localhost,mysqltest_2,,); +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +SET DEBUG_SYNC= 'RESET'; +disconnect con1; +--echo connection default +connection default; +DROP USER mysqltest_2@localhost; + +# +# Example 1. +# +# Preparative cleanup. +--disable_warnings +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +--enable_warnings +# +# Test. +CREATE TABLE t1 (c1 INT); + --echo connection con1 + connect (con1,localhost,root,,); + SET DEBUG_SYNC= 'before_lock_tables_takes_lock + SIGNAL opened WAIT_FOR flushed'; + send INSERT INTO t1 VALUES(1); +--echo connection default +connection default; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +SET DEBUG_SYNC= 'after_flush_unlock SIGNAL flushed'; +FLUSH TABLE t1; + --echo connection con1 + connection con1; + reap; + disconnect con1; +--echo connection default +connection default; +DROP TABLE t1; + +# +# Example 2. +# +# Preparative cleanup. +--disable_warnings +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +--enable_warnings +# +# Test. +CREATE TABLE t1 (c1 INT); +LOCK TABLE t1 WRITE; + --echo connection con1 + connect (con1,localhost,root,,); + # Retain action after use. First used by general_log. + SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2'; + send INSERT INTO t1 VALUES (1); +--echo connection default +connection default; +# Wait until INSERT waits for lock. +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# let INSERT continue. +UNLOCK TABLES; + --echo connection con1 + connection con1; + --echo retrieve INSERT result. + reap; + disconnect con1; +--echo connection default +connection default; +DROP TABLE t1; + +# +# Cleanup after test case. +# Otherwise signal would contain 'flushed' here, +# which could confuse the next test. +# +SET DEBUG_SYNC= 'RESET'; + diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 602e30687c8..d77f5eb128b 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -292,3 +292,47 @@ DROP TABLE t1; DROP FUNCTION f1; --echo End of 5.0 tests + +--echo # +--echo # Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, +--echo # merge table +--echo # +CREATE TABLE t1 ( a INT ); +CREATE TABLE t2 ( a INT ); +CREATE TABLE t3 ( a INT ); + +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1), (2); +INSERT INTO t3 VALUES (1), (2); + +CREATE TRIGGER tr1 BEFORE DELETE ON t2 +FOR EACH ROW INSERT INTO no_such_table VALUES (1); + +--error ER_NO_SUCH_TABLE +DELETE t1, t2, t3 FROM t1, t2, t3; + +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; + +DROP TABLE t1, t2, t3; + +CREATE TABLE t1 ( a INT ); +CREATE TABLE t2 ( a INT ); +CREATE TABLE t3 ( a INT ); + +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1), (2); +INSERT INTO t3 VALUES (1), (2); + +CREATE TRIGGER tr1 AFTER DELETE ON t2 +FOR EACH ROW INSERT INTO no_such_table VALUES (1); + +--error ER_NO_SUCH_TABLE +DELETE t1, t2, t3 FROM t1, t2, t3; + +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; + +DROP TABLE t1, t2, t3; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 6d8f0af0c28..6f0b1716d38 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -12,3 +12,5 @@ kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. innodb_bug39438 : Bug#42383 2009-01-28 lsoares "This fails in embedded and on windows. Note that this test is not run on windows and on embedded in PB for main trees currently" query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically +partition_innodb_builtin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes +partition_innodb_plugin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index adc074259ad..61ae812d874 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -456,4 +456,89 @@ SELECT SUM( DISTINCT e ) FROM t1 GROUP BY b,c,d HAVING (b,c,d) IN ((AVG( 1 ), 1 + c, 1 + d), (AVG( 1 ), 2 + c, 2 + d)); DROP TABLE t1; +--echo # +--echo # Bug #44139: Table scan when NULL appears in IN clause +--echo # + +--disable_warnings + +CREATE TABLE t1 ( + c_int INT NOT NULL, + c_decimal DECIMAL(5,2) NOT NULL, + c_float FLOAT(5, 2) NOT NULL, + c_bit BIT(10) NOT NULL, + c_date DATE NOT NULL, + c_datetime DATETIME NOT NULL, + c_timestamp TIMESTAMP NOT NULL, + c_time TIME NOT NULL, + c_year YEAR NOT NULL, + c_char CHAR(10) NOT NULL, + INDEX(c_int), INDEX(c_decimal), INDEX(c_float), INDEX(c_bit), INDEX(c_date), + INDEX(c_datetime), INDEX(c_timestamp), INDEX(c_time), INDEX(c_year), + INDEX(c_char)); + +INSERT INTO t1 (c_int) VALUES (1), (2), (3), (4), (5); +INSERT INTO t1 (c_int) SELECT 0 FROM t1; +INSERT INTO t1 (c_int) SELECT 0 FROM t1; + +--enable_warnings + +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, 1, 2, 3); + +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, NULL, 2, NULL, 3, NULL); +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, 1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_float IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, 1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, 1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_date + IN ('2009-09-01', '2009-09-02', '2009-09-03'); +EXPLAIN SELECT * FROM t1 WHERE c_date + IN (NULL, '2009-09-01', '2009-09-02', '2009-09-03'); +EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_datetime + IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); +EXPLAIN SELECT * FROM t1 WHERE c_datetime + IN (NULL, '2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); +EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_timestamp + IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); +EXPLAIN SELECT * FROM t1 WHERE c_timestamp + IN (NULL, '2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); +EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_year IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, 1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_char IN ('1', '2', '3'); +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, '1', '2', '3'); +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL); + +DROP TABLE t1; + +--echo # + --echo End of 5.1 tests diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index 52ec0d9272a..ef66f71d2ad 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -184,7 +184,6 @@ show fields from testdb_1.v7; --error ER_TABLEACCESS_DENIED_ERROR show create view testdb_1.v7; ---error ER_VIEW_NO_EXPLAIN show create view v4; #--error ER_VIEW_NO_EXPLAIN show fields from v4; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index b5e30e63f54..1cd05c8cb65 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -729,4 +729,24 @@ SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a; SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a; DROP TABLE IF EXISTS t1,t2; + --echo End of 5.0 tests. + + +# +# Bug#47150 Assertion in Field_long::val_int() on MERGE + TRIGGER + multi-table UPDATE +# +CREATE TABLE t1 (f1 int); + +CREATE TABLE t2 (f1 int); +INSERT INTO t2 VALUES (1); +CREATE VIEW v1 AS SELECT * FROM t2; + +PREPARE stmt FROM 'UPDATE t2 AS A NATURAL JOIN v1 B SET B.f1 = 1'; +EXECUTE stmt; +EXECUTE stmt; + +DEALLOCATE PREPARE stmt; + +DROP VIEW v1; +DROP TABLE t1, t2; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index ba6bc05cfea..5de7c997a24 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1503,5 +1503,20 @@ SELECT h+0, d + 0, e, g + 0 FROM t1; DROP TABLE t1; +--echo # +--echo # Test of BUG#35570 CHECKSUM TABLE unreliable if LINESTRING field +--echo # (same content / differen checksum) +--echo # + +CREATE TABLE t1 (line LINESTRING NOT NULL) engine=myisam; +INSERT INTO t1 VALUES (GeomFromText("POINT(0 0)")); +checksum table t1; +CREATE TABLE t2 (line LINESTRING NOT NULL) engine=myisam; +INSERT INTO t2 VALUES (GeomFromText("POINT(0 0)")); +checksum table t2; +CREATE TABLE t3 select * from t1; +checksum table t3; +drop table t1,t2,t3; + --echo End of 5.1 tests diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index 7767abe43d0..78661b1bbc4 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -71,7 +71,8 @@ select "--- --position --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --position=239 $MYSQLD_DATADIR/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --position=330 $MYSQLD_DATADIR/master-bin.000002 + # These are tests for remote binlog. # They should return the same as previous test. @@ -107,7 +108,7 @@ select "--- --position --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --position=239 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --position=330 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 # Bug#7853 mysqlbinlog does not accept input from stdin --disable_query_log @@ -377,6 +378,68 @@ FLUSH LOGS; # We do not need the results, just make sure that mysqlbinlog does not crash --exec $MYSQL_BINLOG --hexdump --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 >/dev/null +# +# #46998 +# This test verifies if the 'BEGIN', 'COMMIT' and 'ROLLBACK' are output +# in regardless of database filtering +# + +RESET MASTER; +FLUSH LOGS; + +# The following three test cases were wrtten into binlog_transaction.000001 +# Test case1: Test if the 'BEGIN' and 'COMMIT' are output for the 'test' database +# in transaction1 base on innodb engine tables +# use test; +# create table t1(a int) engine= innodb; +# use mysql; +# create table t2(a int) engine= innodb; +# Transaction1 begin +# begin; +# use test; +# insert into t1 (a) values (1); +# use mysql; +# insert into t2 (a) values (1); +# commit; +# Transaction1 end + +# Test case2: Test if the 'BEGIN' and 'ROLLBACK' are output for the 'test' database +# in transaction2 base on innodb and myisam engine tables +# use test; +# create table t3(a int) engine= innodb; +# use mysql; +# create table t4(a int) engine= myisam; +# Transaction2 begin +# begin; +# use test; +# insert into t3 (a) values (2); +# use mysql; +# insert into t4 (a) values (2); +# rollback; +# Transaction2 end + +# Test case3: Test if the 'BEGIN' and 'COMMIT' are output for the 'test' database +# in transaction3 base on NDB engine tables +# use test; +# create table t5(a int) engine= NDB; +# use mysql; +# create table t6(a int) engine= NDB; +# Transaction3 begin +# begin; +# use test; +# insert into t5 (a) values (3); +# use mysql; +# insert into t6 (a) values (3); +# commit; +# Transaction3 end + +--echo # +--echo # Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is exist +--exec $MYSQL_BINLOG --database=test --short-form $MYSQLTEST_VARDIR/std_data/binlog_transaction.000001 +--echo # +--echo # Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is not exist +--exec $MYSQL_BINLOG --database=not_exist --short-form $MYSQLTEST_VARDIR/std_data/binlog_transaction.000001 + --echo End of 5.0 tests --echo End of 5.1 tests diff --git a/mysql-test/t/partition_innodb_builtin.test b/mysql-test/t/partition_innodb_builtin.test new file mode 100644 index 00000000000..a9be41c7455 --- /dev/null +++ b/mysql-test/t/partition_innodb_builtin.test @@ -0,0 +1,67 @@ +--source include/have_partition.inc +--source include/have_innodb.inc +--source include/have_not_innodb_plugin.inc + +# +# Bug#32430 - show engine innodb status causes errors +# +SET NAMES utf8; +CREATE TABLE `t``\""e` (a INT, PRIMARY KEY (a)) +ENGINE=InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION `p0``\""e` VALUES LESS THAN (100) + (SUBPARTITION `sp0``\""e`, + SUBPARTITION `sp1``\""e`), + PARTITION `p1``\""e` VALUES LESS THAN (MAXVALUE) + (SUBPARTITION `sp2``\""e`, + SUBPARTITION `sp3``\""e`)); +INSERT INTO `t``\""e` VALUES (0), (2), (6), (10), (14), (18), (22); +START TRANSACTION; +--echo # con1 +connect(con1,localhost,root,,); +SET NAMES utf8; +START TRANSACTION; +--echo # default connection +connection default; +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +--echo # con1 +connection con1; +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +let $id_1= `SELECT CONNECTION_ID()`; +SEND; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +--echo # default connection +connection default; +let $wait_timeout= 2; +let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID = $id_1 AND STATE = 'Searching rows for update'; +--source include/wait_condition.inc +#--echo # tested wait condition $wait_condition_reps times +--error ER_LOCK_DEADLOCK +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +--echo # First table reported in 'SHOW ENGINE InnoDB STATUS' +# RECORD LOCKS space id 0 page no 50 n bits 80 index `PRIMARY` in \ +# Database `test`, Table `t1`, Partition `p0`, Subpartition `sp0` \ +# trx id 0 775 +# NOTE: replace_regex is very slow on match copy/past '(.*)' regex's +# on big texts, removing a lot of text before + after makes it much faster. +#/.*in (.*) trx.*/\1/ +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +# INNODB_LOCKS only exists in innodb_plugin +#SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @@sql_mode = @old_sql_mode; +--echo # con1 +connection con1; +REAP; +ROLLBACK; +disconnect con1; +--echo # default connection +connection default; +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; diff --git a/mysql-test/t/partition_innodb_plugin.test b/mysql-test/t/partition_innodb_plugin.test new file mode 100644 index 00000000000..fed8c96424a --- /dev/null +++ b/mysql-test/t/partition_innodb_plugin.test @@ -0,0 +1,75 @@ +--source include/have_partition.inc +--source include/have_innodb.inc +--source suite/innodb/include/have_innodb_plugin.inc + +# +# Bug#32430 - show engine innodb status causes errors +# +SET NAMES utf8; +CREATE TABLE `t``\""e` (a INT, PRIMARY KEY (a)) +ENGINE=InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION `p0``\""e` VALUES LESS THAN (100) + (SUBPARTITION `sp0``\""e`, + SUBPARTITION `sp1``\""e`), + PARTITION `p1``\""e` VALUES LESS THAN (MAXVALUE) + (SUBPARTITION `sp2``\""e`, + SUBPARTITION `sp3``\""e`)); +INSERT INTO `t``\""e` VALUES (0), (2), (6), (10), (14), (18), (22); +START TRANSACTION; +--echo # con1 +connect(con1,localhost,root,,); +SET NAMES utf8; +START TRANSACTION; +--echo # default connection +connection default; +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +--echo # con1 +connection con1; +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +let $id_1= `SELECT CONNECTION_ID()`; +SEND; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +--echo # default connection +connection default; +let $wait_timeout= 2; +let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID = $id_1 AND STATE = 'Searching rows for update'; +--source include/wait_condition.inc +#--echo # tested wait condition $wait_condition_reps times +# INNODB_LOCKS only exists in innodb_plugin +--sorted_result +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +--sorted_result +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +set @@sql_mode = @old_sql_mode; +--error ER_LOCK_DEADLOCK +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +--echo # First table reported in 'SHOW ENGINE InnoDB STATUS' +# RECORD LOCKS space id 0 page no 50 n bits 80 index `PRIMARY` in \ +# Database `test`, Table `t1`, Partition `p0`, Subpartition `sp0` \ +# trx id 0 775 +# NOTE: replace_regex is very slow on match copy/past '(.*)' regex's +# on big texts, removing a lot of text before + after makes it much faster. +#/.*in (.*) trx.*/\1/ +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @@sql_mode = @old_sql_mode; +--echo # con1 +connection con1; +REAP; +ROLLBACK; +disconnect con1; +--echo # default connection +connection default; +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index ff4cdf3c439..440eca22828 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -28,5 +28,37 @@ SELECT 1; DROP TABLE t1,t2,t3; +--echo # +--echo # Bug #47106: Crash / segfault on adding EXPLAIN to a non-crashing +--echo # query +--echo # + +CREATE TABLE t1 ( + a INT, + b INT, + PRIMARY KEY (a), + KEY b (b) +); +INSERT INTO t1 VALUES (1, 1), (2, 1); + +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 SELECT * FROM t1; + +CREATE TABLE t3 LIKE t1; +INSERT INTO t3 SELECT * FROM t1; + +--echo # Should not crash. +--echo # Should have 1 impossible where and 2 dependent subqs. +EXPLAIN +SELECT + (SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +FROM t3 WHERE 1 = 0 GROUP BY 1; + +--echo # should return 0 rows +SELECT + (SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +FROM t3 WHERE 1 = 0 GROUP BY 1; + +DROP TABLE t1,t2,t3; --echo End of 5.0 tests. diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 824c67d867e..2ad488b7529 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1382,6 +1382,127 @@ DROP VIEW test.v3; DROP USER mysqluser1@localhost; USE test; +--echo # +--echo # Bug#35996: SELECT + SHOW VIEW should be enough to display view +--echo # definition +--echo # +-- source include/not_embedded.inc +CREATE USER mysqluser1@localhost; +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +GRANT USAGE, SELECT, CREATE VIEW, SHOW VIEW +ON mysqltest2.* TO mysqluser1@localhost; + +USE mysqltest1; + +CREATE TABLE t1( a INT ); +CREATE TABLE t2( a INT, b INT ); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE VIEW v1 AS SELECT 1 AS a; +CREATE VIEW v2 AS SELECT 1 AS a, 2 AS b; + +GRANT SELECT ON TABLE t1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE t2 TO mysqluser1@localhost; +GRANT EXECUTE ON FUNCTION f1 TO mysqluser1@localhost; +GRANT SELECT ON TABLE v1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE v2 TO mysqluser1@localhost; + +CREATE VIEW v_t1 AS SELECT * FROM t1; +CREATE VIEW v_t2 AS SELECT * FROM t2; +CREATE VIEW v_f1 AS SELECT f1() AS a; +CREATE VIEW v_v1 AS SELECT * FROM v1; +CREATE VIEW v_v2 AS SELECT * FROM v2; + +GRANT SELECT, SHOW VIEW ON v_t1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_t2 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_f1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v2 TO mysqluser1@localhost; + +--connect (connection1, localhost, mysqluser1,, mysqltest2) +CREATE VIEW v_mysqluser1_t1 AS SELECT * FROM mysqltest1.t1; +CREATE VIEW v_mysqluser1_t2 AS SELECT * FROM mysqltest1.t2; +CREATE VIEW v_mysqluser1_f1 AS SELECT mysqltest1.f1() AS a; +CREATE VIEW v_mysqluser1_v1 AS SELECT * FROM mysqltest1.v1; +CREATE VIEW v_mysqluser1_v2 AS SELECT * FROM mysqltest1.v2; + +SHOW CREATE VIEW mysqltest1.v_t1; +SHOW CREATE VIEW mysqltest1.v_t2; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW mysqltest1.v_v2; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_t2; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; +SHOW CREATE VIEW v_mysqluser1_v2; + +--connection default +REVOKE SELECT ON TABLE t1 FROM mysqluser1@localhost; +REVOKE SELECT (a) ON TABLE t2 FROM mysqluser1@localhost; +REVOKE EXECUTE ON FUNCTION f1 FROM mysqluser1@localhost; +REVOKE SELECT ON TABLE v1 FROM mysqluser1@localhost; + +--connection connection1 +SHOW CREATE VIEW mysqltest1.v_t1; +SHOW CREATE VIEW mysqltest1.v_t2; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW mysqltest1.v_v2; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_t2; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; +SHOW CREATE VIEW v_mysqluser1_v2; + +--connection default +--echo # Testing the case when the views reference missing objects. +--echo # Obviously, there are no privileges to check for, so we +--echo # need only each object type once. +DROP TABLE t1; +DROP FUNCTION f1; +DROP VIEW v1; + +--connection connection1 +SHOW CREATE VIEW mysqltest1.v_t1; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; + +--connection default +REVOKE SHOW VIEW ON v_t1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_f1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_v1 FROM mysqluser1@localhost; + +--connection connection1 +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_t1; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_f1; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; + +--disconnect connection1 +--connection default +DROP USER mysqluser1@localhost; +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +USE test; + +CREATE TABLE t1( a INT ); +CREATE DEFINER = no_such_user@no_such_host VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +DROP TABLE t1; +DROP VIEW v1; + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/windows.test b/mysql-test/t/windows.test index 89cd2ed19e8..b7d31948d23 100755 --- a/mysql-test/t/windows.test +++ b/mysql-test/t/windows.test @@ -92,3 +92,9 @@ execute abc; execute abc; deallocate prepare abc; +--echo # +--echo # Bug#45498: Socket variable not available on Windows +--echo # + +SELECT VARIABLE_NAME FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES + WHERE VARIABLE_NAME = 'socket'; diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index 21c6c30abcc..6b10e4cb544 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -1,4 +1,3 @@ -# # Suppress some common (not fatal) errors in system libraries found by valgrind # @@ -625,3 +624,101 @@ fun:start_thread } +# suppressions for glibc 2.6.1 64 bit + +{ + Mem loss within nptl_pthread_exit_hack_handler 6 + Memcheck:Leak + fun:malloc + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/libc-*.so + obj:*/ld-*.so + obj:*/libc-*.so + fun:__libc_dlopen_mode + fun:pthread_cancel_init + fun:_Unwind_ForcedUnwind + fun:__pthread_unwind + fun:pthread_exit + fun:nptl_pthread_exit_hack_handler +} + +{ + Mem loss within nptl_pthread_exit_hack_handler 7 + Memcheck:Leak + fun:malloc + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/libc-*.so + obj:*/ld-*.so + obj:*/libc-*.so + fun:__libc_dlopen_mode + fun:pthread_cancel_init + fun:_Unwind_ForcedUnwind + fun:__pthread_unwind + fun:pthread_exit + fun:nptl_pthread_exit_hack_handler + fun:start_thread + fun:clone +} + +{ + Mem loss within nptl_pthread_exit_hack_handler 8 + Memcheck:Leak + fun:calloc + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/libc-*.so + obj:*/ld-*.so + obj:*/libc-*.so + fun:__libc_dlopen_mode + fun:pthread_cancel_init + fun:_Unwind_ForcedUnwind + fun:__pthread_unwind + fun:pthread_exit + fun:nptl_pthread_exit_hack_handler + fun:start_thread + fun:clone +} + +{ + Mem loss within nptl_pthread_exit_hack_handler 8 + Memcheck:Leak + fun:calloc + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/ld-*.so + obj:*/libc-*.so + obj:*/ld-*.so + obj:*/libc-*.so + fun:__libc_dlopen_mode + fun:pthread_cancel_init + fun:_Unwind_ForcedUnwind + fun:__pthread_unwind + fun:pthread_exit + fun:nptl_pthread_exit_hack_handler +} + +# +# Pthread doesn't free all thread specific memory before program exists +# +{ + pthread allocate_tls memory loss in 2.6.1. + Memcheck:Leak + fun:calloc + obj:*/ld-*.so + fun:_dl_allocate_tls + fun:pthread_create* +} + |