diff options
Diffstat (limited to 'mysql-test')
38 files changed, 687 insertions, 428 deletions
diff --git a/mysql-test/lib/My/Debugger.pm b/mysql-test/lib/My/Debugger.pm index 0854a1c4363..5ace6b6ecec 100644 --- a/mysql-test/lib/My/Debugger.pm +++ b/mysql-test/lib/My/Debugger.pm @@ -61,7 +61,7 @@ my %debuggers = ( lldb => { term => 1, options => '-s {script} {exe}', - script => 'process launch --stop-at-entry {args}', + script => 'process launch --stop-at-entry -- {args}', }, valgrind => { options => '--tool=memcheck --show-reachable=yes --leak-check=yes --num-callers=16 --quiet --suppressions='.cwd().'/valgrind.supp {exe} {args} --loose-wait-for-pos-timeout=1500', diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 4532c9ca990..33b625a0f6d 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -17106,6 +17106,220 @@ a 3 DROP VIEW v1; DROP TABLE t1; +# +# MDEV-25128: Split optimization for join with materialized semi-join +# +create table t1 (id int, a int, index (a), index (id, a)) engine=myisam; +insert into t1 values +(17,1),(17,3010),(17,3013),(17,3053),(21,2446),(21,2467),(21,2); +create table t2 (a int) engine=myisam; +insert into t2 values (1),(2),(3); +create table t3 (id int) engine=myisam; +insert into t3 values (1),(2); +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +set optimizer_switch="split_materialized=off"; +select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +id a a id +17 1 1 1 +21 2 2 2 +explain select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 ref a a 5 test.t3.id 1 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <derived2> ref key0 key0 5 test.t3.id 2 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +2 DERIVED cp2 range NULL a 5 NULL 8 Using index for group-by +explain format=json select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t3.`id` is not null and t3.`id` is not null" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.id"], + "rows": 1, + "filtered": 100 + }, + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "4", + "used_key_parts": ["a"], + "ref": ["func"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.id"], + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 2, + "table": { + "table_name": "cp2", + "access_type": "range", + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "rows": 8, + "filtered": 100, + "using_index_for_group_by": true + } + } + } + } + } +} +set optimizer_switch="split_materialized=default"; +select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +id a a id +17 1 1 1 +21 2 2 2 +explain select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 ref a a 5 test.t3.id 1 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <derived2> ref key0 key0 5 test.t3.id 2 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +2 LATERAL DERIVED cp2 ref a a 5 test.t1.a 1 Using index +explain format=json select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t3.`id` is not null and t3.`id` is not null" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.id"], + "rows": 1, + "filtered": 100 + }, + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "4", + "used_key_parts": ["a"], + "ref": ["func"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.id"], + "rows": 2, + "filtered": 100, + "materialized": { + "lateral": 1, + "query_block": { + "select_id": 2, + "outer_ref_condition": "t1.a is not null", + "table": { + "table_name": "cp2", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t1.a"], + "rows": 1, + "filtered": 100, + "using_index": true + } + } + } + } + } +} +prepare stmt from "select * from t1, (select a from t1 cp2 group by a) dt, t3 +where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2)"; +execute stmt; +id a a id +17 1 1 1 +21 2 2 2 +execute stmt; +id a a id +17 1 1 1 +21 2 2 2 +deallocate prepare stmt; +drop table t1,t2,t3; # End of 10.3 tests # # MDEV-18679: materialized view with SELECT S containing materialized diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 34c09c9e948..a880712c8bd 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3479,6 +3479,43 @@ SELECT * from v1 WHERE a=3; DROP VIEW v1; DROP TABLE t1; +--echo # +--echo # MDEV-25128: Split optimization for join with materialized semi-join +--echo # + +create table t1 (id int, a int, index (a), index (id, a)) engine=myisam; +insert into t1 values +(17,1),(17,3010),(17,3013),(17,3053),(21,2446),(21,2467),(21,2); + +create table t2 (a int) engine=myisam; +insert into t2 values (1),(2),(3); + +create table t3 (id int) engine=myisam; +insert into t3 values (1),(2); + +analyze table t1,t2,t3; + +let $q= +select * from t1, (select a from t1 cp2 group by a) dt, t3 + where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); + +set optimizer_switch="split_materialized=off"; +eval $q; +eval explain $q; +eval explain format=json $q; + +set optimizer_switch="split_materialized=default"; +eval $q; +eval explain $q; +eval explain format=json $q; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop table t1,t2,t3; + --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 36cc3ad0a93..79c5b7923bd 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -6126,6 +6126,36 @@ a b c d e DROP TABLE t1,t2,t3,t4; set join_cache_level=@save_join_cache_level; # +# MDEV-24767: forced BNLH used for equi-join supported by compound index +# +create table t1 (a int, b int, c int ) engine=myisam ; +create table t2 (a int, b int, c int, primary key (c,a,b)) engine=myisam ; +insert into t1 values (3,4,2), (5,6,4); +insert into t2 values (3,4,2), (5,6,4); +select t1.a, t1.b, t1.c from t1,t2 +where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; +a b c +3 4 2 +5 6 4 +explain select t1.a, t1.b, t1.c from t1,t2 +where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 12 test.t1.c,test.t1.a,test.t1.b 1 Using index +set join_cache_level=3; +select t1.a, t1.b, t1.c from t1,t2 +where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; +a b c +3 4 2 +5 6 4 +explain select t1.a, t1.b, t1.c from t1,t2 +where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 hash_index PRIMARY #hash#PRIMARY:PRIMARY 12:12 test.t1.c,test.t1.a,test.t1.b 2 Using index; Using join buffer (flat, BNLH join) +drop table t1,t2; +set join_cache_level=@save_join_cache_level; +# # MDEV-21243: Join buffer: condition is checked in wrong place for range access # create table t1(a int primary key); diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index 506e613887b..b4271f648e3 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -4106,6 +4106,30 @@ DROP TABLE t1,t2,t3,t4; set join_cache_level=@save_join_cache_level; --echo # +--echo # MDEV-24767: forced BNLH used for equi-join supported by compound index +--echo # + +create table t1 (a int, b int, c int ) engine=myisam ; +create table t2 (a int, b int, c int, primary key (c,a,b)) engine=myisam ; +insert into t1 values (3,4,2), (5,6,4); +insert into t2 values (3,4,2), (5,6,4); + +let $q= +select t1.a, t1.b, t1.c from t1,t2 + where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; + +eval $q; +eval explain $q; + +set join_cache_level=3; +eval $q; +eval explain $q; + +drop table t1,t2; + +set join_cache_level=@save_join_cache_level; + +--echo # --echo # MDEV-21243: Join buffer: condition is checked in wrong place for range access --echo # create table t1(a int primary key); @@ -4145,7 +4169,8 @@ where drop table t1,t2,t3; --echo # End of 10.3 tests -# The following command must be the last one in the file + +# The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/userstat.result b/mysql-test/main/userstat.result index 6ba3d0b7811..6d9cd491de6 100644 --- a/mysql-test/main/userstat.result +++ b/mysql-test/main/userstat.result @@ -1,4 +1,3 @@ -DROP TABLE IF EXISTS t1; select variable_value from information_schema.global_status where variable_name="handler_read_key" into @global_read_key; Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead @@ -237,3 +236,15 @@ select @@in_transaction; 0 drop table t1; set @@global.general_log=@save_general_log; +# +# MDEV-25242 Server crashes in check_grant upon invoking function with userstat enabled +# +create function f() returns int return (select 1 from performance_schema.threads); +set global userstat= 1; +select f() from information_schema.table_statistics; +ERROR 21000: Subquery returns more than 1 row +set global userstat= 0; +drop function f; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/userstat.test b/mysql-test/main/userstat.test index 547138cfeaa..42fe1c2ad17 100644 --- a/mysql-test/main/userstat.test +++ b/mysql-test/main/userstat.test @@ -6,10 +6,6 @@ -- source include/have_innodb.inc -- source include/have_log_bin.inc ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings - select variable_value from information_schema.global_status where variable_name="handler_read_key" into @global_read_key; show columns from information_schema.client_statistics; show columns from information_schema.user_statistics; @@ -115,5 +111,18 @@ set @@autocommit=1; select @@in_transaction; drop table t1; -# Cleanup set @@global.general_log=@save_general_log; + +--echo # +--echo # MDEV-25242 Server crashes in check_grant upon invoking function with userstat enabled +--echo # +create function f() returns int return (select 1 from performance_schema.threads); +set global userstat= 1; +--error ER_SUBQUERY_NO_1_ROW +select f() from information_schema.table_statistics; +set global userstat= 0; +drop function f; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 4585f370c1d..9499c7a41e4 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6827,3 +6827,19 @@ Drop table t1; # # End of 10.3 tests # +# +# MDEV-25206: view specification contains unknown column reference +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (c int); +CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +INSERT INTO t3 SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +CREATE TABLE t4 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +DROP TABLE t1,t2,t3; +# End of 10.4 tests diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 78ab6a98ebb..3325d4e6dd5 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6542,3 +6542,24 @@ Drop table t1; --echo # --echo # End of 10.3 tests --echo # + +--echo # +--echo # MDEV-25206: view specification contains unknown column reference +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (c int); + +--error ER_BAD_FIELD_ERROR +CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +--error ER_BAD_FIELD_ERROR +INSERT INTO t3 SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +--error ER_BAD_FIELD_ERROR +CREATE TABLE t4 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; + +DROP TABLE t1,t2,t3; + +--echo # End of 10.4 tests diff --git a/mysql-test/suite/galera/include/galera_st_disconnect_slave.inc b/mysql-test/suite/galera/include/galera_st_disconnect_slave.inc index 3ac52deb284..bd63a7fd1d8 100644 --- a/mysql-test/suite/galera/include/galera_st_disconnect_slave.inc +++ b/mysql-test/suite/galera/include/galera_st_disconnect_slave.inc @@ -21,7 +21,7 @@ INSERT INTO t1 VALUES ('node2_committed_before'); INSERT INTO t1 VALUES ('node2_committed_before'); COMMIT; ---source suite/galera/include/galera_unload_provider.inc +--source suite/galera/include/galera_stop_replication.inc --connection node_1 --let $wait_condition = SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' @@ -53,7 +53,7 @@ INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); --connection node_2 ---source suite/galera/include/galera_load_provider.inc +--source suite/galera/include/galera_start_replication.inc # # client connections were killed by provider load, so have to re-open here diff --git a/mysql-test/suite/galera/include/galera_load_provider.inc b/mysql-test/suite/galera/include/galera_start_replication.inc index b9b201106d7..b9b201106d7 100644 --- a/mysql-test/suite/galera/include/galera_load_provider.inc +++ b/mysql-test/suite/galera/include/galera_start_replication.inc diff --git a/mysql-test/suite/galera/include/galera_unload_provider.inc b/mysql-test/suite/galera/include/galera_stop_replication.inc index ed7e9bc41f0..ed7e9bc41f0 100644 --- a/mysql-test/suite/galera/include/galera_unload_provider.inc +++ b/mysql-test/suite/galera/include/galera_stop_replication.inc diff --git a/mysql-test/suite/galera/r/galera_ctas.result b/mysql-test/suite/galera/r/galera_ctas.result new file mode 100644 index 00000000000..f044f807410 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_ctas.result @@ -0,0 +1,88 @@ +connection node_2; +connection node_1; +connection node_1; +create table t1_Aria(a int, count int, b int, key(b)) engine=Aria; +INSERT INTO t1_Aria values (1,1,1); +create table t1_MyISAM(a int, count int, b int, key(b)) engine=MyISAM; +INSERT INTO t1_MyISAM values (1,1,1); +create table t1_InnoDB(a int, count int, b int, key(b)) engine=InnoDB; +INSERT INTO t1_InnoDB values (1,1,1); +SET SESSION default_storage_engine=MyISAM; +CREATE TABLE t2 AS SELECT * FROM t1_Aria; +CREATE TABLE t3 AS SELECT * FROM t1_MyISAM; +CREATE TABLE t4 AS SELECT * FROM t1_InnoDB; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `count` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` int(11) DEFAULT NULL, + `count` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t4; +Table Create Table +t4 CREATE TABLE `t4` ( + `a` int(11) DEFAULT NULL, + `count` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2, t3,t4; +SET SESSION default_storage_engine=Aria; +CREATE TABLE t2 AS SELECT * FROM t1_Aria; +CREATE TABLE t3 AS SELECT * FROM t1_MyISAM; +CREATE TABLE t4 AS SELECT * FROM t1_InnoDB; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `count` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` int(11) DEFAULT NULL, + `count` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +SHOW CREATE TABLE t4; +Table Create Table +t4 CREATE TABLE `t4` ( + `a` int(11) DEFAULT NULL, + `count` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +DROP TABLE t2, t3,t4; +SET SESSION default_storage_engine=InnoDB; +CREATE TABLE t2 AS SELECT * FROM t1_Aria; +CREATE TABLE t3 AS SELECT * FROM t1_MyISAM; +CREATE TABLE t4 AS SELECT * FROM t1_InnoDB; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `count` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` int(11) DEFAULT NULL, + `count` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t4; +Table Create Table +t4 CREATE TABLE `t4` ( + `a` int(11) DEFAULT NULL, + `count` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t2, t3,t4; +DROP TABLE t1_MyISAM, t1_Aria,t1_InnoDB; diff --git a/mysql-test/suite/galera/r/galera_ist_restart_joiner.result b/mysql-test/suite/galera/r/galera_ist_restart_joiner.result index 80d2c90642b..7cb6d90840e 100644 --- a/mysql-test/suite/galera/r/galera_ist_restart_joiner.result +++ b/mysql-test/suite/galera/r/galera_ist_restart_joiner.result @@ -5,8 +5,9 @@ connection node_2; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1)); INSERT INTO t1 VALUES (1, 'a'), (2, 'a'), (3, 'a'), (4, 'a'), (5, 'a'),(6, 'a'); connection node_2; +SET SESSION wsrep_sync_wait=0; Unloading wsrep provider ... -SET GLOBAL wsrep_provider = 'none'; +SET GLOBAL wsrep_cluster_address = ''; connection node_1; UPDATE t1 SET f2 = 'b' WHERE f1 > 1; UPDATE t1 SET f2 = 'c' WHERE f1 > 2; diff --git a/mysql-test/suite/galera/r/galera_log_bin_opt.result b/mysql-test/suite/galera/r/galera_log_bin_opt.result new file mode 100644 index 00000000000..160575df412 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_log_bin_opt.result @@ -0,0 +1,80 @@ +connection node_2; +connection node_1; +connection node_1; +set global wsrep_on=OFF; +reset master; +set global wsrep_on=ON; +connection node_2; +set global wsrep_on=OFF; +reset master; +set global wsrep_on=ON; +CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (id INT) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1); +INSERT INTO t2 VALUES (1); +connection node_2; +SELECT COUNT(*) = 1 FROM t1; +COUNT(*) = 1 +1 +SELECT COUNT(*) = 2 FROM t2; +COUNT(*) = 2 +1 +connection node_1; +ALTER TABLE t1 ADD COLUMN f2 INTEGER; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +mysqld-bin.000001 # Gtid # # GTID #-#-# +mysqld-bin.000001 # Query # # use `test`; CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB +mysqld-bin.000001 # Gtid # # BEGIN GTID #-#-# +mysqld-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (1) +mysqld-bin.000001 # Table_map # # table_id: # (test.t1) +mysqld-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +mysqld-bin.000001 # Xid # # COMMIT /* XID */ +mysqld-bin.000001 # Gtid # # GTID #-#-# +mysqld-bin.000001 # Query # # use `test`; CREATE TABLE t2 (id INT) ENGINE=InnoDB +mysqld-bin.000001 # Gtid # # BEGIN GTID #-#-# +mysqld-bin.000001 # Annotate_rows # # INSERT INTO t2 VALUES (1) +mysqld-bin.000001 # Table_map # # table_id: # (test.t2) +mysqld-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +mysqld-bin.000001 # Xid # # COMMIT /* XID */ +mysqld-bin.000001 # Gtid # # BEGIN GTID #-#-# +mysqld-bin.000001 # Annotate_rows # # INSERT INTO t2 VALUES (1) +mysqld-bin.000001 # Table_map # # table_id: # (test.t2) +mysqld-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +mysqld-bin.000001 # Xid # # COMMIT /* XID */ +mysqld-bin.000001 # Gtid # # GTID #-#-# +mysqld-bin.000001 # Query # # use `test`; ALTER TABLE t1 ADD COLUMN f2 INTEGER +connection node_2; +SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; +COUNT(*) = 2 +1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +mysqld-bin.000001 # Gtid # # GTID #-#-# +mysqld-bin.000001 # Query # # use `test`; CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB +mysqld-bin.000001 # Gtid # # BEGIN GTID #-#-# +mysqld-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (1) +mysqld-bin.000001 # Table_map # # table_id: # (test.t1) +mysqld-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +mysqld-bin.000001 # Xid # # COMMIT /* XID */ +mysqld-bin.000001 # Gtid # # GTID #-#-# +mysqld-bin.000001 # Query # # use `test`; CREATE TABLE t2 (id INT) ENGINE=InnoDB +mysqld-bin.000001 # Gtid # # BEGIN GTID #-#-# +mysqld-bin.000001 # Annotate_rows # # INSERT INTO t2 VALUES (1) +mysqld-bin.000001 # Table_map # # table_id: # (test.t2) +mysqld-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +mysqld-bin.000001 # Xid # # COMMIT /* XID */ +mysqld-bin.000001 # Gtid # # BEGIN GTID #-#-# +mysqld-bin.000001 # Annotate_rows # # INSERT INTO t2 VALUES (1) +mysqld-bin.000001 # Table_map # # table_id: # (test.t2) +mysqld-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +mysqld-bin.000001 # Xid # # COMMIT /* XID */ +mysqld-bin.000001 # Gtid # # GTID #-#-# +mysqld-bin.000001 # Query # # use `test`; ALTER TABLE t1 ADD COLUMN f2 INTEGER +DROP TABLE t1; +DROP TABLE t2; +#cleanup +connection node_1; +SET GLOBAL wsrep_on=OFF; +RESET MASTER; diff --git a/mysql-test/suite/galera/t/galera_ctas.test b/mysql-test/suite/galera/t/galera_ctas.test new file mode 100644 index 00000000000..8b9ad9c4a20 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_ctas.test @@ -0,0 +1,39 @@ +--source include/galera_cluster.inc + +--connection node_1 +create table t1_Aria(a int, count int, b int, key(b)) engine=Aria; +INSERT INTO t1_Aria values (1,1,1); +create table t1_MyISAM(a int, count int, b int, key(b)) engine=MyISAM; +INSERT INTO t1_MyISAM values (1,1,1); +create table t1_InnoDB(a int, count int, b int, key(b)) engine=InnoDB; +INSERT INTO t1_InnoDB values (1,1,1); + +SET SESSION default_storage_engine=MyISAM; +CREATE TABLE t2 AS SELECT * FROM t1_Aria; +CREATE TABLE t3 AS SELECT * FROM t1_MyISAM; +CREATE TABLE t4 AS SELECT * FROM t1_InnoDB; +SHOW CREATE TABLE t2; +SHOW CREATE TABLE t3; +SHOW CREATE TABLE t4; +DROP TABLE t2, t3,t4; + +SET SESSION default_storage_engine=Aria; +CREATE TABLE t2 AS SELECT * FROM t1_Aria; +CREATE TABLE t3 AS SELECT * FROM t1_MyISAM; +CREATE TABLE t4 AS SELECT * FROM t1_InnoDB; +SHOW CREATE TABLE t2; +SHOW CREATE TABLE t3; +SHOW CREATE TABLE t4; +DROP TABLE t2, t3,t4; + +SET SESSION default_storage_engine=InnoDB; +CREATE TABLE t2 AS SELECT * FROM t1_Aria; +CREATE TABLE t3 AS SELECT * FROM t1_MyISAM; +CREATE TABLE t4 AS SELECT * FROM t1_InnoDB; +SHOW CREATE TABLE t2; +SHOW CREATE TABLE t3; +SHOW CREATE TABLE t4; + +DROP TABLE t2, t3,t4; +DROP TABLE t1_MyISAM, t1_Aria,t1_InnoDB; + diff --git a/mysql-test/suite/galera/t/galera_gcache_recover.cnf b/mysql-test/suite/galera/t/galera_gcache_recover.cnf index c7b59b6a27e..34c757de77e 100644 --- a/mysql-test/suite/galera/t/galera_gcache_recover.cnf +++ b/mysql-test/suite/galera/t/galera_gcache_recover.cnf @@ -4,4 +4,4 @@ wsrep_provider_options='base_port=@mysqld.1.#galera_port;gcache.recover=yes;pc.ignore_sb=true' [mysqld.2] -wsrep_provider_options='base_port=@mysqld.2.#galera_port;gcache.recover=yes;pc.ignore_sb=true' +wsrep_provider_options='base_port=@mysqld.2.#galera_port;gcache.recover=yes' diff --git a/mysql-test/suite/galera/t/galera_ist_restart_joiner.test b/mysql-test/suite/galera/t/galera_ist_restart_joiner.test index 42f210170bc..f56d0e657bd 100644 --- a/mysql-test/suite/galera/t/galera_ist_restart_joiner.test +++ b/mysql-test/suite/galera/t/galera_ist_restart_joiner.test @@ -21,7 +21,8 @@ INSERT INTO t1 VALUES (1, 'a'), (2, 'a'), (3, 'a'), (4, 'a'), (5, 'a'),(6, 'a'); # Disconnect node #2 --connection node_2 ---source suite/galera/include/galera_unload_provider.inc +SET SESSION wsrep_sync_wait=0; +--source suite/galera/include/galera_stop_replication.inc --connection node_1 UPDATE t1 SET f2 = 'b' WHERE f1 > 1; @@ -43,7 +44,6 @@ UPDATE t1 SET f2 = 'c' WHERE f1 > 2; # ... and restart provider to force IST --echo Loading wsrep_provider ... --disable_query_log ---eval SET GLOBAL wsrep_provider = '$wsrep_provider_orig'; # Make sure IST will block ... --let $galera_sync_point = recv_IST_after_apply_trx --source include/galera_set_sync_point.inc diff --git a/mysql-test/suite/galera/t/galera_log_bin.inc b/mysql-test/suite/galera/t/galera_log_bin.inc new file mode 100644 index 00000000000..cc78367b510 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_log_bin.inc @@ -0,0 +1,46 @@ +--source include/galera_cluster.inc +--source include/force_restart.inc + +--connection node_1 +set global wsrep_on=OFF; +reset master; +set global wsrep_on=ON; +--connection node_2 +set global wsrep_on=OFF; +reset master; +set global wsrep_on=ON; + +# +# Test Galera with --log-bin --log-slave-updates . +# This way the actual MySQL binary log is used, +# rather than Galera's own implementation +# + +CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (id INT) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1); +INSERT INTO t2 VALUES (1); + +--connection node_2 +SELECT COUNT(*) = 1 FROM t1; +SELECT COUNT(*) = 2 FROM t2; + +--connection node_1 +ALTER TABLE t1 ADD COLUMN f2 INTEGER; +--let $MASTER_MYPORT=$NODE_MYPORT_1 +--source include/show_binlog_events.inc + +--connection node_2 +SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; +--let $MASTER_MYPORT=$NODE_MYPORT_2 +--source include/show_binlog_events.inc + +DROP TABLE t1; +DROP TABLE t2; + +--echo #cleanup +--connection node_1 +SET GLOBAL wsrep_on=OFF; +RESET MASTER; diff --git a/mysql-test/suite/galera/t/galera_log_bin.test b/mysql-test/suite/galera/t/galera_log_bin.test index cc78367b510..923bd623a8a 100644 --- a/mysql-test/suite/galera/t/galera_log_bin.test +++ b/mysql-test/suite/galera/t/galera_log_bin.test @@ -1,46 +1 @@ ---source include/galera_cluster.inc ---source include/force_restart.inc - ---connection node_1 -set global wsrep_on=OFF; -reset master; -set global wsrep_on=ON; ---connection node_2 -set global wsrep_on=OFF; -reset master; -set global wsrep_on=ON; - -# -# Test Galera with --log-bin --log-slave-updates . -# This way the actual MySQL binary log is used, -# rather than Galera's own implementation -# - -CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1); - -CREATE TABLE t2 (id INT) ENGINE=InnoDB; -INSERT INTO t2 VALUES (1); -INSERT INTO t2 VALUES (1); - ---connection node_2 -SELECT COUNT(*) = 1 FROM t1; -SELECT COUNT(*) = 2 FROM t2; - ---connection node_1 -ALTER TABLE t1 ADD COLUMN f2 INTEGER; ---let $MASTER_MYPORT=$NODE_MYPORT_1 ---source include/show_binlog_events.inc - ---connection node_2 -SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ---let $MASTER_MYPORT=$NODE_MYPORT_2 ---source include/show_binlog_events.inc - -DROP TABLE t1; -DROP TABLE t2; - ---echo #cleanup ---connection node_1 -SET GLOBAL wsrep_on=OFF; -RESET MASTER; +--source galera_log_bin.inc diff --git a/mysql-test/suite/galera/t/galera_log_bin_opt-master.opt b/mysql-test/suite/galera/t/galera_log_bin_opt-master.opt new file mode 100644 index 00000000000..03fcb5d040d --- /dev/null +++ b/mysql-test/suite/galera/t/galera_log_bin_opt-master.opt @@ -0,0 +1 @@ +--log-slave-updates --log-bin diff --git a/mysql-test/suite/galera/t/galera_log_bin_opt.cnf b/mysql-test/suite/galera/t/galera_log_bin_opt.cnf new file mode 100644 index 00000000000..a09efd2e011 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_log_bin_opt.cnf @@ -0,0 +1,15 @@ +!include ../galera_2nodes.cnf + +[mysqld] +wsrep_sst_method=mariabackup +wsrep_sst_auth="root:" + +[mysqld.1] +wsrep_provider_options='base_port=@mysqld.1.#galera_port;gcache.size=1;pc.ignore_sb=true' + +[mysqld.2] +wsrep_provider_options='base_port=@mysqld.2.#galera_port;gcache.size=1;pc.ignore_sb=true' + +[sst] +transferfmt=@ENV.MTR_GALERA_TFMT +streamfmt=xbstream diff --git a/mysql-test/suite/galera/t/galera_log_bin_opt.test b/mysql-test/suite/galera/t/galera_log_bin_opt.test new file mode 100644 index 00000000000..f3d0afbc8bc --- /dev/null +++ b/mysql-test/suite/galera/t/galera_log_bin_opt.test @@ -0,0 +1,2 @@ +--source include/have_mariabackup.inc +--source galera_log_bin.inc diff --git a/mysql-test/suite/galera/t/galera_sst_mariabackup_lost_found.test b/mysql-test/suite/galera/t/galera_sst_mariabackup_lost_found.test index d1c30656139..e8dcbd849d8 100644 --- a/mysql-test/suite/galera/t/galera_sst_mariabackup_lost_found.test +++ b/mysql-test/suite/galera/t/galera_sst_mariabackup_lost_found.test @@ -10,7 +10,7 @@ --connection node_2 #--connection node_2 -#--source suite/galera/include/galera_unload_provider.inc +#--source suite/galera/include/galera_stop_replication.inc --echo Shutting down server ... --source include/shutdown_mysqld.inc diff --git a/mysql-test/suite/galera_3nodes/t/galera_ipv6_mysqldump.test b/mysql-test/suite/galera_3nodes/t/galera_ipv6_mysqldump.test index f5dd1aeb06d..2f8bf07b5b5 100644 --- a/mysql-test/suite/galera_3nodes/t/galera_ipv6_mysqldump.test +++ b/mysql-test/suite/galera_3nodes/t/galera_ipv6_mysqldump.test @@ -37,7 +37,7 @@ SET GLOBAL wsrep_sst_method = 'mysqldump'; # #--connection node_2 -#--source suite/galera/include/galera_unload_provider.inc +#--source suite/galera/include/galera_stop_replication.inc --echo Shutting down server ... --source include/shutdown_mysqld.inc @@ -63,7 +63,7 @@ INSERT INTO t1 VALUES (1); let $restart_noprint=2; --source include/start_mysqld.inc -#--source suite/galera/include/galera_load_provider.inc +#--source suite/galera/include/galera_start_replication.inc --let $wait_condition = SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' --source include/wait_condition.inc diff --git a/mysql-test/suite/galera_3nodes/t/galera_ist_gcache_rollover.test b/mysql-test/suite/galera_3nodes/t/galera_ist_gcache_rollover.test index 69e80ee6c3d..b77a810f37d 100644 --- a/mysql-test/suite/galera_3nodes/t/galera_ist_gcache_rollover.test +++ b/mysql-test/suite/galera_3nodes/t/galera_ist_gcache_rollover.test @@ -29,10 +29,10 @@ INSERT INTO t1 VALUES (01), (02), (03), (04), (05); # Disconnect nodes #2 and #3 --connection node_2 ---source suite/galera/include/galera_unload_provider.inc +--source suite/galera/include/galera_stop_replication.inc --connection node_3 ---source suite/galera/include/galera_unload_provider.inc +--source suite/galera/include/galera_stop_replication.inc --connection node_1 --source include/wait_until_connected_again.inc diff --git a/mysql-test/suite/innodb/include/innodb_simulate_comp_failures.inc b/mysql-test/suite/innodb/include/innodb_simulate_comp_failures.inc deleted file mode 100644 index d9c0294faf5..00000000000 --- a/mysql-test/suite/innodb/include/innodb_simulate_comp_failures.inc +++ /dev/null @@ -1,152 +0,0 @@ ---echo # ---echo # Testing robustness against random compression failures ---echo # - ---source include/not_embedded.inc ---source include/have_innodb.inc - ---let $simulate_comp_failures_save = `SELECT @@innodb_simulate_comp_failures` - ---disable_query_log -call mtr.add_suppression("InnoDB: Simulating a compression failure for table `test`\\.`t1`"); ---enable_query_log - -# create the table with compressed pages of size 8K. -CREATE TABLE t1(id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(255), KEY msg_i(msg)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; - -SHOW CREATE TABLE t1; - -# percentage of compressions that will be forced to fail -SET GLOBAL innodb_simulate_comp_failures = 25; - ---disable_query_log ---disable_result_log - -let $num_inserts_ind = $num_inserts; -let $commit_iterations=50; - -while ($num_inserts_ind) -{ - let $repeat = `select floor(rand() * 10)`; - eval INSERT INTO t1(id, msg) - VALUES ($num_inserts_ind, REPEAT('abcdefghijklmnopqrstuvwxyz', $repeat)); - dec $num_inserts_ind; -} - ---enable_query_log ---enable_result_log - -COMMIT; -SELECT COUNT(id) FROM t1; - ---disable_query_log ---disable_result_log - -# do random ops, making sure that some pages will get fragmented and reorganized. -let $num_ops_ind = $num_ops; -let $commit_count= $commit_iterations; - -BEGIN; - -while($num_ops_ind) -{ - let $idx = `select floor(rand()*$num_inserts)`; - let $insert_or_update = `select floor(rand()*3)`; - - let $repeat = `select floor(rand() * 9) + 1`; - - let $msg = query_get_value(`select repeat('abcdefghijklmnopqrstuvwxyz', $repeat) as x`, x, 1); - - let $single_or_multi = `select floor(rand()*10)`; - - if ($insert_or_update) - { - let $cnt = query_get_value(SELECT COUNT(*) cnt FROM t1 WHERE id=$idx, cnt, 1); - - if ($cnt) - { - let $update = `select floor(rand()*2)`; - - if ($update) - { - if ($single_or_multi) - { - eval UPDATE t1 SET msg=\"$msg\" WHERE id=$idx; - } - - if (!$single_or_multi) - { - eval UPDATE t1 SET msg=\"$msg\" WHERE id >= $idx - 100 AND id <= $idx + 100; - } - - } - - if (!$update) - { - if ($single_or_multi) - { - eval INSERT INTO t1(msg, id) VALUES (\"$msg\", $idx) ON DUPLICATE KEY UPDATE msg=VALUES(msg), id = VALUES(id); - } - - if (!$single_or_multi) - { - let $diff = 200; - - while ($diff) - { - eval INSERT INTO t1(msg, id) VALUES (\"$msg\", $idx + 100 - $diff) ON DUPLICATE KEY UPDATE msg=VALUES(msg), id=VALUES(id); - - dec $diff; - } - } - } - } - - if (!$cnt) - { - let $null_msg = `select floor(rand()*2)`; - - if ($null_msg) - { - eval INSERT INTO t1(id,msg) VALUES ($idx, NULL); - } - - if (!$null_msg) - { - eval INSERT INTO t1(id, msg) VALUES ($idx, \"$msg\"); - } - } - } - - if (!$insert_or_update) - { - if ($single_or_multi) - { - eval DELETE from t1 WHERE id=$idx; - } - - if (!$single_or_multi) - { - eval DELETE from t1 WHERE id >= $idx - 100 AND id <= $idx + 100; - } - } - - dec $commit_count; - if (!$commit_count) - { - let $commit_count= $commit_iterations; - COMMIT; - BEGIN; - } - - dec $num_ops_ind; -} - -COMMIT; - -# final cleanup -DROP TABLE t1; - -eval SET GLOBAL innodb_simulate_comp_failures = $simulate_comp_failures_save; - ---enable_query_log diff --git a/mysql-test/suite/innodb/r/innodb_simulate_comp_failures.result b/mysql-test/suite/innodb/r/innodb_simulate_comp_failures.result deleted file mode 100644 index f35e4159603..00000000000 --- a/mysql-test/suite/innodb/r/innodb_simulate_comp_failures.result +++ /dev/null @@ -1,17 +0,0 @@ -# -# Testing robustness against random compression failures -# -CREATE TABLE t1(id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(255), KEY msg_i(msg)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `msg` varchar(255) DEFAULT NULL, - PRIMARY KEY (`id`), - KEY `msg_i` (`msg`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 -SET GLOBAL innodb_simulate_comp_failures = 25; -COMMIT; -SELECT COUNT(id) FROM t1; -COUNT(id) -1500 diff --git a/mysql-test/suite/innodb/r/innodb_simulate_comp_failures_small.result b/mysql-test/suite/innodb/r/innodb_simulate_comp_failures_small.result deleted file mode 100644 index 099c673bca7..00000000000 --- a/mysql-test/suite/innodb/r/innodb_simulate_comp_failures_small.result +++ /dev/null @@ -1,17 +0,0 @@ -# -# Testing robustness against random compression failures -# -CREATE TABLE t1(id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(255), KEY msg_i(msg)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `msg` varchar(255) DEFAULT NULL, - PRIMARY KEY (`id`), - KEY `msg_i` (`msg`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 -SET GLOBAL innodb_simulate_comp_failures = 25; -COMMIT; -SELECT COUNT(id) FROM t1; -COUNT(id) -1000 diff --git a/mysql-test/suite/innodb/r/instant_alter_debug.result b/mysql-test/suite/innodb/r/instant_alter_debug.result index 60557d8ae0a..bf1204724a4 100644 --- a/mysql-test/suite/innodb/r/instant_alter_debug.result +++ b/mysql-test/suite/innodb/r/instant_alter_debug.result @@ -354,6 +354,20 @@ SELECT * FROM t1; a b c DROP TABLE t1; SET GLOBAL innodb_limit_optimistic_insert_debug = @saved_limit; +# +# MDEV-24796 Assertion page_has_next... failed +# in btr_pcur_store_position() +# +CREATE TABLE t1 (c INT KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1),(2); +SET GLOBAL innodb_limit_optimistic_insert_debug=2; +ALTER TABLE t1 ADD COLUMN d INT; +DELETE FROM t1; +InnoDB 0 transactions not purged +SELECT * FROM t1 WHERE c<>1 ORDER BY c DESC; +c d +DROP TABLE t1; +SET GLOBAL innodb_limit_optimistic_insert_debug = @saved_limit; # End of 10.3 tests # # MDEV-17899 Assertion failures on rollback of instant ADD/DROP @@ -446,4 +460,4 @@ SELECT variable_value-@old_instant instants FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; instants -33 +34 diff --git a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures-master.opt b/mysql-test/suite/innodb/t/innodb_simulate_comp_failures-master.opt deleted file mode 100644 index 39b205c9b68..00000000000 --- a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures-master.opt +++ /dev/null @@ -1,2 +0,0 @@ ---innodb-file-per-table ---skip-innodb-doublewrite diff --git a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures.test b/mysql-test/suite/innodb/t/innodb_simulate_comp_failures.test deleted file mode 100644 index 5a4978c9b37..00000000000 --- a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures.test +++ /dev/null @@ -1,9 +0,0 @@ ---source include/big_test.inc -# test takes too long with valgrind ---source include/not_valgrind.inc ---source include/have_debug.inc ---let $num_inserts = 1500 ---let $num_ops = 3500 ---source suite/innodb/include/innodb_simulate_comp_failures.inc -# clean exit ---exit diff --git a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small-master.opt b/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small-master.opt deleted file mode 100644 index fae32059249..00000000000 --- a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small-master.opt +++ /dev/null @@ -1,2 +0,0 @@ ---innodb-file-per-table - diff --git a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small.test b/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small.test deleted file mode 100644 index 79a16d36917..00000000000 --- a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small.test +++ /dev/null @@ -1,8 +0,0 @@ ---source include/have_debug.inc ---source include/not_valgrind.inc - ---let $num_inserts = 1000 ---let $num_ops = 30 ---source suite/innodb/include/innodb_simulate_comp_failures.inc -# clean exit ---exit diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test index 9a386c4a1b4..da0692990c4 100644 --- a/mysql-test/suite/innodb/t/instant_alter_debug.test +++ b/mysql-test/suite/innodb/t/instant_alter_debug.test @@ -408,6 +408,22 @@ SELECT * FROM t1; DROP TABLE t1; SET GLOBAL innodb_limit_optimistic_insert_debug = @saved_limit; +--echo # +--echo # MDEV-24796 Assertion page_has_next... failed +--echo # in btr_pcur_store_position() +--echo # + +CREATE TABLE t1 (c INT KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1),(2); +SET GLOBAL innodb_limit_optimistic_insert_debug=2; +ALTER TABLE t1 ADD COLUMN d INT; +DELETE FROM t1; +--source include/wait_all_purged.inc +SELECT * FROM t1 WHERE c<>1 ORDER BY c DESC; +DROP TABLE t1; + +SET GLOBAL innodb_limit_optimistic_insert_debug = @saved_limit; + --echo # End of 10.3 tests --echo # diff --git a/mysql-test/suite/sys_vars/r/innodb_simulate_comp_failures_basic.result b/mysql-test/suite/sys_vars/r/innodb_simulate_comp_failures_basic.result deleted file mode 100644 index 7a6c9ca2db6..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_simulate_comp_failures_basic.result +++ /dev/null @@ -1,77 +0,0 @@ -SET @start_global_value = @@global.innodb_simulate_comp_failures; -SELECT @start_global_value; -@start_global_value -0 -Valid values are between 0 and 99 -select @@global.innodb_simulate_comp_failures between 0 and 99; -@@global.innodb_simulate_comp_failures between 0 and 99 -1 -select @@global.innodb_simulate_comp_failures; -@@global.innodb_simulate_comp_failures -0 -select @@session.innodb_simulate_comp_failures; -ERROR HY000: Variable 'innodb_simulate_comp_failures' is a GLOBAL variable -show global variables like 'innodb_simulate_comp_failures'; -Variable_name Value -innodb_simulate_comp_failures 0 -show session variables like 'innodb_simulate_comp_failures'; -Variable_name Value -innodb_simulate_comp_failures 0 -select * from information_schema.global_variables where variable_name='innodb_simulate_comp_failures'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_SIMULATE_COMP_FAILURES 0 -select * from information_schema.session_variables where variable_name='innodb_simulate_comp_failures'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_SIMULATE_COMP_FAILURES 0 -set global innodb_simulate_comp_failures=10; -select @@global.innodb_simulate_comp_failures; -@@global.innodb_simulate_comp_failures -10 -select * from information_schema.global_variables where variable_name='innodb_simulate_comp_failures'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_SIMULATE_COMP_FAILURES 10 -select * from information_schema.session_variables where variable_name='innodb_simulate_comp_failures'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_SIMULATE_COMP_FAILURES 10 -set session innodb_simulate_comp_failures=1; -ERROR HY000: Variable 'innodb_simulate_comp_failures' is a GLOBAL variable and should be set with SET GLOBAL -set global innodb_simulate_comp_failures=1.1; -ERROR 42000: Incorrect argument type to variable 'innodb_simulate_comp_failures' -set global innodb_simulate_comp_failures=1e1; -ERROR 42000: Incorrect argument type to variable 'innodb_simulate_comp_failures' -set global innodb_simulate_comp_failures="foo"; -ERROR 42000: Incorrect argument type to variable 'innodb_simulate_comp_failures' -set global innodb_simulate_comp_failures=-7; -Warnings: -Warning 1292 Truncated incorrect innodb_simulate_comp_failures value: '-7' -select @@global.innodb_simulate_comp_failures; -@@global.innodb_simulate_comp_failures -0 -select * from information_schema.global_variables where variable_name='innodb_simulate_comp_failures'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_SIMULATE_COMP_FAILURES 0 -set global innodb_simulate_comp_failures=106; -Warnings: -Warning 1292 Truncated incorrect innodb_simulate_comp_failures value: '106' -select @@global.innodb_simulate_comp_failures; -@@global.innodb_simulate_comp_failures -99 -select * from information_schema.global_variables where variable_name='innodb_simulate_comp_failures'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_SIMULATE_COMP_FAILURES 99 -set global innodb_simulate_comp_failures=0; -select @@global.innodb_simulate_comp_failures; -@@global.innodb_simulate_comp_failures -0 -set global innodb_simulate_comp_failures=99; -select @@global.innodb_simulate_comp_failures; -@@global.innodb_simulate_comp_failures -99 -set global innodb_simulate_comp_failures=DEFAULT; -select @@global.innodb_simulate_comp_failures; -@@global.innodb_simulate_comp_failures -0 -SET @@global.innodb_simulate_comp_failures = @start_global_value; -SELECT @@global.innodb_simulate_comp_failures; -@@global.innodb_simulate_comp_failures -0 diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result index 545e3ea276b..ecd107f67c8 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result @@ -1689,18 +1689,6 @@ NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL -VARIABLE_NAME INNODB_SIMULATE_COMP_FAILURES -SESSION_VALUE NULL -DEFAULT_VALUE 0 -VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE INT UNSIGNED -VARIABLE_COMMENT Simulate compression failures. -NUMERIC_MIN_VALUE 0 -NUMERIC_MAX_VALUE 99 -NUMERIC_BLOCK_SIZE 0 -ENUM_VALUE_LIST NULL -READ_ONLY NO -COMMAND_LINE_ARGUMENT NONE VARIABLE_NAME INNODB_SORT_BUFFER_SIZE SESSION_VALUE NULL DEFAULT_VALUE 1048576 @@ -2042,7 +2030,7 @@ SESSION_VALUE NULL DEFAULT_VALUE ON VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Enable atomic writes, instead of using the doublewrite buffer, for files on devices that supports atomic writes. To use this option one must use innodb_file_per_table=1, innodb_flush_method=O_DIRECT. This option only works on Linux with either FusionIO cards using the directFS filesystem or with Shannon cards using any file system. +VARIABLE_COMMENT Enable atomic writes, instead of using the doublewrite buffer, for files on devices that supports atomic writes. This option only works on Linux with either FusionIO cards using the directFS filesystem or with Shannon cards using any file system. NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL diff --git a/mysql-test/suite/sys_vars/t/innodb_simulate_comp_failures_basic.test b/mysql-test/suite/sys_vars/t/innodb_simulate_comp_failures_basic.test deleted file mode 100644 index 07e70bf7343..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_simulate_comp_failures_basic.test +++ /dev/null @@ -1,65 +0,0 @@ ---source include/have_innodb.inc ---source include/have_debug.inc - -SET @start_global_value = @@global.innodb_simulate_comp_failures; -SELECT @start_global_value; - -# -# exists as global only -# - ---echo Valid values are between 0 and 99 -select @@global.innodb_simulate_comp_failures between 0 and 99; -select @@global.innodb_simulate_comp_failures; - ---error ER_INCORRECT_GLOBAL_LOCAL_VAR -select @@session.innodb_simulate_comp_failures; - -show global variables like 'innodb_simulate_comp_failures'; -show session variables like 'innodb_simulate_comp_failures'; -select * from information_schema.global_variables where variable_name='innodb_simulate_comp_failures'; -select * from information_schema.session_variables where variable_name='innodb_simulate_comp_failures'; - -# -# show that it's writable -# - -set global innodb_simulate_comp_failures=10; -select @@global.innodb_simulate_comp_failures; -select * from information_schema.global_variables where variable_name='innodb_simulate_comp_failures'; -select * from information_schema.session_variables where variable_name='innodb_simulate_comp_failures'; - ---error ER_GLOBAL_VARIABLE -set session innodb_simulate_comp_failures=1; - -# -# incorrect types -# - ---error ER_WRONG_TYPE_FOR_VAR -set global innodb_simulate_comp_failures=1.1; ---error ER_WRONG_TYPE_FOR_VAR -set global innodb_simulate_comp_failures=1e1; ---error ER_WRONG_TYPE_FOR_VAR -set global innodb_simulate_comp_failures="foo"; - -set global innodb_simulate_comp_failures=-7; -select @@global.innodb_simulate_comp_failures; -select * from information_schema.global_variables where variable_name='innodb_simulate_comp_failures'; -set global innodb_simulate_comp_failures=106; -select @@global.innodb_simulate_comp_failures; -select * from information_schema.global_variables where variable_name='innodb_simulate_comp_failures'; - -# -# min/max/DEFAULT values -# - -set global innodb_simulate_comp_failures=0; -select @@global.innodb_simulate_comp_failures; -set global innodb_simulate_comp_failures=99; -select @@global.innodb_simulate_comp_failures; -set global innodb_simulate_comp_failures=DEFAULT; -select @@global.innodb_simulate_comp_failures; - -SET @@global.innodb_simulate_comp_failures = @start_global_value; -SELECT @@global.innodb_simulate_comp_failures; |