diff options
Diffstat (limited to 'mysql-test')
136 files changed, 3303 insertions, 1099 deletions
diff --git a/mysql-test/extra/binlog_tests/binlog.test b/mysql-test/extra/binlog_tests/binlog.test index 98bd116ba29..d72dc693cee 100644 --- a/mysql-test/extra/binlog_tests/binlog.test +++ b/mysql-test/extra/binlog_tests/binlog.test @@ -164,6 +164,46 @@ DROP TABLE t1; DROP DATABASE bug39182; USE test; +# +# Bug#35383: binlog playback and replication breaks due to +# name_const substitution +# +DELIMITER //; +CREATE PROCEDURE p1(IN v1 INT) +BEGIN + CREATE TABLE t1 SELECT v1; + DROP TABLE t1; +END// +CREATE PROCEDURE p2() +BEGIN + DECLARE v1 INT; + CREATE TABLE t1 SELECT v1+1; + DROP TABLE t1; +END// +CREATE PROCEDURE p3(IN v1 INT) +BEGIN + CREATE TABLE t1 SELECT 1 FROM DUAL WHERE v1!=0; + DROP TABLE t1; +END// +CREATE PROCEDURE p4(IN v1 INT) +BEGIN + DECLARE v2 INT; + CREATE TABLE t1 SELECT 1, v1, v2; + DROP TABLE t1; + CREATE TABLE t1 SELECT 1, v1+1, v2; + DROP TABLE t1; +END// +DELIMITER ;// + +CALL p1(1); +CALL p2(); +CALL p3(0); +CALL p4(0); +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; + --echo End of 5.0 tests # Test of a too big SET INSERT_ID: see if the truncated value goes diff --git a/mysql-test/extra/rpl_tests/rpl_loadfile.test b/mysql-test/extra/rpl_tests/rpl_loadfile.test new file mode 100644 index 00000000000..85620b58a97 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_loadfile.test @@ -0,0 +1,36 @@ +# Begin clean up test section +--disable_warnings +connection master; +DROP PROCEDURE IF EXISTS test.p1; +DROP TABLE IF EXISTS test.t1; +--enable_warnings + +# Section 1 test + +CREATE TABLE test.t1 (a INT, blob_column LONGBLOB, PRIMARY KEY(a)); +INSERT INTO test.t1 VALUES(1,'test'); +UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=1; +delimiter |; +create procedure test.p1() +begin + INSERT INTO test.t1 VALUES(2,'test'); + UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=2; +end| +delimiter ;| + +CALL test.p1(); +SELECT * FROM test.t1 ORDER BY blob_column; +save_master_pos; +sync_slave_with_master; +connection slave; +# Need to allow some time when NDB engine is used for +# the injector thread to have time to populate binlog +let $wait_condition= SELECT INSTR(blob_column,'aberration') > 0 FROM test.t1 WHERE a = 2; +--source include/wait_condition.inc +SELECT * FROM test.t1 ORDER BY blob_column; + +# Cleanup +connection master; +DROP PROCEDURE IF EXISTS test.p1; +DROP TABLE test.t1; +sync_slave_with_master; diff --git a/mysql-test/include/concurrent.inc b/mysql-test/include/concurrent.inc index e2bf686aecb..b0383e655d7 100644 --- a/mysql-test/include/concurrent.inc +++ b/mysql-test/include/concurrent.inc @@ -11,6 +11,11 @@ # $engine_type storage engine to be tested # # Last update: +# 2009-02-13 HH "Release_lock("hello")" is now also successful when delivering NULL, +# replaced two sleeps by wait_condition. The last two "sleep 1" have not been +# replaced as all tried wait conditions leaded to nondeterministic results, especially +# to succeeding concurrent updates. To replace the sleeps there should be some time +# planned (or internal knowledge of the server may help). # 2006-08-02 ML test refactored # old name was t/innodb_concurrent.test # main code went into include/concurrent.inc @@ -20,8 +25,9 @@ # new wrapper t/concurrent_innodb_safelog.test # -connection default; +--source include/not_embedded.inc +connection default; # # Show prerequisites for this test. # @@ -50,8 +56,6 @@ GRANT USAGE ON test.* TO mysqltest@localhost; # # Preparatory cleanup. # -DO release_lock("hello"); -DO release_lock("hello2"); --disable_warnings drop table if exists t1; --enable_warnings @@ -86,13 +90,14 @@ drop table if exists t1; connection thread2; --echo ** Start transaction for thread 2 begin; - --echo ** Update will cause a table scan and a new ULL will + --echo ** Update will cause a table scan and a new ULL will --echo ** be created and blocked on the first row where tipo=11. send update t1 set eta=1+get_lock("hello",10)*0 where tipo=11; - sleep 1; --echo ** connection thread1 connection thread1; + let $wait_condition= select count(*)= 1 from information_schema.processlist where state= 'User lock'; + --source include/wait_condition.inc --echo ** Start new transaction for thread 1 begin; --echo ** Update on t1 will cause a table scan which will be blocked because @@ -111,7 +116,9 @@ drop table if exists t1; } --echo ** Release user level name lock from thread 1. This will cause the ULL --echo ** on thread 2 to end its wait. - select release_lock("hello"); +# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically) the success of the following +# is also guaranteed for NULL. Replaced SELECT by DO (no result). + DO release_lock("hello"); --echo ** Table is now updated with a new eta on tipo=22 for thread 1. select * from t1; @@ -119,7 +126,9 @@ drop table if exists t1; connection thread2; --echo ** Release the lock and collect result from update on thread 2 reap; - select release_lock("hello"); +# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically) the success of the following +# is also guaranteed for NULL. Replaced SELECT by DO (no result). + DO release_lock("hello"); --echo ** Table should have eta updates where tipo=11 but updates made by --echo ** thread 1 shouldn't be visible yet. select * from t1; @@ -183,10 +192,11 @@ drop table t1; --echo ** This will cause a hang on the first row where tipo=1 until the --echo ** blocking ULL is released. send update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; - sleep 1; - --echo ** connection thread1 +--echo ** connection thread1 connection thread1; + let $wait_condition= select count(*)= 1 from information_schema.processlist where state= 'User lock'; + --source include/wait_condition.inc --echo ** Start transaction on thread 1 begin; --echo ** Update on t1 will cause a table scan which will be blocked because @@ -204,7 +214,9 @@ drop table t1; update t1 set tipo=1 where tipo=2; } --echo ** Release ULL. This will release the next waiting ULL on thread 2. - select release_lock("hello"); +# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically)the success of the following +# is also guaranteed for NULL. Replaced SELECT by DO (no result). + DO release_lock("hello"); --echo ** The table should still be updated with updates for thread 1 only: select * from t1; @@ -212,7 +224,9 @@ drop table t1; connection thread2; --echo ** Release the lock and collect result from thread 2: reap; - select release_lock("hello"); +# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically) the success of the following +# is also guaranteed for NULL. Replaced SELECT by DO (no result). + DO release_lock("hello"); --echo ** Seen from thread 2 the table should have been updated on four --echo ** places. select * from t1; @@ -264,15 +278,18 @@ drop table t1; --echo ** Update will create a table scan which creates a ULL where a=2; --echo ** this will hang waiting on thread 1. send update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2; - sleep 1; --echo ** connection thread1 connection thread1; + let $wait_condition= select count(*)= 1 from information_schema.processlist where state= 'User lock'; + --source include/wait_condition.inc --echo ** Insert new values to t1 from thread 1; this created an implicit --echo ** commit since there are no on-going transactions. insert into t1 values (1,1); --echo ** Release the ULL (thread 2 updates will finish). - select release_lock("hello2"); +# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically) the success of the following +# is also guaranteed for NULL. Replaced SELECT by DO (no result). + DO release_lock("hello2"); --echo ** ..but thread 1 will still see t1 as if nothing has happend: select * from t1; @@ -280,7 +297,9 @@ drop table t1; connection thread2; --echo ** Collect results from thread 2 and release the lock. reap; - select release_lock("hello2"); +# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically) the success of the following +# is also guaranteed for NULL. Replaced SELECT by DO (no result). + DO release_lock("hello2"); --echo ** The table should look like the original+updates for thread 2, --echo ** and consist of new rows: select * from t1; @@ -534,6 +553,9 @@ drop table t1; connection thread2; begin; send delete from t1 where tipo=2; +# The sleep has not been replaced as all tried wait conditions leaded to sporadically +# succeding update in the following thread. Also the used status variables '%lock%' and +# 'innodb_deleted_rows' and infos in processlist where not sucessful. sleep 1; --echo ** connection thread1 @@ -594,8 +616,11 @@ drop table t1; connection thread2; begin; send delete from t1 where tipo=2; +# The sleep has not been replaced as all tried wait conditions leaded to sporadically +# succeding update in the following thread. Also the used status variables '%lock%' and +# 'innodb_deleted_rows' and infos in processlist where not sucessful. sleep 1; - + --echo ** connection thread1 connection thread1; begin; diff --git a/mysql-test/include/ndb_backup.inc b/mysql-test/include/ndb_backup.inc index ea56d79cbba..5262f1231a2 100644 --- a/mysql-test/include/ndb_backup.inc +++ b/mysql-test/include/ndb_backup.inc @@ -3,27 +3,44 @@ # in test cases and can be reused. # ###################################################### -# Bug#41307: Tests using include/ndb_backup.inc won't work on Windows due to -# 'grep' call -# This test is disabled on Windows via the next line until the above bug is -# resolved ---source include/not_windows.inc - --exec $NDB_MGM --no-defaults --ndb-connectstring="$NDB_CONNECTSTRING" -e "start backup" >> $NDB_TOOLS_OUTPUT -# there is no neat way to find the backupid, this is a hack to find it... -let $dump_file= $MYSQLTEST_VARDIR/tmp/tmp.dat; ---exec $NDB_TOOLS_DIR/ndb_select_all --ndb-connectstring="$NDB_CONNECTSTRING" -d sys --delimiter=',' SYSTAB_0 | grep 520093696 > $dump_file - -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; - ---replace_result $dump_file DUMP_FILE -eval LOAD DATA INFILE '$dump_file' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +# To find the backupid, we must dump this data to a table, and SELECT +# what we want into an outfile. This could be accomplished with grep, but +# grep isn't Windows-portable + +--disable_query_log +# create a table to help us out +--disable_warnings # leave this on until done with the entire process +# cleanup +DROP TABLE IF EXISTS helper1; +CREATE TABLE helper1(c1 VARCHAR(20)); +# dump raw data to file +let $ndb_backup_file1= $MYSQLTEST_VARDIR/ndb_backup_tmp.dat; +let $ndb_backup_file2= $MYSQLTEST_VARDIR/tmp.dat; +--error 0,1 +--remove_file $ndb_backup_file1 +--exec $NDB_TOOLS_DIR/ndb_select_all --ndb-connectstring="$NDB_CONNECTSTRING" -d sys --delimiter=',' SYSTAB_0 > $ndb_backup_file1 +# load the table from the raw data file +eval LOAD DATA INFILE '$ndb_backup_file1' INTO TABLE helper1; +--remove_file $ndb_backup_file1 +# output what we need +eval SELECT * FROM helper1 WHERE c1 LIKE '%520093696%' +INTO OUTFILE '$ndb_backup_file2'; +# cleanup +DROP TABLE helper1; +--enable_warnings +--enable_query_log + +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; + +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> +eval LOAD DATA INFILE '$ndb_backup_file2' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +--remove_file $ndb_backup_file2 # Load backup id into environment variable let the_backup_id=`SELECT backup_id from test.backup_info`; -DROP TABLE test.backup_info; - -remove_file $dump_file; +DROP TABLE test.backup_info; diff --git a/mysql-test/lib/My/SafeProcess/safe_process.cc b/mysql-test/lib/My/SafeProcess/safe_process.cc index 6ad45a3226e..dc7b7da28c7 100644 --- a/mysql-test/lib/My/SafeProcess/safe_process.cc +++ b/mysql-test/lib/My/SafeProcess/safe_process.cc @@ -45,6 +45,7 @@ #include <sys/types.h> #include <sys/wait.h> +#include <sys/time.h> #include <sys/resource.h> #include <unistd.h> #include <stdarg.h> diff --git a/mysql-test/r/concurrent_innodb_safelog.result b/mysql-test/r/concurrent_innodb_safelog.result index 92d274993d9..e6adaac1068 100644 --- a/mysql-test/r/concurrent_innodb_safelog.result +++ b/mysql-test/r/concurrent_innodb_safelog.result @@ -7,8 +7,6 @@ SELECT @@global.innodb_locks_unsafe_for_binlog; 0 # keep_locks == 1 GRANT USAGE ON test.* TO mysqltest@localhost; -DO release_lock("hello"); -DO release_lock("hello2"); drop table if exists t1; ** @@ -36,7 +34,7 @@ get_lock("hello",10) ** connection thread2 ** Start transaction for thread 2 begin; -** Update will cause a table scan and a new ULL will +** Update will cause a table scan and a new ULL will ** be created and blocked on the first row where tipo=11. update t1 set eta=1+get_lock("hello",10)*0 where tipo=11; ** connection thread1 @@ -51,9 +49,7 @@ update t1 set eta=2 where tipo=22; ERROR HY000: Lock wait timeout exceeded; try restarting transaction ** Release user level name lock from thread 1. This will cause the ULL ** on thread 2 to end its wait. -select release_lock("hello"); -release_lock("hello") -1 +DO release_lock("hello"); ** Table is now updated with a new eta on tipo=22 for thread 1. select * from t1; eta tipo c @@ -70,9 +66,7 @@ eta tipo c 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk ** connection thread2 ** Release the lock and collect result from update on thread 2 -select release_lock("hello"); -release_lock("hello") -1 +DO release_lock("hello"); ** Table should have eta updates where tipo=11 but updates made by ** thread 1 shouldn't be visible yet. select * from t1; @@ -194,9 +188,7 @@ begin; update t1 set tipo=1 where tipo=2; ERROR HY000: Lock wait timeout exceeded; try restarting transaction ** Release ULL. This will release the next waiting ULL on thread 2. -select release_lock("hello"); -release_lock("hello") -1 +DO release_lock("hello"); ** The table should still be updated with updates for thread 1 only: select * from t1; eta tipo c @@ -213,9 +205,7 @@ eta tipo c 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk ** connection thread2 ** Release the lock and collect result from thread 2: -select release_lock("hello"); -release_lock("hello") -1 +DO release_lock("hello"); ** Seen from thread 2 the table should have been updated on four ** places. select * from t1; @@ -319,9 +309,7 @@ update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2; ** commit since there are no on-going transactions. insert into t1 values (1,1); ** Release the ULL (thread 2 updates will finish). -select release_lock("hello2"); -release_lock("hello2") -1 +DO release_lock("hello2"); ** ..but thread 1 will still see t1 as if nothing has happend: select * from t1; a b @@ -332,9 +320,7 @@ a b 1 1 ** connection thread2 ** Collect results from thread 2 and release the lock. -select release_lock("hello2"); -release_lock("hello2") -1 +DO release_lock("hello2"); ** The table should look like the original+updates for thread 2, ** and consist of new rows: select * from t1; diff --git a/mysql-test/r/concurrent_innodb_unsafelog.result b/mysql-test/r/concurrent_innodb_unsafelog.result index 2a6c15d38c1..e9c53d4cfa0 100644 --- a/mysql-test/r/concurrent_innodb_unsafelog.result +++ b/mysql-test/r/concurrent_innodb_unsafelog.result @@ -7,8 +7,6 @@ SELECT @@global.innodb_locks_unsafe_for_binlog; 1 # keep_locks == 0 GRANT USAGE ON test.* TO mysqltest@localhost; -DO release_lock("hello"); -DO release_lock("hello2"); drop table if exists t1; ** @@ -36,7 +34,7 @@ get_lock("hello",10) ** connection thread2 ** Start transaction for thread 2 begin; -** Update will cause a table scan and a new ULL will +** Update will cause a table scan and a new ULL will ** be created and blocked on the first row where tipo=11. update t1 set eta=1+get_lock("hello",10)*0 where tipo=11; ** connection thread1 @@ -50,9 +48,7 @@ begin; update t1 set eta=2 where tipo=22; ** Release user level name lock from thread 1. This will cause the ULL ** on thread 2 to end its wait. -select release_lock("hello"); -release_lock("hello") -1 +DO release_lock("hello"); ** Table is now updated with a new eta on tipo=22 for thread 1. select * from t1; eta tipo c @@ -69,9 +65,7 @@ eta tipo c 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk ** connection thread2 ** Release the lock and collect result from update on thread 2 -select release_lock("hello"); -release_lock("hello") -1 +DO release_lock("hello"); ** Table should have eta updates where tipo=11 but updates made by ** thread 1 shouldn't be visible yet. select * from t1; @@ -192,9 +186,7 @@ begin; ** do not match the WHERE condition are released. update t1 set tipo=1 where tipo=2; ** Release ULL. This will release the next waiting ULL on thread 2. -select release_lock("hello"); -release_lock("hello") -1 +DO release_lock("hello"); ** The table should still be updated with updates for thread 1 only: select * from t1; eta tipo c @@ -211,9 +203,7 @@ eta tipo c 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk ** connection thread2 ** Release the lock and collect result from thread 2: -select release_lock("hello"); -release_lock("hello") -1 +DO release_lock("hello"); ** Seen from thread 2 the table should have been updated on four ** places. select * from t1; @@ -317,9 +307,7 @@ update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2; ** commit since there are no on-going transactions. insert into t1 values (1,1); ** Release the ULL (thread 2 updates will finish). -select release_lock("hello2"); -release_lock("hello2") -1 +DO release_lock("hello2"); ** ..but thread 1 will still see t1 as if nothing has happend: select * from t1; a b @@ -330,9 +318,7 @@ a b 1 1 ** connection thread2 ** Collect results from thread 2 and release the lock. -select release_lock("hello2"); -release_lock("hello2") -1 +DO release_lock("hello2"); ** The table should look like the original+updates for thread 2, ** and consist of new rows: select * from t1; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 2901020ab45..bd1d9e825cc 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1731,7 +1731,7 @@ t1 CREATE TABLE `t1` ( `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', - `TIME` bigint(7) NOT NULL DEFAULT '0', + `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) DEFAULT CHARSET=utf8 @@ -1745,7 +1745,7 @@ t1 CREATE TEMPORARY TABLE `t1` ( `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', - `TIME` bigint(7) NOT NULL DEFAULT '0', + `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) DEFAULT CHARSET=utf8 diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result index 69b48ee2952..b42094550bd 100644 --- a/mysql-test/r/ctype_collate.result +++ b/mysql-test/r/ctype_collate.result @@ -611,3 +611,22 @@ check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; +select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci); +least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) +a +create table t1 +select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) as f1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` varchar(1) CHARACTER SET latin5 NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate +latin5_turkish_ci then 2 else 3 end; +case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate +latin5_turkish_ci then 2 else 3 end +3 +select concat(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci); +concat(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) +abc diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 19826aca43a..8a935d87457 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1391,3 +1391,174 @@ WHERE `RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND `TESTID`='' AND `UCCHECK`=''; drop table t1; +# +# Generic @@optimizer_switch tests (move those into a separate file if +# we get another @@optimizer_switch user) +# +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on +set optimizer_switch='index_merge=off,index_merge_union=off'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on +set optimizer_switch='index_merge_union=on'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on +set optimizer_switch='default,index_merge_sort_union=off'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on +set optimizer_switch=4; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4' +set optimizer_switch=NULL; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'NULL' +set optimizer_switch='default,index_merge'; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge' +set optimizer_switch='index_merge=index_merge'; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=index_merge' +set optimizer_switch='index_merge=on,but...'; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'but...' +set optimizer_switch='index_merge='; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=' +set optimizer_switch='index_merge'; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge' +set optimizer_switch='on'; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'on' +set optimizer_switch='index_merge=on,index_merge=off'; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=off' +set optimizer_switch='index_merge_union=on,index_merge_union=default'; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge_union=default' +set optimizer_switch='default,index_merge=on,index_merge=off,default'; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=off,default' +set optimizer_switch=default; +set optimizer_switch='index_merge=off,index_merge_union=off,default'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on +set optimizer_switch=default; +select @@global.optimizer_switch; +@@global.optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on +set @@global.optimizer_switch=default; +select @@global.optimizer_switch; +@@global.optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on +# +# Check index_merge's @@optimizer_switch flags +# +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int, filler char(100), +key(a), key(b), key(c)); +insert into t1 select +A.a * B.a*10 + C.a*100, +A.a * B.a*10 + C.a*100, +A.a, +'filler' +from t0 A, t0 B, t0 C; +This should use union: +explain select * from t1 where a=1 or b=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using union(a,b); Using where +This should use ALL: +set optimizer_switch='default,index_merge=off'; +explain select * from t1 where a=1 or b=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where +This should use sort-union: +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a=1 or b=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using sort_union(a,b); Using where +This will use sort-union: +set optimizer_switch=default; +explain select * from t1 where a<1 or b <1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where +This should use ALL: +set optimizer_switch='default,index_merge_sort_union=off'; +explain select * from t1 where a<1 or b <1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where +This should use ALL: +set optimizer_switch='default,index_merge=off'; +explain select * from t1 where a<1 or b <1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where +This will use sort-union: +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a<1 or b <1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where +alter table t1 add d int, add key(d); +update t1 set d=a; +This will use sort_union: +set optimizer_switch=default; +explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b,c,d a,b 5,5 NULL 3 Using sort_union(a,b); Using where +And if we disable sort_union, union: +set optimizer_switch='default,index_merge_sort_union=off'; +explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b,c,d c,d 5,5 NULL 100 Using union(c,d); Using where +drop table t1; +create table t1 ( +a int, b int, c int, +filler1 char(200), filler2 char(200), +key(a),key(b),key(c) +); +insert into t1 +select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar' +from t0 A, t0 B, t0 C, t0 D where D.a<5; +This should be intersect: +set optimizer_switch=default; +explain select * from t1 where a=10 and b=10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where +No intersect when index_merge is disabled: +set optimizer_switch='default,index_merge=off'; +explain select * from t1 where a=10 and b=10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b a 5 const 49 Using where +No intersect if it is disabled: +set optimizer_switch='default,index_merge_intersection=off'; +explain select * from t1 where a=10 and b=10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b a 5 const 49 Using where +Do intersect when union was disabled +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a=10 and b=10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where +Do intersect when sort_union was disabled +set optimizer_switch='default,index_merge_sort_union=off'; +explain select * from t1 where a=10 and b=10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where +This will use intersection inside a union: +set optimizer_switch=default; +explain select * from t1 where a=10 and b=10 or c=10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b,c a,b,c 5,5,5 NULL 6 Using union(intersect(a,b),c); Using where +Should be only union left: +set optimizer_switch='default,index_merge_intersection=off'; +explain select * from t1 where a=10 and b=10 or c=10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using union(a,c); Using where +This will switch to sort-union (intersection will be gone, too, +thats a known limitation: +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a=10 and b=10 or c=10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using sort_union(a,c); Using where +set optimizer_switch=default; +show variables like 'optimizer_switch'; +Variable_name Value +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on +drop table t0, t1; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 5d751cf6688..43913cf88ff 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1582,4 +1582,9 @@ SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; CREATE_OPTIONS key_block_size=1 DROP TABLE t1; +SET TIMESTAMP=@@TIMESTAMP + 10000000; +SELECT 'OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0; +TEST_RESULT +OK +SET TIMESTAMP=DEFAULT; End of 5.1 tests. diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result index 9ada5dc0784..08d8059f61b 100644 --- a/mysql-test/r/key_cache.result +++ b/mysql-test/r/key_cache.result @@ -276,8 +276,6 @@ Variable_name Value Key_blocks_unused KEY_BLOCKS_UNUSED set global keycache2.key_buffer_size=0; set global keycache3.key_buffer_size=100; -Warnings: -Warning 1292 Truncated incorrect key_buffer_size value: '100' set global keycache3.key_buffer_size=0; create table t1 (mytext text, FULLTEXT (mytext)); insert t1 values ('aaabbb'); diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 50e37d28dd6..35bc7376296 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -96,40 +96,6 @@ alter table t1 auto_increment=0; alter table t1 auto_increment=0; unlock tables; drop table t1; -CREATE TABLE t1 ( -a int(11) unsigned default NULL, -b varchar(255) default NULL, -UNIQUE KEY a (a), -KEY b (b) -); -INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); -CREATE TABLE t2 SELECT * FROM t1; -CREATE TABLE t3 SELECT * FROM t1; -# test altering of columns that multiupdate doesn't use -# normal mode -# PS mode -# test altering of columns that multiupdate uses -# normal mode -# PS mode -DROP TABLE t1, t2, t3; -CREATE TABLE t1( a INT, b INT ); -INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4); -# 1. test regular tables -# 1.1. test altering of columns that multiupdate doesn't use -# 1.1.1. normal mode -# 1.1.2. PS mode -# 1.2. test altering of columns that multiupdate uses -# 1.2.1. normal mode -# 1.2.2. PS mode -ALTER TABLE t1 ADD COLUMN a INT; -# 2. test UNIONs -# 2.1. test altering of columns that multiupdate doesn't use -# 2.1.1. normal mode -# 2.1.2. PS mode -# 2.2. test altering of columns that multiupdate uses -# 2.2.1. normal mode -# 2.2.2. PS mode -DROP TABLE t1; End of 5.0 tests create table t1 (i int); lock table t1 read; diff --git a/mysql-test/r/lock_multi_bug38499.result b/mysql-test/r/lock_multi_bug38499.result new file mode 100644 index 00000000000..fd0f2138a8d --- /dev/null +++ b/mysql-test/r/lock_multi_bug38499.result @@ -0,0 +1,19 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1( a INT, b INT ); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4); +# 1. test regular tables +# 1.1. test altering of columns that multiupdate doesn't use +# 1.1.1. normal mode +# 1.1.2. PS mode +# 1.2. test altering of columns that multiupdate uses +# 1.2.1. normal mode +# 1.2.2. PS mode +ALTER TABLE t1 ADD COLUMN a INT; +# 2. test UNIONs +# 2.1. test altering of columns that multiupdate doesn't use +# 2.1.1. normal mode +# 2.1.2. PS mode +# 2.2. test altering of columns that multiupdate uses +# 2.2.1. normal mode +# 2.2.2. PS mode +DROP TABLE t1; diff --git a/mysql-test/r/lock_multi_bug38691.result b/mysql-test/r/lock_multi_bug38691.result new file mode 100644 index 00000000000..74b9603d8e3 --- /dev/null +++ b/mysql-test/r/lock_multi_bug38691.result @@ -0,0 +1,17 @@ +DROP TABLE IF EXISTS t1,t2,t3; +CREATE TABLE t1 ( +a int(11) unsigned default NULL, +b varchar(255) default NULL, +UNIQUE KEY a (a), +KEY b (b) +); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); +CREATE TABLE t2 SELECT * FROM t1; +CREATE TABLE t3 SELECT * FROM t1; +# test altering of columns that multiupdate doesn't use +# normal mode +# PS mode +# test altering of columns that multiupdate uses +# normal mode +# PS mode +DROP TABLE t1, t2, t3; diff --git a/mysql-test/r/mysql-bug41486.result b/mysql-test/r/mysql-bug41486.result new file mode 100644 index 00000000000..02777ab587f --- /dev/null +++ b/mysql-test/r/mysql-bug41486.result @@ -0,0 +1,13 @@ +DROP TABLE IF EXISTS t1; +SET @old_max_allowed_packet= @@global.max_allowed_packet; +SET @@global.max_allowed_packet = 2 * 1024 * 1024 + 1024; +CREATE TABLE t1(data LONGBLOB); +INSERT INTO t1 SELECT REPEAT('1', 2*1024*1024); +SET @old_general_log = @@global.general_log; +SET @@global.general_log = 0; +SET @@global.general_log = @old_general_log; +SELECT LENGTH(data) FROM t1; +LENGTH(data) +2097152 +DROP TABLE t1; +SET @@global.max_allowed_packet = @old_max_allowed_packet; diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 34d695a0272..b55a96b6f30 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; 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 /*!*/; SET TIMESTAMP=1000000000/*!*/; drop table t1 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 621c11906cb..0771c7fb370 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4416,4 +4416,32 @@ date_nokey Warnings: Warning 1292 Incorrect date value: '10:41:7' for column 'date_nokey' at row 1 DROP TABLE A,C; +CREATE TABLE t1 (a INT NOT NULL, b INT); +INSERT INTO t1 VALUES (1, 1); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select '1' AS `a`,'1' AS `b` from `test`.`t1` where 1 +SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; +a b +1 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found +Warnings: +Note 1003 select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found +Warnings: +Note 1003 select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found +Warnings: +Note 1003 select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 2a10f34d081..da89c7ce386 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -160,12 +160,12 @@ SELECT a FROM t1 UNION SELECT a FROM t1 ) alias; -SELECT a INTO OUTFILE 'union.out.file' FROM ( +SELECT a INTO OUTFILE '<MYSQLTEST_VARDIR>/tmp/union.out.file' FROM ( SELECT a FROM t1 UNION SELECT a FROM t1 WHERE 0 ) alias; -SELECT a INTO DUMPFILE 'union.out.file2' FROM ( +SELECT a INTO DUMPFILE '<MYSQLTEST_VARDIR>/tmp/union.out.file' FROM ( SELECT a FROM t1 UNION SELECT a FROM t1 WHERE 0 @@ -178,21 +178,21 @@ SELECT a INTO @v FROM t1 SELECT a FROM ( SELECT a FROM t1 UNION -SELECT a INTO OUTFILE 'union.out.file3' FROM t1 +SELECT a INTO OUTFILE '<MYSQLTEST_VARDIR>/tmp/union.out.file' FROM t1 ) alias; SELECT a FROM ( SELECT a FROM t1 UNION -SELECT a INTO DUMPFILE 'union.out.file4' FROM t1 +SELECT a INTO DUMPFILE '<MYSQLTEST_VARDIR>/tmp/union.out.file' FROM t1 ) alias; SELECT a FROM t1 UNION SELECT a INTO @v FROM t1; -SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file5' FROM t1; -SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file6' FROM t1; +SELECT a FROM t1 UNION SELECT a INTO OUTFILE '<MYSQLTEST_VARDIR>/tmp/union.out.file' FROM t1; +SELECT a FROM t1 UNION SELECT a INTO DUMPFILE '<MYSQLTEST_VARDIR>/tmp/union.out.file' FROM t1; SELECT a INTO @v FROM t1 UNION SELECT a FROM t1; ERROR HY000: Incorrect usage of UNION and INTO -SELECT a INTO OUTFILE 'union.out.file7' FROM t1 UNION SELECT a FROM t1; +SELECT a INTO OUTFILE '<MYSQLTEST_VARDIR>/tmp/union.out.file' FROM t1 UNION SELECT a FROM t1; ERROR HY000: Incorrect usage of UNION and INTO -SELECT a INTO DUMPFILE 'union.out.file8' FROM t1 UNION SELECT a FROM t1; +SELECT a INTO DUMPFILE '<MYSQLTEST_VARDIR>/tmp/union.out.file' FROM t1 UNION SELECT a FROM t1; ERROR HY000: Incorrect usage of UNION and INTO DROP TABLE t1; CREATE TABLE t1 ( diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 93894d22a15..db6479615c9 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -28,6 +28,7 @@ set @my_storage_engine =@@global.storage_engine; set @my_thread_cache_size =@@global.thread_cache_size; set @my_max_allowed_packet =@@global.max_allowed_packet; set @my_delay_key_write =@@global.delay_key_write; +set @my_join_buffer_size =@@global.join_buffer_size; set @`test`=1; select @test, @`test`, @TEST, @`TEST`, @"teSt"; @test @`test` @TEST @`TEST` @"teSt" @@ -1019,6 +1020,11 @@ show variables like 'hostname'; Variable_name Value hostname # End of 5.0 tests +set join_buffer_size=1; +Warnings: +Warning 1292 Truncated incorrect join_buffer_size value: '1' +set @save_join_buffer_size=@@join_buffer_size; +set join_buffer_size=@save_join_buffer_size; set global binlog_cache_size =@my_binlog_cache_size; set global connect_timeout =@my_connect_timeout; set global delayed_insert_timeout =@my_delayed_insert_timeout; @@ -1050,6 +1056,7 @@ set global storage_engine =@my_storage_engine; set global thread_cache_size =@my_thread_cache_size; set global max_allowed_packet = default; set global delay_key_write =@my_delay_key_write; +set global join_buffer_size =@my_join_buffer_size; show global variables where Variable_name='table_definition_cache' or Variable_name='table_lock_wait_timeout'; Variable_name Value diff --git a/mysql-test/suite/binlog/r/binlog_auto_increment_bug33029.result b/mysql-test/suite/binlog/r/binlog_auto_increment_bug33029.result index 8df0568a755..8226469fcf7 100644 --- a/mysql-test/suite/binlog/r/binlog_auto_increment_bug33029.result +++ b/mysql-test/suite/binlog/r/binlog_auto_increment_bug33029.result @@ -38,4 +38,5 @@ DROP PROCEDURE IF EXISTS p2; DROP FUNCTION IF EXISTS f1; DROP TRIGGER IF EXISTS tr1; stop slave sql_thread; +reset slave; SET @@global.relay_log_purge= @old_relay_log_purge; diff --git a/mysql-test/suite/binlog/r/binlog_row_binlog.result b/mysql-test/suite/binlog/r/binlog_row_binlog.result index 1a56e048b27..25cb7a4726f 100644 --- a/mysql-test/suite/binlog/r/binlog_row_binlog.result +++ b/mysql-test/suite/binlog/r/binlog_row_binlog.result @@ -1137,6 +1137,38 @@ DROP PROCEDURE p1; DROP TABLE t1; DROP DATABASE bug39182; USE test; +CREATE PROCEDURE p1(IN v1 INT) +BEGIN +CREATE TABLE t1 SELECT v1; +DROP TABLE t1; +END// +CREATE PROCEDURE p2() +BEGIN +DECLARE v1 INT; +CREATE TABLE t1 SELECT v1+1; +DROP TABLE t1; +END// +CREATE PROCEDURE p3(IN v1 INT) +BEGIN +CREATE TABLE t1 SELECT 1 FROM DUAL WHERE v1!=0; +DROP TABLE t1; +END// +CREATE PROCEDURE p4(IN v1 INT) +BEGIN +DECLARE v2 INT; +CREATE TABLE t1 SELECT 1, v1, v2; +DROP TABLE t1; +CREATE TABLE t1 SELECT 1, v1+1, v2; +DROP TABLE t1; +END// +CALL p1(1); +CALL p2(); +CALL p3(0); +CALL p4(0); +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; End of 5.0 tests reset master; create table t1 (id tinyint auto_increment primary key); diff --git a/mysql-test/suite/binlog/r/binlog_stm_binlog.result b/mysql-test/suite/binlog/r/binlog_stm_binlog.result index aadbf950b21..efdeb30a2af 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_binlog.result +++ b/mysql-test/suite/binlog/r/binlog_stm_binlog.result @@ -644,6 +644,38 @@ DROP PROCEDURE p1; DROP TABLE t1; DROP DATABASE bug39182; USE test; +CREATE PROCEDURE p1(IN v1 INT) +BEGIN +CREATE TABLE t1 SELECT v1; +DROP TABLE t1; +END// +CREATE PROCEDURE p2() +BEGIN +DECLARE v1 INT; +CREATE TABLE t1 SELECT v1+1; +DROP TABLE t1; +END// +CREATE PROCEDURE p3(IN v1 INT) +BEGIN +CREATE TABLE t1 SELECT 1 FROM DUAL WHERE v1!=0; +DROP TABLE t1; +END// +CREATE PROCEDURE p4(IN v1 INT) +BEGIN +DECLARE v2 INT; +CREATE TABLE t1 SELECT 1, v1, v2; +DROP TABLE t1; +CREATE TABLE t1 SELECT 1, v1+1, v2; +DROP TABLE t1; +END// +CALL p1(1); +CALL p2(); +CALL p3(0); +CALL p4(0); +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; End of 5.0 tests reset master; create table t1 (id tinyint auto_increment primary key); diff --git a/mysql-test/suite/binlog/t/binlog_auto_increment_bug33029.test b/mysql-test/suite/binlog/t/binlog_auto_increment_bug33029.test index f20cc33f820..5297767675c 100644 --- a/mysql-test/suite/binlog/t/binlog_auto_increment_bug33029.test +++ b/mysql-test/suite/binlog/t/binlog_auto_increment_bug33029.test @@ -52,9 +52,10 @@ DROP FUNCTION IF EXISTS f1; DROP TRIGGER IF EXISTS tr1; enable_warnings; -remove_file $MYSQLD_DATADIR/slave-relay-bin.000001; -remove_file $MYSQLD_DATADIR/slave-relay-bin.index; stop slave sql_thread; +reset slave; source include/wait_for_slave_sql_to_stop.inc; +remove_file $MYSQLD_DATADIR/slave-relay-bin.000001; +remove_file $MYSQLD_DATADIR/slave-relay-bin.index; SET @@global.relay_log_purge= @old_relay_log_purge; diff --git a/mysql-test/suite/federated/federated.result b/mysql-test/suite/federated/federated.result index e54aa63b2ac..57f665995a1 100644 --- a/mysql-test/suite/federated/federated.result +++ b/mysql-test/suite/federated/federated.result @@ -2130,6 +2130,26 @@ SELECT t1.a FROM t1, t1 as t2 WHERE t2.b NOT LIKE t1.b; a DROP TABLE t1; DROP TABLE t1; +# +# BUG#21360 - mysqldump error on federated tables +# +#Switch to Connection Slave +CREATE TABLE t1(id VARCHAR(20) NOT NULL, PRIMARY KEY(id)); +INSERT INTO t1 VALUES ('text1'),('text2'),('text3'),('text4'); +#Switch to Connection Master +CREATE TABLE t1(id VARCHAR(20) NOT NULL, PRIMARY KEY(id)) ENGINE=FEDERATED +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/test/t1'; +# Dump table t1 using mysqldump tool +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `id` varchar(20) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/test/t1'; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE t1; +#Switch to Connection Slave +DROP TABLE t1; End of 5.0 tests create server 's1' foreign data wrapper 'mysql' options (port 3306); drop server 's1'; diff --git a/mysql-test/suite/federated/federated.test b/mysql-test/suite/federated/federated.test index fa448e74a18..870bbcb8f71 100644 --- a/mysql-test/suite/federated/federated.test +++ b/mysql-test/suite/federated/federated.test @@ -1942,6 +1942,28 @@ DROP TABLE t1; connection master; DROP TABLE t1; +--echo # +--echo # BUG#21360 - mysqldump error on federated tables +--echo # +connection slave; +--echo #Switch to Connection Slave +CREATE TABLE t1(id VARCHAR(20) NOT NULL, PRIMARY KEY(id)); +INSERT INTO t1 VALUES ('text1'),('text2'),('text3'),('text4'); + +connection master; +--echo #Switch to Connection Master +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE t1(id VARCHAR(20) NOT NULL, PRIMARY KEY(id)) ENGINE=FEDERATED + CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/t1'; +--echo # Dump table t1 using mysqldump tool +--replace_result $SLAVE_MYPORT SLAVE_PORT +--exec $MYSQL_DUMP --compact test t1 +DROP TABLE t1; + +connection slave; +--echo #Switch to Connection Slave +DROP TABLE t1; + connection default; --echo End of 5.0 tests diff --git a/mysql-test/suite/funcs_1/r/is_columns_is.result b/mysql-test/suite/funcs_1/r/is_columns_is.result index 94aa75c6db2..ccb94c63d46 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is.result @@ -166,7 +166,7 @@ NULL information_schema PROCESSLIST HOST 3 NO varchar 64 192 NULL NULL utf8 utf NULL information_schema PROCESSLIST ID 1 0 NO bigint NULL NULL 19 0 NULL NULL bigint(4) select NULL information_schema PROCESSLIST INFO 8 NULL YES longtext 4294967295 4294967295 NULL NULL utf8 utf8_general_ci longtext select NULL information_schema PROCESSLIST STATE 7 NULL YES varchar 64 192 NULL NULL utf8 utf8_general_ci varchar(64) select -NULL information_schema PROCESSLIST TIME 6 0 NO bigint NULL NULL 19 0 NULL NULL bigint(7) select +NULL information_schema PROCESSLIST TIME 6 0 NO int NULL NULL 10 0 NULL NULL int(7) select NULL information_schema PROCESSLIST USER 2 NO varchar 16 48 NULL NULL utf8 utf8_general_ci varchar(16) select NULL information_schema REFERENTIAL_CONSTRAINTS CONSTRAINT_CATALOG 1 NULL YES varchar 512 1536 NULL NULL utf8 utf8_general_ci varchar(512) select NULL information_schema REFERENTIAL_CONSTRAINTS CONSTRAINT_NAME 3 NO varchar 64 192 NULL NULL utf8 utf8_general_ci varchar(64) select @@ -340,6 +340,7 @@ ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME NULL bigint NULL NULL NULL datetime NULL NULL +NULL int NULL NULL --> CHAR(0) is allowed (see manual), and here both CHARACHTER_* values --> are 0, which is intended behavior, and the result of 0 / 0 IS NULL SELECT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML, @@ -519,7 +520,7 @@ NULL information_schema PROCESSLIST ID bigint NULL NULL NULL NULL bigint(4) 3.0000 information_schema PROCESSLIST HOST varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema PROCESSLIST DB varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema PROCESSLIST COMMAND varchar 16 48 utf8 utf8_general_ci varchar(16) -NULL information_schema PROCESSLIST TIME bigint NULL NULL NULL NULL bigint(7) +NULL information_schema PROCESSLIST TIME int NULL NULL NULL NULL int(7) 3.0000 information_schema PROCESSLIST STATE varchar 64 192 utf8 utf8_general_ci varchar(64) 1.0000 information_schema PROCESSLIST INFO longtext 4294967295 4294967295 utf8 utf8_general_ci longtext 3.0000 information_schema REFERENTIAL_CONSTRAINTS CONSTRAINT_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512) diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result index 9d1f316a9bf..2f1f61c0525 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -48,7 +48,7 @@ NULL mysql event last_executed 10 NULL YES datetime NULL NULL NULL NULL NULL NUL NULL mysql event modified 9 0000-00-00 00:00:00 NO timestamp NULL NULL NULL NULL NULL NULL timestamp select,insert,update,references NULL mysql event name 2 NO char 64 192 NULL NULL utf8 utf8_general_ci char(64) PRI select,insert,update,references NULL mysql event on_completion 14 DROP NO enum 8 24 NULL NULL utf8 utf8_general_ci enum('DROP','PRESERVE') select,insert,update,references -NULL mysql event originator 17 NULL NO int NULL NULL 10 0 NULL NULL int(10) select,insert,update,references +NULL mysql event originator 17 NULL NO int NULL NULL 10 0 NULL NULL int(10) unsigned select,insert,update,references NULL mysql event sql_mode 15 NO set 478 1434 NULL NULL utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') select,insert,update,references NULL mysql event starts 11 NULL YES datetime NULL NULL NULL NULL NULL NULL datetime select,insert,update,references NULL mysql event status 13 ENABLED NO enum 18 54 NULL NULL utf8 utf8_general_ci enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') select,insert,update,references @@ -60,7 +60,7 @@ NULL mysql func type 4 NULL NO enum 9 27 NULL NULL utf8 utf8_general_ci enum('fu NULL mysql general_log argument 6 NULL NO mediumtext 16777215 16777215 NULL NULL utf8 utf8_general_ci mediumtext select,insert,update,references NULL mysql general_log command_type 5 NULL NO varchar 64 192 NULL NULL utf8 utf8_general_ci varchar(64) select,insert,update,references NULL mysql general_log event_time 1 CURRENT_TIMESTAMP NO timestamp NULL NULL NULL NULL NULL NULL timestamp on update CURRENT_TIMESTAMP select,insert,update,references -NULL mysql general_log server_id 4 NULL NO int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references +NULL mysql general_log server_id 4 NULL NO int NULL NULL 10 0 NULL NULL int(10) unsigned select,insert,update,references NULL mysql general_log thread_id 3 NULL NO int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references NULL mysql general_log user_host 2 NULL NO mediumtext 16777215 16777215 NULL NULL utf8 utf8_general_ci mediumtext select,insert,update,references NULL mysql help_category help_category_id 1 NULL NO smallint NULL NULL 5 0 NULL NULL smallint(5) unsigned PRI select,insert,update,references @@ -150,7 +150,7 @@ NULL mysql slow_log lock_time 4 NULL NO time NULL NULL NULL NULL NULL NULL time NULL mysql slow_log query_time 3 NULL NO time NULL NULL NULL NULL NULL NULL time select,insert,update,references NULL mysql slow_log rows_examined 6 NULL NO int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references NULL mysql slow_log rows_sent 5 NULL NO int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references -NULL mysql slow_log server_id 10 NULL NO int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references +NULL mysql slow_log server_id 10 NULL NO int NULL NULL 10 0 NULL NULL int(10) unsigned select,insert,update,references NULL mysql slow_log sql_text 11 NULL NO mediumtext 16777215 16777215 NULL NULL utf8 utf8_general_ci mediumtext select,insert,update,references NULL mysql slow_log start_time 1 CURRENT_TIMESTAMP NO timestamp NULL NULL NULL NULL NULL NULL timestamp on update CURRENT_TIMESTAMP select,insert,update,references NULL mysql slow_log user_host 2 NULL NO mediumtext 16777215 16777215 NULL NULL utf8 utf8_general_ci mediumtext select,insert,update,references @@ -329,7 +329,7 @@ NULL mysql event ends datetime NULL NULL NULL NULL datetime 3.0000 mysql event on_completion enum 8 24 utf8 utf8_general_ci enum('DROP','PRESERVE') 3.0000 mysql event sql_mode set 478 1434 utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') 3.0000 mysql event comment char 64 192 utf8 utf8_bin char(64) -NULL mysql event originator int NULL NULL NULL NULL int(10) +NULL mysql event originator int NULL NULL NULL NULL int(10) unsigned 1.0000 mysql event time_zone char 64 64 latin1 latin1_swedish_ci char(64) 3.0000 mysql event character_set_client char 32 96 utf8 utf8_bin char(32) 3.0000 mysql event collation_connection char 32 96 utf8 utf8_bin char(32) @@ -342,7 +342,7 @@ NULL mysql func ret tinyint NULL NULL NULL NULL tinyint(1) NULL mysql general_log event_time timestamp NULL NULL NULL NULL timestamp 1.0000 mysql general_log user_host mediumtext 16777215 16777215 utf8 utf8_general_ci mediumtext NULL mysql general_log thread_id int NULL NULL NULL NULL int(11) -NULL mysql general_log server_id int NULL NULL NULL NULL int(11) +NULL mysql general_log server_id int NULL NULL NULL NULL int(10) unsigned 3.0000 mysql general_log command_type varchar 64 192 utf8 utf8_general_ci varchar(64) 1.0000 mysql general_log argument mediumtext 16777215 16777215 utf8 utf8_general_ci mediumtext NULL mysql help_category help_category_id smallint NULL NULL NULL NULL smallint(5) unsigned @@ -434,7 +434,7 @@ NULL mysql slow_log rows_examined int NULL NULL NULL NULL int(11) 3.0000 mysql slow_log db varchar 512 1536 utf8 utf8_general_ci varchar(512) NULL mysql slow_log last_insert_id int NULL NULL NULL NULL int(11) NULL mysql slow_log insert_id int NULL NULL NULL NULL int(11) -NULL mysql slow_log server_id int NULL NULL NULL NULL int(11) +NULL mysql slow_log server_id int NULL NULL NULL NULL int(10) unsigned 1.0000 mysql slow_log sql_text mediumtext 16777215 16777215 utf8 utf8_general_ci mediumtext 3.0000 mysql tables_priv Host char 60 180 utf8 utf8_bin char(60) 3.0000 mysql tables_priv Db char 64 192 utf8 utf8_bin char(64) diff --git a/mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result b/mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result index b1e1cbbc4a9..275c356f753 100644 --- a/mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result +++ b/mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result @@ -27,7 +27,7 @@ PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', - `TIME` bigint(7) NOT NULL DEFAULT '0', + `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) DEFAULT CHARSET=utf8 @@ -97,7 +97,7 @@ PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', - `TIME` bigint(7) NOT NULL DEFAULT '0', + `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) DEFAULT CHARSET=utf8 diff --git a/mysql-test/suite/funcs_1/r/processlist_priv_ps.result b/mysql-test/suite/funcs_1/r/processlist_priv_ps.result index e9aef731ade..c5aabfe315c 100644 --- a/mysql-test/suite/funcs_1/r/processlist_priv_ps.result +++ b/mysql-test/suite/funcs_1/r/processlist_priv_ps.result @@ -27,7 +27,7 @@ PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', - `TIME` bigint(7) NOT NULL DEFAULT '0', + `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) DEFAULT CHARSET=utf8 @@ -97,7 +97,7 @@ PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', - `TIME` bigint(7) NOT NULL DEFAULT '0', + `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) DEFAULT CHARSET=utf8 diff --git a/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result b/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result index 6edcd98784d..0019c3997e5 100644 --- a/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result +++ b/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result @@ -17,7 +17,7 @@ PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', - `TIME` bigint(7) NOT NULL DEFAULT '0', + `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) DEFAULT CHARSET=utf8 diff --git a/mysql-test/suite/funcs_1/r/processlist_val_ps.result b/mysql-test/suite/funcs_1/r/processlist_val_ps.result index b9b1887d146..448c68eadb8 100644 --- a/mysql-test/suite/funcs_1/r/processlist_val_ps.result +++ b/mysql-test/suite/funcs_1/r/processlist_val_ps.result @@ -17,7 +17,7 @@ PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', - `TIME` bigint(7) NOT NULL DEFAULT '0', + `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) DEFAULT CHARSET=utf8 diff --git a/mysql-test/suite/ndb/r/ndb_restore.result b/mysql-test/suite/ndb/r/ndb_restore.result index 48c32bc14b6..f6b50a1dfdc 100644 --- a/mysql-test/suite/ndb/r/ndb_restore.result +++ b/mysql-test/suite/ndb/r/ndb_restore.result @@ -131,8 +131,9 @@ create table t9 engine=myisam as select * from t9_c; create table t10 engine=myisam as select * from t10_c; ForceVarPart: 0 ForceVarPart: 1 -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c,t10_c; ForceVarPart: 0 @@ -286,8 +287,9 @@ auto_increment 10001 ALTER TABLE t7_c PARTITION BY LINEAR KEY (`dardtestard`); -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c,t10_c; select count(*) from t1; @@ -490,8 +492,9 @@ select * from t9_c) a; count(*) 3 drop table t1_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c,t10_c; -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; drop table if exists t2_c; diff --git a/mysql-test/suite/ndb/r/ndb_restore_partition.result b/mysql-test/suite/ndb/r/ndb_restore_partition.result index 58a35437a2e..b984c76a91d 100644 --- a/mysql-test/suite/ndb/r/ndb_restore_partition.result +++ b/mysql-test/suite/ndb/r/ndb_restore_partition.result @@ -125,8 +125,9 @@ create table t6 engine=myisam as select * from t6_c; create table t7 engine=myisam as select * from t7_c; create table t8 engine=myisam as select * from t8_c; create table t9 engine=myisam as select * from t9_c; -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c; select count(*) from t1; @@ -244,8 +245,9 @@ PARTITION BY LINEAR HASH (`relatta`) PARTITIONS 4; ALTER TABLE t7_c PARTITION BY LINEAR KEY (`dardtestard`); -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c; select count(*) from t1; @@ -448,8 +450,9 @@ select * from t9_c) a; count(*) 3 drop table t1_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c; -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; Create table test/def/t2_c failed: Translate frm error drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/suite/ndb/r/ndb_restore_print.result b/mysql-test/suite/ndb/r/ndb_restore_print.result index 7ff15652b3f..fa52513e7d0 100644 --- a/mysql-test/suite/ndb/r/ndb_restore_print.result +++ b/mysql-test/suite/ndb/r/ndb_restore_print.result @@ -227,8 +227,9 @@ hex(h3) NULL hex(i1) NULL hex(i2) NULL hex(i3) NULL -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; 1;0x1;0x17;0x789A;0x789ABCDE;0xFEDC0001;127;255;32767;65535;2147483647;4294967295;9223372036854775807;18446744073709551615;1;12345678901234567890123456789012;123456789;1;12345678901234567890123456789012;123456789;0x12;0x123456789ABCDEF0;0x012345;0x12;0x123456789ABCDEF0;0x00123450 2;0x0;0x0;0x0;0x0;0x0;-128;0;-32768;0;-2147483648;0;-9223372036854775808;0;;;;;;;0x0;0x0;0x0;0x0;0x0;0x0 @@ -257,8 +258,9 @@ create table t4 (pk int key, a int) engine ndb; insert into t2 values (1,11),(2,12),(3,13),(4,14),(5,15); insert into t3 values (1,21),(2,22),(3,23),(4,24),(5,25); insert into t4 values (1,31),(2,32),(3,33),(4,34),(5,35); -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; '1' '1' '12345678901234567890123456789012' '123456789' '1' '12345678901234567890123456789012' '123456789' '0x20' '0x123456789ABCDEF020' '0x012345000020' '0x1200000020' '0x123456789ABCDEF000000020' '0x00123450000020' @@ -297,8 +299,9 @@ create table t1 insert into t1 values(1, 8388607, 16777215); insert into t1 values(2, -8388608, 0); insert into t1 values(3, -1, 1); -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; 1;8388607;16777215 2;-8388608;0 diff --git a/mysql-test/suite/ndb_team/r/ndb_dd_backuprestore.result b/mysql-test/suite/ndb_team/r/ndb_dd_backuprestore.result index 102a96a15f4..12a65a433a3 100644 --- a/mysql-test/suite/ndb_team/r/ndb_dd_backuprestore.result +++ b/mysql-test/suite/ndb_team/r/ndb_dd_backuprestore.result @@ -27,8 +27,9 @@ pk1 c2 c3 hex(c4) 3 Sweden 498 1 4 Sweden 497 1 5 Sweden 496 1 -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; DROP TABLE test.t1; ALTER TABLESPACE table_space1 @@ -91,8 +92,9 @@ LENGTH(data) SELECT LENGTH(data) FROM test.t4 WHERE c1 = 2; LENGTH(data) 16384 -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; DROP TABLE test.t1; DROP TABLE test.t2; @@ -317,8 +319,9 @@ pk1 c2 c3 hex(c4) 248 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 4 1 247 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 6 1 246 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 8 1 -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; DROP TABLE test.t1; DROP TABLE test.t2; diff --git a/mysql-test/suite/parts/r/ndb_dd_backuprestore.result b/mysql-test/suite/parts/r/ndb_dd_backuprestore.result index 102a96a15f4..12a65a433a3 100644 --- a/mysql-test/suite/parts/r/ndb_dd_backuprestore.result +++ b/mysql-test/suite/parts/r/ndb_dd_backuprestore.result @@ -27,8 +27,9 @@ pk1 c2 c3 hex(c4) 3 Sweden 498 1 4 Sweden 497 1 5 Sweden 496 1 -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; DROP TABLE test.t1; ALTER TABLESPACE table_space1 @@ -91,8 +92,9 @@ LENGTH(data) SELECT LENGTH(data) FROM test.t4 WHERE c1 = 2; LENGTH(data) 16384 -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; DROP TABLE test.t1; DROP TABLE test.t2; @@ -317,8 +319,9 @@ pk1 c2 c3 hex(c4) 248 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 4 1 247 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 6 1 246 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 8 1 -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; DROP TABLE test.t1; DROP TABLE test.t2; diff --git a/mysql-test/suite/rpl/r/rpl_do_grant.result b/mysql-test/suite/rpl/r/rpl_do_grant.result index f7f1ce66656..69bcfad4347 100644 --- a/mysql-test/suite/rpl/r/rpl_do_grant.result +++ b/mysql-test/suite/rpl/r/rpl_do_grant.result @@ -89,3 +89,81 @@ show grants for rpl_do_grant2@localhost; ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost' show grants for rpl_do_grant2@localhost; ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost' +DROP DATABASE IF EXISTS bug42217_db; +CREATE DATABASE bug42217_db; +GRANT CREATE ROUTINE ON bug42217_db.* TO 'create_rout_db'@'localhost' + IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION; +USE bug42217_db; +CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30) +BEGIN +RETURN "INSIDE upgrade_del_func()"; +END// +USE bug42217_db; +SELECT * FROM mysql.procs_priv; +Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp +localhost bug42217_db create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter Routine # +SELECT upgrade_del_func(); +upgrade_del_func() +INSIDE upgrade_del_func() +SELECT * FROM mysql.procs_priv; +Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp +localhost bug42217_db create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter Routine # +SHOW GRANTS FOR 'create_rout_db'@'localhost'; +Grants for create_rout_db@localhost +GRANT USAGE ON *.* TO 'create_rout_db'@'localhost' IDENTIFIED BY PASSWORD '*08792480350CBA057BDE781B9DF183B263934601' +GRANT CREATE ROUTINE ON `bug42217_db`.* TO 'create_rout_db'@'localhost' WITH GRANT OPTION +GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `bug42217_db`.`upgrade_del_func` TO 'create_rout_db'@'localhost' +USE bug42217_db; +SHOW CREATE FUNCTION upgrade_del_func; +Function sql_mode Create Function character_set_client collation_connection Database Collation +upgrade_del_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET latin1 +BEGIN +RETURN "INSIDE upgrade_del_func()"; +END latin1 latin1_swedish_ci latin1_swedish_ci +SELECT upgrade_del_func(); +upgrade_del_func() +INSIDE upgrade_del_func() +"Check whether the definer user will be able to execute the replicated routine on slave" +USE bug42217_db; +SHOW CREATE FUNCTION upgrade_del_func; +Function sql_mode Create Function character_set_client collation_connection Database Collation +upgrade_del_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET latin1 +BEGIN +RETURN "INSIDE upgrade_del_func()"; +END latin1 latin1_swedish_ci latin1_swedish_ci +SELECT upgrade_del_func(); +upgrade_del_func() +INSIDE upgrade_del_func() +DELETE FROM mysql.procs_priv; +FLUSH PRIVILEGES; +USE bug42217_db; +"Can't execute the replicated routine on slave like before after procs privilege is deleted " +SELECT upgrade_del_func(); +ERROR 42000: execute command denied to user 'create_rout_db'@'localhost' for routine 'bug42217_db.upgrade_del_func' +"Test the user who creates a function on master doesn't exist on slave." +"Hence SQL thread ACL_GLOBAL privilege jumps in and no mysql.procs_priv is inserted" +DROP USER 'create_rout_db'@'localhost'; +CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(30) +BEGIN +RETURN "INSIDE upgrade_alter_func()"; +END// +SELECT upgrade_alter_func(); +upgrade_alter_func() +INSIDE upgrade_alter_func() +SHOW CREATE FUNCTION upgrade_alter_func; +Function sql_mode Create Function character_set_client collation_connection Database Collation +upgrade_alter_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_alter_func`() RETURNS char(30) CHARSET latin1 +BEGIN +RETURN "INSIDE upgrade_alter_func()"; +END latin1 latin1_swedish_ci latin1_swedish_ci +"Should no privilege record for upgrade_alter_func in mysql.procs_priv" +SELECT * FROM mysql.procs_priv; +Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp +SELECT upgrade_alter_func(); +ERROR HY000: The user specified as a definer ('create_rout_db'@'localhost') does not exist +USE bug42217_db; +DROP FUNCTION upgrade_del_func; +DROP FUNCTION upgrade_alter_func; +DROP DATABASE bug42217_db; +DROP USER 'create_rout_db'@'localhost'; +"End of test" diff --git a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result b/mysql-test/suite/rpl/r/rpl_loaddatalocal.result index 96de55e9dcf..93ef33f3fc0 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result +++ b/mysql-test/suite/rpl/r/rpl_loaddatalocal.result @@ -29,3 +29,28 @@ a 2 3 drop table t1; +==== Bug22504 Initialize ==== +[on master] +SET sql_mode='ignore_space'; +CREATE TABLE t1(a int); +insert into t1 values (1), (2), (3), (4); +select * into outfile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1; +truncate table t1; +load data local infile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1; +SELECT * FROM t1 ORDER BY a; +a +1 +2 +3 +4 +[on slave] +SELECT * FROM t1 ORDER BY a; +a +1 +2 +3 +4 +==== Clean up ==== +[on master] +DROP TABLE t1; +[on slave] diff --git a/mysql-test/suite/rpl/r/rpl_loadfile.result b/mysql-test/suite/rpl/r/rpl_loadfile.result index 7a5a7bc50c9..22f65c0b5e5 100644 --- a/mysql-test/suite/rpl/r/rpl_loadfile.result +++ b/mysql-test/suite/rpl/r/rpl_loadfile.result @@ -225,3 +225,21 @@ aberration DROP PROCEDURE IF EXISTS test.p1; DROP TABLE test.t1; +**** Resetting master and slave **** +include/stop_slave.inc +RESET SLAVE; +RESET MASTER; +include/start_slave.inc +SELECT repeat('x',20) INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug_39701.data'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (t text); +CREATE PROCEDURE p(file varchar(4096)) +BEGIN +INSERT INTO t1 VALUES (LOAD_FILE(file)); +END| +include/stop_slave.inc +CALL p('MYSQLTEST_VARDIR/tmp/bug_39701.data'); +include/start_slave.inc +Comparing tables master:test.t1 and slave:test.t1 +DROP TABLE t1; +DROP PROCEDURE p; diff --git a/mysql-test/suite/rpl/r/rpl_rotate_logs.result b/mysql-test/suite/rpl/r/rpl_rotate_logs.result index 6314a9a61fb..013ba87ec0b 100644 --- a/mysql-test/suite/rpl/r/rpl_rotate_logs.result +++ b/mysql-test/suite/rpl/r/rpl_rotate_logs.result @@ -87,9 +87,7 @@ show binary logs; Log_name File_size master-bin.000002 # master-bin.000003 # -select @time_for_purge:=DATE_ADD(UPDATE_TIME, INTERVAL 1 SECOND) -from information_schema.tables -where TABLE_SCHEMA="test" and TABLE_NAME="t2"; +SELECT @time_for_purge:=DATE_ADD('tmpval', INTERVAL 1 SECOND); purge master logs before (@time_for_purge); show binary logs; Log_name File_size diff --git a/mysql-test/suite/rpl/r/rpl_row_wide_table.result b/mysql-test/suite/rpl/r/rpl_row_wide_table.result new file mode 100644 index 00000000000..da96e84d1d5 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_wide_table.result @@ -0,0 +1,318 @@ +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 TABLE IF EXISTS t300; +create table t300 ( +f1 int, +f2 int, +f3 int, +f4 int, +f5 int, +f6 int, +f7 int, +f8 int, +f9 int, +f10 int, +f11 int, +f12 int, +f13 int, +f14 int, +f15 int, +f16 int, +f17 int, +f18 int, +f19 int, +f20 int, +f21 int, +f22 int, +f23 int, +f24 int, +f25 int, +f26 int, +f27 int, +f28 int, +f29 int, +f30 int, +f31 int, +f32 int, +f33 int, +f34 int, +f35 int, +f36 int, +f37 int, +f38 int, +f39 int, +f40 int, +f41 int, +f42 int, +f43 int, +f44 int, +f45 int, +f46 int, +f47 int, +f48 int, +f49 int, +f50 int, +f51 int, +f52 int, +f53 int, +f54 int, +f55 int, +f56 int, +f57 int, +f58 int, +f59 int, +f60 int, +f61 int, +f62 int, +f63 int, +f64 int, +f65 int, +f66 int, +f67 int, +f68 int, +f69 int, +f70 int, +f71 int, +f72 int, +f73 int, +f74 int, +f75 int, +f76 int, +f77 int, +f78 int, +f79 int, +f80 int, +f81 int, +f82 int, +f83 int, +f84 int, +f85 int, +f86 int, +f87 int, +f88 int, +f89 int, +f90 int, +f91 int, +f92 int, +f93 int, +f94 int, +f95 int, +f96 int, +f97 int, +f98 int, +f99 int, +f100 int, +f101 int, +f102 int, +f103 int, +f104 int, +f105 int, +f106 int, +f107 int, +f108 int, +f109 int, +f110 int, +f111 int, +f112 int, +f113 int, +f114 int, +f115 int, +f116 int, +f117 int, +f118 int, +f119 int, +f120 int, +f121 int, +f122 int, +f123 int, +f124 int, +f125 int, +f126 int, +f127 int, +f128 int, +f129 int, +f130 int, +f131 int, +f132 int, +f133 int, +f134 int, +f135 int, +f136 int, +f137 int, +f138 int, +f139 int, +f140 int, +f141 int, +f142 int, +f143 int, +f144 int, +f145 int, +f146 int, +f147 int, +f148 int, +f149 int, +f150 int, +f151 int, +f152 int, +f153 int, +f154 int, +f155 int, +f156 int, +f157 int, +f158 int, +f159 int, +f160 int, +f161 int, +f162 int, +f163 int, +f164 int, +f165 int, +f166 int, +f167 int, +f168 int, +f169 int, +f170 int, +f171 int, +f172 int, +f173 int, +f174 int, +f175 int, +f176 int, +f177 int, +f178 int, +f179 int, +f180 int, +f181 int, +f182 int, +f183 int, +f184 int, +f185 int, +f186 int, +f187 int, +f188 int, +f189 int, +f190 int, +f191 int, +f192 int, +f193 int, +f194 int, +f195 int, +f196 int, +f197 int, +f198 int, +f199 int, +f200 int, +f201 int, +f202 int, +f203 int, +f204 int, +f205 int, +f206 int, +f207 int, +f208 int, +f209 int, +f210 int, +f211 int, +f212 int, +f213 int, +f214 int, +f215 int, +f216 int, +f217 int, +f218 int, +f219 int, +f220 int, +f221 int, +f222 int, +f223 int, +f224 int, +f225 int, +f226 int, +f227 int, +f228 int, +f229 int, +f230 int, +f231 int, +f232 int, +f233 int, +f234 int, +f235 int, +f236 int, +f237 int, +f238 int, +f239 int, +f240 int, +f241 int, +f242 int, +f243 int, +f244 int, +f245 int, +f246 int, +f247 int, +f248 int, +f249 int, +f250 int, +f251 int, +f252 int, +f253 int, +f254 int, +f255 int, +f256 int, +f257 int, +f258 int, +f259 int, +f260 int, +f261 int, +f262 int, +f263 int, +f264 int, +f265 int, +f266 int, +f267 int, +f268 int, +f269 int, +f270 int, +f271 int, +f272 int, +f273 int, +f274 int, +f275 int, +f276 int, +f277 int, +f278 int, +f279 int, +f280 int, +f281 int, +f282 int, +f283 int, +f284 int, +f285 int, +f286 int, +f287 int, +f288 int, +f289 int, +f290 int, +f291 int, +f292 int, +f293 int, +f294 int, +f295 int, +f296 int, +f297 int, +f298 int, +f299 int, +f300 int, +primary key (f1)); +insert into t300 set f1= 1; +select f300 from t300; +f300 +NULL +select count(*) as one from t300; +one +1 +*** Cleanup *** +DROP TABLE t300; diff --git a/mysql-test/suite/rpl/r/rpl_slave_load_in.result b/mysql-test/suite/rpl/r/rpl_slave_load_in.result index 0685d024f26..2cc83fd0a19 100644 --- a/mysql-test/suite/rpl/r/rpl_slave_load_in.result +++ b/mysql-test/suite/rpl/r/rpl_slave_load_in.result @@ -5,6 +5,15 @@ reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; create table t1(a int not null auto_increment, b int, primary key(a)); +create table t2(a int not null auto_increment, b int, primary key(a)) engine=innodb; load data infile '../../std_data/rpl_loaddata.dat' into table t1; +start transaction; +insert into t2(b) values (1); +insert into t2(b) values (2); +load data infile '../../std_data/rpl_loaddata.dat' into table t2; +load data infile '../../std_data/rpl_loaddata.dat' into table t2; +commit; Comparing tables master:test.t1 and slave:test.t1 +Comparing tables master:test.t2 and slave:test.t2 drop table t1; +drop table t2; diff --git a/mysql-test/suite/rpl/r/rpl_slave_load_remove_tmpfile.result b/mysql-test/suite/rpl/r/rpl_slave_load_remove_tmpfile.result new file mode 100644 index 00000000000..777f7d8427b --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_slave_load_remove_tmpfile.result @@ -0,0 +1,53 @@ +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(a int not null auto_increment, b int, primary key(a)) engine=innodb; +start transaction; +insert into t1(b) values (1); +insert into t1(b) values (2); +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +commit; +show slave status; +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_MYPORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos # +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running No +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 9 +Last_Error Error in Begin_load_query event: write to '../../tmp/SQL_LOAD.data' failed +Skip_Counter 0 +Exec_Master_Log_Pos # +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +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 No +Last_IO_Errno # +Last_IO_Error # +Last_SQL_Errno 9 +Last_SQL_Error Error in Begin_load_query event: write to '../../tmp/SQL_LOAD.data' failed +drop table t1; +drop table t1; diff --git a/mysql-test/suite/rpl/r/rpl_slave_load_tmpdir_not_exist.result b/mysql-test/suite/rpl/r/rpl_slave_load_tmpdir_not_exist.result new file mode 100644 index 00000000000..a158fb5dfc4 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_slave_load_tmpdir_not_exist.result @@ -0,0 +1,6 @@ +CHANGE MASTER TO MASTER_USER='root', +MASTER_CONNECT_RETRY=1, +MASTER_HOST='127.0.0.1', +MASTER_PORT=MASTER_MYPORT; +START SLAVE; +Unable to use slave's temporary directory ../../../error - Can't read dir of '../../../error' (Errcode: 2) diff --git a/mysql-test/suite/rpl/r/rpl_stm_loadfile.result b/mysql-test/suite/rpl/r/rpl_stm_loadfile.result new file mode 100644 index 00000000000..d18befe6e4c --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_stm_loadfile.result @@ -0,0 +1,231 @@ +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 PROCEDURE IF EXISTS test.p1; +DROP TABLE IF EXISTS test.t1; +CREATE TABLE test.t1 (a INT, blob_column LONGBLOB, PRIMARY KEY(a)); +INSERT INTO test.t1 VALUES(1,'test'); +UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=1; +Warnings: +Warning 1592 Statement is not safe to log in statement format. +create procedure test.p1() +begin +INSERT INTO test.t1 VALUES(2,'test'); +UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=2; +end| +CALL test.p1(); +Warnings: +Warning 1592 Statement is not safe to log in statement format. +SELECT * FROM test.t1 ORDER BY blob_column; +a blob_column +1 abase +abased +abasement +abasements +abases +abash +abashed +abashes +abashing +abasing +abate +abated +abatement +abatements +abater +abates +abating +Abba +abbe +abbey +abbeys +abbot +abbots +Abbott +abbreviate +abbreviated +abbreviates +abbreviating +abbreviation +abbreviations +Abby +abdomen +abdomens +abdominal +abduct +abducted +abduction +abductions +abductor +abductors +abducts +Abe +abed +Abel +Abelian +Abelson +Aberdeen +Abernathy +aberrant +aberration + +2 abase +abased +abasement +abasements +abases +abash +abashed +abashes +abashing +abasing +abate +abated +abatement +abatements +abater +abates +abating +Abba +abbe +abbey +abbeys +abbot +abbots +Abbott +abbreviate +abbreviated +abbreviates +abbreviating +abbreviation +abbreviations +Abby +abdomen +abdomens +abdominal +abduct +abducted +abduction +abductions +abductor +abductors +abducts +Abe +abed +Abel +Abelian +Abelson +Aberdeen +Abernathy +aberrant +aberration + +SELECT * FROM test.t1 ORDER BY blob_column; +a blob_column +1 abase +abased +abasement +abasements +abases +abash +abashed +abashes +abashing +abasing +abate +abated +abatement +abatements +abater +abates +abating +Abba +abbe +abbey +abbeys +abbot +abbots +Abbott +abbreviate +abbreviated +abbreviates +abbreviating +abbreviation +abbreviations +Abby +abdomen +abdomens +abdominal +abduct +abducted +abduction +abductions +abductor +abductors +abducts +Abe +abed +Abel +Abelian +Abelson +Aberdeen +Abernathy +aberrant +aberration + +2 abase +abased +abasement +abasements +abases +abash +abashed +abashes +abashing +abasing +abate +abated +abatement +abatements +abater +abates +abating +Abba +abbe +abbey +abbeys +abbot +abbots +Abbott +abbreviate +abbreviated +abbreviates +abbreviating +abbreviation +abbreviations +Abby +abdomen +abdomens +abdominal +abduct +abducted +abduction +abductions +abductor +abductors +abducts +Abe +abed +Abel +Abelian +Abelson +Aberdeen +Abernathy +aberrant +aberration + +DROP PROCEDURE IF EXISTS test.p1; +DROP TABLE test.t1; diff --git a/mysql-test/suite/rpl/r/rpl_stm_log.result b/mysql-test/suite/rpl/r/rpl_stm_log.result index 715d4976a95..bcefc6f9d3d 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_log.result +++ b/mysql-test/suite/rpl/r/rpl_stm_log.result @@ -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 ignore 1 lines ;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/r/rpl_temporary.result b/mysql-test/suite/rpl/r/rpl_temporary.result index 568d5368adb..8a9ddaec9f6 100644 --- a/mysql-test/suite/rpl/r/rpl_temporary.result +++ b/mysql-test/suite/rpl/r/rpl_temporary.result @@ -108,3 +108,16 @@ select * from t1; a 1 drop table t1; +-- Bug#43748 +-- make a user on the slave that can list but not kill system threads. +FLUSH PRIVILEGES; +GRANT USAGE ON *.* TO user43748@127.0.0.1 IDENTIFIED BY 'meow'; +GRANT PROCESS ON *.* TO user43748@127.0.0.1; +-- try to KILL system-thread as that non-privileged user (on slave). +SELECT id INTO @id FROM information_schema.processlist WHERE user='system user' LIMIT 1; +KILL @id; +Got one of the listed errors +-- throw out test-user on slave. +DROP USER user43748@127.0.0.1; +-- done. back to master. +End of 5.1 tests diff --git a/mysql-test/suite/rpl/r/rpl_timezone.result b/mysql-test/suite/rpl/r/rpl_timezone.result index 927756dd530..0b5c03b5300 100644 --- a/mysql-test/suite/rpl/r/rpl_timezone.result +++ b/mysql-test/suite/rpl/r/rpl_timezone.result @@ -122,4 +122,23 @@ a b SET @@session.time_zone = default; DROP TABLE t1; SET @@session.time_zone = default; +reset master; +CREATE TABLE t1 (date timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, a int(11) default NULL); +SET @@session.time_zone='+01:00'; +insert into t1 values('2008-12-23 19:39:39',1); +SET @@session.time_zone='+02:00'; +insert delayed into t1 values ('2008-12-23 19:39:39',2); +flush table t1; +flush logs; +select * from t1; +date a +2008-12-23 20:39:39 1 +2008-12-23 19:39:39 2 +DROP TABLE t1; +select * from t1 order by a; +date a +2008-12-23 20:39:39 1 +2008-12-23 19:39:39 2 +DROP TABLE t1; +SET @@session.time_zone = default; End of 5.0 tests diff --git a/mysql-test/suite/rpl/t/disabled.def b/mysql-test/suite/rpl/t/disabled.def index f3f329c4b5b..b5f158b2257 100644 --- a/mysql-test/suite/rpl/t/disabled.def +++ b/mysql-test/suite/rpl/t/disabled.def @@ -11,3 +11,5 @@ ############################################################################## rpl_binlog_corruption : BUG#41793 2008-12-30 sven rpl_binlog_corruption disabled in main (needs new mtr) +rpl_temp_table_mix_row : BUG#43440 2009-03-23 joro rpl.rpl_temp_table_mix_row fails sporadicly +rpl_cross_version : BUG#42311 2009-03-27 joro rpl_cross_version fails on macosx diff --git a/mysql-test/suite/rpl/t/rpl_do_grant.test b/mysql-test/suite/rpl/t/rpl_do_grant.test index 5615900c2dd..806de780086 100644 --- a/mysql-test/suite/rpl/t/rpl_do_grant.test +++ b/mysql-test/suite/rpl/t/rpl_do_grant.test @@ -112,3 +112,100 @@ show grants for rpl_do_grant2@localhost; sync_slave_with_master; --error 1141 show grants for rpl_do_grant2@localhost; + +##################################################### +# Purpose +# Test whether mysql.procs_priv get replicated +# Related bugs: +# BUG42217 mysql.procs_priv does not get replicated +##################################################### +connection master; + +--disable_warnings +DROP DATABASE IF EXISTS bug42217_db; +--enable_warnings +CREATE DATABASE bug42217_db; + +GRANT CREATE ROUTINE ON bug42217_db.* TO 'create_rout_db'@'localhost' + IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION; + +connect (create_rout_db_master, localhost, create_rout_db, create_rout_db, bug42217_db,$MASTER_MYPORT,); +connect (create_rout_db_slave, localhost, create_rout_db, create_rout_db, bug42217_db, $SLAVE_MYPORT,); + +connection create_rout_db_master; + + +USE bug42217_db; + +DELIMITER //; +CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30) +BEGIN + RETURN "INSIDE upgrade_del_func()"; +END// + +DELIMITER ;// + +connection master; + +USE bug42217_db; +--replace_column 8 # +SELECT * FROM mysql.procs_priv; +SELECT upgrade_del_func(); + +sync_slave_with_master; +--replace_column 8 # +SELECT * FROM mysql.procs_priv; +SHOW GRANTS FOR 'create_rout_db'@'localhost'; + +USE bug42217_db; +SHOW CREATE FUNCTION upgrade_del_func; +SELECT upgrade_del_func(); + +--echo "Check whether the definer user will be able to execute the replicated routine on slave" +connection create_rout_db_slave; +USE bug42217_db; +SHOW CREATE FUNCTION upgrade_del_func; +SELECT upgrade_del_func(); + +connection slave; +DELETE FROM mysql.procs_priv; +FLUSH PRIVILEGES; +USE bug42217_db; +--echo "Can't execute the replicated routine on slave like before after procs privilege is deleted " +--error 1370 +SELECT upgrade_del_func(); + +--echo "Test the user who creates a function on master doesn't exist on slave." +--echo "Hence SQL thread ACL_GLOBAL privilege jumps in and no mysql.procs_priv is inserted" +DROP USER 'create_rout_db'@'localhost'; + +connection create_rout_db_master; +DELIMITER //; +CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(30) +BEGIN + RETURN "INSIDE upgrade_alter_func()"; +END// +DELIMITER ;// + +connection master; +SELECT upgrade_alter_func(); + +sync_slave_with_master; +SHOW CREATE FUNCTION upgrade_alter_func; +--echo "Should no privilege record for upgrade_alter_func in mysql.procs_priv" +--replace_column 8 # +SELECT * FROM mysql.procs_priv; +--error 1449 +SELECT upgrade_alter_func(); + +###### CLEAN UP SECTION ############## +disconnect create_rout_db_master; +disconnect create_rout_db_slave; +connection master; +USE bug42217_db; +DROP FUNCTION upgrade_del_func; +DROP FUNCTION upgrade_alter_func; +DROP DATABASE bug42217_db; +DROP USER 'create_rout_db'@'localhost'; + +--echo "End of test" diff --git a/mysql-test/suite/rpl/t/rpl_loaddatalocal.test b/mysql-test/suite/rpl/t/rpl_loaddatalocal.test index 0de402f301a..23c802ab3de 100644 --- a/mysql-test/suite/rpl/t/rpl_loaddatalocal.test +++ b/mysql-test/suite/rpl/t/rpl_loaddatalocal.test @@ -64,3 +64,37 @@ drop table t1; save_master_pos; connection slave; sync_with_master; + + +# +# Bug22504 load data infile sql statement in replication architecture get error +# +--echo ==== Bug22504 Initialize ==== + +--echo [on master] +--connection master + +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/rpl_loaddatalocal.select_outfile' from t1; +truncate table t1; +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval load data local infile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1; +--remove_file $MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile +SELECT * FROM t1 ORDER BY a; + +--echo [on slave] +sync_slave_with_master; +SELECT * FROM t1 ORDER BY a; + +--echo ==== Clean up ==== + +--echo [on master] +connection master; +DROP TABLE t1; + +--echo [on slave] +sync_slave_with_master; + diff --git a/mysql-test/suite/rpl/t/rpl_loadfile.test b/mysql-test/suite/rpl/t/rpl_loadfile.test index 5aa4da41fe5..adb23d2c2bc 100644 --- a/mysql-test/suite/rpl/t/rpl_loadfile.test +++ b/mysql-test/suite/rpl/t/rpl_loadfile.test @@ -11,43 +11,105 @@ # Includes -- source include/master-slave.inc +-- source include/have_binlog_format_mixed_or_row.inc +-- source extra/rpl_tests/rpl_loadfile.test + +# BUG#39701: Mixed binlog format does not switch to row mode on LOAD_FILE +# +# DESCRIPTION +# +# Problem: when using load_file string function and mixed binlogging format +# there was no switch to row based binlogging format. This leads +# to scenarios on which the slave replicates the statement and it +# will try to load the file from local file system, which in most +# likely it will not exist. +# +# Solution: +# Marking this function as unsafe for statement format, makes the +# statement using it to be logged in row based format. As such, data +# replicated from the master, becomes the content of the loaded file. +# Consequently, the slave receives the necessary data to complete +# the load_file instruction correctly. +# +# IMPLEMENTATION +# +# The test is implemented as follows: +# +# On Master, +# i) write to file the desired content. +# ii) create table and stored procedure with load_file +# iii) stop slave +# iii) execute load_file +# iv) remove file +# +# On Slave, +# v) start slave +# vi) sync it with master so that it gets the updates from binlog (which +# should have bin logged in row format). +# +# If the the binlog format does not change to row, then the assertion +# done in the following step fails. This happens because tables differ +# since the file does not exist anymore, meaning that when slave +# attempts to execute LOAD_FILE statement it inserts NULL on table +# instead of the same contents that the master loaded when it executed +# the procedure (which was executed when file existed). +# +# vii) assert that the contents of master and slave +# table are the same -# Begin clean up test section ---disable_warnings connection master; -DROP PROCEDURE IF EXISTS test.p1; -DROP TABLE IF EXISTS test.t1; ---enable_warnings - -# Section 1 test - -CREATE TABLE test.t1 (a INT, blob_column LONGBLOB, PRIMARY KEY(a)); -INSERT INTO test.t1 VALUES(1,'test'); -UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=1; -delimiter |; -create procedure test.p1() -begin - INSERT INTO test.t1 VALUES(2,'test'); - UPDATE test.t1 SET blob_column=LOAD_FILE('../../std_data/words2.dat') WHERE a=2; -end| -delimiter ;| - -CALL test.p1(); -SELECT * FROM test.t1 ORDER BY blob_column; -save_master_pos; -sync_slave_with_master; +source include/reset_master_and_slave.inc; + +connection master; +let $file= $MYSQLTEST_VARDIR/tmp/bug_39701.data; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT repeat('x',20) INTO OUTFILE '$file' + +disable_warnings; +DROP TABLE IF EXISTS t1; +enable_warnings; + +CREATE TABLE t1 (t text); +DELIMITER |; +CREATE PROCEDURE p(file varchar(4096)) + BEGIN + INSERT INTO t1 VALUES (LOAD_FILE(file)); + END| +DELIMITER ;| + +# stop slave before issuing the load_file on master connection slave; -# Need to allow some time when NDB engine is used for -# the injector thread to have time to populate binlog -let $wait_condition= SELECT INSTR(blob_column,'aberration') > 0 FROM test.t1 WHERE a = 2; ---source include/wait_condition.inc -SELECT * FROM test.t1 ORDER BY blob_column; +source include/stop_slave.inc; + +connection master; + +# test: check that logging falls back to rbr. +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval CALL p('$file') + +# test: remove the file from the filesystem and assert that slave still +# gets the loaded file +remove_file $file; + +# now that the file is removed it is safe (regarding what we want to test) +# to start slave +connection slave; +source include/start_slave.inc; -# Cleanup connection master; -DROP PROCEDURE IF EXISTS test.p1; -DROP TABLE test.t1; sync_slave_with_master; -# End of 5.0 test case +# assertion: assert that the slave got the updates even +# if the file was removed before the slave started, +# meaning that contents were indeed transfered +# through binlog (in row format) +let $diff_table_1=master:test.t1; +let $diff_table_2=slave:test.t1; +source include/diff_tables.inc; + +# CLEAN UP +DROP TABLE t1; +DROP PROCEDURE p; +sync_slave_with_master; diff --git a/mysql-test/suite/rpl/t/rpl_rotate_logs.test b/mysql-test/suite/rpl/t/rpl_rotate_logs.test index 2bad7b27272..e06099fd707 100644 --- a/mysql-test/suite/rpl/t/rpl_rotate_logs.test +++ b/mysql-test/suite/rpl/t/rpl_rotate_logs.test @@ -112,14 +112,24 @@ source include/show_master_logs.inc; purge binary logs to 'master-bin.000002'; source include/show_binary_logs.inc; -# Calculate time to use in "purge master logs before" by taking -# last modification time of t2 and adding 1 second -# This is donw in order to handle the case where file system -# time differs from mysqld's time +# Set the purge time 1 second after the last modify time of master-bin.000002. +perl; +open F, ">>".$ENV{'MYSQLTEST_VARDIR'}.'/tmp/rpl_rotate_logs.tmp' or die "Tmp file rpl_rotate_logs.tmp not found"; +my $binlogpath = $ENV{'MYSQLTEST_VARDIR'}.'/mysqld.1/data/master-bin.000002'; +my @array = stat($binlogpath); +my $filemodifytime = $array[9]; +my @t = localtime $filemodifytime; +my $modifytime = sprintf "%04u-%02u-%02u %02u:%02u:%02u",$t[5]+1900,$t[4]+1,$t[3],$t[2],$t[1],$t[0]; +printf F ("let \$tmpval = %s;",$modifytime); +close F; +EOF + +--source $MYSQLTEST_VARDIR/tmp/rpl_rotate_logs.tmp +remove_file $MYSQLTEST_VARDIR/tmp/rpl_rotate_logs.tmp; + --disable_result_log -select @time_for_purge:=DATE_ADD(UPDATE_TIME, INTERVAL 1 SECOND) - from information_schema.tables - where TABLE_SCHEMA="test" and TABLE_NAME="t2"; +--replace_result $tmpval tmpval +--eval SELECT @time_for_purge:=DATE_ADD('$tmpval', INTERVAL 1 SECOND) --enable_result_log purge master logs before (@time_for_purge); diff --git a/mysql-test/suite/rpl/t/rpl_row_wide_table.test b/mysql-test/suite/rpl/t/rpl_row_wide_table.test new file mode 100644 index 00000000000..7b17d7c4866 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_wide_table.test @@ -0,0 +1,339 @@ +################################################################## +# rpl_row_wide_table +# +# This test verifies that the table with number of attributes more +# than 250 is replicated. +# Related bugs: +# Bug #42977 RBR logs for rows with more than 250 column results +# in corrupt binlog +################################################################## + +-- source include/master-slave.inc +-- source include/have_binlog_format_row.inc + +--disable_warnings +DROP TABLE IF EXISTS t300; +--enable_warnings + +connection master; + +create table t300 ( +f1 int, +f2 int, +f3 int, +f4 int, +f5 int, +f6 int, +f7 int, +f8 int, +f9 int, +f10 int, +f11 int, +f12 int, +f13 int, +f14 int, +f15 int, +f16 int, +f17 int, +f18 int, +f19 int, +f20 int, +f21 int, +f22 int, +f23 int, +f24 int, +f25 int, +f26 int, +f27 int, +f28 int, +f29 int, +f30 int, +f31 int, +f32 int, +f33 int, +f34 int, +f35 int, +f36 int, +f37 int, +f38 int, +f39 int, +f40 int, +f41 int, +f42 int, +f43 int, +f44 int, +f45 int, +f46 int, +f47 int, +f48 int, +f49 int, +f50 int, +f51 int, +f52 int, +f53 int, +f54 int, +f55 int, +f56 int, +f57 int, +f58 int, +f59 int, +f60 int, +f61 int, +f62 int, +f63 int, +f64 int, +f65 int, +f66 int, +f67 int, +f68 int, +f69 int, +f70 int, +f71 int, +f72 int, +f73 int, +f74 int, +f75 int, +f76 int, +f77 int, +f78 int, +f79 int, +f80 int, +f81 int, +f82 int, +f83 int, +f84 int, +f85 int, +f86 int, +f87 int, +f88 int, +f89 int, +f90 int, +f91 int, +f92 int, +f93 int, +f94 int, +f95 int, +f96 int, +f97 int, +f98 int, +f99 int, +f100 int, +f101 int, +f102 int, +f103 int, +f104 int, +f105 int, +f106 int, +f107 int, +f108 int, +f109 int, +f110 int, +f111 int, +f112 int, +f113 int, +f114 int, +f115 int, +f116 int, +f117 int, +f118 int, +f119 int, +f120 int, +f121 int, +f122 int, +f123 int, +f124 int, +f125 int, +f126 int, +f127 int, +f128 int, +f129 int, +f130 int, +f131 int, +f132 int, +f133 int, +f134 int, +f135 int, +f136 int, +f137 int, +f138 int, +f139 int, +f140 int, +f141 int, +f142 int, +f143 int, +f144 int, +f145 int, +f146 int, +f147 int, +f148 int, +f149 int, +f150 int, +f151 int, +f152 int, +f153 int, +f154 int, +f155 int, +f156 int, +f157 int, +f158 int, +f159 int, +f160 int, +f161 int, +f162 int, +f163 int, +f164 int, +f165 int, +f166 int, +f167 int, +f168 int, +f169 int, +f170 int, +f171 int, +f172 int, +f173 int, +f174 int, +f175 int, +f176 int, +f177 int, +f178 int, +f179 int, +f180 int, +f181 int, +f182 int, +f183 int, +f184 int, +f185 int, +f186 int, +f187 int, +f188 int, +f189 int, +f190 int, +f191 int, +f192 int, +f193 int, +f194 int, +f195 int, +f196 int, +f197 int, +f198 int, +f199 int, +f200 int, +f201 int, +f202 int, +f203 int, +f204 int, +f205 int, +f206 int, +f207 int, +f208 int, +f209 int, +f210 int, +f211 int, +f212 int, +f213 int, +f214 int, +f215 int, +f216 int, +f217 int, +f218 int, +f219 int, +f220 int, +f221 int, +f222 int, +f223 int, +f224 int, +f225 int, +f226 int, +f227 int, +f228 int, +f229 int, +f230 int, +f231 int, +f232 int, +f233 int, +f234 int, +f235 int, +f236 int, +f237 int, +f238 int, +f239 int, +f240 int, +f241 int, +f242 int, +f243 int, +f244 int, +f245 int, +f246 int, +f247 int, +f248 int, +f249 int, +f250 int, +f251 int, +f252 int, +f253 int, +f254 int, +f255 int, +f256 int, +f257 int, +f258 int, +f259 int, +f260 int, +f261 int, +f262 int, +f263 int, +f264 int, +f265 int, +f266 int, +f267 int, +f268 int, +f269 int, +f270 int, +f271 int, +f272 int, +f273 int, +f274 int, +f275 int, +f276 int, +f277 int, +f278 int, +f279 int, +f280 int, +f281 int, +f282 int, +f283 int, +f284 int, +f285 int, +f286 int, +f287 int, +f288 int, +f289 int, +f290 int, +f291 int, +f292 int, +f293 int, +f294 int, +f295 int, +f296 int, +f297 int, +f298 int, +f299 int, +f300 int, + primary key (f1)); + +insert into t300 set f1= 1; + +sync_slave_with_master; + +# +# prove that slave processed the create as well as the insert +# +eval select f300 from t300; +select count(*) as one from t300; + +--echo *** Cleanup *** +connection master; +DROP TABLE t300; +sync_slave_with_master; + +# END of Test Case + diff --git a/mysql-test/suite/rpl/t/rpl_slave_load_in.test b/mysql-test/suite/rpl/t/rpl_slave_load_in.test index f920c136d43..54ebdffce69 100644 --- a/mysql-test/suite/rpl/t/rpl_slave_load_in.test +++ b/mysql-test/suite/rpl/t/rpl_slave_load_in.test @@ -3,10 +3,11 @@ # event while the "--secure-file-priv" option is set. # # The test is divided in two steps: -# 1 - Creates a table and populates it through "LOAD DATA INFILE". +# 1 - Creates tables and populates them through "LOAD DATA INFILE". # 2 - Compares the master and slave. ########################################################################## -source include/master-slave.inc; +--source include/have_innodb.inc +--source include/master-slave.inc ########################################################################## # Loading data @@ -14,8 +15,17 @@ source include/master-slave.inc; connection master; create table t1(a int not null auto_increment, b int, primary key(a)); +create table t2(a int not null auto_increment, b int, primary key(a)) engine=innodb; + load data infile '../../std_data/rpl_loaddata.dat' into table t1; +start transaction; + insert into t2(b) values (1); + insert into t2(b) values (2); + load data infile '../../std_data/rpl_loaddata.dat' into table t2; + load data infile '../../std_data/rpl_loaddata.dat' into table t2; +commit; + ########################################################################## # Checking Consistency ########################################################################## @@ -25,11 +35,16 @@ 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; + ########################################################################## # Clean up ########################################################################## connection master; drop table t1; +drop table t2; sync_slave_with_master; diff --git a/mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile-slave.opt b/mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile-slave.opt new file mode 100644 index 00000000000..51e410f911f --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile-slave.opt @@ -0,0 +1 @@ +--loose-debug=d,remove_slave_load_file_before_write diff --git a/mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile.test b/mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile.test new file mode 100644 index 00000000000..39f3b700f94 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile.test @@ -0,0 +1,49 @@ +########################################################################## +# This test verifies if the slave fails gracefully when the temporary +# file used to load data is removed while it is about to be used it. +# Similar errors are caught if the temporary directory is removed. +# +# Steps: +# 1 - Creates a table and populates it through "LOAD DATA INFILE". +# 2 - Catches error. +########################################################################## +--source include/have_binlog_format_mixed_or_statement.inc +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/master-slave.inc +--source include/not_embedded.inc + +########################################################################## +# Loading data +########################################################################## +connection master; + +create table t1(a int not null auto_increment, b int, primary key(a)) engine=innodb; + +start transaction; + insert into t1(b) values (1); + insert into t1(b) values (2); + load data infile '../../std_data/rpl_loaddata.dat' into table t1; +commit; + +########################################################################## +# Catch Error +########################################################################## +connection slave; +source include/wait_for_slave_sql_to_stop.inc; + +--replace_result $MASTER_MYPORT MASTER_MYPORT +--replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 # 35 # 36 # +--replace_regex /SQL_LOAD-[0-9]-[0-9]-[0-9]*/SQL_LOAD/ +query_vertical show slave status; + +########################################################################## +# Clean up +########################################################################## +connection master; + +drop table t1; + +connection slave; + +drop table t1; diff --git a/mysql-test/suite/rpl/t/rpl_slave_load_tmpdir_not_exist-slave.opt b/mysql-test/suite/rpl/t/rpl_slave_load_tmpdir_not_exist-slave.opt new file mode 100644 index 00000000000..c4f91e97e3e --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_slave_load_tmpdir_not_exist-slave.opt @@ -0,0 +1 @@ +--slave-load-tmpdir=../../../error diff --git a/mysql-test/suite/rpl/t/rpl_slave_load_tmpdir_not_exist.test b/mysql-test/suite/rpl/t/rpl_slave_load_tmpdir_not_exist.test new file mode 100644 index 00000000000..3a80fa43f20 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_slave_load_tmpdir_not_exist.test @@ -0,0 +1,24 @@ +########################################################################## +# This test verifies if the start slave fails gracefuly when an +# invalid directory is used to set --slave-load-tmpdir. +########################################################################## +--source include/have_log_bin.inc +--source include/not_embedded.inc + +connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,); +connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,); +connect (slave,127.0.0.1,root,,test,$SLAVE_MYPORT,); +connect (slave1,127.0.0.1,root,,test,$SLAVE_MYPORT,); + +connection slave; + +--replace_result $MASTER_MYPORT MASTER_MYPORT +eval CHANGE MASTER TO MASTER_USER='root', + MASTER_CONNECT_RETRY=1, + MASTER_HOST='127.0.0.1', + MASTER_PORT=$MASTER_MYPORT; +START SLAVE; + +source include/wait_for_slave_sql_to_stop.inc; +let $error=query_get_value("show slave status", Last_SQL_Error, 1); +echo $error; diff --git a/mysql-test/suite/rpl/t/rpl_stm_loadfile.test b/mysql-test/suite/rpl/t/rpl_stm_loadfile.test new file mode 100644 index 00000000000..3db8385a2f2 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_stm_loadfile.test @@ -0,0 +1,20 @@ +############################################################################# +# Original Author: JBM # +# Original Date: Aug/18/2005 # +############################################################################# +# TEST: To test the LOAD_FILE() in rbr # +############################################################################# +# Change Author: JBM +# Change Date: 2006-01-16 +# Change: Added Order by for NDB +# Change: Split the original test file. This one forces STATEMENT only because +# when in STATEMENT mode, the load_file will issue a warning, whereas +# in RBR or MIXED mode it does not (by lsoares). +########## + +# Includes +-- source include/master-slave.inc +-- source include/have_binlog_format_statement.inc + +-- source extra/rpl_tests/rpl_loadfile.test + diff --git a/mysql-test/suite/rpl/t/rpl_temporary.test b/mysql-test/suite/rpl/t/rpl_temporary.test index 0bf3ecf97a2..4e83d39710c 100644 --- a/mysql-test/suite/rpl/t/rpl_temporary.test +++ b/mysql-test/suite/rpl/t/rpl_temporary.test @@ -222,4 +222,40 @@ drop table t1; # Delete the anonymous users source include/delete_anonymous_users.inc; -# End of tests + + +# +# Bug#43748: crash when non-super user tries to kill the replication threads +# + +--echo -- Bug#43748 + +--echo -- make a user on the slave that can list but not kill system threads. +connection slave; + +FLUSH PRIVILEGES; +GRANT USAGE ON *.* TO user43748@127.0.0.1 IDENTIFIED BY 'meow'; +GRANT PROCESS ON *.* TO user43748@127.0.0.1; + +--echo -- try to KILL system-thread as that non-privileged user (on slave). +connect (cont43748,127.0.0.1,user43748,meow,test,$SLAVE_MYPORT,); +connection cont43748; + +SELECT id INTO @id FROM information_schema.processlist WHERE user='system user' LIMIT 1; + +--error ER_KILL_DENIED_ERROR,ER_NO_SUCH_THREAD +KILL @id; + +disconnect cont43748; + +--echo -- throw out test-user on slave. +connection slave; + +DROP USER user43748@127.0.0.1; + +--echo -- done. back to master. +connection master; + + + +--echo End of 5.1 tests diff --git a/mysql-test/suite/rpl/t/rpl_timezone.test b/mysql-test/suite/rpl/t/rpl_timezone.test index d65242f2a22..40a2a4444b9 100644 --- a/mysql-test/suite/rpl/t/rpl_timezone.test +++ b/mysql-test/suite/rpl/t/rpl_timezone.test @@ -165,5 +165,32 @@ connection master; DROP TABLE t1; SET @@session.time_zone = default; +# Bug#41719 delayed INSERT into timestamp col needs set time_zone for concurrent binlogging +# To test that time_zone is correctly binloging for 'insert delayed' statement +# Insert 2 values into timestamp col with different time_zone. Check result. + +--connection master +reset master; +CREATE TABLE t1 (date timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, a int(11) default NULL); + +SET @@session.time_zone='+01:00'; +insert into t1 values('2008-12-23 19:39:39',1); + +--connection master1 +SET @@session.time_zone='+02:00'; +insert delayed into t1 values ('2008-12-23 19:39:39',2); +# Forces table t1 to be closed and flushes the query cache. +# This makes sure that 'delayed insert' is executed before next statement. +flush table t1; +flush logs; +select * from t1; +DROP TABLE t1; + +let $MYSQLD_DATADIR= `select @@datadir;`; +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL +--connection master1 +select * from t1 order by a; +DROP TABLE t1; +SET @@session.time_zone = default; --echo End of 5.0 tests diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_sync.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_sync.result index c788893e055..3ef5e2b7e53 100644 --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_sync.result +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_sync.result @@ -25,8 +25,9 @@ hex(c2) hex(c3) c1 0 1 BCDEF 1 0 CD 0 0 DEFGHIJKL -CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP; -LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info +(id INT, backup_id INT) ENGINE = MEMORY; +LOAD DATA INFILE '<MYSQLTEST_VARDIR>/tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; DROP TABLE test.backup_info; UPDATE t1 SET c2=0 WHERE c3="row2"; SELECT hex(c1),hex(c2),c3 FROM t1 ORDER BY c3; diff --git a/mysql-test/suite/sys_vars/inc/slave_transaction_retries_basic.inc b/mysql-test/suite/sys_vars/inc/slave_transaction_retries_basic.inc index 6bbed3b1982..28ba8791b0a 100644 --- a/mysql-test/suite/sys_vars/inc/slave_transaction_retries_basic.inc +++ b/mysql-test/suite/sys_vars/inc/slave_transaction_retries_basic.inc @@ -23,6 +23,7 @@ # # ############################################################################### +--source include/not_embedded.inc --source include/load_sysvars.inc ################################################################### diff --git a/mysql-test/suite/sys_vars/inc/sync_binlog_basic.inc b/mysql-test/suite/sys_vars/inc/sync_binlog_basic.inc index b66509bbb5e..04adab85ac8 100644 --- a/mysql-test/suite/sys_vars/inc/sync_binlog_basic.inc +++ b/mysql-test/suite/sys_vars/inc/sync_binlog_basic.inc @@ -23,6 +23,7 @@ # # ############################################################################### +--source include/not_embedded.inc --source include/load_sysvars.inc ################################################################## diff --git a/mysql-test/suite/sys_vars/r/binlog_cache_size_basic_32.result b/mysql-test/suite/sys_vars/r/binlog_cache_size_basic_32.result index 6267c5493da..2f439c8f834 100644 --- a/mysql-test/suite/sys_vars/r/binlog_cache_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/binlog_cache_size_basic_32.result @@ -44,7 +44,7 @@ SET @@global.binlog_cache_size = 10000.01; ERROR 42000: Incorrect argument type to variable 'binlog_cache_size' SET @@global.binlog_cache_size = -1024; Warnings: -Warning 1292 Truncated incorrect binlog_cache_size value: '0' +Warning 1292 Truncated incorrect binlog_cache_size value: '-1024' SELECT @@global.binlog_cache_size; @@global.binlog_cache_size 4096 diff --git a/mysql-test/suite/sys_vars/r/bulk_insert_buffer_size_basic_32.result b/mysql-test/suite/sys_vars/r/bulk_insert_buffer_size_basic_32.result index 1194a0e7ce7..3e071f76f98 100644 --- a/mysql-test/suite/sys_vars/r/bulk_insert_buffer_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/bulk_insert_buffer_size_basic_32.result @@ -68,6 +68,8 @@ SELECT @@global.bulk_insert_buffer_size; @@global.bulk_insert_buffer_size 4294967295 SET @@global.bulk_insert_buffer_size = -1024; +Warnings: +Warning 1292 Truncated incorrect bulk_insert_buffer_size value: '-1024' SELECT @@global.bulk_insert_buffer_size; @@global.bulk_insert_buffer_size 0 @@ -84,6 +86,8 @@ SELECT @@session.bulk_insert_buffer_size; @@session.bulk_insert_buffer_size 4294967295 SET @@session.bulk_insert_buffer_size = -2; +Warnings: +Warning 1292 Truncated incorrect bulk_insert_buffer_size value: '-2' SELECT @@session.bulk_insert_buffer_size; @@session.bulk_insert_buffer_size 0 diff --git a/mysql-test/suite/sys_vars/r/delayed_insert_limit_basic_32.result b/mysql-test/suite/sys_vars/r/delayed_insert_limit_basic_32.result index cb6820b2941..f55c966dc38 100644 --- a/mysql-test/suite/sys_vars/r/delayed_insert_limit_basic_32.result +++ b/mysql-test/suite/sys_vars/r/delayed_insert_limit_basic_32.result @@ -35,7 +35,7 @@ SELECT @@global.delayed_insert_limit; 1 SET @@global.delayed_insert_limit = -1024; Warnings: -Warning 1292 Truncated incorrect delayed_insert_limit value: '0' +Warning 1292 Truncated incorrect delayed_insert_limit value: '-1024' SELECT @@global.delayed_insert_limit; @@global.delayed_insert_limit 1 diff --git a/mysql-test/suite/sys_vars/r/delayed_insert_limit_func.result b/mysql-test/suite/sys_vars/r/delayed_insert_limit_func.result index d7129d24498..0496efa4296 100644 --- a/mysql-test/suite/sys_vars/r/delayed_insert_limit_func.result +++ b/mysql-test/suite/sys_vars/r/delayed_insert_limit_func.result @@ -3,98 +3,75 @@ Creating connection con0 Creating connection con1 SET @global_delayed_insert_limit = @@GLOBAL.delayed_insert_limit; -CREATE TABLE t1 (a varchar(100)); +CREATE TABLE t1 (a VARCHAR(100),b VARCHAR(100),c VARCHAR(100)); '#--------------------FN_DYNVARS_25_01-------------------------#' -SET GLOBAL delayed_insert_limit = 9; -** Connection con0 ** -SET GLOBAL delayed_insert_limit = 9; -** Connection con1 ** -SET GLOBAL delayed_insert_limit = 9; -** Connection default ** -SET GLOBAL delayed_insert_limit = 9; -INSERT INTO t1 VALUES('1'); -INSERT INTO t1 VALUES('2'); -INSERT INTO t1 VALUES('3'); -INSERT INTO t1 VALUES('4'); -INSERT INTO t1 VALUES('5'); -INSERT INTO t1 VALUES('6'); +SET GLOBAL delayed_insert_limit = 14; +INSERT INTO t1 VALUES('1','1','1'); +INSERT INTO t1 VALUES('2','1','1'); +INSERT INTO t1 VALUES('3','1','1'); +INSERT INTO t1 VALUES('4','1','1'); +INSERT INTO t1 VALUES('5','1','1'); +INSERT INTO t1 VALUES('6','1','1'); LOCK TABLE t1 WRITE; ** Connection con1 ** -INSERT DELAYED INTO t1 VALUES('7'); -INSERT DELAYED INTO t1 VALUES('8'); -INSERT DELAYED INTO t1 VALUES('9'); -INSERT DELAYED INTO t1 VALUES('10'); -INSERT DELAYED INTO t1 VALUES('11'); -INSERT DELAYED INTO t1 VALUES('12'); -INSERT DELAYED INTO t1 VALUES('13'); -INSERT DELAYED INTO t1 VALUES('14'); -INSERT DELAYED INTO t1 VALUES('15'); -INSERT DELAYED INTO t1 VALUES('16'); -INSERT DELAYED INTO t1 VALUES('17'); -INSERT DELAYED INTO t1 VALUES('18'); -INSERT DELAYED INTO t1 VALUES('19'); -INSERT DELAYED INTO t1 VALUES('20'); -INSERT DELAYED INTO t1 VALUES('21'); -INSERT DELAYED INTO t1 VALUES('22');| +INSERT DELAYED INTO t1 VALUES('7','1','1'); +INSERT DELAYED INTO t1 VALUES('8','1','1'); +INSERT DELAYED INTO t1 VALUES('9','1','1'); +INSERT DELAYED INTO t1 VALUES('10','1','1'); +INSERT DELAYED INTO t1 VALUES('11','1','1'); +INSERT DELAYED INTO t1 VALUES('12','1','1'); +INSERT DELAYED INTO t1 VALUES('13','1','1'); +INSERT DELAYED INTO t1 VALUES('14','1','1'); +INSERT DELAYED INTO t1 VALUES('15','1','1'); +INSERT DELAYED INTO t1 VALUES('16','1','1'); +INSERT DELAYED INTO t1 VALUES('17','1','1'); +INSERT DELAYED INTO t1 VALUES('18','1','1'); +INSERT DELAYED INTO t1 VALUES('19','1','1'); +INSERT DELAYED INTO t1 VALUES('20','1','1'); +INSERT DELAYED INTO t1 VALUES('21','1','1'); +INSERT DELAYED INTO t1 VALUES('22','1','1'); +INSERT DELAYED INTO t1 VALUES('23','1','1'); +INSERT DELAYED INTO t1 VALUES('24','1','1'); +INSERT DELAYED INTO t1 VALUES('25','1','1'); +INSERT DELAYED INTO t1 VALUES('26','1','1'); +INSERT DELAYED INTO t1 VALUES('27','1','1'); +INSERT DELAYED INTO t1 VALUES('28','1','1'); +INSERT DELAYED INTO t1 VALUES('29','1','1'); +INSERT DELAYED INTO t1 VALUES('30','1','1'); +INSERT DELAYED INTO t1 VALUES('31','1','1'); +INSERT DELAYED INTO t1 VALUES('32','1','1'); +INSERT DELAYED INTO t1 VALUES('33','1','1'); +INSERT DELAYED INTO t1 VALUES('34','1','1'); +INSERT DELAYED INTO t1 VALUES('35','1','1'); +INSERT DELAYED INTO t1 VALUES('36','1','1'); +INSERT DELAYED INTO t1 VALUES('37','1','1'); +INSERT DELAYED INTO t1 VALUES('38','1','1'); +INSERT DELAYED INTO t1 VALUES('39','1','1'); +INSERT DELAYED INTO t1 VALUES('40','1','1'); +INSERT DELAYED INTO t1 VALUES('41','1','1'); +INSERT DELAYED INTO t1 VALUES('42','1','1'); +INSERT DELAYED INTO t1 VALUES('43','1','1');| ** Connection con0 ** -SELECT * FROM t1;| +SELECT COUNT(*) FROM t1; ** Connection default ** -Waiting for 1 sec +** Wait till con0 is blocked ** UNLOCK TABLES; +** Connection con1 ** +Asynchronous "reap" result ** Connection con0 ** -a -1 -2 -3 -4 -5 -6 -7 -8 -9 -10 -11 -12 -13 -14 -15 -16 -'Bug#35386: insert delayed inserts 1 + limit rows instead of just limit rows' +Asynchronous "reap" result +The next result suffers from +'# Bug#35386 insert delayed inserts 1 + limit rows instead of just limit rows' +COUNT(*) +21 ** Connection default ** -Waiting for 1 sec Checking if the delayed insert continued afterwards -SELECT * FROM t1; -a -1 -2 -3 -4 -5 -6 -7 -8 -9 -10 -11 -12 -13 -14 -15 -16 -17 -18 -19 -20 -21 -22 -DELETE FROM t1; +SELECT COUNT(*) FROM t1; +COUNT(*) +43 +DROP TABLE t1; '#--------------------FN_DYNVARS_25_02-------------------------#' -SET GLOBAL delayed_insert_limit = 20; -** Connection con0 ** -SET GLOBAL delayed_insert_limit = 20; -** Connection con1 ** -SET GLOBAL delayed_insert_limit = 20; -** Connection default ** +CREATE TABLE t1 (a VARCHAR(100)); SET GLOBAL delayed_insert_limit = 20; INSERT INTO t1 VALUES('1'); INSERT INTO t1 VALUES('2'); @@ -123,64 +100,21 @@ INSERT DELAYED INTO t1 VALUES('21'); INSERT DELAYED INTO t1 VALUES('22');| ** Connection con0 ** Asynchronous execute -SELECT * FROM t1;| +SELECT COUNT(*) = 22 FROM t1; ** Connection default ** -Waiting for 1 sec +** Wait till con0 is blocked ** UNLOCK TABLES; +** Connection con1 ** ** Connection con0 ** -Asynchronous execute result -a +Asynchronous "reap" result +COUNT(*) = 22 1 -2 -3 -4 -5 -6 -7 -8 -9 -10 -11 -12 -13 -14 -15 -16 -17 -18 -19 -20 -21 -22 ** Connection default** -Waiting for 1 sec Checking if the delayed insert gives the same result afterwards -SELECT * FROM t1; -a +SELECT COUNT(*) = 22 FROM t1; +COUNT(*) = 22 1 -2 -3 -4 -5 -6 -7 -8 -9 -10 -11 -12 -13 -14 -15 -16 -17 -18 -19 -20 -21 -22 -DELETE FROM t1; -Switching to default -Disconnecting from con1, con0 +** Connection default** DROP TABLE t1; SET @@GLOBAL.delayed_insert_limit = @global_delayed_insert_limit; +Disconnecting from con1, con0 diff --git a/mysql-test/suite/sys_vars/r/delayed_queue_size_basic_32.result b/mysql-test/suite/sys_vars/r/delayed_queue_size_basic_32.result index 6bfa6f0de40..3b356c6d866 100644 --- a/mysql-test/suite/sys_vars/r/delayed_queue_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/delayed_queue_size_basic_32.result @@ -35,7 +35,7 @@ SELECT @@global.delayed_queue_size; 1 SET @@global.delayed_queue_size = -1024; Warnings: -Warning 1292 Truncated incorrect delayed_queue_size value: '0' +Warning 1292 Truncated incorrect delayed_queue_size value: '-1024' SELECT @@global.delayed_queue_size; @@global.delayed_queue_size 1 diff --git a/mysql-test/suite/sys_vars/r/event_scheduler_func.result b/mysql-test/suite/sys_vars/r/event_scheduler_func.result deleted file mode 100644 index 8da942e919c..00000000000 --- a/mysql-test/suite/sys_vars/r/event_scheduler_func.result +++ /dev/null @@ -1,44 +0,0 @@ -drop table if exists t1; -## Creating new table ## -CREATE TABLE t1 -( -id INT NOT NULL auto_increment, -PRIMARY KEY (id), -name VARCHAR(30) -); -'#--------------------FN_DYNVARS_018_01-------------------------#' -## Setting initial value of variable to ON ## -SET @@global.event_scheduler = ON; -SELECT @@event_scheduler; -@@event_scheduler -ON -## Creating new event ## -CREATE EVENT test_event_1 -ON SCHEDULE EVERY 3 SECOND -DO -INSERT into t1(name) values('Record_1'); -SELECT * from t1; -id name -1 Record_1 -2 Record_1 -DROP EVENT test_event_1; -DELETE from t1; -select * from t1; -id name -'#--------------------FN_DYNVARS_018_02-------------------------#' -## Setting value of variable to OFF ## -SET @@global.event_scheduler = OFF; -SELECT @@event_scheduler; -@@event_scheduler -OFF -## Creating new event ## -CREATE EVENT test_event_1 -ON SCHEDULE EVERY 3 SECOND -DO -INSERT into t1(name) values('Record_2'); -## Table should be empty ## -SELECT * from t1; -id name -DROP EVENT test_event_1; -## Dropping table ## -DROP table t1; diff --git a/mysql-test/suite/sys_vars/r/ft_boolean_syntax_func.result b/mysql-test/suite/sys_vars/r/ft_boolean_syntax_func.result index 4a3159c77c3..0096c553155 100644 --- a/mysql-test/suite/sys_vars/r/ft_boolean_syntax_func.result +++ b/mysql-test/suite/sys_vars/r/ft_boolean_syntax_func.result @@ -70,7 +70,7 @@ FROM articles WHERE MATCH (title,body) AGAINST ('+security configuring' IN BOOLEAN MODE); id title body relevance 8 MySQL Security When configured properly, MySQL ... 1 -9 Database Security Configuring MySQL for ... 1.3333333730698 +9 Database Security Configuring MySQL for ... 1.33333337306976 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"faster than"' IN BOOLEAN MODE); id title body @@ -91,7 +91,7 @@ AGAINST ('+MySQL +(>show <dbms)' IN BOOLEAN MODE) ORDER BY relevance DESC; id title body relevance 4 Optimizing MySQL In this tutorial we will show .... Run command line ... 1.25 -1 MySQL Tutorial DBMS stands for DataBase ... 0.83333337306976 +1 MySQL Tutorial DBMS stands for DataBase ... 0.833333373069763 '---try setting different operators. Default '+ -><()~*:""&|'--' SET @@global.ft_boolean_syntax='~ /!@#$%^&*()-'; SELECT * FROM articles WHERE MATCH (title,body) diff --git a/mysql-test/suite/sys_vars/r/interactive_timeout_func.result b/mysql-test/suite/sys_vars/r/interactive_timeout_func.result index b97f7c90908..2ecd32f53bd 100644 --- a/mysql-test/suite/sys_vars/r/interactive_timeout_func.result +++ b/mysql-test/suite/sys_vars/r/interactive_timeout_func.result @@ -9,22 +9,27 @@ name VARCHAR(30) '#--------------------FN_DYNVARS_052_01-------------------------#' ## Setting initial value of variable to 1 ## SET @@global.interactive_timeout = 1; -## Creating new interactive connection test_con1 ## +## Creating new connection test_con1 ## ## Inserting record in table ## INSERT into t1(name) values('Record_1'); -## Setting session value of interactive_timeout ## +## Setting session value of interactive_timeout ## SET @@session.interactive_timeout = 1; -## Verifying values of variable ## +## Verifying values of variable ## SELECT @@session.interactive_timeout; @@session.interactive_timeout 1 SELECT @@global.interactive_timeout; @@global.interactive_timeout 1 -## Using sleep to check timeout ## +connection default; +## Using sleep to check timeout ## +sleep 2; +connection test_con1; SELECT * from t1; id name 1 Record_1 -'Bug#35377: Error should appear here because interactive_timeout value'; -'is 1 and connection remains idle for 5 secs'; INSERT into t1(name) values('Record_2'); +connection default; +disconnect test_con1; +DROP TABLE t1; +SET @@global.interactive_timeout= 28800; diff --git a/mysql-test/suite/sys_vars/r/join_buffer_size_basic_32.result b/mysql-test/suite/sys_vars/r/join_buffer_size_basic_32.result index e3016a2b22c..2318c6d7055 100644 --- a/mysql-test/suite/sys_vars/r/join_buffer_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/join_buffer_size_basic_32.result @@ -75,7 +75,7 @@ SELECT @@global.join_buffer_size=8200 OR @@global.join_buffer_size= 8228; 1 SET @@global.join_buffer_size = -1024; Warnings: -Warning 1292 Truncated incorrect join_buffer_size value: '0' +Warning 1292 Truncated incorrect join_buffer_size value: '-1024' SELECT @@global.join_buffer_size=8200 OR @@global.join_buffer_size= 8228; @@global.join_buffer_size=8200 OR @@global.join_buffer_size= 8228 1 @@ -109,7 +109,7 @@ SELECT @@session.join_buffer_size=8200 OR @@session.join_buffer_size= 8228; 1 SET @@session.join_buffer_size = -2; Warnings: -Warning 1292 Truncated incorrect join_buffer_size value: '0' +Warning 1292 Truncated incorrect join_buffer_size value: '-2' SELECT @@session.join_buffer_size=8200 OR @@session.join_buffer_size= 8228; @@session.join_buffer_size=8200 OR @@session.join_buffer_size= 8228 1 diff --git a/mysql-test/suite/sys_vars/r/key_buffer_size_basic_32.result b/mysql-test/suite/sys_vars/r/key_buffer_size_basic_32.result index 7221da16ed8..eea782701bb 100644 --- a/mysql-test/suite/sys_vars/r/key_buffer_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/key_buffer_size_basic_32.result @@ -17,8 +17,6 @@ SELECT @@global.key_buffer_size BETWEEN 8 AND 36; @@global.key_buffer_size BETWEEN 8 AND 36 1 SET @@global.key_buffer_size = 1800; -Warnings: -Warning 1292 Truncated incorrect key_buffer_size value: '1800' SELECT @@global.key_buffer_size BETWEEN 8 AND 36; @@global.key_buffer_size BETWEEN 8 AND 36 1 @@ -55,13 +53,13 @@ SELECT @@global.key_buffer_size BETWEEN 8 AND 36; @@global.key_buffer_size BETWEEN 8 AND 36 1 '#----------------------FN_DYNVARS_055_06------------------------#' -SELECT @@global.key_buffer_size = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +SELECT @@global.key_buffer_size = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='key_buffer_size'; @@global.key_buffer_size = VARIABLE_VALUE 1 -SELECT @@key_buffer_size = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.SESSION_VARIABLES +SELECT @@key_buffer_size = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='key_buffer_size'; @@key_buffer_size = VARIABLE_VALUE 1 diff --git a/mysql-test/suite/sys_vars/r/key_cache_age_threshold_basic_32.result b/mysql-test/suite/sys_vars/r/key_cache_age_threshold_basic_32.result index 023b23ea425..c79e7beb198 100644 --- a/mysql-test/suite/sys_vars/r/key_cache_age_threshold_basic_32.result +++ b/mysql-test/suite/sys_vars/r/key_cache_age_threshold_basic_32.result @@ -37,10 +37,14 @@ SELECT @@global.key_cache_age_threshold; 'Bug# 34877 : Invalid Values are coming in variable on assigning valid values and Out Of Memory Warnings are coming'; '#--------------------FN_DYNVARS_056_04-------------------------#' SET @@global.key_cache_age_threshold = -1; +Warnings: +Warning 1292 Truncated incorrect key_cache_age_threshold value: '18446744073709551615' SELECT @@global.key_cache_age_threshold; @@global.key_cache_age_threshold 4294967200 SET @@global.key_cache_age_threshold = 42949672951; +Warnings: +Warning 1292 Truncated incorrect key_cache_age_threshold value: '42949672951' SELECT @@global.key_cache_age_threshold; @@global.key_cache_age_threshold 4294967200 @@ -50,9 +54,11 @@ SELECT @@global.key_cache_age_threshold; @@global.key_cache_age_threshold 4294967200 SET @@global.key_cache_age_threshold = -1024; +Warnings: +Warning 1292 Truncated incorrect key_cache_age_threshold value: '18446744073709550592' SELECT @@global.key_cache_age_threshold; @@global.key_cache_age_threshold -4294966200 +4294967200 SET @@global.key_cache_age_threshold = 99; Warnings: Warning 1292 Truncated incorrect key_cache_age_threshold value: '99' diff --git a/mysql-test/suite/sys_vars/r/key_cache_block_size_basic_32.result b/mysql-test/suite/sys_vars/r/key_cache_block_size_basic_32.result index ac78acb8244..46ce1f26b29 100644 --- a/mysql-test/suite/sys_vars/r/key_cache_block_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/key_cache_block_size_basic_32.result @@ -36,13 +36,13 @@ SELECT @@global.key_cache_block_size; '#--------------------FN_DYNVARS_057_04-------------------------#' SET @@global.key_cache_block_size = -1; Warnings: -Warning 1292 Truncated incorrect key_cache_block_size value: '4294967295' +Warning 1292 Truncated incorrect key_cache_block_size value: '18446744073709551615' SELECT @@global.key_cache_block_size; @@global.key_cache_block_size 16384 SET @@global.key_cache_block_size = 42949672951; Warnings: -Warning 1292 Truncated incorrect key_cache_block_size value: '4294967287' +Warning 1292 Truncated incorrect key_cache_block_size value: '42949672951' SELECT @@global.key_cache_block_size; @@global.key_cache_block_size 16384 @@ -53,7 +53,7 @@ SELECT @@global.key_cache_block_size; 16384 SET @@global.key_cache_block_size = -1024; Warnings: -Warning 1292 Truncated incorrect key_cache_block_size value: '4294966272' +Warning 1292 Truncated incorrect key_cache_block_size value: '18446744073709550592' SELECT @@global.key_cache_block_size; @@global.key_cache_block_size 16384 diff --git a/mysql-test/suite/sys_vars/r/key_cache_division_limit_basic_32.result b/mysql-test/suite/sys_vars/r/key_cache_division_limit_basic_32.result index 5902dbcdf89..cd0cdcbca05 100644 --- a/mysql-test/suite/sys_vars/r/key_cache_division_limit_basic_32.result +++ b/mysql-test/suite/sys_vars/r/key_cache_division_limit_basic_32.result @@ -35,7 +35,7 @@ SELECT @@global.key_cache_division_limit; '#--------------------FN_DYNVARS_058_04-------------------------#' SET @@global.key_cache_division_limit = -1; Warnings: -Warning 1292 Truncated incorrect key_cache_division_limit value: '4294967295' +Warning 1292 Truncated incorrect key_cache_division_limit value: '18446744073709551615' SELECT @@global.key_cache_division_limit; @@global.key_cache_division_limit 100 @@ -52,7 +52,7 @@ SELECT @@global.key_cache_division_limit; 100 SET @@global.key_cache_division_limit = -1024; Warnings: -Warning 1292 Truncated incorrect key_cache_division_limit value: '4294966272' +Warning 1292 Truncated incorrect key_cache_division_limit value: '18446744073709550592' SELECT @@global.key_cache_division_limit; @@global.key_cache_division_limit 100 diff --git a/mysql-test/suite/sys_vars/r/log_warnings_basic_32.result b/mysql-test/suite/sys_vars/r/log_warnings_basic_32.result index 900bb487706..92b0f4e5e91 100644 --- a/mysql-test/suite/sys_vars/r/log_warnings_basic_32.result +++ b/mysql-test/suite/sys_vars/r/log_warnings_basic_32.result @@ -76,6 +76,8 @@ SELECT @@global.log_warnings; @@global.log_warnings 4294967295 SET @@global.log_warnings = -1024; +Warnings: +Warning 1292 Truncated incorrect log_warnings value: '-1024' SELECT @@global.log_warnings; @@global.log_warnings 0 @@ -96,6 +98,8 @@ SELECT @@session.log_warnings; @@session.log_warnings 4294967295 SET @@session.log_warnings = -2; +Warnings: +Warning 1292 Truncated incorrect log_warnings value: '-2' SELECT @@session.log_warnings; @@session.log_warnings 0 diff --git a/mysql-test/suite/sys_vars/r/max_binlog_cache_size_basic_32.result b/mysql-test/suite/sys_vars/r/max_binlog_cache_size_basic_32.result index a594295c0f5..1b3b65010b2 100644 --- a/mysql-test/suite/sys_vars/r/max_binlog_cache_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/max_binlog_cache_size_basic_32.result @@ -39,7 +39,7 @@ SELECT @@global.max_binlog_cache_size; '#--------------------FN_DYNVARS_072_04-------------------------#' SET @@global.max_binlog_cache_size = -1; Warnings: -Warning 1292 Truncated incorrect max_binlog_cache_size value: '0' +Warning 1292 Truncated incorrect max_binlog_cache_size value: '-1' SELECT @@global.max_binlog_cache_size; @@global.max_binlog_cache_size 4096 @@ -56,7 +56,7 @@ SELECT @@global.max_binlog_cache_size; 4294963200 SET @@global.max_binlog_cache_size = -1024; Warnings: -Warning 1292 Truncated incorrect max_binlog_cache_size value: '0' +Warning 1292 Truncated incorrect max_binlog_cache_size value: '-1024' SELECT @@global.max_binlog_cache_size; @@global.max_binlog_cache_size 4096 diff --git a/mysql-test/suite/sys_vars/r/max_connect_errors_basic_32.result b/mysql-test/suite/sys_vars/r/max_connect_errors_basic_32.result index b786e0ce31a..42e64f1b418 100644 --- a/mysql-test/suite/sys_vars/r/max_connect_errors_basic_32.result +++ b/mysql-test/suite/sys_vars/r/max_connect_errors_basic_32.result @@ -39,7 +39,7 @@ SELECT @@global.max_connect_errors; '#--------------------FN_DYNVARS_073_04-------------------------#' SET @@global.max_connect_errors = -1; Warnings: -Warning 1292 Truncated incorrect max_connect_errors value: '0' +Warning 1292 Truncated incorrect max_connect_errors value: '-1' SELECT @@global.max_connect_errors; @@global.max_connect_errors 1 @@ -56,7 +56,7 @@ SELECT @@global.max_connect_errors; 4294967295 SET @@global.max_connect_errors = -1024; Warnings: -Warning 1292 Truncated incorrect max_connect_errors value: '0' +Warning 1292 Truncated incorrect max_connect_errors value: '-1024' SELECT @@global.max_connect_errors; @@global.max_connect_errors 1 diff --git a/mysql-test/suite/sys_vars/r/max_heap_table_size_basic_32.result b/mysql-test/suite/sys_vars/r/max_heap_table_size_basic_32.result index 04eaa3ddd19..deedb9ae43c 100644 --- a/mysql-test/suite/sys_vars/r/max_heap_table_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/max_heap_table_size_basic_32.result @@ -40,10 +40,14 @@ SELECT @@global.max_heap_table_size; @@global.max_heap_table_size 64512 SET @@global.max_heap_table_size = 4294967294; +Warnings: +Warning 1292 Truncated incorrect max_heap_table_size value: '4294967294' SELECT @@global.max_heap_table_size; @@global.max_heap_table_size 4294966272 SET @@global.max_heap_table_size = 4294967295; +Warnings: +Warning 1292 Truncated incorrect max_heap_table_size value: '4294967295' SELECT @@global.max_heap_table_size; @@global.max_heap_table_size 4294966272 @@ -62,10 +66,14 @@ SELECT @@session.max_heap_table_size; @@session.max_heap_table_size 64512 SET @@session.max_heap_table_size = 4294967294; +Warnings: +Warning 1292 Truncated incorrect max_heap_table_size value: '4294967294' SELECT @@session.max_heap_table_size; @@session.max_heap_table_size 4294966272 SET @@session.max_heap_table_size = 4294967295; +Warnings: +Warning 1292 Truncated incorrect max_heap_table_size value: '4294967295' SELECT @@session.max_heap_table_size; @@session.max_heap_table_size 4294966272 @@ -73,13 +81,13 @@ SELECT @@session.max_heap_table_size; '#------------------FN_DYNVARS_077_05-----------------------#' SET @@global.max_heap_table_size = -1; Warnings: -Warning 1292 Truncated incorrect max_heap_table_size value: '0' +Warning 1292 Truncated incorrect max_heap_table_size value: '-1' SELECT @@global.max_heap_table_size; @@global.max_heap_table_size 16384 SET @@global.max_heap_table_size = -1024; Warnings: -Warning 1292 Truncated incorrect max_heap_table_size value: '0' +Warning 1292 Truncated incorrect max_heap_table_size value: '-1024' SELECT @@global.max_heap_table_size; @@global.max_heap_table_size 16384 @@ -96,6 +104,8 @@ SELECT @@global.max_heap_table_size; @@global.max_heap_table_size 16384 SET @@global.max_heap_table_size = 4294967296; +Warnings: +Warning 1292 Truncated incorrect max_heap_table_size value: '4294967296' SELECT @@global.max_heap_table_size; @@global.max_heap_table_size 4294966272 @@ -111,7 +121,7 @@ SELECT @@global.max_heap_table_size; 4294966272 SET @@session.max_heap_table_size = -1; Warnings: -Warning 1292 Truncated incorrect max_heap_table_size value: '0' +Warning 1292 Truncated incorrect max_heap_table_size value: '-1' SELECT @@session.max_heap_table_size; @@session.max_heap_table_size 16384 @@ -122,12 +132,16 @@ SELECT @@session.max_heap_table_size; @@session.max_heap_table_size 16384 SET @@session.max_heap_table_size = 4294967296; +Warnings: +Warning 1292 Truncated incorrect max_heap_table_size value: '4294967296' SELECT @@session.max_heap_table_size; @@session.max_heap_table_size 4294966272 SET @@session.max_heap_table_size = 65530.34.; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.' at line 1 SET @@session.max_heap_table_size = 10737418241; +Warnings: +Warning 1292 Truncated incorrect max_heap_table_size value: '10737418241' SELECT @@session.max_heap_table_size; @@session.max_heap_table_size 4294966272 diff --git a/mysql-test/suite/sys_vars/r/max_seeks_for_key_basic_32.result b/mysql-test/suite/sys_vars/r/max_seeks_for_key_basic_32.result index 64828fbe619..9a58ad44cff 100644 --- a/mysql-test/suite/sys_vars/r/max_seeks_for_key_basic_32.result +++ b/mysql-test/suite/sys_vars/r/max_seeks_for_key_basic_32.result @@ -77,7 +77,7 @@ SELECT @@global.max_seeks_for_key; 1 SET @@global.max_seeks_for_key = -1024; Warnings: -Warning 1292 Truncated incorrect max_seeks_for_key value: '0' +Warning 1292 Truncated incorrect max_seeks_for_key value: '-1024' SELECT @@global.max_seeks_for_key; @@global.max_seeks_for_key 1 @@ -105,7 +105,7 @@ SELECT @@session.max_seeks_for_key; 1 SET @@session.max_seeks_for_key = -2; Warnings: -Warning 1292 Truncated incorrect max_seeks_for_key value: '0' +Warning 1292 Truncated incorrect max_seeks_for_key value: '-2' SELECT @@session.max_seeks_for_key; @@session.max_seeks_for_key 1 diff --git a/mysql-test/suite/sys_vars/r/max_tmp_tables_basic_32.result b/mysql-test/suite/sys_vars/r/max_tmp_tables_basic_32.result index 5f959069661..1346d24ca47 100644 --- a/mysql-test/suite/sys_vars/r/max_tmp_tables_basic_32.result +++ b/mysql-test/suite/sys_vars/r/max_tmp_tables_basic_32.result @@ -71,7 +71,7 @@ SELECT @@session.max_tmp_tables; '#------------------FN_DYNVARS_086_05-----------------------#' SET @@global.max_tmp_tables = -1024; Warnings: -Warning 1292 Truncated incorrect max_tmp_tables value: '0' +Warning 1292 Truncated incorrect max_tmp_tables value: '-1024' SELECT @@global.max_tmp_tables; @@global.max_tmp_tables 1 @@ -83,7 +83,7 @@ SELECT @@global.max_tmp_tables; 4294967295 SET @@global.max_tmp_tables = -1; Warnings: -Warning 1292 Truncated incorrect max_tmp_tables value: '0' +Warning 1292 Truncated incorrect max_tmp_tables value: '-1' SELECT @@global.max_tmp_tables; @@global.max_tmp_tables 1 @@ -111,7 +111,7 @@ SELECT @@session.max_tmp_tables; 4294967295 SET @@session.max_tmp_tables = -1; Warnings: -Warning 1292 Truncated incorrect max_tmp_tables value: '0' +Warning 1292 Truncated incorrect max_tmp_tables value: '-1' SELECT @@session.max_tmp_tables; @@session.max_tmp_tables 1 @@ -123,7 +123,7 @@ SELECT @@session.max_tmp_tables; 4294967295 SET @@session.max_tmp_tables = -001; Warnings: -Warning 1292 Truncated incorrect max_tmp_tables value: '0' +Warning 1292 Truncated incorrect max_tmp_tables value: '-1' SELECT @@session.max_tmp_tables; @@session.max_tmp_tables 1 diff --git a/mysql-test/suite/sys_vars/r/max_write_lock_count_basic_32.result b/mysql-test/suite/sys_vars/r/max_write_lock_count_basic_32.result index 0c9c3c00c1f..24af37260b7 100644 --- a/mysql-test/suite/sys_vars/r/max_write_lock_count_basic_32.result +++ b/mysql-test/suite/sys_vars/r/max_write_lock_count_basic_32.result @@ -37,7 +37,7 @@ SELECT @@global.max_write_lock_count; '#------------------FN_DYNVARS_088_04-----------------------#' SET @@global.max_write_lock_count = -1024; Warnings: -Warning 1292 Truncated incorrect max_write_lock_count value: '0' +Warning 1292 Truncated incorrect max_write_lock_count value: '-1024' SELECT @@global.max_write_lock_count; @@global.max_write_lock_count 1 @@ -49,7 +49,7 @@ SELECT @@global.max_write_lock_count; 4294967295 SET @@global.max_write_lock_count = -1; Warnings: -Warning 1292 Truncated incorrect max_write_lock_count value: '0' +Warning 1292 Truncated incorrect max_write_lock_count value: '-1' SELECT @@global.max_write_lock_count; @@global.max_write_lock_count 1 diff --git a/mysql-test/suite/sys_vars/r/min_examined_row_limit_basic_32.result b/mysql-test/suite/sys_vars/r/min_examined_row_limit_basic_32.result index c0315944a8a..c40640ffa3a 100644 --- a/mysql-test/suite/sys_vars/r/min_examined_row_limit_basic_32.result +++ b/mysql-test/suite/sys_vars/r/min_examined_row_limit_basic_32.result @@ -82,6 +82,8 @@ SELECT @@global.min_examined_row_limit; @@global.min_examined_row_limit 429496726 SET @@global.min_examined_row_limit = -1024; +Warnings: +Warning 1292 Truncated incorrect min_examined_row_limit value: '-1024' SELECT @@global.min_examined_row_limit; @@global.min_examined_row_limit 0 @@ -108,6 +110,8 @@ SELECT @@session.min_examined_row_limit; @@session.min_examined_row_limit 4294967295 SET @@session.min_examined_row_limit = -1; +Warnings: +Warning 1292 Truncated incorrect min_examined_row_limit value: '-1' SELECT @@session.min_examined_row_limit; @@session.min_examined_row_limit 0 diff --git a/mysql-test/suite/sys_vars/r/multi_range_count_basic_32.result b/mysql-test/suite/sys_vars/r/multi_range_count_basic_32.result index f6ac6490479..33222f781f2 100644 --- a/mysql-test/suite/sys_vars/r/multi_range_count_basic_32.result +++ b/mysql-test/suite/sys_vars/r/multi_range_count_basic_32.result @@ -83,7 +83,7 @@ SELECT @@global.multi_range_count; 4294967295 SET @@global.multi_range_count = -1024; Warnings: -Warning 1292 Truncated incorrect multi_range_count value: '0' +Warning 1292 Truncated incorrect multi_range_count value: '-1024' SELECT @@global.multi_range_count; @@global.multi_range_count 1 @@ -117,7 +117,7 @@ SELECT @@session.multi_range_count; 4294967295 SET @@session.multi_range_count = -1; Warnings: -Warning 1292 Truncated incorrect multi_range_count value: '0' +Warning 1292 Truncated incorrect multi_range_count value: '-1' SELECT @@session.multi_range_count; @@session.multi_range_count 1 diff --git a/mysql-test/suite/sys_vars/r/myisam_max_sort_file_size_basic_32.result b/mysql-test/suite/sys_vars/r/myisam_max_sort_file_size_basic_32.result index 7467537eaba..b2a8603de92 100644 --- a/mysql-test/suite/sys_vars/r/myisam_max_sort_file_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/myisam_max_sort_file_size_basic_32.result @@ -48,14 +48,20 @@ SET @@local.myisam_max_sort_file_size = 4; ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL '#------------------FN_DYNVARS_094_05-----------------------#' SET @@global.myisam_max_sort_file_size = -1; +Warnings: +Warning 1292 Truncated incorrect myisam_max_sort_file_size value: '-1' SELECT @@global.myisam_max_sort_file_size; @@global.myisam_max_sort_file_size 0 SET @@global.myisam_max_sort_file_size = -2147483648; +Warnings: +Warning 1292 Truncated incorrect myisam_max_sort_file_size value: '-2147483648' SELECT @@global.myisam_max_sort_file_size; @@global.myisam_max_sort_file_size 0 SET @@global.myisam_max_sort_file_size = -2147483649; +Warnings: +Warning 1292 Truncated incorrect myisam_max_sort_file_size value: '-2147483649' SELECT @@global.myisam_max_sort_file_size; @@global.myisam_max_sort_file_size 0 diff --git a/mysql-test/suite/sys_vars/r/myisam_repair_threads_basic_32.result b/mysql-test/suite/sys_vars/r/myisam_repair_threads_basic_32.result index c91128e3f4c..64321814746 100644 --- a/mysql-test/suite/sys_vars/r/myisam_repair_threads_basic_32.result +++ b/mysql-test/suite/sys_vars/r/myisam_repair_threads_basic_32.result @@ -61,7 +61,7 @@ SELECT @@global.myisam_repair_threads ; 1 SET @@global.myisam_repair_threads = -1024; Warnings: -Warning 1292 Truncated incorrect myisam_repair_threads value: '0' +Warning 1292 Truncated incorrect myisam_repair_threads value: '-1024' SELECT @@global.myisam_repair_threads ; @@global.myisam_repair_threads 1 @@ -104,7 +104,7 @@ SELECT @@session.myisam_repair_threads ; 1 SET @@session.myisam_repair_threads = -2; Warnings: -Warning 1292 Truncated incorrect myisam_repair_threads value: '0' +Warning 1292 Truncated incorrect myisam_repair_threads value: '-2' SELECT @@session.myisam_repair_threads ; @@session.myisam_repair_threads 1 diff --git a/mysql-test/suite/sys_vars/r/myisam_sort_buffer_size_basic_32.result b/mysql-test/suite/sys_vars/r/myisam_sort_buffer_size_basic_32.result index 74d2fb3ec86..2657d599df7 100644 --- a/mysql-test/suite/sys_vars/r/myisam_sort_buffer_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/myisam_sort_buffer_size_basic_32.result @@ -61,7 +61,7 @@ SELECT @@global.myisam_sort_buffer_size ; 4 SET @@global.myisam_sort_buffer_size = -1024; Warnings: -Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '0' +Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '-1024' SELECT @@global.myisam_sort_buffer_size ; @@global.myisam_sort_buffer_size 4 @@ -104,7 +104,7 @@ SELECT @@session.myisam_sort_buffer_size ; 4 SET @@session.myisam_sort_buffer_size = -2; Warnings: -Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '0' +Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '-2' SELECT @@session.myisam_sort_buffer_size ; @@session.myisam_sort_buffer_size 4 diff --git a/mysql-test/suite/sys_vars/r/net_retry_count_basic_32.result b/mysql-test/suite/sys_vars/r/net_retry_count_basic_32.result index 3923df539e7..7a8b99ac55c 100644 --- a/mysql-test/suite/sys_vars/r/net_retry_count_basic_32.result +++ b/mysql-test/suite/sys_vars/r/net_retry_count_basic_32.result @@ -77,7 +77,7 @@ SELECT @@global.net_retry_count; 1 SET @@global.net_retry_count = -1024; Warnings: -Warning 1292 Truncated incorrect net_retry_count value: '0' +Warning 1292 Truncated incorrect net_retry_count value: '-1024' SELECT @@global.net_retry_count; @@global.net_retry_count 1 @@ -111,7 +111,7 @@ SELECT @@session.net_retry_count; 1 SET @@session.net_retry_count = -2; Warnings: -Warning 1292 Truncated incorrect net_retry_count value: '0' +Warning 1292 Truncated incorrect net_retry_count value: '-2' SELECT @@session.net_retry_count; @@session.net_retry_count 1 diff --git a/mysql-test/suite/sys_vars/r/query_alloc_block_size_basic_32.result b/mysql-test/suite/sys_vars/r/query_alloc_block_size_basic_32.result index 32b9371977c..2ea0831a36d 100644 --- a/mysql-test/suite/sys_vars/r/query_alloc_block_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/query_alloc_block_size_basic_32.result @@ -86,7 +86,7 @@ SELECT @@global.query_alloc_block_size; 1024 SET @@global.query_alloc_block_size = -1; Warnings: -Warning 1292 Truncated incorrect query_alloc_block_size value: '0' +Warning 1292 Truncated incorrect query_alloc_block_size value: '-1' SELECT @@global.query_alloc_block_size; @@global.query_alloc_block_size 1024 @@ -120,7 +120,7 @@ SELECT @@session.query_alloc_block_size; 1024 SET @@session.query_alloc_block_size = -2; Warnings: -Warning 1292 Truncated incorrect query_alloc_block_size value: '0' +Warning 1292 Truncated incorrect query_alloc_block_size value: '-2' SELECT @@session.query_alloc_block_size; @@session.query_alloc_block_size 1024 diff --git a/mysql-test/suite/sys_vars/r/query_cache_limit_basic_32.result b/mysql-test/suite/sys_vars/r/query_cache_limit_basic_32.result index bb66233732d..4fab29b8952 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_limit_basic_32.result +++ b/mysql-test/suite/sys_vars/r/query_cache_limit_basic_32.result @@ -32,6 +32,8 @@ SELECT @@global.query_cache_limit; 1048575 '#--------------------FN_DYNVARS_131_04-------------------------#' SET @@global.query_cache_limit = -1; +Warnings: +Warning 1292 Truncated incorrect query_cache_limit value: '-1' SELECT @@global.query_cache_limit; @@global.query_cache_limit 0 @@ -53,6 +55,8 @@ SELECT @@global.query_cache_limit; @@global.query_cache_limit 4294967295 SET @@global.query_cache_limit = -1024; +Warnings: +Warning 1292 Truncated incorrect query_cache_limit value: '-1024' SELECT @@global.query_cache_limit; @@global.query_cache_limit 0 diff --git a/mysql-test/suite/sys_vars/r/query_cache_min_res_unit_basic_32.result b/mysql-test/suite/sys_vars/r/query_cache_min_res_unit_basic_32.result index e0d8a0d2a30..b6e274d6ad2 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_min_res_unit_basic_32.result +++ b/mysql-test/suite/sys_vars/r/query_cache_min_res_unit_basic_32.result @@ -42,6 +42,8 @@ SELECT @@global.query_cache_min_res_unit; 1048576 '#--------------------FN_DYNVARS_132_04-------------------------#' SET @@global.query_cache_min_res_unit = -1; +Warnings: +Warning 1292 Truncated incorrect query_cache_min_res_unit value: '-1' SELECT @@global.query_cache_min_res_unit; @@global.query_cache_min_res_unit 512 @@ -61,6 +63,8 @@ SELECT @@global.query_cache_min_res_unit; @@global.query_cache_min_res_unit 512 SET @@global.query_cache_min_res_unit = -1024; +Warnings: +Warning 1292 Truncated incorrect query_cache_min_res_unit value: '-1024' SELECT @@global.query_cache_min_res_unit; @@global.query_cache_min_res_unit 512 diff --git a/mysql-test/suite/sys_vars/r/query_cache_size_basic_32.result b/mysql-test/suite/sys_vars/r/query_cache_size_basic_32.result index 1793726b2f1..5ceb02182b3 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/query_cache_size_basic_32.result @@ -41,6 +41,8 @@ SELECT @@global.query_cache_size; 1047552 '#--------------------FN_DYNVARS_133_04-------------------------#' SET @@global.query_cache_size = -1; +Warnings: +Warning 1292 Truncated incorrect query_cache_size value: '-1' SELECT @@global.query_cache_size; @@global.query_cache_size 0 @@ -61,6 +63,8 @@ SELECT @@global.query_cache_size; @@global.query_cache_size 0 SET @@global.query_cache_size = -1024; +Warnings: +Warning 1292 Truncated incorrect query_cache_size value: '-1024' SELECT @@global.query_cache_size; @@global.query_cache_size 0 diff --git a/mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result b/mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result index 71ee11a5235..6b90b61a035 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result +++ b/mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result @@ -53,7 +53,7 @@ LOCK TABLE t1 WRITE; ** Asynchronous Execution ** SELECT * FROM t1; ** Connection con0 ** -Sleeping 2 Seconds before unlock +wait until table is locked UNLOCK TABLES; ** Connection con1 ** ** Asynchronous Result ** @@ -108,8 +108,17 @@ id value 1 val1 2 val2 3 val3 +SELECT * FROM t1; +id value +1 val1 +2 val2 +3 val3 +SELECT * FROM t1; +id value +1 val1 +2 val2 +3 val3 ** Connection con0 ** -Sleeping 2 Seconds before unlock UNLOCK TABLES; ** Connection con1 ** '#----------------------------FN_DYNVARS_136_05------------------------#' diff --git a/mysql-test/suite/sys_vars/r/range_alloc_block_size_basic_32.result b/mysql-test/suite/sys_vars/r/range_alloc_block_size_basic_32.result index 95e22dfcf14..9e3b7851d8a 100644 --- a/mysql-test/suite/sys_vars/r/range_alloc_block_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/range_alloc_block_size_basic_32.result @@ -79,7 +79,7 @@ SELECT @@global.range_alloc_block_size; 4096 SET @@global.range_alloc_block_size = -1024; Warnings: -Warning 1292 Truncated incorrect range_alloc_block_size value: '0' +Warning 1292 Truncated incorrect range_alloc_block_size value: '-1024' SELECT @@global.range_alloc_block_size; @@global.range_alloc_block_size 4096 @@ -107,7 +107,7 @@ SELECT @@session.range_alloc_block_size; 4096 SET @@session.range_alloc_block_size = -2; Warnings: -Warning 1292 Truncated incorrect range_alloc_block_size value: '0' +Warning 1292 Truncated incorrect range_alloc_block_size value: '-2' SELECT @@session.range_alloc_block_size; @@session.range_alloc_block_size 4096 diff --git a/mysql-test/suite/sys_vars/r/read_buffer_size_basic.result b/mysql-test/suite/sys_vars/r/read_buffer_size_basic.result index 046ba977c23..6aee841c939 100644 --- a/mysql-test/suite/sys_vars/r/read_buffer_size_basic.result +++ b/mysql-test/suite/sys_vars/r/read_buffer_size_basic.result @@ -155,15 +155,11 @@ SELECT @@global.read_buffer_size= 8200 OR @@global.read_buffer_size= 8228 ; 'Bug: FN_DYNVARS_138_08- Errors are not coming on assigning TRUE/FALSE to variable' '#---------------------FN_DYNVARS_138_09----------------------#' SET @@global.read_buffer_size = 9000; -Warnings: -Warning 1292 Truncated incorrect read_buffer_size value: '9000' SELECT @@read_buffer_size = @@global.read_buffer_size; @@read_buffer_size = @@global.read_buffer_size 0 '#---------------------FN_DYNVARS_138_10----------------------#' SET @@read_buffer_size = 9000; -Warnings: -Warning 1292 Truncated incorrect read_buffer_size value: '9000' SELECT @@read_buffer_size = @@local.read_buffer_size; @@read_buffer_size = @@local.read_buffer_size 1 @@ -172,8 +168,6 @@ SELECT @@local.read_buffer_size = @@session.read_buffer_size; 1 '#---------------------FN_DYNVARS_138_11----------------------#' SET read_buffer_size = 9100; -Warnings: -Warning 1292 Truncated incorrect read_buffer_size value: '9100' SELECT @@read_buffer_size= 8200 OR @@read_buffer_size= 8228 ; @@read_buffer_size= 8200 OR @@read_buffer_size= 8228 1 diff --git a/mysql-test/suite/sys_vars/r/read_rnd_buffer_size_basic.result b/mysql-test/suite/sys_vars/r/read_rnd_buffer_size_basic.result index 817d5a2324a..f2645e4d527 100644 --- a/mysql-test/suite/sys_vars/r/read_rnd_buffer_size_basic.result +++ b/mysql-test/suite/sys_vars/r/read_rnd_buffer_size_basic.result @@ -154,15 +154,11 @@ SELECT @@global.read_rnd_buffer_size= 8200 OR @@global.read_rnd_buffer_size= 822 1 '#---------------------FN_DYNVARS_140_09----------------------#' SET @@global.read_rnd_buffer_size = 9000; -Warnings: -Warning 1292 Truncated incorrect read_rnd_buffer_size value: '9000' SELECT @@read_rnd_buffer_size = @@global.read_rnd_buffer_size; @@read_rnd_buffer_size = @@global.read_rnd_buffer_size 0 '#---------------------FN_DYNVARS_140_10----------------------#' SET @@read_rnd_buffer_size = 9000; -Warnings: -Warning 1292 Truncated incorrect read_rnd_buffer_size value: '9000' SELECT @@read_rnd_buffer_size = @@local.read_rnd_buffer_size; @@read_rnd_buffer_size = @@local.read_rnd_buffer_size 1 @@ -171,8 +167,6 @@ SELECT @@local.read_rnd_buffer_size = @@session.read_rnd_buffer_size; 1 '#---------------------FN_DYNVARS_140_11----------------------#' SET read_rnd_buffer_size = 9100; -Warnings: -Warning 1292 Truncated incorrect read_rnd_buffer_size value: '9100' SELECT @@read_rnd_buffer_size= 8200 OR @@read_rnd_buffer_size= 8228; @@read_rnd_buffer_size= 8200 OR @@read_rnd_buffer_size= 8228 1 diff --git a/mysql-test/suite/sys_vars/r/rpl_init_slave_func.result b/mysql-test/suite/sys_vars/r/rpl_init_slave_func.result index 0d1f4d483d8..5f730bff882 100644 --- a/mysql-test/suite/sys_vars/r/rpl_init_slave_func.result +++ b/mysql-test/suite/sys_vars/r/rpl_init_slave_func.result @@ -12,7 +12,7 @@ DROP TABLE IF EXISTS t1; CREATE TEMPORARY TABLE t1 AS SELECT @@global.init_slave AS my_column; DESCRIBE t1; Field Type Null Key Default Extra -my_column longtext NO NULL +my_column varchar(59) NO DROP TABLE t1; SELECT @@global.init_slave = 'SET @@global.max_connections = @@global.max_connections + 1'; @@global.init_slave = 'SET @@global.max_connections = @@global.max_connections + 1' diff --git a/mysql-test/suite/sys_vars/r/rpl_max_binlog_size_func.result b/mysql-test/suite/sys_vars/r/rpl_max_binlog_size_func.result index 0d0a1ae03c4..57b70814698 100644 --- a/mysql-test/suite/sys_vars/r/rpl_max_binlog_size_func.result +++ b/mysql-test/suite/sys_vars/r/rpl_max_binlog_size_func.result @@ -1,8 +1,11 @@ DROP TABLE IF EXISTS t1; -'--- check if log file is rotated after 4096 bytes ----' +'--- check if log file is rotated after 4096 bytes ----' SET @saved_max_binlog_size= @@global.max_binlog_size; SET @@global.max_binlog_size = 4096; CREATE TABLE t1(a CHAR(5)); +SELECT COUNT(*) FROM t1; +COUNT(*) +50 'mylog.000002 exists' SET @@global.max_binlog_size= @saved_max_binlog_size; DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/rpl_recovery_rank_basic_32.result b/mysql-test/suite/sys_vars/r/rpl_recovery_rank_basic_32.result index 2f0c9beea9c..3b7ecf9dc4a 100644 --- a/mysql-test/suite/sys_vars/r/rpl_recovery_rank_basic_32.result +++ b/mysql-test/suite/sys_vars/r/rpl_recovery_rank_basic_32.result @@ -51,14 +51,20 @@ SET @@local.rpl_recovery_rank = 4; ERROR HY000: Variable 'rpl_recovery_rank' is a GLOBAL variable and should be set with SET GLOBAL '#------------------FN_DYNVARS_142_04-----------------------#' SET @@global.rpl_recovery_rank = -1; +Warnings: +Warning 1292 Truncated incorrect rpl_recovery_rank value: '-1' SELECT @@global.rpl_recovery_rank; @@global.rpl_recovery_rank 0 SET @@global.rpl_recovery_rank = -2147483648; +Warnings: +Warning 1292 Truncated incorrect rpl_recovery_rank value: '-2147483648' SELECT @@global.rpl_recovery_rank; @@global.rpl_recovery_rank 0 SET @@global.rpl_recovery_rank = -2147483649; +Warnings: +Warning 1292 Truncated incorrect rpl_recovery_rank value: '-2147483649' SELECT @@global.rpl_recovery_rank; @@global.rpl_recovery_rank 0 diff --git a/mysql-test/suite/sys_vars/r/server_id_basic_32.result b/mysql-test/suite/sys_vars/r/server_id_basic_32.result index b1e74736a10..12d49248f54 100644 --- a/mysql-test/suite/sys_vars/r/server_id_basic_32.result +++ b/mysql-test/suite/sys_vars/r/server_id_basic_32.result @@ -52,10 +52,14 @@ SET @@local.server_id = 4; ERROR HY000: Variable 'server_id' is a GLOBAL variable and should be set with SET GLOBAL '#------------------FN_DYNVARS_144_05-----------------------#' SET @@global.server_id = -1; +Warnings: +Warning 1292 Truncated incorrect server_id value: '-1' SELECT @@global.server_id; @@global.server_id 0 SET @@global.server_id = -2147483648; +Warnings: +Warning 1292 Truncated incorrect server_id value: '-2147483648' SELECT @@global.server_id; @@global.server_id 0 diff --git a/mysql-test/suite/sys_vars/r/slave_transaction_retries_basic_32.result b/mysql-test/suite/sys_vars/r/slave_transaction_retries_basic_32.result index 5b77c500aa0..1b6edb18f8d 100644 --- a/mysql-test/suite/sys_vars/r/slave_transaction_retries_basic_32.result +++ b/mysql-test/suite/sys_vars/r/slave_transaction_retries_basic_32.result @@ -57,6 +57,8 @@ SET @@local.slave_transaction_retries = 4; ERROR HY000: Variable 'slave_transaction_retries' is a GLOBAL variable and should be set with SET GLOBAL '#------------------FN_DYNVARS_149_05-----------------------#' SET @@global.slave_transaction_retries = -1; +Warnings: +Warning 1292 Truncated incorrect slave_transaction_retries value: '-1' SELECT @@global.slave_transaction_retries; @@global.slave_transaction_retries 0 diff --git a/mysql-test/suite/sys_vars/r/slow_query_log_func.result b/mysql-test/suite/sys_vars/r/slow_query_log_func.result index 83edb4c187e..d5485e2e6d7 100644 --- a/mysql-test/suite/sys_vars/r/slow_query_log_func.result +++ b/mysql-test/suite/sys_vars/r/slow_query_log_func.result @@ -17,8 +17,8 @@ TRUNCATE mysql.slow_log; SELECT sleep(2); sleep(2) 0 -SELECT count(*) FROM mysql.slow_log; -count(*) +SELECT count(*) > 0 FROM mysql.slow_log; +count(*) > 0 1 SET @@global.log_output = @global_log_output; SET @global.slow_query_log = @global_slow_query_log; diff --git a/mysql-test/suite/sys_vars/r/sql_low_priority_updates_func.result b/mysql-test/suite/sys_vars/r/sql_low_priority_updates_func.result index f0874ae7414..584ca4c6f8f 100644 --- a/mysql-test/suite/sys_vars/r/sql_low_priority_updates_func.result +++ b/mysql-test/suite/sys_vars/r/sql_low_priority_updates_func.result @@ -29,7 +29,6 @@ LOCK TABLE t1 READ; SELECT * FROM t1; UNLOCK TABLES;| ** Connection default ** -Sleeping for 1 secs UNLOCK TABLES; ** Connection con0 ** ** Asynchronous Result ** @@ -66,7 +65,6 @@ LOCK TABLE t1 READ; SELECT * FROM t1; UNLOCK TABLES;| ** Connection default ** -Sleeping for 1 secs UNLOCK TABLES; ** Connection con0 ** ** Asynchronous Result ** diff --git a/mysql-test/suite/sys_vars/r/sync_binlog_basic_32.result b/mysql-test/suite/sys_vars/r/sync_binlog_basic_32.result index 3d9bfb6d218..cae626ad63b 100644 --- a/mysql-test/suite/sys_vars/r/sync_binlog_basic_32.result +++ b/mysql-test/suite/sys_vars/r/sync_binlog_basic_32.result @@ -36,47 +36,57 @@ SELECT @@global.sync_binlog; 65536 '#--------------------FN_DYNVARS_168_04-------------------------#' SET @@global.sync_binlog = -1; +Warnings: +Warning 1292 Truncated incorrect sync_binlog value: '-1' SELECT @@global.sync_binlog; @@global.sync_binlog 0 SET @@global.sync_binlog = 4294967296; +Warnings: +Warning 1292 Truncated incorrect sync_binlog value: '4294967296' SELECT @@global.sync_binlog; @@global.sync_binlog -0 +4294967295 SET @@global.sync_binlog = 10240022115; +Warnings: +Warning 1292 Truncated incorrect sync_binlog value: '10240022115' SELECT @@global.sync_binlog; @@global.sync_binlog -1650087523 +4294967295 SET @@global.sync_binlog = 10000.01; ERROR 42000: Incorrect argument type to variable 'sync_binlog' SELECT @@global.sync_binlog; @@global.sync_binlog -1650087523 +4294967295 SET @@global.sync_binlog = -1024; +Warnings: +Warning 1292 Truncated incorrect sync_binlog value: '-1024' SELECT @@global.sync_binlog; @@global.sync_binlog 0 SET @@global.sync_binlog = 42949672950; +Warnings: +Warning 1292 Truncated incorrect sync_binlog value: '42949672950' SELECT @@global.sync_binlog; @@global.sync_binlog -4294967286 +4294967295 'Bug # 34837: Errors are not coming on assigning invalid values to variable'; SET @@global.sync_binlog = ON; ERROR 42000: Incorrect argument type to variable 'sync_binlog' SELECT @@global.sync_binlog; @@global.sync_binlog -4294967286 +4294967295 SET @@global.sync_binlog = 'test'; ERROR 42000: Incorrect argument type to variable 'sync_binlog' SELECT @@global.sync_binlog; @@global.sync_binlog -4294967286 +4294967295 '#-------------------FN_DYNVARS_168_05----------------------------#' SET @@session.sync_binlog = 0; ERROR HY000: Variable 'sync_binlog' is a GLOBAL variable and should be set with SET GLOBAL SELECT @@sync_binlog; @@sync_binlog -4294967286 +4294967295 '#----------------------FN_DYNVARS_168_06------------------------#' SELECT @@global.sync_binlog = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES @@ -88,7 +98,7 @@ SET sync_binlog = 1; ERROR HY000: Variable 'sync_binlog' is a GLOBAL variable and should be set with SET GLOBAL SELECT @@sync_binlog; @@sync_binlog -4294967286 +4294967295 SET local.sync_binlog = 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sync_binlog = 1' at line 1 SELECT local.sync_binlog; diff --git a/mysql-test/suite/sys_vars/r/tmp_table_size_basic.result b/mysql-test/suite/sys_vars/r/tmp_table_size_basic.result index 3b4099d30ae..2ebeb8d61d8 100644 --- a/mysql-test/suite/sys_vars/r/tmp_table_size_basic.result +++ b/mysql-test/suite/sys_vars/r/tmp_table_size_basic.result @@ -51,7 +51,7 @@ SELECT @@global.tmp_table_size; 1024 SET @@global.tmp_table_size = -1024; Warnings: -Warning 1292 Truncated incorrect tmp_table_size value: '0' +Warning 1292 Truncated incorrect tmp_table_size value: '-1024' SELECT @@global.tmp_table_size; @@global.tmp_table_size 1024 @@ -100,6 +100,8 @@ SELECT @@session.tmp_table_size; SET @@session.tmp_table_size = "Test"; ERROR 42000: Incorrect argument type to variable 'tmp_table_size' SET @@session.tmp_table_size = 12345678901; +Warnings: +Warning 1292 Truncated incorrect tmp_table_size value: '12345678901' SELECT @@session.tmp_table_size IN (12345678901,4294967295); @@session.tmp_table_size IN (12345678901,4294967295) 1 diff --git a/mysql-test/suite/sys_vars/r/transaction_alloc_block_size_basic_32.result b/mysql-test/suite/sys_vars/r/transaction_alloc_block_size_basic_32.result index 4aaf67c4064..8c6a788862d 100644 --- a/mysql-test/suite/sys_vars/r/transaction_alloc_block_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/transaction_alloc_block_size_basic_32.result @@ -71,7 +71,7 @@ SELECT @@global.transaction_alloc_block_size; 1024 SET @@global.transaction_alloc_block_size = -1024; Warnings: -Warning 1292 Truncated incorrect transaction_alloc_block_size value: '0' +Warning 1292 Truncated incorrect transaction_alloc_block_size value: '-1024' SELECT @@global.transaction_alloc_block_size; @@global.transaction_alloc_block_size 1024 diff --git a/mysql-test/suite/sys_vars/r/transaction_prealloc_size_basic_32.result b/mysql-test/suite/sys_vars/r/transaction_prealloc_size_basic_32.result index 3df7a55595e..4912653a8e5 100644 --- a/mysql-test/suite/sys_vars/r/transaction_prealloc_size_basic_32.result +++ b/mysql-test/suite/sys_vars/r/transaction_prealloc_size_basic_32.result @@ -66,7 +66,7 @@ SELECT @@global.transaction_prealloc_size; 1024 SET @@global.transaction_prealloc_size = -1024; Warnings: -Warning 1292 Truncated incorrect transaction_prealloc_size value: '0' +Warning 1292 Truncated incorrect transaction_prealloc_size value: '-1024' SELECT @@global.transaction_prealloc_size; @@global.transaction_prealloc_size 1024 diff --git a/mysql-test/suite/sys_vars/r/wait_timeout_basic_32.result b/mysql-test/suite/sys_vars/r/wait_timeout_basic_32.result index ae03e677e56..c9bffc61b6f 100644 --- a/mysql-test/suite/sys_vars/r/wait_timeout_basic_32.result +++ b/mysql-test/suite/sys_vars/r/wait_timeout_basic_32.result @@ -44,7 +44,7 @@ Warnings: Warning 1292 Truncated incorrect wait_timeout value: '0' SET @@global.wait_timeout = -1024; Warnings: -Warning 1292 Truncated incorrect wait_timeout value: '0' +Warning 1292 Truncated incorrect wait_timeout value: '-1024' 'Bug # 34837: Errors are not coming on assigning invalid values to variable'; SET @@global.wait_timeout = ON; ERROR 42000: Incorrect argument type to variable 'wait_timeout' diff --git a/mysql-test/suite/sys_vars/t/concurrent_insert_func.test b/mysql-test/suite/sys_vars/t/concurrent_insert_func.test index cdeb76d9cac..1a600ffd7f6 100644 --- a/mysql-test/suite/sys_vars/t/concurrent_insert_func.test +++ b/mysql-test/suite/sys_vars/t/concurrent_insert_func.test @@ -11,6 +11,11 @@ # Creation Date: 2008-03-07 # # Author: Salman Rawala # # # +# Modified: HHunger 2009-02-23 Inserted a wait condition right after the # +# "INSERT ..record_6" to wait for the end of # +# the insert. # +# mleich This test needs some inporovements # +# # # Description: Test Cases of Dynamic System Variable "concurrent_insert" # # that checks functionality of this variable # # # @@ -62,7 +67,6 @@ connection test_con1; INSERT INTO t1(name) VALUES('Record_4'); SELECT * FROM t1; - --echo ## unlocking tables ## --echo connection default; connection default; @@ -106,6 +110,8 @@ UNLOCK TABLES; --echo ## table contens after UNLOCK ## SELECT * FROM t1; INSERT INTO t1(name) VALUES('Record_6'); +let $wait_condition= SELECT COUNT(*) = 5 FROM t1; +--source include/wait_condition.inc --echo connection test_con1; connection test_con1; diff --git a/mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test b/mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test index 97e1f2daed1..61f7d801a1a 100644 --- a/mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test +++ b/mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test @@ -1,23 +1,26 @@ -############# mysql-test\t\sql_low_priority_updates_func.test ########################### -# # -# Variable Name: sql_low_priority_updates # -# Scope: GLOBAL # -# Access Type: Dynamic # -# Data Type: BOOLEAN # -# Default Value: 1 TRUE # -# Values: 1 TRUE, 0 FALSE # -# # -# # -# Creation Date: 2008-02-25 # -# Author: Sharique Abdullah # -# # -# Description: Test Cases of Dynamic System Variable "sql_low_priority_updates" # -# that checks behavior of this variable in the following ways # -# * Functionality based on different values # -# # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html # -# # -######################################################################################### +################################################################################ +# # +# Variable Name: sql_low_priority_updates # +# Scope: GLOBAL # +# Access Type: Dynamic # +# Data Type: BOOLEAN # +# Default Value: 1 TRUE # +# Values: 1 TRUE, 0 FALSE # +# # +# # +# Creation Date: 2008-02-25 # +# Author: Sharique Abdullah # +# Modified: HHunger 2009-02-26 Replaced 2 sleeps by wait conditions # +# Modified: mleich 2009-03-18 Partially reimplemented # +# # +# Description: Test Cases of Dynamic System Variable "sql_low_priority_updates"# +# that checks behavior of this variable in the following ways # +# * Functionality based on different values # +# # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # +# # +################################################################################ --echo ** Setup ** --echo @@ -29,8 +32,10 @@ --echo Creating connection con0 connect (con0,localhost,root,,); +let $con0_id=`SELECT CONNECTION_ID()`; --echo Creating connection con1 connect (con1,localhost,root,,); +let $con1_id=`SELECT CONNECTION_ID()`; connection default; @@ -40,31 +45,20 @@ SET @global_delayed_insert_limit = @@GLOBAL.delayed_insert_limit; # Create Table # -CREATE TABLE t1 (a varchar(100)); +CREATE TABLE t1 (a VARCHAR(100),b VARCHAR(100),c VARCHAR(100)); --echo '#--------------------FN_DYNVARS_25_01-------------------------#' -# -# Value less than the provided INSERTS (9) -# -SET GLOBAL delayed_insert_limit = 9; +# delayed_insert_limit is smaller than the number of inserted rows ---echo ** Connection con0 ** -connection con0; -SET GLOBAL delayed_insert_limit = 9; ---echo ** Connection con1 ** -connection con1; -SET GLOBAL delayed_insert_limit = 9; ---echo ** Connection default ** -connection default; -SET GLOBAL delayed_insert_limit = 9; +SET GLOBAL delayed_insert_limit = 14; -INSERT INTO t1 VALUES('1'); -INSERT INTO t1 VALUES('2'); -INSERT INTO t1 VALUES('3'); -INSERT INTO t1 VALUES('4'); -INSERT INTO t1 VALUES('5'); -INSERT INTO t1 VALUES('6'); +INSERT INTO t1 VALUES('1','1','1'); +INSERT INTO t1 VALUES('2','1','1'); +INSERT INTO t1 VALUES('3','1','1'); +INSERT INTO t1 VALUES('4','1','1'); +INSERT INTO t1 VALUES('5','1','1'); +INSERT INTO t1 VALUES('6','1','1'); LOCK TABLE t1 WRITE; @@ -72,76 +66,94 @@ LOCK TABLE t1 WRITE; connection con1; delimiter |; - send -INSERT DELAYED INTO t1 VALUES('7'); -INSERT DELAYED INTO t1 VALUES('8'); -INSERT DELAYED INTO t1 VALUES('9'); -INSERT DELAYED INTO t1 VALUES('10'); -INSERT DELAYED INTO t1 VALUES('11'); -INSERT DELAYED INTO t1 VALUES('12'); -INSERT DELAYED INTO t1 VALUES('13'); -INSERT DELAYED INTO t1 VALUES('14'); -INSERT DELAYED INTO t1 VALUES('15'); -INSERT DELAYED INTO t1 VALUES('16'); -INSERT DELAYED INTO t1 VALUES('17'); -INSERT DELAYED INTO t1 VALUES('18'); -INSERT DELAYED INTO t1 VALUES('19'); -INSERT DELAYED INTO t1 VALUES('20'); -INSERT DELAYED INTO t1 VALUES('21'); -INSERT DELAYED INTO t1 VALUES('22');| - +INSERT DELAYED INTO t1 VALUES('7','1','1'); +INSERT DELAYED INTO t1 VALUES('8','1','1'); +INSERT DELAYED INTO t1 VALUES('9','1','1'); +INSERT DELAYED INTO t1 VALUES('10','1','1'); +INSERT DELAYED INTO t1 VALUES('11','1','1'); +INSERT DELAYED INTO t1 VALUES('12','1','1'); +INSERT DELAYED INTO t1 VALUES('13','1','1'); +INSERT DELAYED INTO t1 VALUES('14','1','1'); +INSERT DELAYED INTO t1 VALUES('15','1','1'); +INSERT DELAYED INTO t1 VALUES('16','1','1'); +INSERT DELAYED INTO t1 VALUES('17','1','1'); +INSERT DELAYED INTO t1 VALUES('18','1','1'); +INSERT DELAYED INTO t1 VALUES('19','1','1'); +INSERT DELAYED INTO t1 VALUES('20','1','1'); +INSERT DELAYED INTO t1 VALUES('21','1','1'); +INSERT DELAYED INTO t1 VALUES('22','1','1'); +INSERT DELAYED INTO t1 VALUES('23','1','1'); +INSERT DELAYED INTO t1 VALUES('24','1','1'); +INSERT DELAYED INTO t1 VALUES('25','1','1'); +INSERT DELAYED INTO t1 VALUES('26','1','1'); +INSERT DELAYED INTO t1 VALUES('27','1','1'); +INSERT DELAYED INTO t1 VALUES('28','1','1'); +INSERT DELAYED INTO t1 VALUES('29','1','1'); +INSERT DELAYED INTO t1 VALUES('30','1','1'); +INSERT DELAYED INTO t1 VALUES('31','1','1'); +INSERT DELAYED INTO t1 VALUES('32','1','1'); +INSERT DELAYED INTO t1 VALUES('33','1','1'); +INSERT DELAYED INTO t1 VALUES('34','1','1'); +INSERT DELAYED INTO t1 VALUES('35','1','1'); +INSERT DELAYED INTO t1 VALUES('36','1','1'); +INSERT DELAYED INTO t1 VALUES('37','1','1'); +INSERT DELAYED INTO t1 VALUES('38','1','1'); +INSERT DELAYED INTO t1 VALUES('39','1','1'); +INSERT DELAYED INTO t1 VALUES('40','1','1'); +INSERT DELAYED INTO t1 VALUES('41','1','1'); +INSERT DELAYED INTO t1 VALUES('42','1','1'); +INSERT DELAYED INTO t1 VALUES('43','1','1');| delimiter ;| --echo ** Connection con0 ** connection con0; - -delimiter |; - -send -SELECT * FROM t1;| - -delimiter ;| +let $wait_condition= + SELECT variable_value > 0 FROM information_schema.global_status + WHERE variable_name like 'Not_flushed_delayed_rows'; +--source include/wait_condition.inc +let $my_select= SELECT COUNT(*) BETWEEN 21 AND 43 FROM t1; +let $my_select= SELECT COUNT(*) FROM t1; +send; +eval $my_select; --echo ** Connection default ** connection default; - ---echo Waiting for 1 sec ---sleep 1 - +--echo ** Wait till con0 is blocked ** +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = 'Locked' AND info = '$my_select'; +--source include/wait_condition.inc UNLOCK TABLES; +--echo ** Connection con1 ** +connection con1; +--echo Asynchronous "reap" result +reap; + --echo ** Connection con0 ** connection con0; +--echo Asynchronous "reap" result +--echo The next result suffers from +--echo '# Bug#35386 insert delayed inserts 1 + limit rows instead of just limit rows' reap; ---echo 'Bug#35386: insert delayed inserts 1 + limit rows instead of just limit rows' --echo ** Connection default ** connection default; - ---echo Waiting for 1 sec ---sleep 1 +let $wait_condition= SELECT count(*) = 43 FROM t1; +--source include/wait_condition.inc --echo Checking if the delayed insert continued afterwards -SELECT * FROM t1; +SELECT COUNT(*) FROM t1; -DELETE FROM t1; +DROP TABLE t1; --echo '#--------------------FN_DYNVARS_25_02-------------------------#' -# -# Value 5 -# -SET GLOBAL delayed_insert_limit = 20; +# delayed_insert_limit is bigger than the number of inserted rows + +CREATE TABLE t1 (a VARCHAR(100)); ---echo ** Connection con0 ** -connection con0; -SET GLOBAL delayed_insert_limit = 20; ---echo ** Connection con1 ** -connection con1; -SET GLOBAL delayed_insert_limit = 20; ---echo ** Connection default ** -connection default; SET GLOBAL delayed_insert_limit = 20; INSERT INTO t1 VALUES('1'); @@ -181,49 +193,52 @@ delimiter ;| --echo ** Connection con0 ** connection con0; - +let $wait_condition= + SELECT variable_value > 0 FROM information_schema.global_status + WHERE variable_name like 'Not_flushed_delayed_rows'; +--source include/wait_condition.inc --echo Asynchronous execute -delimiter |; - -send -SELECT * FROM t1;| - -delimiter ;| +let $my_select= SELECT COUNT(*) = 22 FROM t1; +send; +eval $my_select; --echo ** Connection default ** connection default; - ---echo Waiting for 1 sec ---sleep 1 - +--echo ** Wait till con0 is blocked ** +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = 'Locked' AND info = '$my_select'; +--source include/wait_condition.inc UNLOCK TABLES; +--echo ** Connection con1 ** +connection con1; +reap; + --echo ** Connection con0 ** connection con0; ---echo Asynchronous execute result +--echo Asynchronous "reap" result reap; --echo ** Connection default** connection default; - ---echo Waiting for 1 sec ---sleep 1 --echo Checking if the delayed insert gives the same result afterwards -SELECT * FROM t1; +eval $my_select; -DELETE FROM t1; # # Cleanup # ---echo Switching to default +--echo ** Connection default** connection default; +DROP TABLE t1; +SET @@GLOBAL.delayed_insert_limit = @global_delayed_insert_limit; --echo Disconnecting from con1, con0 disconnect con0; disconnect con1; - -DROP TABLE t1; - -SET @@GLOBAL.delayed_insert_limit = @global_delayed_insert_limit; +let $wait_condition= + SELECT COUNT(*) = 0 FROM information_schema.processlist + WHERE id IN ($con0_id,$con1_id); +--source include/wait_condition.inc diff --git a/mysql-test/suite/sys_vars/t/event_scheduler_func.test b/mysql-test/suite/sys_vars/t/event_scheduler_func.test deleted file mode 100644 index f727b2b6833..00000000000 --- a/mysql-test/suite/sys_vars/t/event_scheduler_func.test +++ /dev/null @@ -1,91 +0,0 @@ -############## mysql-test\t\event_scheduler_func.test ########################## -# # -# Variable Name: event_scheduler # -# Scope: GLOBAL # -# Access Type: Dynamic # -# Data Type: Boolean # -# Default Value: OFF # -# Valid Values: ON, OFF & DISABLED # -# # -# # -# Creation Date: 2008-03-17 # -# Author: Salman Rawala # -# # -# Description: Test Cases of Dynamic System Variable "event_scheduler" # -# that checks functionality of this variable # -# # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html#option_mysqld_event_scheduler # -# # -################################################################################ - --- source include/not_embedded.inc - ---disable_warnings -drop table if exists t1; ---enable_warnings - -######################### -# Creating new table # -######################### - ---echo ## Creating new table ## -CREATE TABLE t1 -( -id INT NOT NULL auto_increment, -PRIMARY KEY (id), -name VARCHAR(30) -); - ---echo '#--------------------FN_DYNVARS_018_01-------------------------#' -#################################################################### -# Setting initial value of event_scheduler to ON and verifying -# its behavior -#################################################################### - ---echo ## Setting initial value of variable to ON ## -SET @@global.event_scheduler = ON; -SELECT @@event_scheduler; - ---echo ## Creating new event ## -CREATE EVENT test_event_1 -ON SCHEDULE EVERY 3 SECOND -DO - INSERT into t1(name) values('Record_1'); - ---sleep 4 - -SELECT * from t1; - -DROP EVENT test_event_1; - ---sleep 1 -DELETE from t1; -select * from t1; - - ---echo '#--------------------FN_DYNVARS_018_02-------------------------#' -#################################################################### -# Setting initial value of event_scheduler to OFF and verifying -# its behavior -#################################################################### - ---echo ## Setting value of variable to OFF ## -SET @@global.event_scheduler = OFF; -SELECT @@event_scheduler; - ---echo ## Creating new event ## -CREATE EVENT test_event_1 -ON SCHEDULE EVERY 3 SECOND -DO - INSERT into t1(name) values('Record_2'); - ---sleep 4 - ---echo ## Table should be empty ## -SELECT * from t1; - -DROP EVENT test_event_1; ---echo ## Dropping table ## -DROP table t1; - diff --git a/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test b/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test index d077a3acf50..b577ae5fcc4 100644 --- a/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test +++ b/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test @@ -1,4 +1,4 @@ -################# mysql-test\t\innodb_max_dirty_pages_pct_func.test ########## +############################################################################### # # # Variable Name: innodb_max_dirty_pages_pct # # Scope: GLOBAL # diff --git a/mysql-test/suite/sys_vars/t/interactive_timeout_func.test b/mysql-test/suite/sys_vars/t/interactive_timeout_func.test index e3a210bf508..0df069ad73e 100644 --- a/mysql-test/suite/sys_vars/t/interactive_timeout_func.test +++ b/mysql-test/suite/sys_vars/t/interactive_timeout_func.test @@ -1,23 +1,26 @@ -############## mysql-test\t\interactive_timeout_func.test ##################### -# # -# Variable Name: interactive_timeout # -# Scope: GLOBAL | SESSION # -# Access Type: Dynamic # -# Data Type: numeric # -# Default Value:28800 # -# Minvalue: 1 # -# # -# # -# Creation Date: 2008-03-07 # -# Author: Salman Rawala # -# # -# Description: Test Cases of Dynamic System Variable interactive_timeout # -# that checks the functionality of this variable # -# # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # -# # -############################################################################### +################################################################################ +# # +# Variable Name: interactive_timeout # +# Scope: GLOBAL | SESSION # +# Access Type: Dynamic # +# Data Type: numeric # +# Default Value:28800 # +# Minvalue: 1 # +# # +# # +# Creation Date: 2008-03-07 # +# Author: Salman Rawala # +# # +# Description: Test Cases of Dynamic System Variable interactive_timeout # +# that checks the functionality of this variable # +# Modified: HHunger 2009-02-26 Inserted clean up, beautifications. # +# It is not yet possible to set CLIENT_INTERACIVE # +# When connecting, so the test has not the # +# desired effect. See 'wait_timeout_func'. # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # +# # +################################################################################ --disable_warnings @@ -42,29 +45,44 @@ name VARCHAR(30) # Setting initial value of interactive_timeout to 1 and verifying its # behavior ####################################################################### +let $start_value= `SELECT @@global.interactive_timeout`; --echo ## Setting initial value of variable to 1 ## SET @@global.interactive_timeout = 1; ---echo ## Creating new interactive connection test_con1 ## -connect (test_con1, localhost, root,); +--echo ## Creating new connection test_con1 ## +# Not yet possible to set CLEAN_INTERACTIVE flag +connect (test_con1, localhost, root,,,,,); connection test_con1; --echo ## Inserting record in table ## INSERT into t1(name) values('Record_1'); ---echo ## Setting session value of interactive_timeout ## +--echo ## Setting session value of interactive_timeout ## SET @@session.interactive_timeout = 1; ---echo ## Verifying values of variable ## +--echo ## Verifying values of variable ## SELECT @@session.interactive_timeout; SELECT @@global.interactive_timeout; ---echo ## Using sleep to check timeout ## -sleep 5; -SELECT * from t1; +--echo connection default; +connection default; +--echo ## Using sleep to check timeout ## +--echo sleep 2; +sleep 2; ---echo 'Bug#35377: Error should appear here because interactive_timeout value'; ---echo 'is 1 and connection remains idle for 5 secs'; +--echo connection test_con1; +connection test_con1; +SELECT * from t1; INSERT into t1(name) values('Record_2'); + +--echo connection default; +connection default; + +--echo disconnect test_con1; +disconnect test_con1; +DROP TABLE t1; + +eval SET @@global.interactive_timeout= $start_value; + diff --git a/mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test b/mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test index 83b0c446b28..e5ced59d175 100644 --- a/mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test +++ b/mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test @@ -1,4 +1,4 @@ -############# mysql-test\t\query_cache_wlock_invalidate_func.test #################### +###################################################################################### # # # Variable Name: query_cache_wlock_invalidate # # Scope: GLOBAL & SESSION # @@ -10,6 +10,7 @@ # # # Creation Date: 2008-02-21 # # Author: Sharique Abdullah # +# Modified: HHunger 2009-02-27 Replaced sleeps, beautifications # # # # Description: Test Cases of Dynamic System Variable "query_cache_wlock_invalidate" # # that checks behavior of this variable in the following ways # @@ -18,8 +19,8 @@ # * Scope & Access method # # * Cache behaviors # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html#option_mysqld_query_cache_wlock_invalidate # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ###################################################################################### @@ -29,6 +30,8 @@ # Setup # +--source include/not_embedded.inc + # disabled due to differences in the result --disable_ps_protocol # @@ -135,8 +138,9 @@ send SELECT * FROM t1; --echo ** Connection con0 ** connection con0; ---echo Sleeping 2 Seconds before unlock ---sleep 2 +--echo wait until table is locked +let $wait_condition= SELECT count(*) > 0 FROM information_schema.processlist WHERE state= 'Locked'; +--source include/wait_condition.inc UNLOCK TABLES; --echo ** Connection con1 ** @@ -195,20 +199,17 @@ SELECT * FROM t1; --echo ** Connection con0 ** connection con0; - LOCK TABLE t1 WRITE; --echo ** Connection con1 ** connection con1; - --echo ** Should not be blocked ** SELECT * FROM t1; +SELECT * FROM t1; +SELECT * FROM t1; --echo ** Connection con0 ** connection con0; - ---echo Sleeping 2 Seconds before unlock ---sleep 2 UNLOCK TABLES; --echo ** Connection con1 ** diff --git a/mysql-test/suite/sys_vars/t/rpl_init_slave_func.test b/mysql-test/suite/sys_vars/t/rpl_init_slave_func.test index f17cc1289b1..f2c66eb3a0a 100644 --- a/mysql-test/suite/sys_vars/t/rpl_init_slave_func.test +++ b/mysql-test/suite/sys_vars/t/rpl_init_slave_func.test @@ -1,4 +1,4 @@ -###################### mysql-test\t\init_slave_func.test ##################### +############################################################################### # # # Variable Name: init_slave # # Scope: GLOBAL # diff --git a/mysql-test/suite/sys_vars/t/rpl_max_binlog_size_func.test b/mysql-test/suite/sys_vars/t/rpl_max_binlog_size_func.test index c00db582deb..efcec4561bd 100644 --- a/mysql-test/suite/sys_vars/t/rpl_max_binlog_size_func.test +++ b/mysql-test/suite/sys_vars/t/rpl_max_binlog_size_func.test @@ -7,9 +7,8 @@ source include/have_log_bin.inc; DROP TABLE IF EXISTS t1; --enable_warnings - #============================================================== ---echo '--- check if log file is rotated after 4096 bytes ----' +--echo '--- check if log file is rotated after 4096 bytes ----' #============================================================== SET @saved_max_binlog_size= @@global.max_binlog_size; @@ -24,8 +23,7 @@ INSERT INTO t1 VALUES ('mysql'); dec $a; } --enable_query_log - ---sleep 2 +SELECT COUNT(*) FROM t1; # if log file is not created then this will fail let $MYSQLD_DATADIR=`select @@datadir`; diff --git a/mysql-test/suite/sys_vars/t/slow_query_log_func.test b/mysql-test/suite/sys_vars/t/slow_query_log_func.test index 9bcbeb14fd7..d2653f89de4 100644 --- a/mysql-test/suite/sys_vars/t/slow_query_log_func.test +++ b/mysql-test/suite/sys_vars/t/slow_query_log_func.test @@ -1,4 +1,3 @@ - # save SET @global_slow_query_log = @@global.slow_query_log; SET @global_log_output = @@global.log_output; @@ -16,6 +15,7 @@ SET @@global.log_output = 'TABLE'; SET @@global.slow_query_log = OFF; TRUNCATE mysql.slow_log; +# The sleep is the slow query SELECT sleep(2); SELECT count(*) FROM mysql.slow_log; @@ -26,9 +26,10 @@ SELECT count(*) FROM mysql.slow_log; SET @@global.slow_query_log = ON; TRUNCATE mysql.slow_log; +# The sleep is the slow query SELECT sleep(2); -SELECT count(*) FROM mysql.slow_log; +SELECT count(*) > 0 FROM mysql.slow_log; #restore SET @@global.log_output = @global_log_output; @@ -37,3 +38,4 @@ SET @global.slow_query_log = @global_slow_query_log; ############################################################################### # End of the functionality test for slow_query_log # ############################################################################### + diff --git a/mysql-test/suite/sys_vars/t/sql_low_priority_updates_func.test b/mysql-test/suite/sys_vars/t/sql_low_priority_updates_func.test index 6ff684af6cd..2ef6e34b0b3 100644 --- a/mysql-test/suite/sys_vars/t/sql_low_priority_updates_func.test +++ b/mysql-test/suite/sys_vars/t/sql_low_priority_updates_func.test @@ -1,23 +1,23 @@ -############# mysql-test\t\sql_low_priority_updates_func.test ################# -# # -# Variable Name: sql_low_priority_updates # -# Scope: GLOBAL & SESSION # -# Access Type: Dynamic # -# Data Type: BOOLEAN # -# Default Value: 1 TRUE # -# Values: 1 TRUE, 0 FALSE # -# # -# # -# Creation Date: 2008-02-25 # -# Author: Sharique Abdullah # -# # -# Description: Test Cases of Dynamic System Variable sql_low_priority_updates# -# that checks behavior of this variable in the following ways # -# * Functionality based on different values # -# # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html # -# # -############################################################################### +################################################################################ +# # +# Variable Name: sql_low_priority_updates # +# Scope: GLOBAL & SESSION # +# Access Type: Dynamic # +# Data Type: BOOLEAN # +# Default Value: 1 TRUE # +# Values: 1 TRUE, 0 FALSE # +# # +# # +# Creation Date: 2008-02-25 # +# Author: Sharique Abdullah # +# # +# Description: Test Cases of Dynamic System Variable sql_low_priority_updates # +# that checks behavior of this variable in the following ways # +# * Functionality based on different values # +# # +# Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html # +# # +################################################################################ --source include/not_embedded.inc @@ -85,6 +85,9 @@ delimiter ;| --echo ** Connection con0 ** connection con0; +let $wait_condition = SELECT COUNT(*) > 0 FROM information_schema.processlist WHERE state='Locked' AND info LIKE 'UPDATE t1 SET a = CONCAT(a,"-updated")'; +--source include/wait_condition.inc + --echo ** Asynchronous Execution ** delimiter |; @@ -98,9 +101,8 @@ delimiter ;| --echo ** Connection default ** connection default; ---echo Sleeping for 1 secs ---sleep 1 - +let $wait_condition= SELECT count(*) = 2 FROM information_schema.processlist WHERE state LIKE 'Locked'; +--source include/wait_condition.inc UNLOCK TABLES; --echo ** Connection con0 ** @@ -153,6 +155,9 @@ delimiter ;| --echo ** Connection con0 ** connection con0; +let $wait_condition = SELECT COUNT(*) > 0 FROM information_schema.processlist WHERE state='Locked' AND info LIKE 'UPDATE t1 SET a = CONCAT(a,"-updated")'; +--source include/wait_condition.inc + --echo ** Asynchronous Execution ** delimiter |; @@ -166,9 +171,8 @@ delimiter ;| --echo ** Connection default ** connection default; ---echo Sleeping for 1 secs ---sleep 1 - +let $wait_condition= SELECT count(*) = 2 FROM information_schema.processlist WHERE state LIKE 'Locked'; +--source include/wait_condition.inc UNLOCK TABLES; --echo ** Connection con0 ** diff --git a/mysql-test/suite/sys_vars/t/timestamp_func.test b/mysql-test/suite/sys_vars/t/timestamp_func.test index e119f1b6253..e93614e89fb 100644 --- a/mysql-test/suite/sys_vars/t/timestamp_func.test +++ b/mysql-test/suite/sys_vars/t/timestamp_func.test @@ -1,4 +1,4 @@ -############# mysql-test\t\timestamp_func.test ############################# +############################################################################ # # # Variable Name: timestamp # # Scope: GLOBAL # diff --git a/mysql-test/suite/sys_vars/t/wait_timeout_func.test b/mysql-test/suite/sys_vars/t/wait_timeout_func.test index 6b7c8d016d2..e33c39016cc 100644 --- a/mysql-test/suite/sys_vars/t/wait_timeout_func.test +++ b/mysql-test/suite/sys_vars/t/wait_timeout_func.test @@ -1,4 +1,4 @@ -############## mysql-test\t\wait_timeout_func.test ############################ +############################################################################### # # # Variable Name: wait_timeout # # Scope: GLOBAL | SESSION # diff --git a/mysql-test/t/ctype_collate.test b/mysql-test/t/ctype_collate.test index cfef8dfe81a..6b6abbcfbcc 100644 --- a/mysql-test/t/ctype_collate.test +++ b/mysql-test/t/ctype_collate.test @@ -229,3 +229,17 @@ insert into t1 set a=0x6c; insert into t1 set a=0x4c98; check table t1 extended; drop table t1; + +# +# Bug#41627 Illegal mix of collations in LEAST / GREATEST / CASE +# +select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci); +create table t1 +select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) as f1; +show create table t1; +drop table t1; + +select case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate +latin5_turkish_ci then 2 else 3 end; + +select concat(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci); diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 3f61176e37b..eab0542314a 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -11,3 +11,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" +#concurrent_innodb_safelog: disabled for embedded server due to bug#43733 Select on processlist let the embedded server crash (concurrent_innodb_safelog). +#concurrent_innodb_unsafelog: disabled for embedded server due to bug#43733. diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test index 8fdda2b772b..dccaecef20a 100644 --- a/mysql-test/t/index_merge_myisam.test +++ b/mysql-test/t/index_merge_myisam.test @@ -19,3 +19,170 @@ let $merge_table_support= 1; --source include/index_merge2.inc --source include/index_merge_2sweeps.inc --source include/index_merge_ror_cpk.inc + +--echo # +--echo # Generic @@optimizer_switch tests (move those into a separate file if +--echo # we get another @@optimizer_switch user) +--echo # + +select @@optimizer_switch; + +set optimizer_switch='index_merge=off,index_merge_union=off'; +select @@optimizer_switch; + +set optimizer_switch='index_merge_union=on'; +select @@optimizer_switch; + +set optimizer_switch='default,index_merge_sort_union=off'; +select @@optimizer_switch; + +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch=4; + +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch=NULL; + +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch='default,index_merge'; + +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch='index_merge=index_merge'; + +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch='index_merge=on,but...'; + +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch='index_merge='; + +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch='index_merge'; + +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch='on'; + +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch='index_merge=on,index_merge=off'; + +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch='index_merge_union=on,index_merge_union=default'; + +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch='default,index_merge=on,index_merge=off,default'; + +set optimizer_switch=default; +set optimizer_switch='index_merge=off,index_merge_union=off,default'; +select @@optimizer_switch; +set optimizer_switch=default; + +# Check setting defaults for global vars +select @@global.optimizer_switch; +set @@global.optimizer_switch=default; +select @@global.optimizer_switch; + +--echo # +--echo # Check index_merge's @@optimizer_switch flags +--echo # +select @@optimizer_switch; + +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int, filler char(100), + key(a), key(b), key(c)); +insert into t1 select + A.a * B.a*10 + C.a*100, + A.a * B.a*10 + C.a*100, + A.a, + 'filler' +from t0 A, t0 B, t0 C; + +--echo This should use union: +explain select * from t1 where a=1 or b=1; + +--echo This should use ALL: +set optimizer_switch='default,index_merge=off'; +explain select * from t1 where a=1 or b=1; + +--echo This should use sort-union: +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a=1 or b=1; + +--echo This will use sort-union: +set optimizer_switch=default; +explain select * from t1 where a<1 or b <1; + +--echo This should use ALL: +set optimizer_switch='default,index_merge_sort_union=off'; +explain select * from t1 where a<1 or b <1; + + +--echo This should use ALL: +set optimizer_switch='default,index_merge=off'; +explain select * from t1 where a<1 or b <1; + +--echo This will use sort-union: +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a<1 or b <1; + +alter table t1 add d int, add key(d); +update t1 set d=a; + +--echo This will use sort_union: +set optimizer_switch=default; +explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); + +--echo And if we disable sort_union, union: +set optimizer_switch='default,index_merge_sort_union=off'; +explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); + +drop table t1; + +# Now test that intersection can be disabled +create table t1 ( + a int, b int, c int, + filler1 char(200), filler2 char(200), + key(a),key(b),key(c) +); + +insert into t1 +select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar' +from t0 A, t0 B, t0 C, t0 D where D.a<5; + +--echo This should be intersect: +set optimizer_switch=default; +explain select * from t1 where a=10 and b=10; + +--echo No intersect when index_merge is disabled: +set optimizer_switch='default,index_merge=off'; +explain select * from t1 where a=10 and b=10; + +--echo No intersect if it is disabled: +set optimizer_switch='default,index_merge_intersection=off'; +explain select * from t1 where a=10 and b=10; + +--echo Do intersect when union was disabled +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a=10 and b=10; + +--echo Do intersect when sort_union was disabled +set optimizer_switch='default,index_merge_sort_union=off'; +explain select * from t1 where a=10 and b=10; + +# Now take union-of-intersection and see how we can disable parts of it +--echo This will use intersection inside a union: +set optimizer_switch=default; +explain select * from t1 where a=10 and b=10 or c=10; + +--echo Should be only union left: +set optimizer_switch='default,index_merge_intersection=off'; +explain select * from t1 where a=10 and b=10 or c=10; + +--echo This will switch to sort-union (intersection will be gone, too, +--echo thats a known limitation: +set optimizer_switch='default,index_merge_union=off'; +explain select * from t1 where a=10 and b=10 or c=10; + +set optimizer_switch=default; +show variables like 'optimizer_switch'; + +drop table t0, t1; + diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index a6b66b48604..5be5d9e3893 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1348,6 +1348,15 @@ CREATE TABLE t1(a INT) KEY_BLOCK_SIZE=1; SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; DROP TABLE t1; +# +# Bug #22047: Time in SHOW PROCESSLIST for SQL thread in replication seems +# to become negative +# + +SET TIMESTAMP=@@TIMESTAMP + 10000000; +SELECT 'OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0; +SET TIMESTAMP=DEFAULT; + --echo End of 5.1 tests. # Wait till all disconnects are completed diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 586cfb174be..e93153f005c 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -262,6 +262,7 @@ DROP DATABASE mysqltest_1; # With bug in place: try to acquire LOCK_mysql_create_table... # When fixed: Reject dropping db because of the read lock. connection con1; +# Wait a bit so that the session con2 is in state "Waiting for release of readlock" let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Waiting for release of readlock" @@ -316,327 +317,6 @@ reap; connection locker; drop table t1; -# -# Bug#38691 segfault/abort in ``UPDATE ...JOIN'' while -# ``FLUSH TABLES WITH READ LOCK'' -# - ---connection default -CREATE TABLE t1 ( - a int(11) unsigned default NULL, - b varchar(255) default NULL, - UNIQUE KEY a (a), - KEY b (b) -); - -INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); -CREATE TABLE t2 SELECT * FROM t1; -CREATE TABLE t3 SELECT * FROM t1; - ---echo # test altering of columns that multiupdate doesn't use - ---echo # normal mode - ---disable_query_log -let $i = 100; -while ($i) { ---dec $i - ---connection writer - send UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) - SET a = NULL WHERE t1.b <> t2.b; - ---connection locker - ALTER TABLE t2 ADD COLUMN (c INT); - ALTER TABLE t2 DROP COLUMN c; - ---connection writer ---reap -} - ---echo # PS mode - ---connection writer -PREPARE stmt FROM 'UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) - SET a = NULL WHERE t1.b <> t2.b'; - -let $i = 100; -while ($i) { ---dec $i - ---connection writer ---send EXECUTE stmt - ---connection locker - ALTER TABLE t2 ADD COLUMN (c INT); - ALTER TABLE t2 DROP COLUMN c; - ---connection writer ---reap -} ---enable_query_log - - ---echo # test altering of columns that multiupdate uses - ---echo # normal mode - ---connection default - ---disable_query_log -let $i = 100; -while ($i) { - dec $i; - ---connection locker ---error 0,ER_DUP_FIELDNAME - ALTER TABLE t2 ADD COLUMN a int(11) unsigned default NULL; - UPDATE t2 SET a=b; - ---connection writer ---send UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) SET a = NULL WHERE t1.b <> t2.b - ---connection locker ---error 0,ER_CANT_DROP_FIELD_OR_KEY - ALTER TABLE t2 DROP COLUMN a; - ---connection writer ---error 0,ER_BAD_FIELD_ERROR ---reap -} ---enable_query_log - ---echo # PS mode - ---disable_query_log -let $i = 100; -while ($i) { - dec $i; - ---connection locker ---error 0,ER_DUP_FIELDNAME - ALTER TABLE t2 ADD COLUMN a int(11) unsigned default NULL; - UPDATE t2 SET a=b; - ---connection writer - PREPARE stmt FROM 'UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) SET a = NULL WHERE t1.b <> t2.b'; ---send EXECUTE stmt - ---connection locker ---error 0,ER_CANT_DROP_FIELD_OR_KEY - ALTER TABLE t2 DROP COLUMN a; - ---connection writer ---error 0,ER_BAD_FIELD_ERROR ---reap - -} ---enable_query_log ---connection default -DROP TABLE t1, t2, t3; - - -# -# Bug#38499 flush tables and multitable table update with derived table cause -# crash -# - -CREATE TABLE t1( a INT, b INT ); -INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4); - ---echo # 1. test regular tables ---echo # 1.1. test altering of columns that multiupdate doesn't use ---echo # 1.1.1. normal mode - ---disable_query_log -let $i = 100; -while ($i) { ---dec $i - ---connection writer - send UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0; - ---connection locker - ALTER TABLE t1 ADD COLUMN (c INT); - ALTER TABLE t1 DROP COLUMN c; - ---connection writer ---reap -} - ---echo # 1.1.2. PS mode - ---connection writer -PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0'; - -let $i = 100; -while ($i) { ---dec $i - ---connection writer ---send EXECUTE stmt - ---connection locker - ALTER TABLE t1 ADD COLUMN (c INT); - ALTER TABLE t1 DROP COLUMN c; - ---connection writer ---reap -} ---enable_query_log - ---echo # 1.2. test altering of columns that multiupdate uses ---echo # 1.2.1. normal mode - ---connection default - ---disable_query_log -let $i = 100; -while ($i) { - dec $i; - ---connection locker ---error 0,ER_DUP_FIELDNAME - ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL; - UPDATE t1 SET a=b; - ---connection writer ---send UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0; - ---connection locker ---error 0,ER_CANT_DROP_FIELD_OR_KEY - ALTER TABLE t1 DROP COLUMN a; - ---connection writer ---error 0,ER_BAD_FIELD_ERROR # unknown column error ---reap -} ---enable_query_log - ---echo # 1.2.2. PS mode - ---disable_query_log -let $i = 100; -while ($i) { - dec $i; - ---connection locker ---error 0,ER_DUP_FIELDNAME - ALTER TABLE t1 ADD COLUMN a INT; - UPDATE t1 SET a=b; - ---connection writer - PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0'; ---send EXECUTE stmt - ---connection locker ---error 0,ER_CANT_DROP_FIELD_OR_KEY - ALTER TABLE t1 DROP COLUMN a; - ---connection writer ---error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' ---reap -} ---enable_query_log ---connection default -ALTER TABLE t1 ADD COLUMN a INT; - ---echo # 2. test UNIONs ---echo # 2.1. test altering of columns that multiupdate doesn't use ---echo # 2.1.1. normal mode - ---disable_query_log -let $i = 100; -while ($i) { ---dec $i - ---connection writer - send UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0; - ---connection locker - ALTER TABLE t1 ADD COLUMN (c INT); - ALTER TABLE t1 DROP COLUMN c; - ---connection writer ---reap -} - ---echo # 2.1.2. PS mode - ---connection writer -PREPARE stmt FROM 'UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0'; - -let $i = 100; -while ($i) { ---dec $i - ---connection writer ---send EXECUTE stmt - ---connection locker - ALTER TABLE t1 ADD COLUMN (c INT); - ALTER TABLE t1 DROP COLUMN c; - ---connection writer ---reap -} ---enable_query_log - ---echo # 2.2. test altering of columns that multiupdate uses ---echo # 2.2.1. normal mode - ---connection default - ---disable_query_log -let $i = 100; -while ($i) { - dec $i; - ---connection locker ---error 0,ER_DUP_FIELDNAME - ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL; - UPDATE t1 SET a=b; - ---connection writer ---send UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0; - ---connection locker ---error 0,ER_CANT_DROP_FIELD_OR_KEY - ALTER TABLE t1 DROP COLUMN a; - ---connection writer ---error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' ---reap -} ---enable_query_log - ---echo # 2.2.2. PS mode - ---disable_query_log -let $i = 100; -while ($i) { - dec $i; - ---connection locker ---error 0,ER_DUP_FIELDNAME - ALTER TABLE t1 ADD COLUMN a INT; - UPDATE t1 SET a=b; - ---connection writer - PREPARE stmt FROM 'UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0'; ---send EXECUTE stmt - ---connection locker ---error 0,ER_CANT_DROP_FIELD_OR_KEY - ALTER TABLE t1 DROP COLUMN a; - ---connection writer ---error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' ---reap -} ---enable_query_log ---connection default -DROP TABLE t1; --echo End of 5.0 tests diff --git a/mysql-test/t/lock_multi_bug38499.test b/mysql-test/t/lock_multi_bug38499.test new file mode 100644 index 00000000000..8178987e802 --- /dev/null +++ b/mysql-test/t/lock_multi_bug38499.test @@ -0,0 +1,221 @@ +# Bug38499 flush tables and multitable table update with derived table cause crash +# MySQL >= 5.0 +# + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +connect (locker,localhost,root,,); +connect (writer,localhost,root,,); + +--connection default +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TABLE t1( a INT, b INT ); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4); + +--echo # 1. test regular tables +--echo # 1.1. test altering of columns that multiupdate doesn't use +--echo # 1.1.1. normal mode + +--disable_query_log +let $i = 100; +while ($i) { +--dec $i + +--connection writer + send UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0; + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} + +--echo # 1.1.2. PS mode + +--connection writer +PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0'; + +let $i = 100; +while ($i) { +--dec $i + +--connection writer +--send EXECUTE stmt + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} +--enable_query_log + +--echo # 1.2. test altering of columns that multiupdate uses +--echo # 1.2.1. normal mode + +--connection default + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,ER_DUP_FIELDNAME + ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL; + UPDATE t1 SET a=b; + +--connection writer +--send UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0; + +--connection locker +--error 0,ER_CANT_DROP_FIELD_OR_KEY + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,ER_BAD_FIELD_ERROR # unknown column error +--reap +} +--enable_query_log + +--echo # 1.2.2. PS mode + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,ER_DUP_FIELDNAME + ALTER TABLE t1 ADD COLUMN a INT; + UPDATE t1 SET a=b; + +--connection writer + PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0'; +--send EXECUTE stmt + +--connection locker +--error 0,ER_CANT_DROP_FIELD_OR_KEY + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' +--reap +} +--enable_query_log +--connection default +ALTER TABLE t1 ADD COLUMN a INT; + +--echo # 2. test UNIONs +--echo # 2.1. test altering of columns that multiupdate doesn't use +--echo # 2.1.1. normal mode + +--disable_query_log +let $i = 100; +while ($i) { +--dec $i + +--connection writer + send UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0; + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} + +--echo # 2.1.2. PS mode + +--connection writer +PREPARE stmt FROM 'UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0'; + +let $i = 100; +while ($i) { +--dec $i + +--connection writer +--send EXECUTE stmt + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} +--enable_query_log + +--echo # 2.2. test altering of columns that multiupdate uses +--echo # 2.2.1. normal mode + +--connection default + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,ER_DUP_FIELDNAME + ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL; + UPDATE t1 SET a=b; + +--connection writer +--send UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0; + +--connection locker +--error 0,ER_CANT_DROP_FIELD_OR_KEY + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' +--reap +} +--enable_query_log + +--echo # 2.2.2. PS mode + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,ER_DUP_FIELDNAME + ALTER TABLE t1 ADD COLUMN a INT; + UPDATE t1 SET a=b; + +--connection writer + PREPARE stmt FROM 'UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0'; +--send EXECUTE stmt + +--connection locker +--error 0,ER_CANT_DROP_FIELD_OR_KEY + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' +--reap +} +--enable_query_log +--connection default +DROP TABLE t1; + + +# Close connections +--disconnect locker +--disconnect writer + +# End of 5.0 tests + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/lock_multi_bug38691.test b/mysql-test/t/lock_multi_bug38691.test new file mode 100644 index 00000000000..0458f31579e --- /dev/null +++ b/mysql-test/t/lock_multi_bug38691.test @@ -0,0 +1,141 @@ +# +# Bug#38691 segfault/abort in ``UPDATE ...JOIN'' while +# ``FLUSH TABLES WITH READ LOCK'' +# MySQL >= 5.0 +# + + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +# Test to see if select will get the lock ahead of low priority update + +connect (locker,localhost,root,,); +connect (writer,localhost,root,,); + +--connection default +--disable_warnings +DROP TABLE IF EXISTS t1,t2,t3; +--enable_warnings + +CREATE TABLE t1 ( + a int(11) unsigned default NULL, + b varchar(255) default NULL, + UNIQUE KEY a (a), + KEY b (b) +); + +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); +CREATE TABLE t2 SELECT * FROM t1; +CREATE TABLE t3 SELECT * FROM t1; + +--echo # test altering of columns that multiupdate doesn't use + +--echo # normal mode + +--disable_query_log +let $i = 100; +while ($i) { +--dec $i + +--connection writer + send UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) + SET a = NULL WHERE t1.b <> t2.b; + +--connection locker + ALTER TABLE t2 ADD COLUMN (c INT); + ALTER TABLE t2 DROP COLUMN c; + +--connection writer +--reap +} + +--echo # PS mode + +--connection writer +PREPARE stmt FROM 'UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) + SET a = NULL WHERE t1.b <> t2.b'; + +let $i = 100; +while ($i) { +--dec $i + +--connection writer +--send EXECUTE stmt + +--connection locker + ALTER TABLE t2 ADD COLUMN (c INT); + ALTER TABLE t2 DROP COLUMN c; + +--connection writer +--reap +} +--enable_query_log + + +--echo # test altering of columns that multiupdate uses + +--echo # normal mode + +--connection default + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,ER_DUP_FIELDNAME + ALTER TABLE t2 ADD COLUMN a int(11) unsigned default NULL; + UPDATE t2 SET a=b; + +--connection writer +--send UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) SET a = NULL WHERE t1.b <> t2.b + +--connection locker +--error 0,ER_CANT_DROP_FIELD_OR_KEY + ALTER TABLE t2 DROP COLUMN a; + +--connection writer +--error 0,ER_BAD_FIELD_ERROR +--reap +} +--enable_query_log + +--echo # PS mode + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,ER_DUP_FIELDNAME + ALTER TABLE t2 ADD COLUMN a int(11) unsigned default NULL; + UPDATE t2 SET a=b; + +--connection writer + PREPARE stmt FROM 'UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) SET a = NULL WHERE t1.b <> t2.b'; +--send EXECUTE stmt + +--connection locker +--error 0,ER_CANT_DROP_FIELD_OR_KEY + ALTER TABLE t2 DROP COLUMN a; + +--connection writer +--error 0,ER_BAD_FIELD_ERROR +--reap + +} +--enable_query_log +--connection default +DROP TABLE t1, t2, t3; + + +# Close connections +--disconnect locker +--disconnect writer + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/mysql-bug41486.test b/mysql-test/t/mysql-bug41486.test new file mode 100644 index 00000000000..6e014bca7d1 --- /dev/null +++ b/mysql-test/t/mysql-bug41486.test @@ -0,0 +1,50 @@ +# +# Bug#41486 extra character appears in BLOB for every ~40Mb after +# mysqldump/import +# +# This test consumes a significant amount of resources. +# Therefore it should be kept separated from other tests. +# Otherwise we might suffer from problems like +# Bug#43801 mysql.test takes too long, fails due to expired timeout +# on debx86-b in PB +# + +-- source include/not_embedded.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +# Have to change the global variable as the session variable is +# read-only. +SET @old_max_allowed_packet= @@global.max_allowed_packet; +# 2 MB blob length + some space for the rest of INSERT query +SET @@global.max_allowed_packet = 2 * 1024 * 1024 + 1024; + +# Create a new connection since the global max_allowed_packet +# has no effect for the current connection +connect (con1, localhost, root,,); + +CREATE TABLE t1(data LONGBLOB); +INSERT INTO t1 SELECT REPEAT('1', 2*1024*1024); + +let $outfile= $MYSQLTEST_VARDIR/tmp/bug41486.sql; +--error 0,1 +remove_file $outfile; +--exec $MYSQL_DUMP test t1 > $outfile +SET @old_general_log = @@global.general_log; +SET @@global.general_log = 0; +# Check that the mysql client does not insert extra newlines when loading +# strings longer than client's max_allowed_packet +--exec $MYSQL --max_allowed_packet=1M test < $outfile 2>&1 +SET @@global.general_log = @old_general_log; +SELECT LENGTH(data) FROM t1; + +DROP TABLE t1; + +# Cleanup +disconnect con1; +--source include/wait_until_disconnected.inc +remove_file $outfile; +connection default; +SET @@global.max_allowed_packet = @old_max_allowed_packet; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index ccdb53ec11f..8981ddbe2e4 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3769,4 +3769,20 @@ SELECT date_nokey FROM C DROP TABLE A,C; +# +# Bug #42957: no results from +# select where .. (col=col and col=col) or ... (false expression) +# +CREATE TABLE t1 (a INT NOT NULL, b INT); +INSERT INTO t1 VALUES (1, 1); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; +SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; +DROP TABLE t1; + +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index ad4d57b7128..ee4c174d76b 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -105,17 +105,25 @@ SELECT a INTO @v FROM ( SELECT a FROM t1 ) alias; -SELECT a INTO OUTFILE 'union.out.file' FROM ( +--let $outfile = $MYSQLTEST_VARDIR/tmp/union.out.file +--error 0,1 +--remove_file $outfile + +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> +eval SELECT a INTO OUTFILE '$outfile' FROM ( SELECT a FROM t1 UNION SELECT a FROM t1 WHERE 0 ) alias; +--remove_file $outfile -SELECT a INTO DUMPFILE 'union.out.file2' FROM ( +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> +eval SELECT a INTO DUMPFILE '$outfile' FROM ( SELECT a FROM t1 UNION SELECT a FROM t1 WHERE 0 ) alias; +--remove_file $outfile # # INTO will not be allowed in subqueries in version 5.1 and above. @@ -126,27 +134,42 @@ SELECT a FROM ( SELECT a INTO @v FROM t1 ) alias; -SELECT a FROM ( +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> +eval SELECT a FROM ( SELECT a FROM t1 UNION - SELECT a INTO OUTFILE 'union.out.file3' FROM t1 + SELECT a INTO OUTFILE '$outfile' FROM t1 ) alias; +--remove_file $outfile -SELECT a FROM ( +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> +eval SELECT a FROM ( SELECT a FROM t1 UNION - SELECT a INTO DUMPFILE 'union.out.file4' FROM t1 + SELECT a INTO DUMPFILE '$outfile' FROM t1 ) alias; +--remove_file $outfile SELECT a FROM t1 UNION SELECT a INTO @v FROM t1; -SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file5' FROM t1; -SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file6' FROM t1; + +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> +eval SELECT a FROM t1 UNION SELECT a INTO OUTFILE '$outfile' FROM t1; +--remove_file $outfile + +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> +eval SELECT a FROM t1 UNION SELECT a INTO DUMPFILE '$outfile' FROM t1; +--remove_file $outfile + --error ER_WRONG_USAGE SELECT a INTO @v FROM t1 UNION SELECT a FROM t1; + +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> --error ER_WRONG_USAGE -SELECT a INTO OUTFILE 'union.out.file7' FROM t1 UNION SELECT a FROM t1; +eval SELECT a INTO OUTFILE '$outfile' FROM t1 UNION SELECT a FROM t1; + +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> --error ER_WRONG_USAGE -SELECT a INTO DUMPFILE 'union.out.file8' FROM t1 UNION SELECT a FROM t1; +eval SELECT a INTO DUMPFILE '$outfile' FROM t1 UNION SELECT a FROM t1; DROP TABLE t1; diff --git a/mysql-test/t/upgrade.test b/mysql-test/t/upgrade.test index a96d1f47cb2..437b0f47cc0 100644 --- a/mysql-test/t/upgrade.test +++ b/mysql-test/t/upgrade.test @@ -53,7 +53,9 @@ drop table `txu#p#p1`; # let $MYSQLD_DATADIR= `select @@datadir`; -system cp $MYSQL_TEST_DIR/std_data/old_table-323.frm $MYSQLD_DATADIR/test/t1.frm; +--error 0,1 +--remove_file $MYSQLD_DATADIR/test/t1.frm +--copy_file std_data/old_table-323.frm $MYSQLD_DATADIR/test/t1.frm truncate t1; drop table t1; diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 8dd11e2cca2..db0657d9bde 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -37,6 +37,7 @@ set @my_storage_engine =@@global.storage_engine; set @my_thread_cache_size =@@global.thread_cache_size; set @my_max_allowed_packet =@@global.max_allowed_packet; set @my_delay_key_write =@@global.delay_key_write; +set @my_join_buffer_size =@@global.join_buffer_size; # case insensitivity tests (new in 5.0) set @`test`=1; select @test, @`test`, @TEST, @`TEST`, @"teSt"; @@ -781,6 +782,18 @@ show variables like 'hostname'; --echo End of 5.0 tests +# +# Bug#36446: Attempt to set @@join_buffer_size to its minimum value +# produces spurious warning +# + +# set to 1 so mysqld will correct to minimum (+ warn) +set join_buffer_size=1; +# save minimum +set @save_join_buffer_size=@@join_buffer_size; +# set minimum +set join_buffer_size=@save_join_buffer_size; + # This is at the very after the versioned tests, since it involves doing # cleanup # @@ -818,6 +831,8 @@ set global storage_engine =@my_storage_engine; set global thread_cache_size =@my_thread_cache_size; set global max_allowed_packet = default; set global delay_key_write =@my_delay_key_write; +set global join_buffer_size =@my_join_buffer_size; + # # Bug#28580 Repeatation of status variables # |