diff options
Diffstat (limited to 'mysql-test/include')
44 files changed, 467 insertions, 61 deletions
diff --git a/mysql-test/include/add_anonymous_users.inc b/mysql-test/include/add_anonymous_users.inc index 635a80e3dee..86ce529d1e2 100644 --- a/mysql-test/include/add_anonymous_users.inc +++ b/mysql-test/include/add_anonymous_users.inc @@ -1,7 +1,7 @@ # Allow anonymous users to connect disable_warnings; disable_query_log; -INSERT IGNORE INTO mysql.user (host, user) VALUES ('localhost',''); +INSERT IGNORE INTO mysql.global_priv (host, user) VALUES ('localhost',''); FLUSH PRIVILEGES; enable_query_log; enable_warnings; diff --git a/mysql-test/include/analyze-format.inc b/mysql-test/include/analyze-format.inc index 65e61b81582..08006e26c79 100644 --- a/mysql-test/include/analyze-format.inc +++ b/mysql-test/include/analyze-format.inc @@ -1,3 +1,3 @@ # The time on ANALYSE FORMAT=JSON is rather variable ---replace_regex /("(r_total_time_ms|r_buffer_size)": )[^, \n]*/\1"REPLACED"/ +--replace_regex /("(r_total_time_ms|r_buffer_size|r_filling_time_ms)": )[^, \n]*/\1"REPLACED"/ diff --git a/mysql-test/include/check-testcase.test b/mysql-test/include/check-testcase.test index 44aa4472cba..191feae1628 100644 --- a/mysql-test/include/check-testcase.test +++ b/mysql-test/include/check-testcase.test @@ -81,6 +81,18 @@ if (!$tmp) { SHOW SLAVE STATUS; } +# +# Ensure that we don't get warnings from mysql.proc (used by check_mysqld) +# + +--disable_query_log +--disable_warnings +--disable_result_log +select count(*) from mysql.proc; +--enable_result_log +--enable_warnings +--enable_query_log + call mtr.check_testcase(); let $datadir=`select @@datadir`; @@ -91,5 +103,56 @@ cat_file $datadir.tempfiles.txt; remove_file $datadir.tempfiles.txt; list_files $datadir/mysql #sql*; +# +# Check that SHOW ENGINE INNODB STATUS does not show any active transactions +# We do this only if wsrep provider is loaded, to avoid disturbing any non-Galera MTR tests +# +if (`SELECT COUNT(*)=1 FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'wsrep' AND PLUGIN_STATUS='ACTIVE'`) { + if (`SELECT @@wsrep_on`) { + if (`SELECT COUNT(*) FROM information_schema.innodb_trx WHERE trx_mysql_thread_id != 0`) { + if ($before) { + --echo Before test start. + } + if (!$before) { + --echo After test end. + } + --echo There is one or more active InnoDB transaction(s) when there should be none. Dumping some diagnostics. + + --let $status_locks = `SELECT @@innodb_status_output_locks` + --let $status_output = `SELECT @@innodb_status_output` + --enable_query_log + SET GLOBAL innodb_status_output_locks=ON; + SHOW ENGINE INNODB STATUS; + --disable_query_log + --eval SET GLOBAL innodb_status_output_locks=$status_locks; + --eval SET GLOBAL innodb_status_output=$status_output; + --enable_query_log + + --vertical_results + if ($before) { + --replace_regex /$/ / + } + SELECT * FROM information_schema.processlist; + + if ($before) { + --replace_regex /$/ / + } + SELECT * FROM information_schema.innodb_trx; + + if ($before) { + --replace_regex /$/ / + } + SELECT * FROM information_schema.innodb_locks; + + if ($before) { + --replace_regex /$/ / + } + SELECT * FROM information_schema.innodb_lock_waits; + --horizontal_results + --disable_query_log + } + } +} + --enable_query_log diff --git a/mysql-test/include/check_ftwrl_incompatible.inc b/mysql-test/include/check_ftwrl_incompatible.inc index 4787a69ea9c..a7e87c3750b 100644 --- a/mysql-test/include/check_ftwrl_incompatible.inc +++ b/mysql-test/include/check_ftwrl_incompatible.inc @@ -68,8 +68,7 @@ connection $con_aux1; --enable_query_log let $wait_condition= select count(*) = 1 from information_schema.processlist - where (state = "Waiting for global read lock" or - state = "Waiting for commit lock") and + where state = "Waiting for backup lock" and info = "$statement"; --source include/wait_condition.inc --disable_result_log @@ -116,8 +115,7 @@ connection $con_aux2; --enable_query_log let $wait_condition= select count(*) = 1 from information_schema.processlist - where (state = "Waiting for global read lock" or - state = "Waiting for commit lock") and + where state = "Waiting for backup lock" and info = "flush tables with read lock"; --source include/wait_condition.inc --disable_result_log diff --git a/mysql-test/include/ctype_numconv.inc b/mysql-test/include/ctype_numconv.inc index db9a9b7f5c0..da87d946d9c 100644 --- a/mysql-test/include/ctype_numconv.inc +++ b/mysql-test/include/ctype_numconv.inc @@ -198,12 +198,12 @@ show create table t1; drop table t1; select hex(concat(ceiling(0.5))); -create table t1 as select concat(ceiling(0.5)) as c1; +create table t1 as select ceiling(0.5) as c0, concat(ceiling(0.5)) as c1; show create table t1; drop table t1; select hex(concat(floor(0.5))); -create table t1 as select concat(floor(0.5)) as c1; +create table t1 as select floor(0.5) as c0, concat(floor(0.5)) as c1; show create table t1; drop table t1; diff --git a/mysql-test/include/deadlock.inc b/mysql-test/include/deadlock.inc index aa714445448..fcf655fce23 100644 --- a/mysql-test/include/deadlock.inc +++ b/mysql-test/include/deadlock.inc @@ -95,7 +95,7 @@ insert into t2 values(0, 0), (1, 20), (2, 30); commit; connection con1; -select a,b from t2 UNION SELECT id, x from t1 FOR UPDATE; +select a,b from t2 UNION (SELECT id, x from t1 FOR UPDATE); select * from t2; select * from t1; diff --git a/mysql-test/include/default_mysqld.cnf b/mysql-test/include/default_mysqld.cnf index cd9b65a5ce3..387a144bacb 100644 --- a/mysql-test/include/default_mysqld.cnf +++ b/mysql-test/include/default_mysqld.cnf @@ -107,6 +107,7 @@ loose-performance-schema-consumer-thread-instrumentation=ON binlog-direct-non-transactional-updates default-storage-engine=myisam +use_stat_tables=preferably loose-ssl-ca=@ENV.MYSQL_TEST_DIR/std_data/cacert.pem loose-ssl-cert=@ENV.MYSQL_TEST_DIR/std_data/server-cert.pem diff --git a/mysql-test/include/default_optimizer_switch.inc b/mysql-test/include/default_optimizer_switch.inc index e9c804be548..8634c2eee22 100644 --- a/mysql-test/include/default_optimizer_switch.inc +++ b/mysql-test/include/default_optimizer_switch.inc @@ -8,9 +8,9 @@ set @save_optimizer_switch=@@optimizer_switch; set @save_join_cache_level=@@join_cache_level; -set optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on"; +set optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on"; -set optimizer_use_condition_selectivity=1; +set optimizer_use_condition_selectivity=4; set optimizer_search_depth=62; set join_buffer_space_limit=2097152; set join_cache_level=2; diff --git a/mysql-test/include/diff_tables.inc b/mysql-test/include/diff_tables.inc index b5ee4db0e8f..a29156cfce9 100644 --- a/mysql-test/include/diff_tables.inc +++ b/mysql-test/include/diff_tables.inc @@ -167,7 +167,7 @@ while ($_dt_tables) # the table to a file. --let $_dt_outfile= `SELECT @@datadir` --let $_dt_outfile= $_dt_outfile/diff_table-$_dt_connection-$_dt_database-$_dt_table - eval SELECT * FROM $_dt_database.$_dt_table ORDER BY `$_dt_column_list` INTO OUTFILE '$_dt_outfile'; + eval SELECT * INTO OUTFILE '$_dt_outfile' FROM $_dt_database.$_dt_table ORDER BY `$_dt_column_list`; # Compare files. if ($_dt_prev_outfile) diff --git a/mysql-test/include/explain_non_select.inc b/mysql-test/include/explain_non_select.inc index 57b96994d20..d22310c9813 100644 --- a/mysql-test/include/explain_non_select.inc +++ b/mysql-test/include/explain_non_select.inc @@ -158,7 +158,7 @@ CREATE TABLE t1 ( a int PRIMARY KEY ); --let $query = DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a --let $select = SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a --source include/explain_utils.inc -INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t1 VALUES (1), (2), (3), (-1), (-2), (-3); --let $query = DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a --let $select = SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a --source include/explain_utils.inc @@ -640,7 +640,7 @@ DROP VIEW v1; --echo #63 CREATE TABLE t1 (a INT, PRIMARY KEY(a)); -INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9); CREATE VIEW v1 (a) AS SELECT a FROM t1; --let $query = DELETE FROM v1 WHERE a < 4 --let $select = SELECT * FROM v1 WHERE a < 4 diff --git a/mysql-test/include/galera_cluster.inc b/mysql-test/include/galera_cluster.inc index c1834c3c26f..7f76ea59c7f 100644 --- a/mysql-test/include/galera_cluster.inc +++ b/mysql-test/include/galera_cluster.inc @@ -8,5 +8,11 @@ --let $galera_cluster_size = 2 --source include/galera_init.inc +--source include/have_innodb.inc +--source include/galera_wait_ready.inc +--connection node_2 +--source include/galera_wait_ready.inc --source include/have_innodb.inc + +--connection node_1 diff --git a/mysql-test/include/galera_no_debug_sync.inc b/mysql-test/include/galera_no_debug_sync.inc new file mode 100644 index 00000000000..0819144c0b6 --- /dev/null +++ b/mysql-test/include/galera_no_debug_sync.inc @@ -0,0 +1,9 @@ +--disable_query_log + +--let $galera_have_debug_sync = `SELECT 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_debug_sync_waiters'` + +--if ($galera_have_debug_sync) { + --skip Test requires Galera debug library with no debug_sync functionality +} + +--enable_query_log diff --git a/mysql-test/include/galera_resume.inc b/mysql-test/include/galera_resume.inc deleted file mode 100644 index af8f2b956fd..00000000000 --- a/mysql-test/include/galera_resume.inc +++ /dev/null @@ -1,9 +0,0 @@ ---echo Resuming node ... ---perl - my $pid_filename = $ENV{'_SUSPEND_NODE_PIDFILE'}; - my $mysqld_pid = `cat $pid_filename`; - chomp($mysqld_pid); - system("kill -SIGCONT $mysqld_pid"); - exit(0); -EOF - diff --git a/mysql-test/include/galera_variables_ok.inc b/mysql-test/include/galera_variables_ok.inc new file mode 100644 index 00000000000..82c5174bc14 --- /dev/null +++ b/mysql-test/include/galera_variables_ok.inc @@ -0,0 +1,9 @@ +--disable_query_log + +--let $galera_variables_ok = `SELECT COUNT(*) = 49 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE 'wsrep%'` + +--if (!$galera_variables_ok) { + --skip Galera number of variables has changed! +} + +--enable_query_log diff --git a/mysql-test/include/galera_variables_ok_debug.inc b/mysql-test/include/galera_variables_ok_debug.inc new file mode 100644 index 00000000000..c9a54724c17 --- /dev/null +++ b/mysql-test/include/galera_variables_ok_debug.inc @@ -0,0 +1,9 @@ +--disable_query_log + +--let $galera_variables_ok = `SELECT COUNT(*) = 50 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE 'wsrep%'` + +--if (!$galera_variables_ok) { + --skip Galera number of variables has changed! +} + +--enable_query_log diff --git a/mysql-test/include/galera_wait_sync_point.inc b/mysql-test/include/galera_wait_sync_point.inc index cf3a4980186..c0951b220b4 100644 --- a/mysql-test/include/galera_wait_sync_point.inc +++ b/mysql-test/include/galera_wait_sync_point.inc @@ -1,6 +1,17 @@ --let $wait_timeout = 10 --let $wsrep_on_orig = `SELECT @@wsrep_on` SET SESSION wsrep_on = 0; + +# +# following is only for debugging purposes +# should be commented out when test wporks as planned +# +#--sleep 1 +# SHOW PROCESSLIST; +#SHOW STATUS LIKE 'wsrep_%'; +#--echo $galera_sync_point + --let $wait_condition = SELECT 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_debug_sync_waiters' AND VARIABLE_VALUE = '$galera_sync_point' --source include/wait_condition.inc --eval SET SESSION wsrep_on = $wsrep_on_orig + diff --git a/mysql-test/include/gis_keys.inc b/mysql-test/include/gis_keys.inc index cc8ec68f7d1..388c7b45898 100644 --- a/mysql-test/include/gis_keys.inc +++ b/mysql-test/include/gis_keys.inc @@ -27,8 +27,8 @@ SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); # the "most rows covered" rule doesn't kick in anymore # now EXPLAIN shows the index used on the table # and we're getting the wrong result again -INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); -INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(3 4)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(3 4)')); EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); diff --git a/mysql-test/include/have_auth_named_pipe.inc b/mysql-test/include/have_auth_named_pipe.inc new file mode 100644 index 00000000000..4f4bf37f11e --- /dev/null +++ b/mysql-test/include/have_auth_named_pipe.inc @@ -0,0 +1,13 @@ +--source include/not_embedded.inc + +if (!$AUTH_NAMED_PIPE_SO) { + skip No auth_named_pipe plugin; +} + +if (!$USERNAME) { + skip USER variable is undefined; +} + +if (`SELECT count(*) <> 0 FROM mysql.user WHERE user = '$USERNAME'`) { + skip %USERNAME%=$USER which exists in mysql.user; +} diff --git a/mysql-test/include/have_garbd.inc b/mysql-test/include/have_garbd.inc deleted file mode 100644 index 0dd693f2c63..00000000000 --- a/mysql-test/include/have_garbd.inc +++ /dev/null @@ -1,4 +0,0 @@ -# -# suite.pm will make sure that all tests including this file -# will be skipped as needed -# diff --git a/mysql-test/include/have_mariabackup.inc b/mysql-test/include/have_mariabackup.inc index 0dd693f2c63..6c4ad240172 100644 --- a/mysql-test/include/have_mariabackup.inc +++ b/mysql-test/include/have_mariabackup.inc @@ -1,4 +1,7 @@ -# -# suite.pm will make sure that all tests including this file -# will be skipped as needed -# +if (!$XTRABACKUP) { + skip Needs mariabackup; +} + +if (!$MTR_GALERA_TFMT) { + skip Needs socat or nc; +} diff --git a/mysql-test/include/have_unix_socket.inc b/mysql-test/include/have_unix_socket.inc index 4246b138e9d..7be828ac384 100644 --- a/mysql-test/include/have_unix_socket.inc +++ b/mysql-test/include/have_unix_socket.inc @@ -1,7 +1,8 @@ --source include/not_embedded.inc -if (!$AUTH_SOCKET_SO) { - skip No unix_socket plugin; +if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'unix_socket' and plugin_status='active'`) +{ + --skip Needs unix_socket plugin } if (!$USER) { diff --git a/mysql-test/include/have_unix_socket.opt b/mysql-test/include/have_unix_socket.opt new file mode 100644 index 00000000000..460e3a26ae2 --- /dev/null +++ b/mysql-test/include/have_unix_socket.opt @@ -0,0 +1 @@ +--plugin-load-add=$AUTH_SOCKET_SO --loose-enable-unix-socket diff --git a/mysql-test/include/have_wsrep_enabled.inc b/mysql-test/include/have_wsrep_enabled.inc index 9287369c87c..7eb8b4372cf 100644 --- a/mysql-test/include/have_wsrep_enabled.inc +++ b/mysql-test/include/have_wsrep_enabled.inc @@ -1,7 +1,6 @@ # To be used in a test which requires wsrep plugin to be ACTIVE and enabled # (i.e. wsrep_on=ON). It includes have_wsrep.inc. ---source include/have_wsrep.inc --source include/have_innodb.inc if (`SELECT COUNT(*)=0 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'wsrep_on' AND VARIABLE_VALUE='ON'`) diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index f29088887d0..66fdc3e754c 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -226,6 +226,7 @@ EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; +--replace_column 9 100 EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; @@ -483,11 +484,11 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); -EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; +EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); SET SESSION optimizer_switch='index_condition_pushdown=off'; -SELECT pk, c1 FROM t1 WHERE pk <> 3; +SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); DROP TABLE t1; @@ -633,6 +634,8 @@ CREATE TABLE t2 ( ); INSERT INTO t2 VALUES (4,1); +ANALYZE TABLE t1,t2; + EXPLAIN SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 WHERE t2.pk <> t1.d1 AND t2.pk = 4; @@ -690,6 +693,7 @@ INSERT INTO t1 VALUES ('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei'); SET SESSION optimizer_switch='index_condition_pushdown=off'; +--replace_column 9 # EXPLAIN SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' @@ -699,6 +703,7 @@ SELECT * FROM t1 ORDER BY a; SET SESSION optimizer_switch='index_condition_pushdown=on'; +--replace_column 9 # EXPLAIN SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' @@ -722,16 +727,16 @@ INSERT INTO t2 VALUES SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; DROP TABLE t1,t2; @@ -840,6 +845,8 @@ INSERT INTO t2 (g,h) VALUES (3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'), (7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'); +ANALYZE TABLE t1,t2; + SET @save_optimize_switch=@@optimizer_switch; SET optimizer_switch='materialization=on'; diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc index 74691cfe217..e54fbe2834f 100644 --- a/mysql-test/include/index_merge1.inc +++ b/mysql-test/include/index_merge1.inc @@ -40,12 +40,12 @@ update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1 analyze table t0; # 1. One index -explain select * from t0 where key1 < 3 or key1 > 1020; +explain select * from t0 where key1 < 3 or key1 > 920 and key1 < 924; # 2. Simple cases explain -select * from t0 where key1 < 3 or key2 > 1020; -select * from t0 where key1 < 3 or key2 > 1020; +select * from t0 where key1 < 3 or key2 > 920 and key2 < 924; +select * from t0 where key1 < 3 or key2 > 920 and key2 < 924; select * from t0 where key1=1022; # MDEV-13535 no-key-read select after keyread @@ -98,7 +98,6 @@ select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4); select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4); - explain select * from t0 where (key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2); @@ -129,6 +128,10 @@ select * from t0 where key1 < 7; # tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>). +select count(*) from t0 where + ((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4)) + or + ((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4)); explain select * from t0 where ((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4)) or @@ -477,6 +480,7 @@ create table t2( ) ENGINE=MEMORY DEFAULT CHARSET=latin1; insert into t2 select * from t1; + --echo must use sort-union rather than union: --replace_column 9 # explain select * from t1 where a=4 or b=4; @@ -487,9 +491,9 @@ select * from t1 ignore index(a,b) where a=4 or b=4; --echo must use union, not sort-union: --replace_column 9 # -explain select * from t2 where a=4 or b=4; +explain select * from t2 where a=2 or b=2; --sorted_result -select * from t2 where a=4 or b=4; +select * from t2 where a=2 or b=2; drop table t1, t2; diff --git a/mysql-test/include/innodb_checksum_algorithm.combinations b/mysql-test/include/innodb_checksum_algorithm.combinations new file mode 100644 index 00000000000..fd237e1190a --- /dev/null +++ b/mysql-test/include/innodb_checksum_algorithm.combinations @@ -0,0 +1,11 @@ +[crc32] +--innodb-checksum-algorithm=crc32 + +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[full_crc32] +--innodb-checksum-algorithm=full_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/include/innodb_checksum_algorithm.inc b/mysql-test/include/innodb_checksum_algorithm.inc new file mode 100644 index 00000000000..c841fece702 --- /dev/null +++ b/mysql-test/include/innodb_checksum_algorithm.inc @@ -0,0 +1 @@ +--source include/have_innodb.inc diff --git a/mysql-test/include/install_plugin_if_exists.inc b/mysql-test/include/install_plugin_if_exists.inc new file mode 100644 index 00000000000..c641f020deb --- /dev/null +++ b/mysql-test/include/install_plugin_if_exists.inc @@ -0,0 +1,45 @@ +--echo # +--echo # MDEV-16294: INSTALL PLUGIN IF NOT EXISTS / UNINSTALL PLUGIN IF EXISTS +--echo # +--echo # INSTALL IF NOT EXISTS PLUGIN name SONAME library / +--echo # UNINSTALL IF EXISTS PLUGIN|SONAME name +--echo # + +if (!$HA_EXAMPLE_SO) { + skip Needs ha_example plugin; +} + +select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE from information_schema.plugins where plugin_library like 'ha_example%'; +INSTALL PLUGIN IF NOT EXISTS example SONAME 'ha_example'; +select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE from information_schema.plugins where plugin_library like 'ha_example%'; + +--replace_regex /\.dll/.so/ +--error ER_PLUGIN_INSTALLED +INSTALL PLUGIN example SONAME 'ha_example'; + +INSTALL PLUGIN IF NOT EXISTS example SONAME 'ha_example'; +SHOW WARNINGS; + +UNINSTALL PLUGIN IF EXISTS example; +select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE from information_schema.plugins where plugin_library like 'ha_example%'; + +UNINSTALL PLUGIN IF EXISTS example; +SHOW WARNINGS; + +--error ER_SP_DOES_NOT_EXIST +UNINSTALL PLUGIN example; + +INSTALL SONAME 'ha_example'; +select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE from information_schema.plugins where plugin_library like 'ha_example%'; + +UNINSTALL SONAME IF EXISTS 'ha_example'; + +--replace_regex /\.dll/.so/ +UNINSTALL SONAME IF EXISTS 'ha_example'; +--replace_regex /\.dll/.so/ +SHOW WARNINGS; +select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE from information_schema.plugins where plugin_library like 'ha_example%'; + +--replace_regex /\.dll/.so/ +--error ER_SP_DOES_NOT_EXIST +UNINSTALL SONAME 'ha_example'; diff --git a/mysql-test/include/kill_galera.inc b/mysql-test/include/kill_galera.inc new file mode 100644 index 00000000000..d7f665df6c7 --- /dev/null +++ b/mysql-test/include/kill_galera.inc @@ -0,0 +1,20 @@ +--echo Killing server ... + +# Write file to make mysql-test-run.pl expect the crash, but don't start it +--let $_server_id= `SELECT @@server_id` +--let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect +--exec echo "wait" > $_expect_file_name + +# Kill the connected server +--disable_reconnect +--let KILL_NODE_PIDFILE = `SELECT @@pid_file` + +--perl + my $pid_filename = $ENV{'KILL_NODE_PIDFILE'}; + my $mysqld_pid = `cat $pid_filename`; + chomp($mysqld_pid); + system("kill -9 $mysqld_pid"); + exit(0); +EOF + +--source include/wait_until_disconnected.inc diff --git a/mysql-test/include/long_test.inc b/mysql-test/include/long_test.inc index d9a3b338229..765f88b8037 100644 --- a/mysql-test/include/long_test.inc +++ b/mysql-test/include/long_test.inc @@ -2,3 +2,5 @@ # We can use this to schedule such test early (to not be left with # only one or two long tests running, and rests of works idle), or to # run a quick test skipping long-running test cases. + +--source include/no_valgrind_without_big.inc diff --git a/mysql-test/include/maria_empty_logs.inc b/mysql-test/include/maria_empty_logs.inc index 78a08228caa..f1835c0d2c3 100644 --- a/mysql-test/include/maria_empty_logs.inc +++ b/mysql-test/include/maria_empty_logs.inc @@ -92,3 +92,15 @@ connection default; --disable_query_log eval use $default_db; --enable_query_log + +# +# Ensure that we don't get warnings from mysql.priv (used by check_mysqld) +# or test running after this one. +# +--disable_query_log +--disable_warnings +--disable_result_log +show table status from mysql; +--enable_result_log +--enable_warnings +--enable_query_log diff --git a/mysql-test/include/maria_verify_recovery.inc b/mysql-test/include/maria_verify_recovery.inc index b0f95d2a94b..bb782d5f4cc 100644 --- a/mysql-test/include/maria_verify_recovery.inc +++ b/mysql-test/include/maria_verify_recovery.inc @@ -97,3 +97,15 @@ while ($mms_table_to_use) connection default; # the effect of "use" is lost after a restart so we are back into db "test" use mysqltest; + +# +# Ensure that we don't get warnings from mysql.proc (used by check_mysqld) +# + +--disable_query_log +--disable_warnings +--disable_result_log +select count(*) from mysql.proc; +--enable_result_log +--enable_warnings +--enable_query_log diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index 6a47b39e5bc..30b17c2e063 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -455,6 +455,16 @@ INSERT INTO t1(id, dept, age, name) VALUES (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'), (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6'); +set + @tmp_uss=@@use_stat_tables, + @tmp_occ=@@optimizer_use_condition_selectivity; +set + use_stat_tables='preferably', + optimizer_use_condition_selectivity=4; + +analyze table t1 persistent for all; +flush tables; + EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; DELETE FROM t1; @@ -464,6 +474,9 @@ EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; DROP TABLE t1; +set + use_stat_tables=@tmp_uss, + optimizer_use_condition_selectivity=@tmp_occ; --source include/innodb_rollback_on_timeout.inc diff --git a/mysql-test/include/mtr_check.sql b/mysql-test/include/mtr_check.sql index e8cdd1789db..76cd06e5d38 100644 --- a/mysql-test/include/mtr_check.sql +++ b/mysql-test/include/mtr_check.sql @@ -86,7 +86,6 @@ BEGIN mysql.help_category, mysql.help_keyword, mysql.help_relation, - mysql.host, mysql.plugin, mysql.proc, mysql.procs_priv, @@ -97,7 +96,7 @@ BEGIN mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, - mysql.user; + mysql.global_priv; -- verify that no plugin changed its disabled/enabled state SELECT * FROM INFORMATION_SCHEMA.PLUGINS; diff --git a/mysql-test/include/not_asan.inc b/mysql-test/include/not_asan.inc new file mode 100644 index 00000000000..9fc86a8525a --- /dev/null +++ b/mysql-test/include/not_asan.inc @@ -0,0 +1,8 @@ +# This file should only be used with test that finds bugs in ASan that can not +# be overcome. In normal cases one should fix the bug server/test case or in +# the worst case add a (temporary?) suppression in asan.supp or lsan.supp + +if (`select count(*) from information_schema.system_variables where variable_name='have_sanitizer' and global_value="ASAN"`) +{ +--skip Can't be run with ASan +} diff --git a/mysql-test/include/not_ubsan.inc b/mysql-test/include/not_ubsan.inc new file mode 100644 index 00000000000..809f505507e --- /dev/null +++ b/mysql-test/include/not_ubsan.inc @@ -0,0 +1,8 @@ +# This file should only be used with test that finds bugs in ASan that can not +# be overcome. In normal cases one should fix the bug server/test case or in +# the worst case add a (temporary?) suppression in asan.supp or lsan.supp + +if (`select count(*) from information_schema.system_variables where variable_name='have_sanitizer' and global_value="UBSAN"`) +{ +--skip Can't be run with UBSAN +} diff --git a/mysql-test/include/rowid_filter_debug_kill.inc b/mysql-test/include/rowid_filter_debug_kill.inc new file mode 100644 index 00000000000..513efed8a4c --- /dev/null +++ b/mysql-test/include/rowid_filter_debug_kill.inc @@ -0,0 +1,59 @@ +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/have_sequence.inc +--source include/count_sessions.inc + +--echo # +--echo # MDEV-22761 KILL QUERY during rowid_filter, crashes +--echo # + +create table t2(a int); +insert into t2 select * from seq_0_to_99; + +# 10K rows +CREATE TABLE t3 ( + key1 int , + key2 int, + filler varchar(255), + KEY (key1), + KEY (key2) +); +select engine from information_schema.tables +where table_schema=database() and table_name='t3'; + +insert into t3 +select + A.seq, + B.seq, + 'filler-data-filler-data' +from seq_0_to_99 A, seq_0_to_99 B; + +analyze table t2,t3; + +explain +select * from t2, t3 +where + t3.key1=t2.a and t3.key2 in (2,3); + +let $target_id= `select connection_id()`; + +set debug_sync='handler_rowid_filter_check SIGNAL at_rowid_filter_check WAIT_FOR go'; +send +select * from t2, t3 +where + t3.key1=t2.a and t3.key2 in (2,3); + +connect (con1, localhost, root,,); +set debug_sync='now WAIT_FOR at_rowid_filter_check'; +evalp kill query $target_id; +set debug_sync='now SIGNAL go'; + +connection default; +disconnect con1; + +--error ER_QUERY_INTERRUPTED +reap; +set debug_sync='RESET'; + +drop table t2,t3; +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/include/start_mysqld.inc b/mysql-test/include/start_mysqld.inc index 14a0b088fc0..6e448cb2efd 100644 --- a/mysql-test/include/start_mysqld.inc +++ b/mysql-test/include/start_mysqld.inc @@ -9,7 +9,7 @@ if (!$restart_noprint) { - --let $restart_noprint= 2 + --let $restart_noprint=0 } --let $restart_cmd= restart @@ -24,7 +24,7 @@ if ($restart_parameters) --exec echo "$restart_cmd: $restart_parameters" > $_expect_file_name if (!$restart_noprint) { - --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR $MYSQLTEST_VARDIR MYSQLTEST_VARDIR + --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --exec echo "# $restart_cmd: $restart_parameters" } if ($restart_noprint == 1) diff --git a/mysql-test/include/switch_to_mysql_global_priv.inc b/mysql-test/include/switch_to_mysql_global_priv.inc new file mode 100644 index 00000000000..3a93746c4e8 --- /dev/null +++ b/mysql-test/include/switch_to_mysql_global_priv.inc @@ -0,0 +1,7 @@ +echo # switching back from mysql.user to mysql.global_priv; +disable_query_log; +drop table mysql.user; +rename table mysql.user_bak to mysql.user; +rename table mysql.global_priv_bak to mysql.global_priv; +flush privileges; +enable_query_log; diff --git a/mysql-test/include/switch_to_mysql_user.inc b/mysql-test/include/switch_to_mysql_user.inc new file mode 100644 index 00000000000..7e5e460390c --- /dev/null +++ b/mysql-test/include/switch_to_mysql_user.inc @@ -0,0 +1,57 @@ +echo # switching from mysql.global_priv to mysql.user; +disable_query_log; +rename table mysql.user to mysql.user_bak; +CREATE TABLE mysql.user ( + Host char(60) binary DEFAULT '' NOT NULL, + User char(80) binary DEFAULT '' NOT NULL, + Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, + Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_tablespace_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Delete_history_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + ssl_type enum('','ANY','X509','SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, + ssl_cipher BLOB NOT NULL, + x509_issuer BLOB NOT NULL, + x509_subject BLOB NOT NULL, + max_questions int(11) unsigned DEFAULT 0 NOT NULL, + max_updates int(11) unsigned DEFAULT 0 NOT NULL, + max_connections int(11) unsigned DEFAULT 0 NOT NULL, + max_user_connections int(11) DEFAULT 0 NOT NULL, + plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL, + authentication_string TEXT NOT NULL, + password_expired ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + is_role enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + default_role char(80) binary DEFAULT '' NOT NULL, + max_statement_time decimal(12,6) DEFAULT 0 NOT NULL, + PRIMARY KEY (Host,User) +) engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; +insert mysql.user select * from mysql.user_bak; +rename table mysql.global_priv to mysql.global_priv_bak; +flush privileges; +enable_query_log; diff --git a/mysql-test/include/system_db_struct.inc b/mysql-test/include/system_db_struct.inc index 9467c023dba..d043d209850 100644 --- a/mysql-test/include/system_db_struct.inc +++ b/mysql-test/include/system_db_struct.inc @@ -5,9 +5,9 @@ -- replace_result Tables_in_mysql Tables_in_db Tables_in_test Tables_in_db show tables; show create table db; -show create table host; show create table user; show create table func; +show create table global_priv; show create table tables_priv; show create table columns_priv; show create table procs_priv; diff --git a/mysql-test/include/type_hrtime.inc b/mysql-test/include/type_hrtime.inc index 55fd3d665fe..128e3295133 100644 --- a/mysql-test/include/type_hrtime.inc +++ b/mysql-test/include/type_hrtime.inc @@ -18,7 +18,7 @@ insert t1 values (20101211030405.789e0); insert ignore t1 values (99991231235959e1); select * from t1; --replace_regex /121000/121094/ /457000/457031/ /789000/789062/ -select truncate(a, 6) from t1; # Field::val_real() +select cast(a AS double(30,6)) from t1; # Field::val_real() select a DIV 1 from t1; # Field::val_int() select group_concat(distinct a) from t1; # Field::cmp() alter table t1 engine=innodb; @@ -55,6 +55,12 @@ select * from t1; delete from t1 where a < 20110101; select * from t1; +if ($type == time) +{ +delete from t1 where a is not null; +select * from t1; +} + # # create ... select # diff --git a/mysql-test/include/wait_until_connected_again.inc b/mysql-test/include/wait_until_connected_again.inc index 26168d10558..15a1e5bf847 100644 --- a/mysql-test/include/wait_until_connected_again.inc +++ b/mysql-test/include/wait_until_connected_again.inc @@ -11,7 +11,7 @@ let $counter= 5000; let $mysql_errno= 9999; while ($mysql_errno) { - --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,ER_LOCK_WAIT_TIMEOUT,2002,2006,2013 + --error 0,ER_ACCESS_DENIED_ERROR,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,ER_LOCK_WAIT_TIMEOUT,2002,2006,2013 show status; dec $counter; @@ -19,13 +19,18 @@ while ($mysql_errno) { --die Server failed to restart } + if (!$mysql_errno) + { + # WSREP: SHOW STATUS queries are allowed even if wsrep + # is not ready. Make sure wsrep is ready before + # returning from this script + source include/wait_wsrep_ready.inc; + } + if ($mysql_errno == 1045) + { + let $mysql_errno=0; + } --sleep 0.1 } --enable_query_log --enable_result_log - -# WSREP: SHOW STATUS queries are allowed even if wsrep -# is not ready. Make sure wsrep is ready before -# returning from this script - ---source include/wait_wsrep_ready.inc diff --git a/mysql-test/include/wsrep_wait_disconnect.inc b/mysql-test/include/wsrep_wait_disconnect.inc new file mode 100644 index 00000000000..740fc0d9426 --- /dev/null +++ b/mysql-test/include/wsrep_wait_disconnect.inc @@ -0,0 +1,20 @@ +let $wait_condition = SELECT 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_ready' AND VARIABLE_VALUE = 'OFF'; +# since this is called until AFTER provider disconnects,we need to allow +# queries in non-prim +# +# We are also forced to use a hard-coded value for wsrep_sync_wait here because +# we can not issue a SELECT query to obtain the original value and then restore +# it +disable_query_log; +SET SESSION wsrep_sync_wait = 7; +--let $restore_wsrep_on = `SHOW VARIABLES WHERE Variable_name = 'wsrep_on' AND Value = 'ON'` +SET SESSION wsrep_on = OFF; + +--source include/wait_condition.inc + +if ($restore_wsrep_on != "") +{ + --eval SET SESSION wsrep_on = ON +} +SET SESSION wsrep_sync_wait = 15; +enable_query_log; |