diff options
Diffstat (limited to 'mysql-test')
40 files changed, 629 insertions, 135 deletions
diff --git a/mysql-test/main/create_drop_server.result b/mysql-test/main/create_drop_server.result index 29c4fe7e123..4f5d13b3541 100644 --- a/mysql-test/main/create_drop_server.result +++ b/mysql-test/main/create_drop_server.result @@ -38,9 +38,9 @@ DROP SERVER server_1; CREATE SERVER server_1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'Remote', HOST 'Server.Example.Com', DATABASE 'test'); SELECT Host FROM mysql.servers WHERE Server_Name = 'server_1'; Host -server.example.com +Server.Example.Com ALTER SERVER server_1 OPTIONS(HOST 'Server.Example.Org'); SELECT Host FROM mysql.servers WHERE Server_Name = 'server_1'; Host -server.example.org +Server.Example.Org DROP SERVER server_1; diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index cfdf9ef9865..5903f59a9f0 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -2119,12 +2119,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 102 94.12 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where `test`.`t1`.`a1` > 'a' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 102 94.12 Using where; Using index Warnings: Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where `test`.`t1`.`a1` > 'a' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` create table t4 as select distinct a1, a2, b, c from t1; diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index 9ba98ba5b13..77995a53383 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -679,7 +679,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; @@ -690,7 +690,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; diff --git a/mysql-test/main/order_by_innodb.result b/mysql-test/main/order_by_innodb.result index 3ff1f92e94a..9cdf9800cee 100644 --- a/mysql-test/main/order_by_innodb.result +++ b/mysql-test/main/order_by_innodb.result @@ -49,6 +49,32 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # Using sort_union(key1,key2); Using where drop table t0, t1; # +# MDEV-18094: Query with order by limit picking index scan over filesort +# +create table t0 (a int); +INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0); +CREATE TABLE t1 ( +a int(11), +b int(11), +c int(11), +KEY a_c (a,c), +KEY a_b (a,b) +) ENGINE=InnoDB; +insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C; +# should use ref access +explain select a,b,c from t1 where a=1 and c=2 order by b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a_c,a_b a_c 10 const,const 20 Using where; Using filesort +# both should use range access +explain select a,b,c from t1 where a=1 and c=2 order by b limit 1000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a_c,a_b a_b 5 NULL 200 Using where +explain select a,b,c from t1 where a=1 and c=2 order by b limit 2000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a_c,a_b a_b 5 NULL 200 Using where +drop table t1,t0; +# Start of 10.2 tests +# # MDEV-14071: wrong results with orderby_uses_equalities=on # (duplicate of MDEV-13994) # @@ -121,3 +147,4 @@ i n 656 eight set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2,t3; +# End of 10.2 tests diff --git a/mysql-test/main/order_by_innodb.test b/mysql-test/main/order_by_innodb.test index 0debb777749..f4c738263ae 100644 --- a/mysql-test/main/order_by_innodb.test +++ b/mysql-test/main/order_by_innodb.test @@ -63,6 +63,32 @@ where key1<3 or key2<3; drop table t0, t1; --echo # +--echo # MDEV-18094: Query with order by limit picking index scan over filesort +--echo # + +create table t0 (a int); +INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0); + +CREATE TABLE t1 ( +a int(11), +b int(11), +c int(11), +KEY a_c (a,c), +KEY a_b (a,b) +) ENGINE=InnoDB; +insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C; + +--echo # should use ref access +explain select a,b,c from t1 where a=1 and c=2 order by b; + +--echo # both should use range access +explain select a,b,c from t1 where a=1 and c=2 order by b limit 1000; +explain select a,b,c from t1 where a=1 and c=2 order by b limit 2000; +drop table t1,t0; + +--echo # Start of 10.2 tests + +--echo # --echo # MDEV-14071: wrong results with orderby_uses_equalities=on --echo # (duplicate of MDEV-13994) --echo # @@ -108,3 +134,5 @@ eval $q2; set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2,t3; + +--echo # End of 10.2 tests diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index bc46a4fdd0b..4f3c36b7660 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -1795,7 +1795,7 @@ SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range state,capital state 71 NULL 12 Using index condition; Using where +1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result index ce90f522d6e..581f512768c 100644 --- a/mysql-test/main/range_vs_index_merge_innodb.result +++ b/mysql-test/main/range_vs_index_merge_innodb.result @@ -1796,7 +1796,7 @@ SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range state,capital state 71 NULL 12 Using index condition; Using where +1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index c4434c4c87a..abab4936af0 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1673,5 +1673,90 @@ drop table t1; set use_stat_tables= @save_use_stat_tables; set @@histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1) +# +set @@optimizer_use_condition_selectivity=2; +set names utf8; +CREATE DATABASE world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +); +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +); +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +); +CREATE INDEX Name ON City(Name); +CREATE INDEX CountryPopulation ON City(Country,Population); +CREATE INDEX CountryName ON City(Country,Name); +set @@optimizer_use_condition_selectivity=2; +EXPLAIN +SELECT * FROM City WHERE Country='FIN'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE City ref Country,CountryPopulation,CountryName CountryName 3 const 5 Using index condition +DROP DATABASE world; +use test; +CREATE TABLE t1 ( +a INT, +b INT NOT NULL, +c char(100), +KEY (b, c), +KEY (b, a, c) +) ENGINE=MyISAM +DEFAULT CHARSET = utf8; +INSERT INTO t1 VALUES +(1, 1, 1), +(2, 2, 2), +(3, 3, 3), +(4, 4, 4), +(5, 5, 5), +(6, 6, 6), +(7, 7, 7), +(8, 8, 8), +(9, 9, 9); +INSERT INTO t1 SELECT a + 10, b, c FROM t1; +INSERT INTO t1 SELECT a + 20, b, c FROM t1; +INSERT INTO t1 SELECT a + 40, b, c FROM t1; +INSERT INTO t1 SELECT a + 80, b, c FROM t1; +INSERT INTO t1 SELECT a + 160, b, c FROM t1; +INSERT INTO t1 SELECT a + 320, b, c FROM t1; +INSERT INTO t1 SELECT a + 640, b, c FROM t1; +INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; +EXPLAIN +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +a +2071 +2061 +2051 +2041 +2031 +2021 +2011 +2001 +1991 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +DROP TABLE t1; # End of 10.1 tests set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index b517b26bc1b..d3fafc2ae52 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1130,6 +1130,87 @@ drop table t1; set use_stat_tables= @save_use_stat_tables; set @@histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1) +--echo # + +set @@optimizer_use_condition_selectivity=2; + +set names utf8; + +CREATE DATABASE world; + +use world; + +--source include/world_schema.inc + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +CREATE INDEX Name ON City(Name); +CREATE INDEX CountryPopulation ON City(Country,Population); +CREATE INDEX CountryName ON City(Country,Name); + +--disable_query_log +--disable_result_log +--disable_warnings +ANALYZE TABLE City; +--enable_warnings +--enable_result_log +--enable_query_log + +set @@optimizer_use_condition_selectivity=2; + +EXPLAIN +SELECT * FROM City WHERE Country='FIN'; + +DROP DATABASE world; + +use test; + +CREATE TABLE t1 ( + a INT, + b INT NOT NULL, + c char(100), + KEY (b, c), + KEY (b, a, c) +) ENGINE=MyISAM +DEFAULT CHARSET = utf8; + +INSERT INTO t1 VALUES +(1, 1, 1), +(2, 2, 2), +(3, 3, 3), +(4, 4, 4), +(5, 5, 5), +(6, 6, 6), +(7, 7, 7), +(8, 8, 8), +(9, 9, 9); + +INSERT INTO t1 SELECT a + 10, b, c FROM t1; +INSERT INTO t1 SELECT a + 20, b, c FROM t1; +INSERT INTO t1 SELECT a + 40, b, c FROM t1; +INSERT INTO t1 SELECT a + 80, b, c FROM t1; +INSERT INTO t1 SELECT a + 160, b, c FROM t1; +INSERT INTO t1 SELECT a + 320, b, c FROM t1; +INSERT INTO t1 SELECT a + 640, b, c FROM t1; +INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; + +EXPLAIN +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +DROP TABLE t1; + --echo # End of 10.1 tests # diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 3cea6a3848b..5c740d71925 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1683,6 +1683,91 @@ drop table t1; set use_stat_tables= @save_use_stat_tables; set @@histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1) +# +set @@optimizer_use_condition_selectivity=2; +set names utf8; +CREATE DATABASE world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +); +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +); +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +); +CREATE INDEX Name ON City(Name); +CREATE INDEX CountryPopulation ON City(Country,Population); +CREATE INDEX CountryName ON City(Country,Name); +set @@optimizer_use_condition_selectivity=2; +EXPLAIN +SELECT * FROM City WHERE Country='FIN'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 7 Using index condition +DROP DATABASE world; +use test; +CREATE TABLE t1 ( +a INT, +b INT NOT NULL, +c char(100), +KEY (b, c), +KEY (b, a, c) +) ENGINE=MyISAM +DEFAULT CHARSET = utf8; +INSERT INTO t1 VALUES +(1, 1, 1), +(2, 2, 2), +(3, 3, 3), +(4, 4, 4), +(5, 5, 5), +(6, 6, 6), +(7, 7, 7), +(8, 8, 8), +(9, 9, 9); +INSERT INTO t1 SELECT a + 10, b, c FROM t1; +INSERT INTO t1 SELECT a + 20, b, c FROM t1; +INSERT INTO t1 SELECT a + 40, b, c FROM t1; +INSERT INTO t1 SELECT a + 80, b, c FROM t1; +INSERT INTO t1 SELECT a + 160, b, c FROM t1; +INSERT INTO t1 SELECT a + 320, b, c FROM t1; +INSERT INTO t1 SELECT a + 640, b, c FROM t1; +INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; +EXPLAIN +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +a +2071 +2061 +2051 +2041 +2031 +2021 +2011 +2001 +1991 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +DROP TABLE t1; # End of 10.1 tests set @@global.histogram_size=@save_histogram_size; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index 795af788310..fb161921d6a 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -797,9 +797,33 @@ col1 2004-01-01 2004-02-29 0000-10-31 -drop table t1; set @@sql_mode= @save_sql_mode; set use_stat_tables=@save_use_stat_tables; set @@histogram_size= @save_histogram_size; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +# +# MDEV-20589: Server still crashes in Field::set_warning_truncated_wrong_value +# +set names utf8; +create table t1 ( a varchar(255) character set utf8); +insert into t1 values (REPEAT('ӥ',255)), (REPEAT('ç',255)); +set use_stat_tables='preferably'; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +set @save_sql_mode= @@sql_mode; +set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; +update mysql.column_stats set min_value= REPEAT('ӥ',256) where db_name='test' and table_name='t1'; +Warnings: +Warning 1265 Data truncated for column 'min_value' at row 1 +set @@sql_mode= @save_sql_mode; +select length(a) from t1 where a=REPEAT('ӥ',255); +length(a) +510 +set names latin1; +set @@use_stat_tables=@save_use_stat_tables; +drop table t1; +# please keep this at the last set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/stat_tables.test b/mysql-test/main/stat_tables.test index 4c49df82ca6..4c21e21ea70 100644 --- a/mysql-test/main/stat_tables.test +++ b/mysql-test/main/stat_tables.test @@ -540,10 +540,33 @@ analyze table t1; update mysql.column_stats set min_value='2004-0-31123' where db_name='test' and table_name='t1'; select min_value from mysql.column_stats where db_name='test' and table_name='t1'; select * from t1; -drop table t1; - set @@sql_mode= @save_sql_mode; set use_stat_tables=@save_use_stat_tables; set @@histogram_size= @save_histogram_size; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; + +--echo # +--echo # MDEV-20589: Server still crashes in Field::set_warning_truncated_wrong_value +--echo # + +set names utf8; +create table t1 ( a varchar(255) character set utf8); +insert into t1 values (REPEAT('ӥ',255)), (REPEAT('ç',255)); + +set use_stat_tables='preferably'; +analyze table t1 persistent for all; + +set @save_sql_mode= @@sql_mode; +set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; +update mysql.column_stats set min_value= REPEAT('ӥ',256) where db_name='test' and table_name='t1'; +set @@sql_mode= @save_sql_mode; + +select length(a) from t1 where a=REPEAT('ӥ',255); + +set names latin1; +set @@use_stat_tables=@save_use_stat_tables; +drop table t1; + +--echo # please keep this at the last set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index 0363fec9adf..524539d1f25 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -824,11 +824,35 @@ col1 2004-01-01 2004-02-29 0000-10-31 -drop table t1; set @@sql_mode= @save_sql_mode; set use_stat_tables=@save_use_stat_tables; set @@histogram_size= @save_histogram_size; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +# +# MDEV-20589: Server still crashes in Field::set_warning_truncated_wrong_value +# +set names utf8; +create table t1 ( a varchar(255) character set utf8); +insert into t1 values (REPEAT('ӥ',255)), (REPEAT('ç',255)); +set use_stat_tables='preferably'; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +set @save_sql_mode= @@sql_mode; +set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; +update mysql.column_stats set min_value= REPEAT('ӥ',256) where db_name='test' and table_name='t1'; +Warnings: +Warning 1265 Data truncated for column 'min_value' at row 1 +set @@sql_mode= @save_sql_mode; +select length(a) from t1 where a=REPEAT('ӥ',255); +length(a) +510 +set names latin1; +set @@use_stat_tables=@save_use_stat_tables; +drop table t1; +# please keep this at the last set @@global.histogram_size=@save_histogram_size; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/main/system_mysql_db.result b/mysql-test/main/system_mysql_db.result index 2abcfb92ffa..300907d9d7a 100644 --- a/mysql-test/main/system_mysql_db.result +++ b/mysql-test/main/system_mysql_db.result @@ -190,14 +190,14 @@ show create table servers; Table Create Table servers CREATE TABLE `servers` ( `Server_name` char(64) NOT NULL DEFAULT '', - `Host` char(64) NOT NULL DEFAULT '', + `Host` varchar(2048) NOT NULL DEFAULT '', `Db` char(64) NOT NULL DEFAULT '', `Username` char(80) NOT NULL DEFAULT '', `Password` char(64) NOT NULL DEFAULT '', `Port` int(4) NOT NULL DEFAULT 0, `Socket` char(64) NOT NULL DEFAULT '', `Wrapper` char(64) NOT NULL DEFAULT '', - `Owner` char(64) NOT NULL DEFAULT '', + `Owner` varchar(512) NOT NULL DEFAULT '', PRIMARY KEY (`Server_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table' show create table proc; diff --git a/mysql-test/main/system_mysql_db_fix40123.result b/mysql-test/main/system_mysql_db_fix40123.result index 2abcfb92ffa..300907d9d7a 100644 --- a/mysql-test/main/system_mysql_db_fix40123.result +++ b/mysql-test/main/system_mysql_db_fix40123.result @@ -190,14 +190,14 @@ show create table servers; Table Create Table servers CREATE TABLE `servers` ( `Server_name` char(64) NOT NULL DEFAULT '', - `Host` char(64) NOT NULL DEFAULT '', + `Host` varchar(2048) NOT NULL DEFAULT '', `Db` char(64) NOT NULL DEFAULT '', `Username` char(80) NOT NULL DEFAULT '', `Password` char(64) NOT NULL DEFAULT '', `Port` int(4) NOT NULL DEFAULT 0, `Socket` char(64) NOT NULL DEFAULT '', `Wrapper` char(64) NOT NULL DEFAULT '', - `Owner` char(64) NOT NULL DEFAULT '', + `Owner` varchar(512) NOT NULL DEFAULT '', PRIMARY KEY (`Server_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table' show create table proc; diff --git a/mysql-test/suite/encryption/disabled.def b/mysql-test/suite/encryption/disabled.def index 746faf49873..d92d3495cb8 100644 --- a/mysql-test/suite/encryption/disabled.def +++ b/mysql-test/suite/encryption/disabled.def @@ -12,4 +12,3 @@ innodb_scrub : MDEV-8139 scrubbing does not work reliably innodb_scrub_background : MDEV-8139 scrubbing does not work reliably -innodb-redo-badkey : MDEV-13893/MDEV-12699 fix recovery of corrupted pages 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 f5452e0c8e8..a8e8cd33e8f 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -175,8 +175,8 @@ def mysql roles_mapping Host 1 '' NO char 60 180 NULL NULL NULL utf8 utf8_bin ch def mysql roles_mapping Role 3 '' NO char 80 240 NULL NULL NULL utf8 utf8_bin char(80) PRI select,insert,update,references NEVER NULL def mysql roles_mapping User 2 '' NO char 80 240 NULL NULL NULL utf8 utf8_bin char(80) PRI select,insert,update,references NEVER NULL def mysql servers Db 3 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) select,insert,update,references NEVER NULL -def mysql servers Host 2 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) select,insert,update,references NEVER NULL -def mysql servers Owner 9 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) select,insert,update,references NEVER NULL +def mysql servers Host 2 '' NO varchar 2048 6144 NULL NULL NULL utf8 utf8_general_ci varchar(2048) select,insert,update,references NEVER NULL +def mysql servers Owner 9 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) select,insert,update,references NEVER NULL def mysql servers Password 5 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) select,insert,update,references NEVER NULL def mysql servers Port 6 0 NO int NULL NULL 10 0 NULL NULL NULL int(4) select,insert,update,references NEVER NULL def mysql servers Server_name 1 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) PRI select,insert,update,references NEVER NULL @@ -518,14 +518,14 @@ NULL mysql proxies_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql roles_mapping Role char 80 240 utf8 utf8_bin char(80) 3.0000 mysql roles_mapping Admin_option enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql servers Server_name char 64 192 utf8 utf8_general_ci char(64) -3.0000 mysql servers Host char 64 192 utf8 utf8_general_ci char(64) +3.0000 mysql servers Host varchar 2048 6144 utf8 utf8_general_ci varchar(2048) 3.0000 mysql servers Db char 64 192 utf8 utf8_general_ci char(64) 3.0000 mysql servers Username char 80 240 utf8 utf8_general_ci char(80) 3.0000 mysql servers Password char 64 192 utf8 utf8_general_ci char(64) NULL mysql servers Port int NULL NULL NULL NULL int(4) 3.0000 mysql servers Socket char 64 192 utf8 utf8_general_ci char(64) 3.0000 mysql servers Wrapper char 64 192 utf8 utf8_general_ci char(64) -3.0000 mysql servers Owner char 64 192 utf8 utf8_general_ci char(64) +3.0000 mysql servers Owner varchar 512 1536 utf8 utf8_general_ci varchar(512) NULL mysql slow_log start_time timestamp NULL NULL NULL NULL timestamp(6) 1.0000 mysql slow_log user_host mediumtext 16777215 16777215 utf8 utf8_general_ci mediumtext NULL mysql slow_log query_time time NULL NULL NULL NULL time(6) diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result index 9f17724b356..8e8d632fa22 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result @@ -161,8 +161,8 @@ def mysql roles_mapping Host 1 '' NO char 60 180 NULL NULL NULL utf8 utf8_bin ch def mysql roles_mapping Role 3 '' NO char 80 240 NULL NULL NULL utf8 utf8_bin char(80) PRI NEVER NULL def mysql roles_mapping User 2 '' NO char 80 240 NULL NULL NULL utf8 utf8_bin char(80) PRI NEVER NULL def mysql servers Db 3 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) NEVER NULL -def mysql servers Host 2 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) NEVER NULL -def mysql servers Owner 9 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) NEVER NULL +def mysql servers Host 2 '' NO varchar 2048 6144 NULL NULL NULL utf8 utf8_general_ci varchar(2048) NEVER NULL +def mysql servers Owner 9 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) NEVER NULL def mysql servers Password 5 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) NEVER NULL def mysql servers Port 6 0 NO int NULL NULL 10 0 NULL NULL NULL int(4) NEVER NULL def mysql servers Server_name 1 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) PRI NEVER NULL @@ -501,14 +501,14 @@ NULL mysql proxies_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql roles_mapping Role char 80 240 utf8 utf8_bin char(80) 3.0000 mysql roles_mapping Admin_option enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql servers Server_name char 64 192 utf8 utf8_general_ci char(64) -3.0000 mysql servers Host char 64 192 utf8 utf8_general_ci char(64) +3.0000 mysql servers Host varchar 2048 6144 utf8 utf8_general_ci varchar(2048) 3.0000 mysql servers Db char 64 192 utf8 utf8_general_ci char(64) 3.0000 mysql servers Username char 80 240 utf8 utf8_general_ci char(80) 3.0000 mysql servers Password char 64 192 utf8 utf8_general_ci char(64) NULL mysql servers Port int NULL NULL NULL NULL int(4) 3.0000 mysql servers Socket char 64 192 utf8 utf8_general_ci char(64) 3.0000 mysql servers Wrapper char 64 192 utf8 utf8_general_ci char(64) -3.0000 mysql servers Owner char 64 192 utf8 utf8_general_ci char(64) +3.0000 mysql servers Owner varchar 512 1536 utf8 utf8_general_ci varchar(512) NULL mysql slow_log start_time timestamp NULL NULL NULL NULL timestamp(6) 1.0000 mysql slow_log user_host mediumtext 16777215 16777215 utf8 utf8_general_ci mediumtext NULL mysql slow_log query_time time NULL NULL NULL NULL time(6) diff --git a/mysql-test/suite/funcs_1/r/is_tables_mysql.result b/mysql-test/suite/funcs_1/r/is_tables_mysql.result index 2ceed585699..ea565e14b7a 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_tables_mysql.result @@ -516,7 +516,7 @@ TABLE_NAME servers TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA VERSION 10 -ROW_FORMAT Fixed +ROW_FORMAT DYNAMIC_OR_PAGE TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# diff --git a/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result index 77fa6ddae1e..5d6c063fbd8 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result @@ -516,7 +516,7 @@ TABLE_NAME servers TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA VERSION 10 -ROW_FORMAT Fixed +ROW_FORMAT DYNAMIC_OR_PAGE TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# @@ -1305,7 +1305,7 @@ TABLE_NAME servers TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA VERSION 10 -ROW_FORMAT Fixed +ROW_FORMAT DYNAMIC_OR_PAGE TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index 1e2833042fc..61820f6e7c1 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -10,7 +10,6 @@ # ############################################################################## -MW-286 : MDEV-19992 Galera test failure on MW-286 MW-329 : MDEV-19962 Galera test failure on MW-329 MW-388: MDEV-19803 Long semaphore wait error on galera.MW-388 galera_account_management : MariaDB 10.0 does not support ALTER USER @@ -26,7 +25,7 @@ galera_ist_mariabackup : MDEV-18829 test leaves port open galera_ist_progress : MDEV-15236 fails when trying to read transfer status galera_migrate : MariaDB does not support START SLAVE USER galera_ssl_upgrade : MDEV-19950 Galera test failure on galera_ssl_upgrade -galera_var_node_address : MDEV-20485 Galera test failure on galera.galera_var_node_address +galera_var_node_address : MDEV-20485 Galera test failure galera_wan : MDEV-17259 Test failure on galera.galera_wan partition : MDEV-19958 Galera test failure on galera.partition -query_cache: MDEV-15805 Test failure on galera.query_cache
\ No newline at end of file +query_cache: MDEV-15805 Test failure on galera.query_cache diff --git a/mysql-test/suite/galera/r/MW-336.result b/mysql-test/suite/galera/r/MW-336.result index ed2f0755f0d..b91e73aaada 100644 --- a/mysql-test/suite/galera/r/MW-336.result +++ b/mysql-test/suite/galera/r/MW-336.result @@ -6,10 +6,6 @@ SET GLOBAL wsrep_slave_threads = 1; connection node_2; INSERT INTO t1 VALUES (1); connection node_1; -SET SESSION wsrep_sync_wait=15; -SELECT COUNT(*) FROM t1; -COUNT(*) -1 SET GLOBAL wsrep_slave_threads = 10; # Set slave threads to 10 step 2 SET GLOBAL wsrep_slave_threads = 20; @@ -43,9 +39,11 @@ INSERT INTO t1 VALUES (17); INSERT INTO t1 VALUES (18); INSERT INTO t1 VALUES (19); INSERT INTO t1 VALUES (20); +INSERT INTO t1 VALUES (21); +INSERT INTO t1 VALUES (22); connection node_1; SELECT COUNT(*) FROM t1; COUNT(*) -21 +23 SET GLOBAL wsrep_slave_threads = 1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_shutdown_nonprim.result b/mysql-test/suite/galera/r/galera_shutdown_nonprim.result new file mode 100644 index 00000000000..5353543e395 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_shutdown_nonprim.result @@ -0,0 +1,9 @@ +connection node_1; +connection node_2; +connection node_1; +SET GLOBAL wsrep_provider_options = 'pc.weight=2'; +connection node_2; +SET GLOBAL wsrep_provider_options = 'gmcast.isolate = 1'; +SET SESSION wsrep_sync_wait = 0; +connection node_1; +SET GLOBAL wsrep_provider_options = 'pc.weight = 1'; diff --git a/mysql-test/suite/galera/t/MW-336.test b/mysql-test/suite/galera/t/MW-336.test index b6df78ac7a6..0e92094da6c 100644 --- a/mysql-test/suite/galera/t/MW-336.test +++ b/mysql-test/suite/galera/t/MW-336.test @@ -13,7 +13,7 @@ SET GLOBAL wsrep_slave_threads = 10; # ensure that the threads have actually started running --echo # Set slave threads to 10 step 1 ---let $wait_condition = SELECT COUNT(*) = 10 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; +--let $wait_condition = SELECT VARIABLE_VALUE = 10 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_applier_thread_count'; --let $wait_condition_on_error_output = SELECT COUNT(*), 10 as EXPECTED_VALUE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; show processlist --source include/wait_condition_with_debug.inc @@ -23,20 +23,18 @@ SET GLOBAL wsrep_slave_threads = 1; INSERT INTO t1 VALUES (1); --connection node_1 -SET SESSION wsrep_sync_wait=15; -SELECT COUNT(*) FROM t1; - SET GLOBAL wsrep_slave_threads = 10; +# Note that above insert could be handled by one of the slave threads --echo # Set slave threads to 10 step 2 ---let $wait_condition = SELECT COUNT(*) = 10 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; ---let $wait_condition_on_error_output = SELECT COUNT(*), 10 as EXPECTED_VALUE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; show processlist +--let $wait_condition = SELECT VARIABLE_VALUE >= 9 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_applier_thread_count'; +--let $wait_condition_on_error_output = SELECT COUNT(*), 9 as EXPECTED_VALUE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; show processlist --source include/wait_condition_with_debug.inc SET GLOBAL wsrep_slave_threads = 20; --echo # Set slave threads to 20 ---let $wait_condition = SELECT COUNT(*) = 20 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; +--let $wait_condition = SELECT VARIABLE_VALUE = 20 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_applier_thread_count'; --let $wait_condition_on_error_output = SELECT COUNT(*), 20 as EXPECTED_VALUE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; show processlist --source include/wait_condition_with_debug.inc @@ -58,7 +56,7 @@ INSERT INTO t1 VALUES (10); SET GLOBAL wsrep_slave_threads = 10; SELECT COUNT(*) FROM t1; --echo # Set slave threads to 10 step 3 ---let $wait_condition = SELECT COUNT(*) = 10 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; +--let $wait_condition = SELECT VARIABLE_VALUE = 10 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_applier_thread_count'; --let $wait_condition_on_error_output = SELECT COUNT(*), 10 as EXPECTED_VALUE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; show processlist --source include/wait_condition_with_debug.inc @@ -73,6 +71,8 @@ INSERT INTO t1 VALUES (17); INSERT INTO t1 VALUES (18); INSERT INTO t1 VALUES (19); INSERT INTO t1 VALUES (20); +INSERT INTO t1 VALUES (21); +INSERT INTO t1 VALUES (22); --connection node_1 SELECT COUNT(*) FROM t1; diff --git a/mysql-test/suite/galera/t/galera_shutdown_nonprim.test b/mysql-test/suite/galera/t/galera_shutdown_nonprim.test new file mode 100644 index 00000000000..cf7018cd751 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_shutdown_nonprim.test @@ -0,0 +1,36 @@ +# +# Check that server can be shut down in non-primary configuration. +# + +--source include/galera_cluster.inc +--source include/have_innodb.inc + +--let $node_1 = node_1 +--let $node_2 = node_2 +--source include/auto_increment_offset_save.inc + +--connection node_1 +# Set higher weight for node_1 to keep it in primary +# while node_2 is isolated. +SET GLOBAL wsrep_provider_options = 'pc.weight=2'; + +--connection node_2 +# Isolate node_2 from the group and wait until wsrep_ready becomes OFF. +SET GLOBAL wsrep_provider_options = 'gmcast.isolate = 1'; +SET SESSION wsrep_sync_wait = 0; +--let $wait_condition = SELECT VARIABLE_VALUE = 'OFF' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_ready' +--source include/wait_condition.inc + +# Verify that graceful shutdown succeeds. +--source include/shutdown_mysqld.inc +--source include/start_mysqld.inc + +--let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +--source include/wait_condition.inc + +--connection node_1 +--source include/wait_condition.inc + +# Restore original settings. +SET GLOBAL wsrep_provider_options = 'pc.weight = 1'; +--source include/auto_increment_offset_restore.inc diff --git a/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result b/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result index 552805110b1..1bbc577ed93 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result @@ -204,5 +204,32 @@ connection truncate; disconnect truncate; connection default; DROP TABLE t1, t2; +# +# MDEV-16222 Assertion `0' failed in row_purge_remove_sec_if_poss_leaf +# on table with virtual columns and indexes +# +set @saved_dbug= @@global.debug_dbug; +set global debug_dbug= "+d,ib_purge_virtual_mdev_16222_1,ib_purge_virtual_mdev_16222_2"; +create table t1 ( +pk serial, vb tinyblob as (b) virtual, b tinyblob, +primary key(pk), index (vb(64))) +engine innodb; +insert ignore into t1 (b) values ('foo'); +select * into outfile 'load.data' from t1; +load data infile 'load.data' replace into table t1; +set debug_sync= "now WAIT_FOR latch_released"; +set global debug_dbug= "-d,ib_purge_virtual_mdev_16222_1"; +drop table t1; +set debug_sync= "now SIGNAL drop_started WAIT_FOR got_no_such_table"; +create table t1 ( +pk serial, vb tinyblob as (b) virtual, b tinyblob, +primary key(pk), index (vb(64))) +engine innodb; +insert ignore into t1 (b) values ('foo'); +select * into outfile 'load.data' from t1; +load data infile 'load.data' replace into table t1; +set debug_sync= "now WAIT_FOR got_no_such_table"; +set global debug_dbug= @saved_dbug; +drop table t1; set debug_sync=reset; SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; diff --git a/mysql-test/suite/innodb/t/purge_secondary_mdev-16222.opt b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.opt index a1207721427..a1207721427 100644 --- a/mysql-test/suite/innodb/t/purge_secondary_mdev-16222.opt +++ b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.opt diff --git a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test index 8568c66eccc..04ab8a88488 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test @@ -259,6 +259,54 @@ disconnect truncate; connection default; DROP TABLE t1, t2; +--echo # +--echo # MDEV-16222 Assertion `0' failed in row_purge_remove_sec_if_poss_leaf +--echo # on table with virtual columns and indexes +--echo # + +--let $datadir= `select @@datadir` +set @saved_dbug= @@global.debug_dbug; +set global debug_dbug= "+d,ib_purge_virtual_mdev_16222_1,ib_purge_virtual_mdev_16222_2"; + +create table t1 ( + pk serial, vb tinyblob as (b) virtual, b tinyblob, + primary key(pk), index (vb(64))) +engine innodb; + +insert ignore into t1 (b) values ('foo'); + +select * into outfile 'load.data' from t1; +load data infile 'load.data' replace into table t1; + +set debug_sync= "now WAIT_FOR latch_released"; +set global debug_dbug= "-d,ib_purge_virtual_mdev_16222_1"; +drop table t1; +--remove_file $datadir/test/load.data + +set debug_sync= "now SIGNAL drop_started WAIT_FOR got_no_such_table"; + +create table t1 ( + pk serial, vb tinyblob as (b) virtual, b tinyblob, + primary key(pk), index (vb(64))) +engine innodb; + +insert ignore into t1 (b) values ('foo'); + +select * into outfile 'load.data' from t1; +load data infile 'load.data' replace into table t1; + +set debug_sync= "now WAIT_FOR got_no_such_table"; + +# FIXME: Race condition here: +# 1. purge thread goes into sending got_no_such_table +# 2. test thread finishes debug_sync= "RESET" below +# 3. purge thread sends got_no_such_table +set global debug_dbug= @saved_dbug; + +# cleanup +drop table t1; +--remove_file $datadir/test/load.data + --source include/wait_until_count_sessions.inc set debug_sync=reset; SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; diff --git a/mysql-test/suite/innodb/r/innodb_stats_persistent.result b/mysql-test/suite/innodb/r/innodb_stats_persistent.result index f4de4b6b82e..41893bf47c1 100644 --- a/mysql-test/suite/innodb/r/innodb_stats_persistent.result +++ b/mysql-test/suite/innodb/r/innodb_stats_persistent.result @@ -29,7 +29,7 @@ connection default; # DELETE must not affect statistics before COMMIT. EXPLAIN SELECT * FROM t1 WHERE val=4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref val val 4 const 16 Using index +1 SIMPLE t1 ref val val 4 const 1 Using index connection con1; COUNT(*) 0 diff --git a/mysql-test/suite/innodb/r/purge_secondary_mdev-16222.result b/mysql-test/suite/innodb/r/purge_secondary_mdev-16222.result deleted file mode 100644 index 48b948c34aa..00000000000 --- a/mysql-test/suite/innodb/r/purge_secondary_mdev-16222.result +++ /dev/null @@ -1,30 +0,0 @@ -# -# MDEV-16222 Assertion `0' failed in row_purge_remove_sec_if_poss_leaf on table with virtual columns and indexes -# -set @saved_frequency= @@global.innodb_purge_rseg_truncate_frequency; -set global innodb_purge_rseg_truncate_frequency= 1; -set @saved_dbug= @@global.debug_dbug; -set global debug_dbug= "+d,ib_purge_virtual_mdev_16222_1,ib_purge_virtual_mdev_16222_2"; -create table t1 ( -pk serial, vb tinyblob as (b) virtual, b tinyblob, -primary key(pk), index (vb(64))) -engine innodb; -insert ignore into t1 (b) values ('foo'); -select * into outfile 'load.data' from t1; -load data infile 'load.data' replace into table t1; -set debug_sync= "now WAIT_FOR latch_released"; -set global debug_dbug= "-d,ib_purge_virtual_mdev_16222_1"; -drop table t1; -set debug_sync= "now SIGNAL drop_started WAIT_FOR got_no_such_table"; -create table t1 ( -pk serial, vb tinyblob as (b) virtual, b tinyblob, -primary key(pk), index (vb(64))) -engine innodb; -insert ignore into t1 (b) values ('foo'); -select * into outfile 'load.data' from t1; -load data infile 'load.data' replace into table t1; -set debug_sync= "now WAIT_FOR got_no_such_table"; -set global debug_dbug= @saved_dbug; -drop table t1; -set global innodb_purge_rseg_truncate_frequency= @saved_frequency; -set debug_sync= "RESET"; diff --git a/mysql-test/suite/innodb/t/purge_secondary_mdev-16222.test b/mysql-test/suite/innodb/t/purge_secondary_mdev-16222.test deleted file mode 100644 index 475eb92ed12..00000000000 --- a/mysql-test/suite/innodb/t/purge_secondary_mdev-16222.test +++ /dev/null @@ -1,53 +0,0 @@ ---source include/have_debug.inc ---source include/have_innodb.inc - ---echo # ---echo # MDEV-16222 Assertion `0' failed in row_purge_remove_sec_if_poss_leaf on table with virtual columns and indexes ---echo # - ---let $datadir= `select @@datadir` -set @saved_frequency= @@global.innodb_purge_rseg_truncate_frequency; -set global innodb_purge_rseg_truncate_frequency= 1; -set @saved_dbug= @@global.debug_dbug; -set global debug_dbug= "+d,ib_purge_virtual_mdev_16222_1,ib_purge_virtual_mdev_16222_2"; - -create table t1 ( - pk serial, vb tinyblob as (b) virtual, b tinyblob, - primary key(pk), index (vb(64))) -engine innodb; - -insert ignore into t1 (b) values ('foo'); - -select * into outfile 'load.data' from t1; -load data infile 'load.data' replace into table t1; - -set debug_sync= "now WAIT_FOR latch_released"; -set global debug_dbug= "-d,ib_purge_virtual_mdev_16222_1"; -drop table t1; ---remove_file $datadir/test/load.data - -set debug_sync= "now SIGNAL drop_started WAIT_FOR got_no_such_table"; - -create table t1 ( - pk serial, vb tinyblob as (b) virtual, b tinyblob, - primary key(pk), index (vb(64))) -engine innodb; - -insert ignore into t1 (b) values ('foo'); - -select * into outfile 'load.data' from t1; -load data infile 'load.data' replace into table t1; - -set debug_sync= "now WAIT_FOR got_no_such_table"; -# FIXME: Racing condition here: -# 1. purge thread goes into sending got_no_such_table -# 2. test thread finishes debug_sync= "RESET" below -# 3. purge thread sends got_no_such_table -set global debug_dbug= @saved_dbug; - -# cleanup -drop table t1; ---remove_file $datadir/test/load.data - -set global innodb_purge_rseg_truncate_frequency= @saved_frequency; -set debug_sync= "RESET"; diff --git a/mysql-test/suite/innodb_fts/r/concurrent_insert.result b/mysql-test/suite/innodb_fts/r/concurrent_insert.result index 5644075038a..9871d43119a 100644 --- a/mysql-test/suite/innodb_fts/r/concurrent_insert.result +++ b/mysql-test/suite/innodb_fts/r/concurrent_insert.result @@ -9,3 +9,26 @@ disconnect dml; connection default; SET DEBUG_SYNC = 'RESET'; DROP TABLE t1; +# +# MDEV-19529 InnoDB hang on DROP FULLTEXT INDEX +# +CREATE TABLE t1(f1 CHAR(100), FULLTEXT(f1))ENGINE=InnoDB; +INSERT INTO t1 VALUES('test'); +CREATE TABLE t2 (f1 char(100), FULLTEXT idx1(f1))ENGINE=InnoDB; +INSERT INTO t2 VALUES('mariadb'); +connection default; +SET GLOBAL debug_dbug ='+d,fts_instrument_sync_request,ib_optimize_wq_hang'; +SET DEBUG_SYNC= 'fts_instrument_sync_request + SIGNAL drop_index_start WAIT_FOR sync_op'; +INSERT INTO t1 VALUES('Keyword'); +connect con1,localhost,root,,,; +SET DEBUG_SYNC='now WAIT_FOR drop_index_start'; +SET DEBUG_SYNC= 'norebuild_fts_drop SIGNAL sync_op WAIT_FOR fts_drop_index'; +ALTER TABLE t2 drop index idx1; +connection default; +set DEBUG_SYNC= 'now SIGNAL fts_drop_index'; +connection con1; +SET global DEBUG_DBUG=RESET; +drop table t1, t2; +connection default; +set DEBUG_SYNC=RESET; diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result index bc9548b7d8e..0ebdf46c9fe 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result @@ -700,15 +700,21 @@ count(*) DROP TABLE t1; "----------Test27---------" CREATE TABLE t1 (id INT,char_column VARCHAR(60)); +CREATE TABLE t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, a TEXT)ENGINE=InnoDB; +ALTER TABLE t2 DROP a; SET @@autocommit=0; CREATE FULLTEXT INDEX i ON t1 (char_column); INSERT INTO t1 values (1,'aaa'); "restart server..." -# Restart the server ---source include/restart_mysqld.inc -DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb') +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`FTS_DOC_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb'); SET @@autocommit=1; -DROP TABLE t1; +DROP TABLE t1, t2; "----------Test28---------" drop table if exists `fts_test`; Warnings: diff --git a/mysql-test/suite/innodb_fts/t/concurrent_insert.test b/mysql-test/suite/innodb_fts/t/concurrent_insert.test index e5d61cd8b05..77097d44dc5 100644 --- a/mysql-test/suite/innodb_fts/t/concurrent_insert.test +++ b/mysql-test/suite/innodb_fts/t/concurrent_insert.test @@ -18,3 +18,34 @@ reap; SET DEBUG_SYNC = 'RESET'; DROP TABLE t1; + +--echo # +--echo # MDEV-19529 InnoDB hang on DROP FULLTEXT INDEX +--echo # + +CREATE TABLE t1(f1 CHAR(100), FULLTEXT(f1))ENGINE=InnoDB; +INSERT INTO t1 VALUES('test'); +CREATE TABLE t2 (f1 char(100), FULLTEXT idx1(f1))ENGINE=InnoDB; +INSERT INTO t2 VALUES('mariadb'); + +connection default; +SET GLOBAL debug_dbug ='+d,fts_instrument_sync_request,ib_optimize_wq_hang'; +SET DEBUG_SYNC= 'fts_instrument_sync_request + SIGNAL drop_index_start WAIT_FOR sync_op'; +send INSERT INTO t1 VALUES('Keyword'); + +connect(con1,localhost,root,,,); +SET DEBUG_SYNC='now WAIT_FOR drop_index_start'; +SET DEBUG_SYNC= 'norebuild_fts_drop SIGNAL sync_op WAIT_FOR fts_drop_index'; +send ALTER TABLE t2 drop index idx1; + +connection default; +reap; +set DEBUG_SYNC= 'now SIGNAL fts_drop_index'; + +connection con1; +reap; +SET global DEBUG_DBUG=RESET; +drop table t1, t2; +connection default; +set DEBUG_SYNC=RESET; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test index e3d350b34a3..9de76a3bbce 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test @@ -670,15 +670,18 @@ DROP TABLE t1; --echo "----------Test27---------" #27 Crash after server restart CREATE TABLE t1 (id INT,char_column VARCHAR(60)); +CREATE TABLE t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, a TEXT)ENGINE=InnoDB; +ALTER TABLE t2 DROP a; SET @@autocommit=0; CREATE FULLTEXT INDEX i ON t1 (char_column); INSERT INTO t1 values (1,'aaa'); -echo "restart server..." +echo "restart server..."; # Restart the server --source include/restart_mysqld.inc +SHOW CREATE TABLE t2; DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb'); SET @@autocommit=1; -DROP TABLE t1; +DROP TABLE t1, t2; --echo "----------Test28---------" drop table if exists `fts_test`; diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 8fc93e861a7..b3b8efc745a 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -167,7 +167,7 @@ WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where DROP TABLE t1; # diff --git a/mysql-test/suite/mariabackup/extra_lsndir_stream.result b/mysql-test/suite/mariabackup/extra_lsndir_stream.result new file mode 100644 index 00000000000..a25c45a13d1 --- /dev/null +++ b/mysql-test/suite/mariabackup/extra_lsndir_stream.result @@ -0,0 +1,2 @@ +xtrabackup_checkpoints +xtrabackup_info diff --git a/mysql-test/suite/mariabackup/extra_lsndir_stream.test b/mysql-test/suite/mariabackup/extra_lsndir_stream.test new file mode 100644 index 00000000000..97023cb179d --- /dev/null +++ b/mysql-test/suite/mariabackup/extra_lsndir_stream.test @@ -0,0 +1,7 @@ +let $extra_lsndir=$MYSQLTEST_VARDIR/tmp/extra_lsndir; +mkdir $extra_lsndir; +--disable_result_log +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --stream=xbstream --extra-lsndir=$extra_lsndir > /dev/null; +--enable_result_log +list_files $extra_lsndir; +rmdir $extra_lsndir; diff --git a/mysql-test/suite/mariabackup/mdev-18438.result b/mysql-test/suite/mariabackup/mdev-18438.result new file mode 100644 index 00000000000..ab3b81bd484 --- /dev/null +++ b/mysql-test/suite/mariabackup/mdev-18438.result @@ -0,0 +1 @@ +stream.xb diff --git a/mysql-test/suite/mariabackup/mdev-18438.test b/mysql-test/suite/mariabackup/mdev-18438.test new file mode 100644 index 00000000000..a6ec45476ff --- /dev/null +++ b/mysql-test/suite/mariabackup/mdev-18438.test @@ -0,0 +1,11 @@ +let $basedir=$MYSQLTEST_VARDIR/tmp/mdev-18438; +mkdir $basedir; +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --extra-lsndir=$basedir/extra_lsndir --stream=xbstream > $basedir/stream.xb; +mkdir $basedir/backup; +rmdir $basedir/extra_lsndir; +--disable_result_log +exec $XBSTREAM -x -C $basedir/backup < $basedir/stream.xb; +--enable_result_log +rmdir $basedir/backup; +list_files $basedir; +rmdir $basedir; |