diff options
160 files changed, 2757 insertions, 1356 deletions
diff --git a/client/mysqlslap.c b/client/mysqlslap.c index 75cd7f14846..15aae234a94 100644 --- a/client/mysqlslap.c +++ b/client/mysqlslap.c @@ -1177,9 +1177,6 @@ get_options(int *argc,char ***argv) if (debug_check_flag) my_end_arg= MY_CHECK_ERROR; - if (!user) - user= (char *)"root"; - /* If something is created and --no-drop is not specified, we drop the schema. diff --git a/extra/mariabackup/xtrabackup.cc b/extra/mariabackup/xtrabackup.cc index 3fef49e787a..217976e637c 100644 --- a/extra/mariabackup/xtrabackup.cc +++ b/extra/mariabackup/xtrabackup.cc @@ -5619,6 +5619,10 @@ static bool xtrabackup_prepare_func(char** argv) if (xtrabackup_incremental) inc_dir_tables_hash.create(1000); + msg("open files limit requested %u, set to %lu", + (uint) xb_open_files_limit, + xb_set_max_open_files(xb_open_files_limit)); + /* Fix DDL for prepare. Process .del,.ren, and .new files. The order in which files are processed, is important (see MDEV-18185, MDEV-18201) diff --git a/include/my_atomic_wrapper.h b/include/my_atomic_wrapper.h index c0e18ea7c91..c574fba4a8e 100644 --- a/include/my_atomic_wrapper.h +++ b/include/my_atomic_wrapper.h @@ -51,6 +51,10 @@ public: { return m.fetch_sub(i, o); } Type fetch_xor(const Type i, std::memory_order o= std::memory_order_relaxed) { return m.fetch_xor(i, o); } + Type fetch_and(const Type i, std::memory_order o= std::memory_order_relaxed) + { return m.fetch_and(i, o); } + Type fetch_or(const Type i, std::memory_order o= std::memory_order_relaxed) + { return m.fetch_or(i, o); } bool compare_exchange_strong(Type& i1, const Type i2, std::memory_order o1= std::memory_order_relaxed, std::memory_order o2= std::memory_order_relaxed) diff --git a/include/mysql/plugin_ftparser.h b/include/mysql/plugin_ftparser.h index 99bb6b24f3f..8db8712926f 100644 --- a/include/mysql/plugin_ftparser.h +++ b/include/mysql/plugin_ftparser.h @@ -158,7 +158,9 @@ typedef struct st_mysql_ftparser_boolean_info the word to MySQL full-text index. When parsing a search query, this function will add the new word to the list of words to search for. The boolean_info argument can be NULL for all cases except - when mode is MYSQL_FTPARSER_FULL_BOOLEAN_INFO. + when mode is MYSQL_FTPARSER_FULL_BOOLEAN_INFO. A plugin can replace this + callback to post-process every parsed word before passing it to the original + mysql_add_word function. ftparser_state: A generic pointer. The plugin can set it to point to information to be used internally for its own purposes. diff --git a/mysql-test/include/ctype_utf8mb4.inc b/mysql-test/include/ctype_utf8mb4.inc index 10d4f99efba..d24ee2fafeb 100644 --- a/mysql-test/include/ctype_utf8mb4.inc +++ b/mysql-test/include/ctype_utf8mb4.inc @@ -1587,7 +1587,7 @@ drop table t1; --echo # --echo # Check strnxfrm() with odd length --echo # -set max_sort_length=9; +set max_sort_length=65; select @@max_sort_length; eval create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine $engine; insert into t1 values ('a'),('b'),('c'); diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index 3de7d902335..a71a4c1edcf 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -4397,7 +4397,7 @@ with t as (select * from t1 where a <=3) select * from t; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci select * from v1; a 3 @@ -4413,7 +4413,7 @@ order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3 order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3 order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci select * from v1; a 4 @@ -4429,7 +4429,7 @@ order by a desc limit 3 ) (select a from t1 where a=4 union select a from t where a=7 order by a desc); show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3) order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) union (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3) order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4469,7 +4469,7 @@ order by 1 desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4484,7 +4484,7 @@ order by 1 desc limit 3 ) select a from t1 where a=1 union select a from t where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4496,7 +4496,7 @@ s as (select * from t1 where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3), s as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 diff --git a/mysql-test/main/cte_cycle.result b/mysql-test/main/cte_cycle.result index e66d090b3c7..e7e0cc5f801 100644 --- a/mysql-test/main/cte_cycle.result +++ b/mysql-test/main/cte_cycle.result @@ -67,7 +67,7 @@ FROM t1, cte WHERE t1.from_ = cte.to_ select * from cte; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive cte(`depth`,`from_`,`to_`) as (select 0 AS `depth`,1 AS `from_`,1 AS `to_` union select `cte`.`depth` + 1 AS `depth+1`,`t1`.`from_` AS `from_`,`t1`.`to_` AS `to_` from (`t1` join `cte`) where `t1`.`from_` = `cte`.`to_`) CYCLE `from_`,`to_` RESTRICT select `cte`.`depth` AS `depth`,`cte`.`from_` AS `from_`,`cte`.`to_` AS `to_` from `cte` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive cte(`depth`,`from_`,`to_`) as (select 0 AS `depth`,1 AS `from_`,1 AS `to_` union select `cte`.`depth` + 1 AS `depth+1`,`test`.`t1`.`from_` AS `from_`,`test`.`t1`.`to_` AS `to_` from (`test`.`t1` join `cte`) where `test`.`t1`.`from_` = `cte`.`to_`) CYCLE `from_`,`to_` RESTRICT select `cte`.`depth` AS `depth`,`cte`.`from_` AS `from_`,`cte`.`to_` AS `to_` from `cte` latin1 latin1_swedish_ci select * from v1; depth from_ to_ 0 1 1 diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index 6ff33e6da29..e773caa7614 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -571,7 +571,7 @@ with t as (select a from t1 where b >= 'c') select * from t2,t where t2.c=t.a; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`b` >= 'c')select `t2`.`c` AS `c`,`t`.`a` AS `a` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a` latin1 latin1_swedish_ci select * from v1; c a 4 4 @@ -588,7 +588,7 @@ with t as (select a, count(*) from t1 where b >= 'c' group by a) select * from t2,t where t2.c=t.a; show create view v2; View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` where `t1`.`b` >= 'c' group by `t1`.`a`)select `t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `test`.`t1`.`a` AS `a`,count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` >= 'c' group by `test`.`t1`.`a`)select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a` latin1 latin1_swedish_ci select * from v2; c a count(*) 4 4 2 @@ -606,7 +606,7 @@ with t(c) as (select a from t1 where b >= 'c') select * from t r1 where r1.c=4; show create view v3; View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(`c`) as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(`c`) as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci select * from v3; c 4 @@ -1349,7 +1349,7 @@ r.r_regionkey in select r_regionkey from t where r_name <> "ASIA"); show create view v; View Create View character_set_client collation_connection -v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`nation` `n` join `region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `region`.`r_regionkey` AS `r_regionkey`,`region`.`r_name` AS `r_name` from `region` where `region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA') latin1 latin1_swedish_ci +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`test`.`nation` `n` join `test`.`region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `test`.`region`.`r_regionkey` AS `r_regionkey`,`test`.`region`.`r_name` AS `r_name` from `test`.`region` where `test`.`region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA') latin1 latin1_swedish_ci select * from v; n_nationkey n_name n_regionkey r_regionkey r_name 0 ALGERIA 0 0 AFRICA @@ -1690,6 +1690,41 @@ ERROR 3D000: No database selected DROP TABLE test.t; connection default; disconnect con1; +# +# MDEV-22781: create view with CTE without default database +# +drop database test; +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3),(7),(1); +create view db1.v1 as with t as (select * from db1.t1) select * from t; +show create view db1.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS with t as (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci +select * from db1.v1; +a +3 +7 +1 +drop view db1.v1; +prepare stmt from " +create view db1.v1 as with t as (select * from db1.t1) select * from t; +"; +execute stmt; +deallocate prepare stmt; +show create view db1.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS with t as (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci +select * from db1.v1; +a +3 +7 +1 +drop view db1.v1; +drop table db1.t1; +drop database db1; +create database test; +use test; # End of 10.2 tests # # MDEV-21673: several references to CTE that uses diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index 807e2e08bac..870f898f239 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1213,6 +1213,35 @@ DROP TABLE test.t; --connection default --disconnect con1 +--echo # +--echo # MDEV-22781: create view with CTE without default database +--echo # + +drop database test; +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3),(7),(1); + +create view db1.v1 as with t as (select * from db1.t1) select * from t; +show create view db1.v1; +select * from db1.v1; +drop view db1.v1; + +prepare stmt from " +create view db1.v1 as with t as (select * from db1.t1) select * from t; +"; +execute stmt; +deallocate prepare stmt; +show create view db1.v1; +select * from db1.v1; +drop view db1.v1; + +drop table db1.t1; +drop database db1; + +create database test; +use test; + --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/cte_nonrecursive_not_embedded.result b/mysql-test/main/cte_nonrecursive_not_embedded.result new file mode 100644 index 00000000000..c96a1ec2849 --- /dev/null +++ b/mysql-test/main/cte_nonrecursive_not_embedded.result @@ -0,0 +1,48 @@ +# +# MDEV-20751: query using many CTEs with grant_tables enabled +# +connection default; +CREATE DATABASE db; +USE db; +CREATE TABLE t1 (a int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (3), (7), (1); +CREATE TABLE t2 (a int) ENGINE=MYISAM; +INSERT INTO t2 VALUES (2), (8), (4); +CREATE USER 'u1'@'localhost'; +GRANT USAGE ON db.* TO 'u1'@'localhost'; +GRANT SELECT ON db.t1 TO 'u1'@'localhost'; +FLUSH PRIVILEGES; +connect u1,'localhost',u1,,; +connection u1; +USE db; +WITH +cte1 AS +(SELECT a FROM t1), +cte2 AS +(SELECT cte1.a FROM t1,cte1 WHERE cte1.a = t1.a), +cte3 AS +(SELECT cte2.a FROM t1,cte1,cte2 WHERE cte1.a = t1.a AND t1.a = cte2.a), +cte4 AS +(SELECT cte2.a FROM t1,cte2 WHERE cte2.a = t1.a) +SELECT * FROM cte4 as r; +a +3 +7 +1 +WITH +cte1 AS +(SELECT a FROM t2), +cte2 AS +(SELECT cte1.a FROM t2,cte1 WHERE cte1.a = t2.a), +cte3 AS +(SELECT cte2.a FROM t2,cte1,cte2 WHERE cte1.a = t2.a AND t2.a = cte2.a), +cte4 AS +(SELECT cte2.a FROM t2,cte2 WHERE cte2.a = t2.a) +SELECT * FROM cte4 as r; +ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 't2' +disconnect u1; +connection default; +DROP USER 'u1'@'localhost'; +DROP DATABASE db; +USE test; +# End of 10.2 tests diff --git a/mysql-test/main/cte_nonrecursive_not_embedded.test b/mysql-test/main/cte_nonrecursive_not_embedded.test new file mode 100644 index 00000000000..e80baeaf591 --- /dev/null +++ b/mysql-test/main/cte_nonrecursive_not_embedded.test @@ -0,0 +1,58 @@ +-- source include/not_embedded.inc + +--echo # +--echo # MDEV-20751: query using many CTEs with grant_tables enabled +--echo # + +--connection default + +CREATE DATABASE db; +USE db; + +CREATE TABLE t1 (a int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (3), (7), (1); +CREATE TABLE t2 (a int) ENGINE=MYISAM; +INSERT INTO t2 VALUES (2), (8), (4); + + +CREATE USER 'u1'@'localhost'; +GRANT USAGE ON db.* TO 'u1'@'localhost'; +GRANT SELECT ON db.t1 TO 'u1'@'localhost'; +FLUSH PRIVILEGES; + +--connect (u1,'localhost',u1,,) +--connection u1 +USE db; + +WITH +cte1 AS +(SELECT a FROM t1), +cte2 AS +(SELECT cte1.a FROM t1,cte1 WHERE cte1.a = t1.a), +cte3 AS +(SELECT cte2.a FROM t1,cte1,cte2 WHERE cte1.a = t1.a AND t1.a = cte2.a), +cte4 AS +(SELECT cte2.a FROM t1,cte2 WHERE cte2.a = t1.a) +SELECT * FROM cte4 as r; + +--error ER_TABLEACCESS_DENIED_ERROR +WITH +cte1 AS +(SELECT a FROM t2), +cte2 AS +(SELECT cte1.a FROM t2,cte1 WHERE cte1.a = t2.a), +cte3 AS +(SELECT cte2.a FROM t2,cte1,cte2 WHERE cte1.a = t2.a AND t2.a = cte2.a), +cte4 AS +(SELECT cte2.a FROM t2,cte2 WHERE cte2.a = t2.a) +SELECT * FROM cte4 as r; + +--disconnect u1 +--connection default + +DROP USER 'u1'@'localhost'; +DROP DATABASE db; + +USE test; + +--echo # End of 10.2 tests diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index d8ea858ad5a..decb1b47f13 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -818,7 +818,7 @@ where p.id = a.father or p.id = a.mother select * from ancestors; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' and `folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' and `test`.`folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci select * from v1; id name dob father mother 100 Me 2000-01-01 20 30 @@ -849,7 +849,7 @@ where p.id = ma.mother select * from ancestors; show create view v2; View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci select * from v2; id name dob father mother 100 Me 2000-01-01 20 30 @@ -4232,6 +4232,273 @@ a b c deallocate prepare stmt; drop table t1; # +# MDEV-24019: query with recursive CTE when no default database is set +# +drop database test; +with recursive a as +(select 1 from dual union select * from a as r) +select * from a; +1 +1 +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3), (7), (1); +with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; +a +3 +7 +1 +explain with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +prepare stmt from "with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte"; +execute stmt; +a +3 +7 +1 +execute stmt; +a +3 +7 +1 +deallocate prepare stmt; +drop database db1; +create database test; +use test; +# +# MDEV-23406: query with mutually recursive CTEs when big_tables=1 +# +set @save_big_tables=@@big_tables; +set big_tables=1; +Warnings: +Warning 1287 '@@big_tables' is deprecated and will be removed in a future release +create table folks(id int, name char(32), dob date, father int, mother int); +insert into folks values +(100, 'Me', '2000-01-01', 20, 30), +(20, 'Dad', '1970-02-02', 10, 9), +(30, 'Mom', '1975-03-03', 8, 7), +(10, 'Grandpa Bill', '1940-04-05', null, null), +(9, 'Grandma Ann', '1941-10-15', null, null), +(25, 'Uncle Jim', '1968-11-18', 8, 7), +(98, 'Sister Amy', '2001-06-20', 20, 30), +(7, 'Grandma Sally', '1943-08-23', null, 6), +(8, 'Grandpa Ben', '1940-10-21', null, null), +(6, 'Grandgrandma Martha', '1923-05-17', null, null), +(67, 'Cousin Eddie', '1992-02-28', 25, 27), +(27, 'Auntie Melinda', '1971-03-29', null, null); +with recursive +ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, +w_id, w_name, w_dob, w_father, w_mother) +as +( +select h.*, w.* +from folks h, folks w, coupled_ancestors a +where a.father = h.id AND a.mother = w.id +union +select h.*, w.* +from folks v, folks h, folks w +where v.name = 'Me' and +(v.father = h.id AND v.mother= w.id) +), +coupled_ancestors (id, name, dob, father, mother) +as +( +select h_id, h_name, h_dob, h_father, h_mother +from ancestor_couples +union +select w_id, w_name, w_dob, w_father, w_mother +from ancestor_couples +) +select h_name, h_dob, w_name, w_dob +from ancestor_couples; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +explain with recursive +ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, +w_id, w_name, w_dob, w_father, w_mother) +as +( +select h.*, w.* +from folks h, folks w, coupled_ancestors a +where a.father = h.id AND a.mother = w.id +union +select h.*, w.* +from folks v, folks h, folks w +where v.name = 'Me' and +(v.father = h.id AND v.mother= w.id) +), +coupled_ancestors (id, name, dob, father, mother) +as +( +select h_id, h_name, h_dob, h_father, h_mother +from ancestor_couples +union +select w_id, w_name, w_dob, w_father, w_mother +from ancestor_couples +) +select h_name, h_dob, w_name, w_dob +from ancestor_couples; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 1728 +4 DERIVED <derived3> ALL NULL NULL NULL NULL 1728 +5 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 1728 +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL +3 DERIVED v ALL NULL NULL NULL NULL 12 Using where +3 DERIVED h ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +3 DERIVED w ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) +2 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 2 +2 RECURSIVE UNION h ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 RECURSIVE UNION w ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) +NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL +prepare stmt from "with recursive +ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, +w_id, w_name, w_dob, w_father, w_mother) +as +( +select h.*, w.* +from folks h, folks w, coupled_ancestors a +where a.father = h.id AND a.mother = w.id +union +select h.*, w.* +from folks v, folks h, folks w +where v.name = 'Me' and +(v.father = h.id AND v.mother= w.id) +), +coupled_ancestors (id, name, dob, father, mother) +as +( +select h_id, h_name, h_dob, h_father, h_mother +from ancestor_couples +union +select w_id, w_name, w_dob, w_father, w_mother +from ancestor_couples +) +select h_name, h_dob, w_name, w_dob +from ancestor_couples"; +execute stmt; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +execute stmt; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +deallocate prepare stmt; +with recursive +ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, +w_id, w_name, w_dob, w_father, w_mother) +as +( +select h.*, w.* +from folks h, folks w, coupled_ancestors a +where a.father = h.id AND a.mother = w.id +), +coupled_ancestors (id, name, dob, father, mother) +as +( +select * +from folks +where name = 'Me' + union all +select h_id, h_name, h_dob, h_father, h_mother +from ancestor_couples +union all +select w_id, w_name, w_dob, w_father, w_mother +from ancestor_couples +) +select h_name, h_dob, w_name, w_dob +from ancestor_couples; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +explain with recursive +ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, +w_id, w_name, w_dob, w_father, w_mother) +as +( +select h.*, w.* +from folks h, folks w, coupled_ancestors a +where a.father = h.id AND a.mother = w.id +), +coupled_ancestors (id, name, dob, father, mother) +as +( +select * +from folks +where name = 'Me' + union all +select h_id, h_name, h_dob, h_father, h_mother +from ancestor_couples +union all +select w_id, w_name, w_dob, w_father, w_mother +from ancestor_couples +) +select h_name, h_dob, w_name, w_dob +from ancestor_couples; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where +4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 +5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 +NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL +2 DERIVED h ALL NULL NULL NULL NULL 12 +2 DERIVED w ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join) +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) +prepare stmt from "with recursive +ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, +w_id, w_name, w_dob, w_father, w_mother) +as +( +select h.*, w.* +from folks h, folks w, coupled_ancestors a +where a.father = h.id AND a.mother = w.id +), +coupled_ancestors (id, name, dob, father, mother) +as +( +select * +from folks +where name = 'Me' + union all +select h_id, h_name, h_dob, h_father, h_mother +from ancestor_couples +union all +select w_id, w_name, w_dob, w_father, w_mother +from ancestor_couples +) +select h_name, h_dob, w_name, w_dob +from ancestor_couples"; +execute stmt; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +execute stmt; +h_name h_dob w_name w_dob +Dad 1970-02-02 Mom 1975-03-03 +Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 +Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 +deallocate prepare stmt; +drop table folks; +set big_tables=@save_big_tables; +Warnings: +Warning 1287 '@@big_tables' is deprecated and will be removed in a future release +# # End of 10.2 tests # # diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 0e29824e97e..125e22ebfa6 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -2726,6 +2726,135 @@ deallocate prepare stmt; drop table t1; --echo # +--echo # MDEV-24019: query with recursive CTE when no default database is set +--echo # + +drop database test; + +let $q= +with recursive a as + (select 1 from dual union select * from a as r) +select * from a; + +eval $q; + +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3), (7), (1); + +let $q= +with recursive cte as + (select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; + +eval $q; +eval explain $q; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop database db1; + +create database test; +use test; + +--echo # +--echo # MDEV-23406: query with mutually recursive CTEs when big_tables=1 +--echo # + +set @save_big_tables=@@big_tables; +set big_tables=1; + +create table folks(id int, name char(32), dob date, father int, mother int); + +insert into folks values +(100, 'Me', '2000-01-01', 20, 30), +(20, 'Dad', '1970-02-02', 10, 9), +(30, 'Mom', '1975-03-03', 8, 7), +(10, 'Grandpa Bill', '1940-04-05', null, null), +(9, 'Grandma Ann', '1941-10-15', null, null), +(25, 'Uncle Jim', '1968-11-18', 8, 7), +(98, 'Sister Amy', '2001-06-20', 20, 30), +(7, 'Grandma Sally', '1943-08-23', null, 6), +(8, 'Grandpa Ben', '1940-10-21', null, null), +(6, 'Grandgrandma Martha', '1923-05-17', null, null), +(67, 'Cousin Eddie', '1992-02-28', 25, 27), +(27, 'Auntie Melinda', '1971-03-29', null, null); + +let q= +with recursive +ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, + w_id, w_name, w_dob, w_father, w_mother) +as +( + select h.*, w.* + from folks h, folks w, coupled_ancestors a + where a.father = h.id AND a.mother = w.id + union + select h.*, w.* + from folks v, folks h, folks w + where v.name = 'Me' and + (v.father = h.id AND v.mother= w.id) +), +coupled_ancestors (id, name, dob, father, mother) +as +( + select h_id, h_name, h_dob, h_father, h_mother + from ancestor_couples + union + select w_id, w_name, w_dob, w_father, w_mother + from ancestor_couples +) +select h_name, h_dob, w_name, w_dob + from ancestor_couples; + +eval $q; +eval explain $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q= +with recursive +ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, + w_id, w_name, w_dob, w_father, w_mother) +as +( + select h.*, w.* + from folks h, folks w, coupled_ancestors a + where a.father = h.id AND a.mother = w.id +), +coupled_ancestors (id, name, dob, father, mother) +as +( + select * + from folks + where name = 'Me' + union all + select h_id, h_name, h_dob, h_father, h_mother + from ancestor_couples + union all + select w_id, w_name, w_dob, w_father, w_mother + from ancestor_couples +) +select h_name, h_dob, w_name, w_dob + from ancestor_couples; + +eval $q; +eval explain $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop table folks; + +set big_tables=@save_big_tables; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/ctype_utf16.result b/mysql-test/main/ctype_utf16.result index 653f18e0ce6..36dedc87321 100644 --- a/mysql-test/main/ctype_utf16.result +++ b/mysql-test/main/ctype_utf16.result @@ -1492,6 +1492,8 @@ ab AE AE SET max_sort_length=8; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '8' SELECT * FROM t1 ORDER BY s1; s1 ab diff --git a/mysql-test/main/ctype_utf16le.result b/mysql-test/main/ctype_utf16le.result index 38d61bd4f1b..c43106aa042 100644 --- a/mysql-test/main/ctype_utf16le.result +++ b/mysql-test/main/ctype_utf16le.result @@ -1765,6 +1765,8 @@ ab AE AE SET max_sort_length=8; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '8' SELECT * FROM t1 ORDER BY s1; s1 ab diff --git a/mysql-test/main/ctype_utf32.result b/mysql-test/main/ctype_utf32.result index 5cbc1b66e40..563c8aa6bad 100644 --- a/mysql-test/main/ctype_utf32.result +++ b/mysql-test/main/ctype_utf32.result @@ -1505,6 +1505,8 @@ ab AE AE SET max_sort_length=8; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '8' SELECT * FROM t1 ORDER BY s1; s1 ab diff --git a/mysql-test/main/ctype_utf8.result b/mysql-test/main/ctype_utf8.result index f9d99d7f246..266292edab6 100644 --- a/mysql-test/main/ctype_utf8.result +++ b/mysql-test/main/ctype_utf8.result @@ -6760,9 +6760,11 @@ DFFFDFFF9CFF9DFF9EFF # Checking strnxfrm() with odd length # set max_sort_length=9; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '9' select @@max_sort_length; @@max_sort_length -9 +64 create table t1 (a varchar(128) character set utf8 collate utf8_general_ci); insert into t1 values ('a'),('b'),('c'); select * from t1 order by a; diff --git a/mysql-test/main/ctype_utf8mb4.result b/mysql-test/main/ctype_utf8mb4.result index 20842b37abb..dfb5c7049c6 100644 --- a/mysql-test/main/ctype_utf8mb4.result +++ b/mysql-test/main/ctype_utf8mb4.result @@ -2371,10 +2371,10 @@ drop table t1; # # Check strnxfrm() with odd length # -set max_sort_length=9; +set max_sort_length=65; select @@max_sort_length; @@max_sort_length -9 +65 create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci); insert into t1 values ('a'),('b'),('c'); select * from t1 order by a; diff --git a/mysql-test/main/ctype_utf8mb4.test b/mysql-test/main/ctype_utf8mb4.test index 532729dafde..8fbbee872ce 100644 --- a/mysql-test/main/ctype_utf8mb4.test +++ b/mysql-test/main/ctype_utf8mb4.test @@ -1520,7 +1520,7 @@ drop table t1; --echo # --echo # Check strnxfrm() with odd length --echo # -set max_sort_length=9; +set max_sort_length=65; select @@max_sort_length; create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci); insert into t1 values ('a'),('b'),('c'); diff --git a/mysql-test/main/ctype_utf8mb4_heap.result b/mysql-test/main/ctype_utf8mb4_heap.result index 4aef2d8cb66..9eb7d48370f 100644 --- a/mysql-test/main/ctype_utf8mb4_heap.result +++ b/mysql-test/main/ctype_utf8mb4_heap.result @@ -2203,10 +2203,10 @@ drop table t1; # # Check strnxfrm() with odd length # -set max_sort_length=9; +set max_sort_length=65; select @@max_sort_length; @@max_sort_length -9 +65 create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine heap; insert into t1 values ('a'),('b'),('c'); select * from t1 order by a; diff --git a/mysql-test/main/ctype_utf8mb4_innodb.result b/mysql-test/main/ctype_utf8mb4_innodb.result index 3c7d0ba2fb7..fc2a368f3f0 100644 --- a/mysql-test/main/ctype_utf8mb4_innodb.result +++ b/mysql-test/main/ctype_utf8mb4_innodb.result @@ -2329,10 +2329,10 @@ drop table t1; # # Check strnxfrm() with odd length # -set max_sort_length=9; +set max_sort_length=65; select @@max_sort_length; @@max_sort_length -9 +65 create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine InnoDB; insert into t1 values ('a'),('b'),('c'); select * from t1 order by a; diff --git a/mysql-test/main/ctype_utf8mb4_myisam.result b/mysql-test/main/ctype_utf8mb4_myisam.result index fd8d7adf3a0..8940d10fc78 100644 --- a/mysql-test/main/ctype_utf8mb4_myisam.result +++ b/mysql-test/main/ctype_utf8mb4_myisam.result @@ -2336,10 +2336,10 @@ drop table t1; # # Check strnxfrm() with odd length # -set max_sort_length=9; +set max_sort_length=65; select @@max_sort_length; @@max_sort_length -9 +65 create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine MyISAM; insert into t1 values ('a'),('b'),('c'); select * from t1 order by a; diff --git a/mysql-test/main/drop_combinations.result b/mysql-test/main/drop_combinations.result index 140665efd19..a939bcd2b51 100644 --- a/mysql-test/main/drop_combinations.result +++ b/mysql-test/main/drop_combinations.result @@ -599,7 +599,8 @@ Note 4091 Unknown SEQUENCE: 'test.s4,test.s3' # Generating test tables drop temporary sequence if exists s1,t1,t2,s2; Warnings: -Note 4091 Unknown SEQUENCE: 'test.t1,test.t2' +Note 4090 'test.t1' is not a SEQUENCE +Note 4090 'test.t2' is not a SEQUENCE # Generating test tables drop temporary sequence if exists s1,v1,v2,s2; Warnings: @@ -607,18 +608,24 @@ Note 4091 Unknown SEQUENCE: 'test.v1,test.v2' # Generating test tables drop temporary sequence if exists v1,t1,t2,v2,s2; Warnings: -Note 4091 Unknown SEQUENCE: 'test.v1,test.t1,test.t2,test.v2' +Note 4090 'test.t1' is not a SEQUENCE +Note 4090 'test.t2' is not a SEQUENCE +Note 4091 Unknown SEQUENCE: 'test.v1,test.v2' # Generating test tables drop temporary sequence if exists s1,t1,v1,t3,s4; Warnings: -Note 4091 Unknown SEQUENCE: 'test.t1,test.v1,test.t3,test.s4' +Note 4090 'test.t1' is not a SEQUENCE +Note 4091 Unknown SEQUENCE: 'test.v1,test.t3,test.s4' drop temporary sequence if exists t2,v2,s2,s1; Warnings: -Note 4091 Unknown SEQUENCE: 'test.t2,test.v2,test.s1' +Note 4090 'test.t2' is not a SEQUENCE +Note 4091 Unknown SEQUENCE: 'test.v2,test.s1' # Generating test tables drop temporary sequence if exists t1,v1,t2,v2; Warnings: -Note 4091 Unknown SEQUENCE: 'test.t1,test.v1,test.t2,test.v2' +Note 4090 'test.t1' is not a SEQUENCE +Note 4090 'test.t2' is not a SEQUENCE +Note 4091 Unknown SEQUENCE: 'test.v1,test.v2' # Generating test tables drop temporary table t1,t2; drop temporary sequence s1,s2; diff --git a/mysql-test/main/func_gconcat.result b/mysql-test/main/func_gconcat.result index 01bf45056c3..328bbc953ab 100644 --- a/mysql-test/main/func_gconcat.result +++ b/mysql-test/main/func_gconcat.result @@ -1280,6 +1280,18 @@ Name_exp_1 DROP VIEW v1; DROP TABLE t1; # +# MDEV-4677 GROUP_CONCAT not showing any output with group_concat_max_len >= 4Gb +# +set group_concat_max_len=1024*1024*1024*4; +Warnings: +Warning 1292 Truncated incorrect group_concat_max_len value: '4294967296' +create table t1 (i int, j int); +insert into t1 values (1,1),(1,2); +select i, group_concat(j) from t1 group by i; +i group_concat(j) +1 1,2 +drop table t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/func_gconcat.test b/mysql-test/main/func_gconcat.test index 2ab856e0edd..3c21aa04ffc 100644 --- a/mysql-test/main/func_gconcat.test +++ b/mysql-test/main/func_gconcat.test @@ -939,6 +939,16 @@ DROP VIEW v1; DROP TABLE t1; + +--echo # +--echo # MDEV-4677 GROUP_CONCAT not showing any output with group_concat_max_len >= 4Gb +--echo # +set group_concat_max_len=1024*1024*1024*4; +create table t1 (i int, j int); +insert into t1 values (1,1),(1,2); +select i, group_concat(j) from t1 group by i; +drop table t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/func_like.result b/mysql-test/main/func_like.result index 0ec6724d7c0..200cb20c4de 100644 --- a/mysql-test/main/func_like.result +++ b/mysql-test/main/func_like.result @@ -289,6 +289,24 @@ a b c d 3 f_ 1 0 1 3 f\_ 0 1 0 drop table t1; +create table t1 (f int); +insert t1 values (1),(2); +select 1 from (select distinct * from t1) as x where f < (select 1 like 2 escape (3=1)); +1 +drop table t1; +create table t1(f1 int); +insert into t1 values(1); +update (select 1 like 2 escape (1 in (select 1 from t1))) x, t1 as d set d.f1 = 1; +ERROR HY000: Incorrect arguments to ESCAPE +select * from (select 1 like 2 escape (1 in (select 1 from t1))) x; +1 like 2 escape (1 in (select 1 from t1)) +0 +drop table t1; +create table t1 (f int); +insert t1 values (1),(2); +create view v1 as select * from t1 where (1 like 2 escape (3 in (('h', 'b') in (select 'k', 'k' union select 'g', 'j'))) and f >= 0); +drop view v1; +drop table t1; # # MDEV-17359 - Extend expression supported by like (| & << >> || + - * / DIV MOD ^ ) # diff --git a/mysql-test/main/func_like.test b/mysql-test/main/func_like.test index cb50fb91879..ef13d46c268 100644 --- a/mysql-test/main/func_like.test +++ b/mysql-test/main/func_like.test @@ -187,7 +187,7 @@ DROP TABLE t1; --echo # # -# Item_func_line::print() +# Item_func_like::print() # create view v1 as select 'foo!' like 'foo!!', 'foo!' like 'foo!!' escape '!'; show create view v1; @@ -208,6 +208,33 @@ set sql_mode=default; select * from t1; drop table t1; +# +# Item_func_like::fix_fields() +# +create table t1 (f int); +insert t1 values (1),(2); +select 1 from (select distinct * from t1) as x where f < (select 1 like 2 escape (3=1)); +drop table t1; + +# +# Item_func_like::fix_fields, ESCAPE, const_item() +# +create table t1(f1 int); +insert into t1 values(1); +--error ER_WRONG_ARGUMENTS +update (select 1 like 2 escape (1 in (select 1 from t1))) x, t1 as d set d.f1 = 1; +select * from (select 1 like 2 escape (1 in (select 1 from t1))) x; +drop table t1; + +# +# Item_func_like::walk +# +create table t1 (f int); +insert t1 values (1),(2); +create view v1 as select * from t1 where (1 like 2 escape (3 in (('h', 'b') in (select 'k', 'k' union select 'g', 'j'))) and f >= 0); +drop view v1; +drop table t1; + --echo # --echo # MDEV-17359 - Extend expression supported by like (| & << >> || + - * / DIV MOD ^ ) --echo # diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index 627fb9e4569..004167f9c8f 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -863,6 +863,7 @@ where data_type = 'longtext' and table_schema != 'performance_schema' order by binary table_name, ordinal_position; table_schema table_name column_name information_schema ALL_PLUGINS PLUGIN_DESCRIPTION +information_schema CHECK_CONSTRAINTS CHECK_CLAUSE information_schema COLUMNS COLUMN_DEFAULT information_schema COLUMNS COLUMN_TYPE information_schema COLUMNS GENERATION_EXPRESSION @@ -2222,17 +2223,10 @@ SCHEMA_NAME # MDEV-14836: Assertion `m_status == DA_ERROR' failed in # Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED # -SELECT * FROM seq_1_to_100 LIMIT ROWS EXAMINED 10; -seq -1 -2 -3 -4 -5 -6 -7 +SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION Warnings: -Warning 1931 Query execution was interrupted. The query examined at least 11 rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete +Warning 1931 Query execution was interrupted. The query examined at least ### rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete # # End of 10.2 Test # diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test index ca49091ef76..e0ab022cd61 100644 --- a/mysql-test/main/information_schema.test +++ b/mysql-test/main/information_schema.test @@ -1928,7 +1928,8 @@ SELECT SCHEMA_NAME from information_schema.schemata where schema_name=REPEAT('a' --echo # Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED --echo # -SELECT * FROM seq_1_to_100 LIMIT ROWS EXAMINED 10; +replace_regex /at least \d+ rows/at least ### rows/; +SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10; --echo # --echo # End of 10.2 Test diff --git a/mysql-test/main/lock_view.result b/mysql-test/main/lock_view.result index 4d375bace42..364c2cddf60 100644 --- a/mysql-test/main/lock_view.result +++ b/mysql-test/main/lock_view.result @@ -229,3 +229,37 @@ drop user definer@localhost; drop database mysqltest1; drop database mysqltest2; drop database mysqltest3; +# +# MDEV-24331 mysqldump fails with "Got error: 1356" if the database contains a view with a subquery +# +create user u1@localhost; +grant all privileges on test.* to u1@localhost; +connect con1,localhost,u1; +use test; +create table t1 (id int not null); +create view v1 as select * from (select * from t1) dt; +lock table v1 read; +disconnect con1; +connection default; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v1` ( + `id` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; +/*!50001 DROP TABLE IF EXISTS `v1`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET character_set_client = latin1 */; +/*!50001 SET character_set_results = latin1 */; +/*!50001 SET collation_connection = latin1_swedish_ci */; +/*!50001 CREATE ALGORITHM=UNDEFINED */ +/*!50013 DEFINER=`u1`@`localhost` SQL SECURITY DEFINER */ +/*!50001 VIEW `v1` AS select `dt`.`id` AS `id` from (select `test`.`t1`.`id` AS `id` from `test`.`t1`) `dt` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +drop view v1; +drop table t1; +drop user u1@localhost; diff --git a/mysql-test/main/lock_view.test b/mysql-test/main/lock_view.test index 4b1adac5be1..abb8d317946 100644 --- a/mysql-test/main/lock_view.test +++ b/mysql-test/main/lock_view.test @@ -75,3 +75,20 @@ drop user definer@localhost; drop database mysqltest1; drop database mysqltest2; drop database mysqltest3; + +--echo # +--echo # MDEV-24331 mysqldump fails with "Got error: 1356" if the database contains a view with a subquery +--echo # +create user u1@localhost; +grant all privileges on test.* to u1@localhost; +connect con1,localhost,u1; +use test; +create table t1 (id int not null); +create view v1 as select * from (select * from t1) dt; +lock table v1 read; +disconnect con1; +connection default; +exec $MYSQL_DUMP test v1 -uu1 --compact; +drop view v1; +drop table t1; +drop user u1@localhost; diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result index 539fe19f072..f7deb74dc7f 100644 --- a/mysql-test/main/mysql_upgrade.result +++ b/mysql-test/main/mysql_upgrade.result @@ -484,8 +484,6 @@ even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@loca DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost; DROP PROCEDURE test.pr; use test; -call mtr.add_suppression('Column last_update in table `mysql`.`innodb_table_stats` is INT NOT NULL but should be'); -alter table mysql.innodb_table_stats modify last_update int not null; create table extralongname_extralongname_extralongname_extralongname_ext ( id int(10) unsigned not null, created_date date not null, diff --git a/mysql-test/main/mysql_upgrade.test b/mysql-test/main/mysql_upgrade.test index a6345af937d..0200f77797d 100644 --- a/mysql-test/main/mysql_upgrade.test +++ b/mysql-test/main/mysql_upgrade.test @@ -172,8 +172,6 @@ DROP PROCEDURE test.pr; # MDEV-13274 mysql_upgrade fails if dbname+tablename+partioname > 64 chars # use test; -call mtr.add_suppression('Column last_update in table `mysql`.`innodb_table_stats` is INT NOT NULL but should be'); -alter table mysql.innodb_table_stats modify last_update int not null; create table extralongname_extralongname_extralongname_extralongname_ext ( id int(10) unsigned not null, diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index b1e510c5377..81127121cde 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -845,18 +845,20 @@ col2 col col 2 2 2 1 3 3 drop table t1, t2; -create table t1 (a char(25)); +create table t1 (a char(70)); insert into t1 set a = repeat('x', 20); -insert into t1 set a = concat(repeat('x', 19), 'z'); -insert into t1 set a = concat(repeat('x', 19), 'ab'); -insert into t1 set a = concat(repeat('x', 19), 'aa'); +insert into t1 set a = concat(repeat('x', 63), 'z'); +insert into t1 set a = concat(repeat('x', 63), 'ab'); +insert into t1 set a = concat(repeat('x', 63), 'aa'); set max_sort_length=20; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '20' select a from t1 order by a; a -xxxxxxxxxxxxxxxxxxxab -xxxxxxxxxxxxxxxxxxxaa xxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxz +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxab +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxaa +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz drop table t1; create table t1 ( `sid` decimal(8,0) default null, @@ -3317,6 +3319,8 @@ drop table t1; SET @save_sort_buffer_size= @@sort_buffer_size; SET @save_max_sort_length= @@max_sort_length; SET max_sort_length=8; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '8' SET sort_buffer_size=1024; CREATE TABLE t1(a INT, b DECIMAL(65), c BLOB); INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_25; @@ -3384,6 +3388,8 @@ SET @save_max_sort_length= @@max_sort_length; SET @save_sort_buffer_size= @@sort_buffer_size; SET @save_max_length_for_sort_data= @@max_length_for_sort_data; SET max_sort_length=8; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '8' SET sort_buffer_size=1024; SET max_length_for_sort_data=7000; CREATE TABLE t1(a VARCHAR(64), b VARCHAR(2048))DEFAULT CHARSET=utf8; @@ -3520,6 +3526,8 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); SET max_length_for_sort_data= 30; SET sql_select_limit = 3; SET max_sort_length=8; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '8' SELECT * FROM t1 ORDER BY a+1; a b 1 1 diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index 9d0eaef5ea4..fbdf193269f 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -533,11 +533,11 @@ drop table t1, t2; # Bug #5428: a problem with small max_sort_length value # -create table t1 (a char(25)); +create table t1 (a char(70)); insert into t1 set a = repeat('x', 20); -insert into t1 set a = concat(repeat('x', 19), 'z'); -insert into t1 set a = concat(repeat('x', 19), 'ab'); -insert into t1 set a = concat(repeat('x', 19), 'aa'); +insert into t1 set a = concat(repeat('x', 63), 'z'); +insert into t1 set a = concat(repeat('x', 63), 'ab'); +insert into t1 set a = concat(repeat('x', 63), 'aa'); set max_sort_length=20; select a from t1 order by a; drop table t1; diff --git a/mysql-test/main/precedence.result b/mysql-test/main/precedence.result index d5d4e662a8e..fc6579651b4 100644 --- a/mysql-test/main/precedence.result +++ b/mysql-test/main/precedence.result @@ -8016,4 +8016,8 @@ create or replace view v1 as select 1 IS TRUE IS FALSE, 2 IS FALSE IS UNKNOWN, 3 Select view_definition from information_schema.views where table_schema='test' and table_name='v1'; view_definition select 1 is true is false AS `1 IS TRUE IS FALSE`,/*always not null*/ 1 is null AS `2 IS FALSE IS UNKNOWN`,/*always not null*/ 1 is null AS `3 IS UNKNOWN IS NULL`,/*always not null*/ 1 is null is true AS `4 IS NULL IS TRUE` +create or replace view v1 as select 2 IS TRUE = 3, 2 IS FALSE = 3, 2 IS UNKNOWN = 3, 2 IS NULL = 3, ISNULL(2) = 1; +Select view_definition from information_schema.views where table_schema='test' and table_name='v1'; +view_definition +select 2 is true = 3 AS `2 IS TRUE = 3`,2 is false = 3 AS `2 IS FALSE = 3`,/*always not null*/ 1 is null = 3 AS `2 IS UNKNOWN = 3`,/*always not null*/ 1 is null = 3 AS `2 IS NULL = 3`,/*always not null*/ 1 is null = 1 AS `ISNULL(2) = 1` drop view v1; diff --git a/mysql-test/main/precedence.test b/mysql-test/main/precedence.test index ad367c23603..cd7cee4f911 100644 --- a/mysql-test/main/precedence.test +++ b/mysql-test/main/precedence.test @@ -4785,4 +4785,7 @@ Select view_definition from information_schema.views where table_schema='test' a create or replace view v1 as select 1 IS TRUE IS FALSE, 2 IS FALSE IS UNKNOWN, 3 IS UNKNOWN IS NULL, 4 IS NULL IS TRUE; Select view_definition from information_schema.views where table_schema='test' and table_name='v1'; +create or replace view v1 as select 2 IS TRUE = 3, 2 IS FALSE = 3, 2 IS UNKNOWN = 3, 2 IS NULL = 3, ISNULL(2) = 1; +Select view_definition from information_schema.views where table_schema='test' and table_name='v1'; + drop view v1; diff --git a/mysql-test/main/query_cache.result b/mysql-test/main/query_cache.result index 4615c63feb2..fc7ca726c48 100644 --- a/mysql-test/main/query_cache.result +++ b/mysql-test/main/query_cache.result @@ -818,33 +818,33 @@ select @@character_set_results; NULL set character_set_results=default; set GLOBAL query_cache_size=1355776; -create table t1 (id int auto_increment primary key, c char(25)); +create table t1 (id int auto_increment primary key, c char(65)); insert into t1 set c = repeat('x',24); -insert into t1 set c = concat(repeat('x',24),'x'); -insert into t1 set c = concat(repeat('x',24),'w'); -insert into t1 set c = concat(repeat('x',24),'y'); +insert into t1 set c = concat(repeat('x',64),'x'); +insert into t1 set c = concat(repeat('x',64),'w'); +insert into t1 set c = concat(repeat('x',64),'y'); set max_sort_length=200; select c from t1 order by c, id; c xxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxw -xxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy reset query cache; -set max_sort_length=20; +set max_sort_length=64; select c from t1 order by c, id; c xxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxw -xxxxxxxxxxxxxxxxxxxxxxxxy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy set max_sort_length=200; select c from t1 order by c, id; c xxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxw -xxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy set max_sort_length=default; select '1' || '3' from t1; '1' || '3' diff --git a/mysql-test/main/query_cache.test b/mysql-test/main/query_cache.test index 118ebbf1703..6e113f0cdb7 100644 --- a/mysql-test/main/query_cache.test +++ b/mysql-test/main/query_cache.test @@ -606,15 +606,15 @@ set character_set_results=default; # # max_sort_length set GLOBAL query_cache_size=1355776; -create table t1 (id int auto_increment primary key, c char(25)); +create table t1 (id int auto_increment primary key, c char(65)); insert into t1 set c = repeat('x',24); -insert into t1 set c = concat(repeat('x',24),'x'); -insert into t1 set c = concat(repeat('x',24),'w'); -insert into t1 set c = concat(repeat('x',24),'y'); +insert into t1 set c = concat(repeat('x',64),'x'); +insert into t1 set c = concat(repeat('x',64),'w'); +insert into t1 set c = concat(repeat('x',64),'y'); set max_sort_length=200; select c from t1 order by c, id; reset query cache; -set max_sort_length=20; +set max_sort_length=64; select c from t1 order by c, id; set max_sort_length=200; select c from t1 order by c, id; diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 7fa6058b892..c7bf8948d3a 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3226,6 +3226,31 @@ pk a b 1 5 50 65 5 50 drop table t1; +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 ( +pk int primary key, +key1 int, +col1 varchar(255), +key (key1, pk) +); +insert into t2 (pk, key1) +select A.a+10 *B.a + 100*C.a, A.a+10 *B.a +100*C.a from t1 A, t1 B, t1 C; +# This must use ALL, not range: +explain select * from t2 force index (primary) where pk not in (1,2,3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1000 Using where +drop table t1,t2; +# +# MDEV-24444: ASAN use-after-poison in Item_func_in::get_func_mm_tree with NOT IN +# +CREATE TABLE t1 (id INT, a CHAR(3), b INT, PRIMARY KEY(id), KEY(b), KEY(a)); +INSERT INTO t1 VALUES (1,'foo',10),(2,'bar',20); +CREATE TABLE t2 (code CHAR(8), num INT, PRIMARY KEY (code)); +INSERT INTO t2 VALUES ('100',1),('111',2); +SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR t2.num != 3; +id a b code num +DROP TABLE t1, t2; # # End of 10.2 tests # diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index b3a0bfe7ebc..4f228e5dce3 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2180,6 +2180,40 @@ eval $q4; drop table t1; +# +# MDEV-21958: Query having many NOT-IN clauses running forever (testcase 2) +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2 ( + pk int primary key, + key1 int, + col1 varchar(255), + key (key1, pk) +); + +insert into t2 (pk, key1) +select A.a+10 *B.a + 100*C.a, A.a+10 *B.a +100*C.a from t1 A, t1 B, t1 C; + +--echo # This must use ALL, not range: +explain select * from t2 force index (primary) where pk not in (1,2,3); + +drop table t1,t2; + +--echo # +--echo # MDEV-24444: ASAN use-after-poison in Item_func_in::get_func_mm_tree with NOT IN +--echo # +CREATE TABLE t1 (id INT, a CHAR(3), b INT, PRIMARY KEY(id), KEY(b), KEY(a)); +INSERT INTO t1 VALUES (1,'foo',10),(2,'bar',20); + +CREATE TABLE t2 (code CHAR(8), num INT, PRIMARY KEY (code)); +INSERT INTO t2 VALUES ('100',1),('111',2); + +SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR t2.num != 3; + +DROP TABLE t1, t2; + --echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index e94c2598aad..3c5b7164163 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -3215,6 +3215,31 @@ pk a b 70 4 40 71 2 20 drop table t1; +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 ( +pk int primary key, +key1 int, +col1 varchar(255), +key (key1, pk) +); +insert into t2 (pk, key1) +select A.a+10 *B.a + 100*C.a, A.a+10 *B.a +100*C.a from t1 A, t1 B, t1 C; +# This must use ALL, not range: +explain select * from t2 force index (primary) where pk not in (1,2,3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1000 Using where +drop table t1,t2; +# +# MDEV-24444: ASAN use-after-poison in Item_func_in::get_func_mm_tree with NOT IN +# +CREATE TABLE t1 (id INT, a CHAR(3), b INT, PRIMARY KEY(id), KEY(b), KEY(a)); +INSERT INTO t1 VALUES (1,'foo',10),(2,'bar',20); +CREATE TABLE t2 (code CHAR(8), num INT, PRIMARY KEY (code)); +INSERT INTO t2 VALUES ('100',1),('111',2); +SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR t2.num != 3; +id a b code num +DROP TABLE t1, t2; # # End of 10.2 tests # diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result new file mode 100644 index 00000000000..4973f449631 --- /dev/null +++ b/mysql-test/main/range_notembedded.result @@ -0,0 +1,37 @@ +drop table if exists t1,t2; +# +# MDEV-21958: Query having many NOT-IN clauses running forever +# +create table t2 ( +pk int primary key, +key1 int, +col1 int, +key (key1, pk) +); +insert into t2 (pk, key1) values (1,1),(2,2),(3,3),(4,4),(5,5); +set @tmp_21958=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t2 where key1 in (1,2,3) and pk not in (1,2,3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY,key1 key1 5 NULL 3 Using index condition +# This should show only ranges in form "(1) <= (key1) <= (1)" +# ranges over "pk" should not be constructed. +select json_detailed(JSON_EXTRACT(trace, '$**.ranges')) +from information_schema.optimizer_trace; +json_detailed(JSON_EXTRACT(trace, '$**.ranges')) +[ + + [ + "(1) <= (key1) <= (1)", + "(2) <= (key1) <= (2)", + "(3) <= (key1) <= (3)" + ], + + [ + "(1) <= (key1) <= (1)", + "(2) <= (key1) <= (2)", + "(3) <= (key1) <= (3)" + ] +] +set optimizer_trace=@tmp_21958; +drop table t2; diff --git a/mysql-test/main/range_notembedded.test b/mysql-test/main/range_notembedded.test new file mode 100644 index 00000000000..4dc49429ff1 --- /dev/null +++ b/mysql-test/main/range_notembedded.test @@ -0,0 +1,33 @@ +# +# Range tests without embedded server. +# The first reason to have them is that embedded server doesn't have +# optimizer trace. +# +--source include/not_embedded.inc +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +--echo # +--echo # MDEV-21958: Query having many NOT-IN clauses running forever +--echo # +create table t2 ( + pk int primary key, + key1 int, + col1 int, + key (key1, pk) +); + +insert into t2 (pk, key1) values (1,1),(2,2),(3,3),(4,4),(5,5); + +set @tmp_21958=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t2 where key1 in (1,2,3) and pk not in (1,2,3); + +--echo # This should show only ranges in form "(1) <= (key1) <= (1)" +--echo # ranges over "pk" should not be constructed. +select json_detailed(JSON_EXTRACT(trace, '$**.ranges')) +from information_schema.optimizer_trace; +set optimizer_trace=@tmp_21958; + +drop table t2; diff --git a/mysql-test/main/set_password.result b/mysql-test/main/set_password.result index 6a4e0da937e..a3d8683ee64 100644 --- a/mysql-test/main/set_password.result +++ b/mysql-test/main/set_password.result @@ -11,9 +11,11 @@ create user oldauth@localhost identified with 'mysql_old_password' using '378b24 create user oldpass@localhost identified by password '378b243e220ca493'; create user oldpassold@localhost identified with 'mysql_old_password'; set password for oldpassold@localhost = '378b243e220ca493'; +create user invalidmysql57auth@localhost identified via 'mysql_native_password' using '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE'; select user, host, password, plugin, authentication_string from mysql.user where user != 'root'; User Host Password plugin authentication_string invalidauth localhost invalid mysql_native_password invalid +invalidmysql57auth localhost *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE mysql_native_password *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE invalidpass localhost invalid mysql_native_password invalid invalidpassnat localhost invalid mysql_native_password invalid mariadb.sys localhost mysql_native_password @@ -95,6 +97,7 @@ set password for oldpassold@localhost = PASSWORD('test2'); select user, host, password, plugin, authentication_string from mysql.user where user != 'root'; User Host Password plugin authentication_string invalidauth localhost invalid mysql_native_password invalid +invalidmysql57auth localhost *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE mysql_native_password *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE invalidpass localhost invalid mysql_native_password invalid invalidpassnat localhost invalid mysql_native_password invalid mariadb.sys localhost mysql_native_password @@ -160,6 +163,9 @@ ERROR 28000: Access denied for user 'invalidpass'@'localhost' (using password: Y connect(localhost,invalidpassnat,invalid,test,MASTER_PORT,MASTER_SOCKET); connect con,localhost,invalidpassnat,invalid,; ERROR 28000: Access denied for user 'invalidpassnat'@'localhost' (using password: YES) +connect(localhost,invalidmysql57auth,invalid,test,MASTER_PORT,MASTER_SOCKET); +connect con,localhost,invalidmysql57auth,invalid,; +ERROR 28000: Access denied for user 'invalidmysql57auth'@'localhost' (using password: YES) connect con,localhost,oldauth,test2,; select current_user(); current_user() @@ -177,7 +183,7 @@ oldpassold@localhost disconnect con; connection default; drop user natauth@localhost, newpass@localhost, newpassnat@localhost; -drop user invalidauth@localhost, invalidpass@localhost, invalidpassnat@localhost; +drop user invalidauth@localhost, invalidpass@localhost, invalidpassnat@localhost,invalidmysql57auth@localhost; drop user oldauth@localhost, oldpass@localhost, oldpassold@localhost; set global secure_auth=default; # switching from mysql.global_priv to mysql.user diff --git a/mysql-test/main/set_password.test b/mysql-test/main/set_password.test index dd5e261346b..ec652a09274 100644 --- a/mysql-test/main/set_password.test +++ b/mysql-test/main/set_password.test @@ -31,6 +31,8 @@ create user oldpass@localhost identified by password '378b243e220ca493'; create user oldpassold@localhost identified with 'mysql_old_password'; set password for oldpassold@localhost = '378b243e220ca493'; +create user invalidmysql57auth@localhost identified via 'mysql_native_password' using '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE'; + --sorted_result select user, host, password, plugin, authentication_string from mysql.user where user != 'root'; @@ -131,6 +133,9 @@ select current_user(); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT --error ER_ACCESS_DENIED_ERROR --connect(con,localhost,invalidpassnat,invalid,) +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error ER_ACCESS_DENIED_ERROR +--connect(con,localhost,invalidmysql57auth,invalid,) --connect(con,localhost,oldauth,test2,) select current_user(); @@ -144,7 +149,7 @@ select current_user(); --connection default drop user natauth@localhost, newpass@localhost, newpassnat@localhost; -drop user invalidauth@localhost, invalidpass@localhost, invalidpassnat@localhost; +drop user invalidauth@localhost, invalidpass@localhost, invalidpassnat@localhost,invalidmysql57auth@localhost; drop user oldauth@localhost, oldpass@localhost, oldpassold@localhost; set global secure_auth=default; diff --git a/mysql-test/main/type_year.result b/mysql-test/main/type_year.result index 4fb38bdf798..a3918580e1f 100644 --- a/mysql-test/main/type_year.result +++ b/mysql-test/main/type_year.result @@ -1,4 +1,3 @@ -drop table if exists t1; create table t1 (y year,y2 year(2)); Warnings: Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead @@ -28,6 +27,9 @@ y y2 2001 01 2069 69 drop table t1; +# +# Bug 2335 +# create table t1 (y year); insert ignore into t1 values (now()); Warnings: @@ -36,6 +38,9 @@ select if(y = now(), 1, 0) from t1; if(y = now(), 1, 0) 1 drop table t1; +# +# Bug #27176: Assigning a string to an year column has unexpected results +# create table t1(a year); insert into t1 values (2000.5), ('2000.5'), ('2001a'), ('2.001E3'); Warnings: @@ -47,7 +52,9 @@ a 2001 2001 drop table t1; -End of 5.0 tests +# +# End of 5.0 tests +# # # Bug #49480: WHERE using YEAR columns returns unexpected results # @@ -373,7 +380,8 @@ Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Pleas Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead DROP TABLE t1; # -End of 5.1 tests +# End of 5.1 tests +# create function y2k() returns int deterministic return 2000; create table t1 (a year(2), b int); Warnings: @@ -456,9 +464,6 @@ DROP TABLE t1; # End of 10.1 tests # # -# Start of 10.2 tests -# -# # MDEV-9392 Copying from DECIMAL to YEAR is not consistent about warnings # CREATE TABLE t1 (a YEAR); @@ -500,6 +505,64 @@ Warnings: Warning 1264 Out of range value for column 'a' at row 1 DROP TABLE t1; # +# Various widths of the YEAR +# +create or replace table t1 (a YEAR(0)); +Warnings: +Note 1287 'YEAR(0)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` year(4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create or replace table t1 (a YEAR(1)); +Warnings: +Note 1287 'YEAR(1)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` year(4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create or replace table t1 (a YEAR(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` year(2) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create or replace table t1 (a YEAR(3)); +Warnings: +Note 1287 'YEAR(3)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` year(4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create or replace table t1 (a YEAR(4)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` year(4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create or replace table t1 (a YEAR(5)); +Warnings: +Note 1287 'YEAR(5)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` year(4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create or replace table t1 (a YEAR(100)); +Warnings: +Note 1287 'YEAR(100)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` year(4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/type_year.test b/mysql-test/main/type_year.test index 1eb528d7af3..0f1f49be5d0 100644 --- a/mysql-test/main/type_year.test +++ b/mysql-test/main/type_year.test @@ -1,9 +1,6 @@ # # Test year # ---disable_warnings -drop table if exists t1; ---enable_warnings create table t1 (y year,y2 year(2)); insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69); @@ -12,24 +9,27 @@ select * from t1 order by y; select * from t1 order by y2; drop table t1; -# -# Bug 2335 -# +--echo # +--echo # Bug 2335 +--echo # create table t1 (y year); insert ignore into t1 values (now()); select if(y = now(), 1, 0) from t1; drop table t1; -# -# Bug #27176: Assigning a string to an year column has unexpected results -# +--echo # +--echo # Bug #27176: Assigning a string to an year column has unexpected results +--echo # + create table t1(a year); insert into t1 values (2000.5), ('2000.5'), ('2001a'), ('2.001E3'); select * from t1; drop table t1; ---echo End of 5.0 tests +--echo # +--echo # End of 5.0 tests +--echo # --echo # --echo # Bug #49480: WHERE using YEAR columns returns unexpected results @@ -169,8 +169,9 @@ ALTER TABLE t1 MODIFY COLUMN c2 YEAR(2); DROP TABLE t1; --echo # +--echo # End of 5.1 tests +--echo # ---echo End of 5.1 tests # # fun with convert_const_to_int # in some cases 00 is equal to 2000, in others it is not. @@ -230,10 +231,6 @@ DROP TABLE t1; --echo # --echo # ---echo # Start of 10.2 tests ---echo # - ---echo # --echo # MDEV-9392 Copying from DECIMAL to YEAR is not consistent about warnings --echo # CREATE TABLE t1 (a YEAR); @@ -268,6 +265,17 @@ SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR ALTER TABLE t1 MODIFY a YEAR; DROP TABLE t1; +--echo # +--echo # Various widths of the YEAR +--echo # +create or replace table t1 (a YEAR(0)); SHOW CREATE TABLE t1; +create or replace table t1 (a YEAR(1)); SHOW CREATE TABLE t1; +create or replace table t1 (a YEAR(2)); SHOW CREATE TABLE t1; +create or replace table t1 (a YEAR(3)); SHOW CREATE TABLE t1; +create or replace table t1 (a YEAR(4)); SHOW CREATE TABLE t1; +create or replace table t1 (a YEAR(5)); SHOW CREATE TABLE t1; +create or replace table t1 (a YEAR(100)); SHOW CREATE TABLE t1; +drop table t1; --echo # --echo # End of 10.2 tests diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 1891e3c44f7..e007e8a07e9 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6726,6 +6726,41 @@ DROP PROCEDURE sp; DROP VIEW v1; DROP TABLE t1; # +# MDEV-24314: create view with derived table without default database +# +drop database test; +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3),(7),(1); +create view db1.v1 as select * from (select * from db1.t1) t; +show create view db1.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS select `t`.`a` AS `a` from (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`) `t` latin1 latin1_swedish_ci +select * from db1.v1; +a +3 +7 +1 +drop view db1.v1; +prepare stmt from " +create view db1.v1 as select * from (select * from db1.t1) t; +"; +execute stmt; +deallocate prepare stmt; +show create view db1.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS select `t`.`a` AS `a` from (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`) `t` latin1 latin1_swedish_ci +select * from db1.v1; +a +3 +7 +1 +drop view db1.v1; +drop table db1.t1; +drop database db1; +create database test; +use test; +# # End of 10.2 tests # # diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 9b2baa69e8c..40cc05fd9c2 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6433,6 +6433,36 @@ DROP VIEW v1; DROP TABLE t1; --echo # +--echo # MDEV-24314: create view with derived table without default database +--echo # + +drop database test; + +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3),(7),(1); + +create view db1.v1 as select * from (select * from db1.t1) t; +show create view db1.v1; +select * from db1.v1; +drop view db1.v1; + +prepare stmt from " +create view db1.v1 as select * from (select * from db1.t1) t; +"; +execute stmt; +deallocate prepare stmt; +show create view db1.v1; +select * from db1.v1; +drop view db1.v1; + +drop table db1.t1; +drop database db1; + +create database test; +use test; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index b2f948fe31d..726712dbd2d 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -3259,6 +3259,7 @@ sub mysql_install_db { mtr_add_arg($args, "--tmpdir=%s", "$opt_vardir/tmp/"); mtr_add_arg($args, "--core-file"); mtr_add_arg($args, "--console"); + mtr_add_arg($args, "--character-set-server=latin1"); if ( $opt_debug ) { diff --git a/mysql-test/suite/binlog/disabled.def b/mysql-test/suite/binlog/disabled.def index 424e5549541..888298bbb09 100644 --- a/mysql-test/suite/binlog/disabled.def +++ b/mysql-test/suite/binlog/disabled.def @@ -9,5 +9,3 @@ # Do not use any TAB characters for whitespace. # ############################################################################## -binlog_truncate_innodb : BUG#11764459 2010-10-20 anitha Originally disabled due to BUG#42643. Product bug fixed, but test changes needed -binlog_spurious_ddl_errors : BUG#11761680 2013-01-18 astha Fixed on mysql-5.6 and trunk diff --git a/mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result b/mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result index 1a81eee1a58..798bd8ab853 100644 --- a/mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result +++ b/mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result @@ -1,5 +1,5 @@ SET @old_binlog_format= @@global.binlog_format; -INSTALL PLUGIN example SONAME 'ha_example.so'; +INSTALL PLUGIN example SONAME 'ha_example'; ################################################################################ # Verifies if ER_BINLOG_STMT_MODE_AND_ROW_ENGINE happens by setting the binlog # format to STATEMENT and the transaction isolation level to READ COMMITTED as @@ -18,7 +18,7 @@ ALTER TABLE t_row ADD COLUMN b INT; CREATE TRIGGER trig_row BEFORE INSERT ON t_row FOR EACH ROW INSERT INTO t_stmt VALUES (1); CREATE INDEX i ON t_row(a); CREATE TABLE t_row_new ENGINE = InnoDB SELECT * FROM t_row; -ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. +ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. DROP TABLE t_row; @@ -36,12 +36,11 @@ DROP TABLE t_row; SET binlog_format = ROW; CREATE TABLE t_stmt (a VARCHAR(100)) ENGINE = EXAMPLE; ALTER TABLE t_stmt ADD COLUMN b INT; -ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE' CREATE TRIGGER trig_stmt BEFORE INSERT ON t_stmt FOR EACH ROW INSERT INTO t_stmt VALUES (1); CREATE INDEX i ON t_stmt(a); ERROR 42000: Too many key parts specified; max 0 parts allowed CREATE TABLE t_stmt_new ENGINE = EXAMPLE SELECT * FROM t_stmt; -ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = ROW and at least one table uses a storage engine limited to statement-based logging. +ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = ROW and at least one table uses a storage engine limited to statement-based logging DROP TABLE t_stmt; diff --git a/mysql-test/suite/binlog/r/binlog_truncate_innodb.result b/mysql-test/suite/binlog/r/binlog_truncate_innodb.result index 8beeeb1a428..87ce8e30dee 100644 --- a/mysql-test/suite/binlog/r/binlog_truncate_innodb.result +++ b/mysql-test/suite/binlog/r/binlog_truncate_innodb.result @@ -7,9 +7,11 @@ INSERT INTO t2 VALUES (1),(2),(3); **** Truncate of empty table shall be logged TRUNCATE TABLE t1; TRUNCATE TABLE t2; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2 DROP TABLE t1,t2; # @@ -18,18 +20,17 @@ DROP TABLE t1,t2; CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2); -# Connection: default BEGIN; INSERT INTO t2 SELECT * FROM t1; -# Connection: truncate +connect truncate,localhost,root,,; TRUNCATE TABLE t1; -# Connection: default +connection default; INSERT INTO t2 SELECT * FROM t1; SELECT COUNT(*) FROM t2; COUNT(*) 4 COMMIT; -# Connection: truncate +connection truncate; # Reaping TRUNCATE TABLE SELECT COUNT(*) FROM t1; COUNT(*) @@ -37,16 +38,20 @@ COUNT(*) SELECT COUNT(*) FROM t2; COUNT(*) 4 -# Connection: default -show binlog events from <binlog_start>; +connection default; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +disconnect truncate; DROP TABLE t1,t2; # Even though the isolation level might be permissive, truncate # table follows a stricter isolation as its locking is based on @@ -59,9 +64,11 @@ SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; TRUNCATE TABLE t1; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; TRUNCATE TABLE t2; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2 DROP TABLE t1,t2; # @@ -71,18 +78,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2); SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -# Connection: default BEGIN; INSERT INTO t2 SELECT * FROM t1; -# Connection: truncate +connect truncate,localhost,root,,; TRUNCATE TABLE t1; -# Connection: default +connection default; INSERT INTO t2 SELECT * FROM t1; SELECT COUNT(*) FROM t2; COUNT(*) 4 COMMIT; -# Connection: truncate +connection truncate; # Reaping TRUNCATE TABLE SELECT COUNT(*) FROM t1; COUNT(*) @@ -90,16 +96,20 @@ COUNT(*) SELECT COUNT(*) FROM t2; COUNT(*) 4 -# Connection: default -show binlog events from <binlog_start>; +connection default; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +disconnect truncate; DROP TABLE t1,t2; CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; @@ -109,9 +119,11 @@ SET TRANSACTION ISOLATION LEVEL READ COMMITTED; TRUNCATE TABLE t1; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; TRUNCATE TABLE t2; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2 DROP TABLE t1,t2; # @@ -121,18 +133,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2); SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -# Connection: default BEGIN; INSERT INTO t2 SELECT * FROM t1; -# Connection: truncate +connect truncate,localhost,root,,; TRUNCATE TABLE t1; -# Connection: default +connection default; INSERT INTO t2 SELECT * FROM t1; SELECT COUNT(*) FROM t2; COUNT(*) 4 COMMIT; -# Connection: truncate +connection truncate; # Reaping TRUNCATE TABLE SELECT COUNT(*) FROM t1; COUNT(*) @@ -140,16 +151,20 @@ COUNT(*) SELECT COUNT(*) FROM t2; COUNT(*) 4 -# Connection: default -show binlog events from <binlog_start>; +connection default; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +disconnect truncate; DROP TABLE t1,t2; CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; @@ -159,9 +174,11 @@ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; TRUNCATE TABLE t1; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; TRUNCATE TABLE t2; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2 DROP TABLE t1,t2; # @@ -171,18 +188,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2); SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -# Connection: default BEGIN; INSERT INTO t2 SELECT * FROM t1; -# Connection: truncate +connect truncate,localhost,root,,; TRUNCATE TABLE t1; -# Connection: default +connection default; INSERT INTO t2 SELECT * FROM t1; SELECT COUNT(*) FROM t2; COUNT(*) 4 COMMIT; -# Connection: truncate +connection truncate; # Reaping TRUNCATE TABLE SELECT COUNT(*) FROM t1; COUNT(*) @@ -190,16 +206,20 @@ COUNT(*) SELECT COUNT(*) FROM t2; COUNT(*) 4 -# Connection: default -show binlog events from <binlog_start>; +connection default; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +disconnect truncate; DROP TABLE t1,t2; CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; @@ -209,9 +229,11 @@ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; TRUNCATE TABLE t1; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; TRUNCATE TABLE t2; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2 DROP TABLE t1,t2; # @@ -221,18 +243,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2); SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -# Connection: default BEGIN; INSERT INTO t2 SELECT * FROM t1; -# Connection: truncate +connect truncate,localhost,root,,; TRUNCATE TABLE t1; -# Connection: default +connection default; INSERT INTO t2 SELECT * FROM t1; SELECT COUNT(*) FROM t2; COUNT(*) 4 COMMIT; -# Connection: truncate +connection truncate; # Reaping TRUNCATE TABLE SELECT COUNT(*) FROM t1; COUNT(*) @@ -240,16 +261,20 @@ COUNT(*) SELECT COUNT(*) FROM t2; COUNT(*) 4 -# Connection: default -show binlog events from <binlog_start>; +connection default; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +disconnect truncate; DROP TABLE t1,t2; SET BINLOG_FORMAT=STATEMENT; RESET MASTER; @@ -261,9 +286,11 @@ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; TRUNCATE TABLE t1; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; TRUNCATE TABLE t2; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2 DROP TABLE t1,t2; # @@ -273,18 +300,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2); SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -# Connection: default BEGIN; INSERT INTO t2 SELECT * FROM t1; -# Connection: truncate +connect truncate,localhost,root,,; TRUNCATE TABLE t1; -# Connection: default +connection default; INSERT INTO t2 SELECT * FROM t1; SELECT COUNT(*) FROM t2; COUNT(*) 4 COMMIT; -# Connection: truncate +connection truncate; # Reaping TRUNCATE TABLE SELECT COUNT(*) FROM t1; COUNT(*) @@ -292,14 +318,16 @@ COUNT(*) SELECT COUNT(*) FROM t2; COUNT(*) 4 -# Connection: default -show binlog events from <binlog_start>; +connection default; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +disconnect truncate; DROP TABLE t1,t2; # Truncate is not supported for SBR if the isolation level is # READ UNCOMMITTED or READ COMMITTED. These specific isolation @@ -312,9 +340,11 @@ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; TRUNCATE TABLE t1; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; TRUNCATE TABLE t2; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2 DROP TABLE t1,t2; # @@ -324,18 +354,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2); SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -# Connection: default BEGIN; INSERT INTO t2 SELECT * FROM t1; -# Connection: truncate +connect truncate,localhost,root,,; TRUNCATE TABLE t1; -# Connection: default +connection default; INSERT INTO t2 SELECT * FROM t1; SELECT COUNT(*) FROM t2; COUNT(*) 4 COMMIT; -# Connection: truncate +connection truncate; # Reaping TRUNCATE TABLE SELECT COUNT(*) FROM t1; COUNT(*) @@ -343,14 +372,16 @@ COUNT(*) SELECT COUNT(*) FROM t2; COUNT(*) 4 -# Connection: default -show binlog events from <binlog_start>; +connection default; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +disconnect truncate; DROP TABLE t1,t2; CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; @@ -360,9 +391,11 @@ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; TRUNCATE TABLE t1; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; TRUNCATE TABLE t2; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2 DROP TABLE t1,t2; # @@ -372,18 +405,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2); SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -# Connection: default BEGIN; INSERT INTO t2 SELECT * FROM t1; -# Connection: truncate +connect truncate,localhost,root,,; TRUNCATE TABLE t1; -# Connection: default +connection default; INSERT INTO t2 SELECT * FROM t1; SELECT COUNT(*) FROM t2; COUNT(*) 4 COMMIT; -# Connection: truncate +connection truncate; # Reaping TRUNCATE TABLE SELECT COUNT(*) FROM t1; COUNT(*) @@ -391,13 +423,16 @@ COUNT(*) SELECT COUNT(*) FROM t2; COUNT(*) 4 -# Connection: default -show binlog events from <binlog_start>; +connection default; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1 master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 +disconnect truncate; DROP TABLE t1,t2; -SET BINLOG_FORMAT=@old_binlog_format; +SET @@global.binlog_format = @old_binlog_format; +SET @@session.binlog_format = @old_binlog_format; diff --git a/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt b/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt deleted file mode 100644 index 627becdbfb5..00000000000 --- a/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb diff --git a/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test b/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test index e64e7838a31..29a860764a9 100644 --- a/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test +++ b/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test @@ -71,7 +71,6 @@ DROP TABLE t_row; SET binlog_format = ROW; CREATE TABLE t_stmt (a VARCHAR(100)) ENGINE = EXAMPLE; ---error ER_NOT_SUPPORTED_YET ALTER TABLE t_stmt ADD COLUMN b INT; CREATE TRIGGER trig_stmt BEFORE INSERT ON t_stmt FOR EACH ROW INSERT INTO t_stmt VALUES (1); diff --git a/mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt b/mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt deleted file mode 100644 index 69cc489a969..00000000000 --- a/mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt +++ /dev/null @@ -1 +0,0 @@ ---loose-innodb
\ No newline at end of file diff --git a/mysql-test/suite/binlog/t/binlog_truncate_innodb.test b/mysql-test/suite/binlog/t/binlog_truncate_innodb.test index 54a32b96ef1..511b82bd717 100644 --- a/mysql-test/suite/binlog/t/binlog_truncate_innodb.test +++ b/mysql-test/suite/binlog/t/binlog_truncate_innodb.test @@ -41,4 +41,5 @@ source include/binlog_truncate.test; let $before_truncate = SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; source include/binlog_truncate.test; -SET BINLOG_FORMAT=@old_binlog_format; +SET @@global.binlog_format = @old_binlog_format; +SET @@session.binlog_format = @old_binlog_format;
\ No newline at end of file diff --git a/mysql-test/suite/binlog_encryption/rpl_semi_sync.result b/mysql-test/suite/binlog_encryption/rpl_semi_sync.result index 106efb555d3..d18bd1efda7 100644 --- a/mysql-test/suite/binlog_encryption/rpl_semi_sync.result +++ b/mysql-test/suite/binlog_encryption/rpl_semi_sync.result @@ -164,20 +164,15 @@ connection slave; connection slave; include/stop_slave.inc connection master; +include/kill_binlog_dump_threads.inc set global rpl_semi_sync_master_timeout= 5000; [ master status should be ON ] -show status like 'Rpl_semi_sync_master_status'; -Variable_name Value -Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 14 -show status like 'Rpl_semi_sync_master_clients'; -Variable_name Value -Rpl_semi_sync_master_clients 0 [ semi-sync replication of these transactions will fail ] insert into t1 values (500); [ master status should be OFF ] @@ -235,9 +230,6 @@ max(a) 500 connection master; [ master status should be ON again after slave catches up ] -show status like 'Rpl_semi_sync_master_status'; -Variable_name Value -Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 12 @@ -304,8 +296,6 @@ connection master; create table t1 (a int) engine = ENGINE_TYPE; drop table t1; connection slave; -show status like 'Rpl_relay%'; -Variable_name Value [ test reset master ] connection master; reset master; @@ -321,7 +311,7 @@ Rpl_semi_sync_master_yes_tx 0 connection slave; include/stop_slave.inc reset slave; -connection master; +include/kill_binlog_dump_threads.inc connection slave; include/start_slave.inc connection master; @@ -353,6 +343,7 @@ include/stop_slave.inc reset slave; connection master; reset master; +include/kill_binlog_dump_threads.inc set sql_log_bin=0; grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password'; flush privileges; @@ -403,10 +394,8 @@ SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status OFF connection master; +include/kill_binlog_dump_threads.inc [ Semi-sync status on master should be ON ] -show status like 'Rpl_semi_sync_master_clients'; -Variable_name Value -Rpl_semi_sync_master_clients 0 show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON diff --git a/mysql-test/suite/federated/federatedx.result b/mysql-test/suite/federated/federatedx.result index 8345f56dba9..2c21e31afe9 100644 --- a/mysql-test/suite/federated/federatedx.result +++ b/mysql-test/suite/federated/federatedx.result @@ -2284,6 +2284,32 @@ connection master; CREATE TABLE t1 (a INT) ENGINE=FEDERATED CONNECTION='mysql://@127.0.0.1:SLAVE_PORT/federated/t1'; ERROR HY000: Can't create federated table. Foreign data src error: database: 'federated' username: '' hostname: '127.0.0.1' # +# MDEV-17573 Assertion in federatedx on multi-update +# +create table t1 ( +x int, +d datetime); +create table t1f engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t1'; +create table t2 ( +x int, y int, +d datetime); +create table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t2'; +create table t3 ( +x int, y int, z int, +d datetime); +create table t3f engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t3'; +insert into t1 values (1, "1990-01-01 00:00"); +insert into t1 values (1, "1991-01-01 11:11"); +insert into t2 values (2, 2, "1992-02-02 22:22"); +insert into t3 values (3, 3, 3, "1993-03-03 23:33"); +update t1f, t2f, t3f set t1f.x= 11, t2f.y= 22, t3f.z= 33; +drop table t1f; +drop table t2f; +drop table t3f; +drop table t1; +drop table t2; +drop table t3; +# # MDEV-21049 Segfault in create federatedx table with empty hostname # connection master; diff --git a/mysql-test/suite/federated/federatedx.test b/mysql-test/suite/federated/federatedx.test index fcc0178c024..51d34298626 100644 --- a/mysql-test/suite/federated/federatedx.test +++ b/mysql-test/suite/federated/federatedx.test @@ -2011,6 +2011,35 @@ connection master; eval CREATE TABLE t1 (a INT) ENGINE=FEDERATED CONNECTION='mysql://@127.0.0.1:$SLAVE_MYPORT/federated/t1'; --echo # +--echo # MDEV-17573 Assertion in federatedx on multi-update +--echo # +create table t1 ( + x int, + d datetime); +--replace_result $MASTER_MYPORT MASTER_MYPORT +eval create table t1f engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t1'; + +create table t2 ( + x int, y int, + d datetime); +--replace_result $MASTER_MYPORT MASTER_MYPORT +eval create table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t2'; + +create table t3 ( + x int, y int, z int, + d datetime); +--replace_result $MASTER_MYPORT MASTER_MYPORT +eval create table t3f engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t3'; + +insert into t1 values (1, "1990-01-01 00:00"); +insert into t1 values (1, "1991-01-01 11:11"); +insert into t2 values (2, 2, "1992-02-02 22:22"); +insert into t3 values (3, 3, 3, "1993-03-03 23:33"); +update t1f, t2f, t3f set t1f.x= 11, t2f.y= 22, t3f.z= 33; + +drop table t1f; drop table t2f; drop table t3f; drop table t1; drop table t2; drop table t3; + +--echo # --echo # MDEV-21049 Segfault in create federatedx table with empty hostname --echo # connection master; diff --git a/mysql-test/suite/funcs_1/r/is_check_constraints.result b/mysql-test/suite/funcs_1/r/is_check_constraints.result index 37fd191d979..2436be8142f 100644 --- a/mysql-test/suite/funcs_1/r/is_check_constraints.result +++ b/mysql-test/suite/funcs_1/r/is_check_constraints.result @@ -90,7 +90,9 @@ CREATE TABLE t3 ( a int, b int check (b>0), # field constraint named 'b' -CONSTRAINT b check (b>10) # table constraint +CONSTRAINT b check (b>10), # table constraint +# `CHECK_CLAUSE` should allow more then `var(64)` constraints +CONSTRAINT b1 check (b<123456789012345678901234567890123456789012345678901234567890123456789) ) ENGINE=InnoDB; SELECT * from information_schema.check_constraints; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE @@ -104,6 +106,7 @@ def foo t2 CHK_dates `start_date` is null def foo t2 name char_length(`name`) > 2 def foo t3 b `b` > 0 def foo t3 b `b` > 10 +def foo t3 b1 `b` < 123456789012345678901234567890123456789012345678901234567890123456789 disconnect con1; CONNECT con2, localhost, boo2,, test; SELECT * from information_schema.check_constraints; diff --git a/mysql-test/suite/funcs_1/r/is_columns_is.result b/mysql-test/suite/funcs_1/r/is_columns_is.result index cd548f426e1..cdacf7a3d1e 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is.result @@ -24,7 +24,7 @@ def information_schema CHARACTER_SETS CHARACTER_SET_NAME 1 '' NO varchar 32 96 N def information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME 2 '' NO varchar 32 96 NULL NULL NULL utf8 utf8_general_ci varchar(32) select NEVER NULL def information_schema CHARACTER_SETS DESCRIPTION 3 '' NO varchar 60 180 NULL NULL NULL utf8 utf8_general_ci varchar(60) select NEVER NULL def information_schema CHARACTER_SETS MAXLEN 4 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(3) select NEVER NULL -def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL +def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO longtext 4294967295 4294967295 NULL NULL NULL utf8 utf8_general_ci longtext select NEVER NULL def information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG 1 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) select NEVER NULL def information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME 4 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL def information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 2 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL @@ -572,7 +572,7 @@ NULL information_schema CHARACTER_SETS MAXLEN bigint NULL NULL NULL NULL bigint( 3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema CHECK_CONSTRAINTS TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) -3.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE varchar 64 192 utf8 utf8_general_ci varchar(64) +1.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE longtext 4294967295 4294967295 utf8 utf8_general_ci longtext 3.0000 information_schema CLIENT_STATISTICS CLIENT varchar 64 192 utf8 utf8_general_ci varchar(64) NULL information_schema CLIENT_STATISTICS TOTAL_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) NULL information_schema CLIENT_STATISTICS CONCURRENT_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) diff --git a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result index b86897af8a4..5c941d279e3 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result @@ -24,7 +24,7 @@ def information_schema CHARACTER_SETS CHARACTER_SET_NAME 1 '' NO varchar 32 96 N def information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME 2 '' NO varchar 32 96 NULL NULL NULL utf8 utf8_general_ci varchar(32) NEVER NULL def information_schema CHARACTER_SETS DESCRIPTION 3 '' NO varchar 60 180 NULL NULL NULL utf8 utf8_general_ci varchar(60) NEVER NULL def information_schema CHARACTER_SETS MAXLEN 4 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(3) NEVER NULL -def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL +def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO longtext 4294967295 4294967295 NULL NULL NULL utf8 utf8_general_ci longtext NEVER NULL def information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG 1 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) NEVER NULL def information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME 4 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL def information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 2 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL @@ -572,7 +572,7 @@ NULL information_schema CHARACTER_SETS MAXLEN bigint NULL NULL NULL NULL bigint( 3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema CHECK_CONSTRAINTS TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) -3.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE varchar 64 192 utf8 utf8_general_ci varchar(64) +1.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE longtext 4294967295 4294967295 utf8 utf8_general_ci longtext 3.0000 information_schema CLIENT_STATISTICS CLIENT varchar 64 192 utf8 utf8_general_ci varchar(64) NULL information_schema CLIENT_STATISTICS TOTAL_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) NULL information_schema CLIENT_STATISTICS CONCURRENT_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) diff --git a/mysql-test/suite/funcs_1/r/is_tables_is.result b/mysql-test/suite/funcs_1/r/is_tables_is.result index 9af3aa860a0..65dae5a0d6b 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is.result @@ -91,9 +91,9 @@ TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME CHECK_CONSTRAINTS TABLE_TYPE SYSTEM VIEW -ENGINE MEMORY +ENGINE MYISAM_OR_MARIA VERSION 11 -ROW_FORMAT Fixed +ROW_FORMAT DYNAMIC_OR_PAGE TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# @@ -1157,9 +1157,9 @@ TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME CHECK_CONSTRAINTS TABLE_TYPE SYSTEM VIEW -ENGINE MEMORY +ENGINE MYISAM_OR_MARIA VERSION 11 -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_is_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result index 9af3aa860a0..65dae5a0d6b 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result @@ -91,9 +91,9 @@ TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME CHECK_CONSTRAINTS TABLE_TYPE SYSTEM VIEW -ENGINE MEMORY +ENGINE MYISAM_OR_MARIA VERSION 11 -ROW_FORMAT Fixed +ROW_FORMAT DYNAMIC_OR_PAGE TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# @@ -1157,9 +1157,9 @@ TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME CHECK_CONSTRAINTS TABLE_TYPE SYSTEM VIEW -ENGINE MEMORY +ENGINE MYISAM_OR_MARIA VERSION 11 -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/t/is_check_constraints.test b/mysql-test/suite/funcs_1/t/is_check_constraints.test index b539de67f73..dbd286e6239 100644 --- a/mysql-test/suite/funcs_1/t/is_check_constraints.test +++ b/mysql-test/suite/funcs_1/t/is_check_constraints.test @@ -69,7 +69,9 @@ CREATE TABLE t3 ( a int, b int check (b>0), # field constraint named 'b' -CONSTRAINT b check (b>10) # table constraint +CONSTRAINT b check (b>10), # table constraint +# `CHECK_CLAUSE` should allow more then `var(64)` constraints +CONSTRAINT b1 check (b<123456789012345678901234567890123456789012345678901234567890123456789) ) ENGINE=InnoDB; --sorted_result SELECT * from information_schema.check_constraints; diff --git a/mysql-test/suite/galera/r/MDEV-24327.result b/mysql-test/suite/galera/r/MDEV-24327.result new file mode 100644 index 00000000000..d309749efa4 --- /dev/null +++ b/mysql-test/suite/galera/r/MDEV-24327.result @@ -0,0 +1,35 @@ +connection node_2; +connection node_1; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1)); +INSERT INTO t1 VALUES (1, 'f'); +INSERT INTO t1 VALUES (2, 'g'); +connection node_1; +SET AUTOCOMMIT=ON; +START TRANSACTION; +UPDATE t1 SET f2 = '1' WHERE f1 = 1; +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +SET SESSION wsrep_sync_wait=0; +connection node_1a; +SET SESSION wsrep_on = 0; +SET SESSION wsrep_on = 1; +SET GLOBAL wsrep_provider_options = 'dbug='; +SET GLOBAL wsrep_provider_options = 'dbug=d,commit_monitor_master_enter_sync'; +connection node_1; +COMMIT; +connection node_1a; +SET SESSION wsrep_on = 0; +SET SESSION wsrep_on = 1; +SET GLOBAL wsrep_provider_options = 'dbug='; +connection node_2; +UPDATE t1 SET f2 = '2' WHERE f1 = 2; +connection node_1a; +SET GLOBAL wsrep_provider_options = 'signal=commit_monitor_master_enter_sync'; +SET GLOBAL wsrep_provider_options = 'dbug='; +connection node_1; +SELECT * FROM t1; +f1 f2 +1 1 +2 2 +"node 1 is complete now" +connection node_2; +DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/MENT-1047.result b/mysql-test/suite/galera/r/MENT-1047.result new file mode 100644 index 00000000000..76d58f6d52f --- /dev/null +++ b/mysql-test/suite/galera/r/MENT-1047.result @@ -0,0 +1,4 @@ +connection node_2; +connection node_1; +XA START 'trx'; +ERROR 42000: This version of MariaDB doesn't yet support 'XA transactions with Galera replication' diff --git a/mysql-test/suite/galera/r/galera#500.result b/mysql-test/suite/galera/r/galera#500.result index a5ab0b19718..d983e844d76 100644 --- a/mysql-test/suite/galera/r/galera#500.result +++ b/mysql-test/suite/galera/r/galera#500.result @@ -1,3 +1,5 @@ +connection node_1; +connection node_2; connection node_2; connection node_1; connection node_1; diff --git a/mysql-test/suite/galera/t/MDEV-24327.cnf b/mysql-test/suite/galera/t/MDEV-24327.cnf new file mode 100644 index 00000000000..390a9aab0f4 --- /dev/null +++ b/mysql-test/suite/galera/t/MDEV-24327.cnf @@ -0,0 +1,6 @@ +!include ../galera_2nodes.cnf + +[mysqld.1] +log-bin=mariadb-bin +log-slave-updates=OFF + diff --git a/mysql-test/suite/galera/t/MDEV-24327.test b/mysql-test/suite/galera/t/MDEV-24327.test new file mode 100644 index 00000000000..7aeffea11fa --- /dev/null +++ b/mysql-test/suite/galera/t/MDEV-24327.test @@ -0,0 +1,87 @@ +# +# MDEV-24327 wsrep XID checkpointing order violation with log_slave_updates=OFF +# +# Here we have configure two node cluster with --log-bin=ON and --log-slave_-updates=OFF +# +# a transaction in node executes so far that it has replicated and reached +# commit phase, We have sync point before entering commit order monitor and +# the transaction is parked there +# +# Then another transaction is executed in node 2, it replicates and commits in node 2 +# and is received and applied in node 1. After applying it will remain waiting for +# commit order monitor, as it has later seqno than the first transaction in node 1. +# +# control connection in node 1 waits to see the +# +# With the buggy version of MDEV-24327, the applier has however, already synced the +# wsrep XID checkpoint +# +# +# + +--source include/galera_cluster.inc +--source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/galera_have_debug_sync.inc + +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1)); +INSERT INTO t1 VALUES (1, 'f'); +INSERT INTO t1 VALUES (2, 'g'); + +--connection node_1 +SET AUTOCOMMIT=ON; +START TRANSACTION; + +UPDATE t1 SET f2 = '1' WHERE f1 = 1; + +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +SET SESSION wsrep_sync_wait=0; +--connection node_1a +--let $expected_wsrep_received = `SELECT VARIABLE_VALUE+1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_received'` +--source include/galera_wait_sync_point.inc +--source include/galera_clear_sync_point.inc + +# Block the commit, send the COMMIT and wait until it gets blocked + +--let $galera_sync_point = commit_monitor_master_enter_sync +--source include/galera_set_sync_point.inc + +--connection node_1 +--send COMMIT + +--connection node_1a + +# wait for the commit to block in sync point + +--let $galera_sync_point = commit_monitor_master_enter_sync +--source include/galera_wait_sync_point.inc +--source include/galera_clear_sync_point.inc + +# +# replicate non conflicting transaction from node 2 +# it will get later seqno and should sync XID checkpoint after transaction in node 1 +# +--connection node_2 +UPDATE t1 SET f2 = '2' WHERE f1 = 2; + +# +# wait until update from node 2 has been committed +# if XID checkpointing order was violated, node 1 would crash for assert +# + +--connection node_1a +--let $wait_condition = SELECT VARIABLE_VALUE = $expected_wsrep_received FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_received' +--source include/wait_condition.inc + +--let $galera_sync_point = commit_monitor_master_enter_sync +--source include/galera_signal_sync_point.inc +--source include/galera_clear_sync_point.inc + +--connection node_1 +--reap +SELECT * FROM t1; +--echo "node 1 is complete now" + + +--connection node_2 +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/MENT-1047.test b/mysql-test/suite/galera/t/MENT-1047.test new file mode 100644 index 00000000000..1431818f806 --- /dev/null +++ b/mysql-test/suite/galera/t/MENT-1047.test @@ -0,0 +1,7 @@ +# +# MENT-1047 - Reject XA with Galera replication +# +--source include/galera_cluster.inc + +--error ER_NOT_SUPPORTED_YET +XA START 'trx'; diff --git a/mysql-test/suite/galera/t/galera#500.test b/mysql-test/suite/galera/t/galera#500.test index 60f303b7103..471620b32c1 100644 --- a/mysql-test/suite/galera/t/galera#500.test +++ b/mysql-test/suite/galera/t/galera#500.test @@ -8,9 +8,10 @@ --source include/galera_cluster.inc --source include/galera_have_debug_sync.inc +# Save original auto_increment_offset values. --let $node_1=node_1 --let $node_2=node_2 ---source suite/galera/include/auto_increment_offset_save.inc +--source include/auto_increment_offset_save.inc # Force node_2 gcomm background thread to terminate via exception. --connection node_2 @@ -41,4 +42,4 @@ SET SESSION wsrep_on=0; --connection node_2 CALL mtr.add_suppression("WSREP: exception from gcomm, backend must be restarted: Gcomm backend termination was requested by setting gmcast.isolate=2."); ---source suite/galera/include/auto_increment_offset_restore.inc +--source include/auto_increment_offset_restore.inc diff --git a/mysql-test/suite/gcol/r/innodb_virtual_fk.result b/mysql-test/suite/gcol/r/innodb_virtual_fk.result index 50685e04a69..68601823e31 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_fk.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_fk.result @@ -790,3 +790,31 @@ t1 CREATE TABLE `t1` ( ALTER TABLE t1 DROP INDEX f1; ALTER TABLE t1 DROP f3; DROP TABLE t1; +# +# MDEV-24041 Generated column DELETE with FOREIGN KEY crash InnoDB +# +CREATE TABLE emails ( +id int, +PRIMARY KEY (id) +) ENGINE=InnoDB; +CREATE TABLE email_stats ( +id int, +email_id int, +date_sent char(4), +generated_email_id int as (email_id), +PRIMARY KEY (id), +KEY mautic_generated_sent_date_email_id (generated_email_id), +FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL +) ENGINE=InnoDB; +CREATE TABLE emails_metadata ( +email_id int, +PRIMARY KEY (email_id), +CONSTRAINT FK FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE CASCADE +) ENGINE=InnoDB; +INSERT INTO emails VALUES (1); +INSERT INTO email_stats (id, email_id, date_sent) VALUES (1,1,'Jan'); +INSERT INTO emails_metadata VALUES (1); +DELETE FROM emails; +DROP TABLE email_stats; +DROP TABLE emails_metadata; +DROP TABLE emails; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_fk.test b/mysql-test/suite/gcol/t/innodb_virtual_fk.test index 23d3ee97290..da20612f0a1 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_fk.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_fk.test @@ -649,3 +649,40 @@ SHOW CREATE TABLE t1; ALTER TABLE t1 DROP INDEX f1; ALTER TABLE t1 DROP f3; DROP TABLE t1; + +--echo # +--echo # MDEV-24041 Generated column DELETE with FOREIGN KEY crash InnoDB +--echo # + +CREATE TABLE emails ( + id int, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +CREATE TABLE email_stats ( + id int, + email_id int, + date_sent char(4), + generated_email_id int as (email_id), + PRIMARY KEY (id), + KEY mautic_generated_sent_date_email_id (generated_email_id), + FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL +) ENGINE=InnoDB; + + +CREATE TABLE emails_metadata ( + email_id int, + PRIMARY KEY (email_id), + CONSTRAINT FK FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE CASCADE +) ENGINE=InnoDB; + + +INSERT INTO emails VALUES (1); +INSERT INTO email_stats (id, email_id, date_sent) VALUES (1,1,'Jan'); +INSERT INTO emails_metadata VALUES (1); + +DELETE FROM emails; + +DROP TABLE email_stats; +DROP TABLE emails_metadata; +DROP TABLE emails; diff --git a/mysql-test/suite/innodb/r/innodb_multi_update.result b/mysql-test/suite/innodb/r/innodb_multi_update.result index 64f9ebc2fc2..93bd4e6716c 100644 --- a/mysql-test/suite/innodb/r/innodb_multi_update.result +++ b/mysql-test/suite/innodb/r/innodb_multi_update.result @@ -81,4 +81,5 @@ CREATE TABLE t1(f1 INT) ENGINE=INNODB; INSERT INTO t1 VALUES(1); UPDATE (SELECT ((SELECT 1 FROM t1), 1) FROM t1 WHERE (SELECT 1 FROM t1)) x, (SELECT 1) AS d SET d.f1 = 1; ERROR 21000: Operand should contain 1 column(s) +UPDATE (SELECT ((SELECT 1 FROM t1),1) = (1,1) FROM t1 WHERE (SELECT 1 FROM t1)) x, t1 AS d SET d.f1 = 1; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_stats.result b/mysql-test/suite/innodb/r/innodb_stats.result index eadce75318b..d2c3bd0127e 100644 --- a/mysql-test/suite/innodb/r/innodb_stats.result +++ b/mysql-test/suite/innodb/r/innodb_stats.result @@ -4,7 +4,6 @@ dummy INSERT, the table should be empty dummy INSERT, the table should be empty ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text -test.test_innodb_stats analyze status Engine-independent statistics collected test.test_innodb_stats analyze status OK SELECT stat_name, @@ -57,7 +56,6 @@ TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text -test.test_innodb_stats analyze status Engine-independent statistics collected test.test_innodb_stats analyze status OK SELECT stat_name, @@ -110,7 +108,6 @@ TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (1); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text -test.test_innodb_stats analyze status Engine-independent statistics collected test.test_innodb_stats analyze status OK SELECT stat_name, @@ -163,7 +160,6 @@ TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (1), (1); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text -test.test_innodb_stats analyze status Engine-independent statistics collected test.test_innodb_stats analyze status OK SELECT stat_name, @@ -216,7 +212,6 @@ TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text -test.test_innodb_stats analyze status Engine-independent statistics collected test.test_innodb_stats analyze status OK SELECT stat_name, @@ -269,7 +264,6 @@ TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (2); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text -test.test_innodb_stats analyze status Engine-independent statistics collected test.test_innodb_stats analyze status OK SELECT stat_name, @@ -322,7 +316,6 @@ TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (1), (2); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text -test.test_innodb_stats analyze status Engine-independent statistics collected test.test_innodb_stats analyze status OK SELECT stat_name, @@ -375,7 +368,6 @@ TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (2), (3); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text -test.test_innodb_stats analyze status Engine-independent statistics collected test.test_innodb_stats analyze status OK SELECT stat_name, @@ -428,7 +420,6 @@ TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (1), (2), (3), (3); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text -test.test_innodb_stats analyze status Engine-independent statistics collected test.test_innodb_stats analyze status OK SELECT stat_name, @@ -481,7 +472,6 @@ TRUNCATE TABLE test_innodb_stats; INSERT INTO test_innodb_stats (a) VALUES (1), (2), (3), (4), (5), (1), (2), (3), (4), (5); ANALYZE TABLE test_innodb_stats; Table Op Msg_type Msg_text -test.test_innodb_stats analyze status Engine-independent statistics collected test.test_innodb_stats analyze status OK SELECT stat_name, diff --git a/mysql-test/suite/innodb/t/innodb_multi_update.test b/mysql-test/suite/innodb/t/innodb_multi_update.test index 8d5283a9ed5..74a7aea7d13 100644 --- a/mysql-test/suite/innodb/t/innodb_multi_update.test +++ b/mysql-test/suite/innodb/t/innodb_multi_update.test @@ -35,4 +35,5 @@ CREATE TABLE t1(f1 INT) ENGINE=INNODB; INSERT INTO t1 VALUES(1); --error ER_OPERAND_COLUMNS UPDATE (SELECT ((SELECT 1 FROM t1), 1) FROM t1 WHERE (SELECT 1 FROM t1)) x, (SELECT 1) AS d SET d.f1 = 1; +UPDATE (SELECT ((SELECT 1 FROM t1),1) = (1,1) FROM t1 WHERE (SELECT 1 FROM t1)) x, t1 AS d SET d.f1 = 1; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_stats.test b/mysql-test/suite/innodb/t/innodb_stats.test index 09515ec9720..1a962e3568e 100644 --- a/mysql-test/suite/innodb/t/innodb_stats.test +++ b/mysql-test/suite/innodb/t/innodb_stats.test @@ -10,7 +10,7 @@ DROP TABLE IF EXISTS test_innodb_stats; set @save_use_stat_tables= @@use_stat_tables; -set @@use_stat_tables= COMPLEMENTARY; +set @@use_stat_tables= NEVER; CREATE TABLE test_innodb_stats ( a INT, diff --git a/mysql-test/suite/plugins/r/server_audit.result b/mysql-test/suite/plugins/r/server_audit.result index ab726a0e22f..6ded4f687a6 100644 --- a/mysql-test/suite/plugins/r/server_audit.result +++ b/mysql-test/suite/plugins/r/server_audit.result @@ -452,8 +452,8 @@ TIME,HOSTNAME,plug,localhost,ID,0,DISCONNECT,,,0 TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'GRANT PROXY ON plug_dest TO plug',0 -TIME,HOSTNAME,plug,localhost,ID,0,PROXY_CONNECT,test,`plug_dest`@`%`,0 TIME,HOSTNAME,plug,localhost,ID,0,CONNECT,test,,0 +TIME,HOSTNAME,plug,localhost,ID,0,PROXY_CONNECT,test,`plug_dest`@`%`,0 TIME,HOSTNAME,plug,localhost,ID,0,DISCONNECT,test,,0 TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, diff --git a/mysql-test/suite/rpl/include/rpl_semi_sync.inc b/mysql-test/suite/rpl/include/rpl_semi_sync.inc index 393b49372e1..c3cd918b5fc 100644 --- a/mysql-test/suite/rpl/include/rpl_semi_sync.inc +++ b/mysql-test/suite/rpl/include/rpl_semi_sync.inc @@ -9,7 +9,6 @@ source include/have_innodb.inc; source include/master-slave.inc; let $engine_type= InnoDB; -#let $engine_type= MyISAM; # Suppress warnings that might be generated during the test connection master; @@ -94,7 +93,6 @@ enable_query_log; echo [ status of semi-sync on master should be OFF ]; show status like 'Rpl_semi_sync_master_clients'; show status like 'Rpl_semi_sync_master_status'; ---replace_result 305 304 show status like 'Rpl_semi_sync_master_yes_tx'; # reset master to make sure the following test will start with a clean environment @@ -201,16 +199,23 @@ connection slave; source include/stop_slave.inc; connection master; +--source include/kill_binlog_dump_threads.inc set global rpl_semi_sync_master_timeout= 5000; # The first semi-sync check should be on because after slave stop, # there are no transactions on the master. echo [ master status should be ON ]; -show status like 'Rpl_semi_sync_master_status'; + +let $status_var= Rpl_semi_sync_master_status; +let $status_var_value= ON; +source include/wait_for_status_var.inc; + +let $status_var= Rpl_semi_sync_master_clients; +let $status_var_value= 0; +source include/wait_for_status_var.inc; + show status like 'Rpl_semi_sync_master_no_tx'; ---replace_result 305 304 show status like 'Rpl_semi_sync_master_yes_tx'; -show status like 'Rpl_semi_sync_master_clients'; echo [ semi-sync replication of these transactions will fail ]; insert into t1 values (500); @@ -225,7 +230,6 @@ source include/wait_for_status_var.inc; echo [ master status should be OFF ]; show status like 'Rpl_semi_sync_master_status'; show status like 'Rpl_semi_sync_master_no_tx'; ---replace_result 305 304 show status like 'Rpl_semi_sync_master_yes_tx'; # Semi-sync status on master is now OFF, so all these transactions @@ -246,7 +250,6 @@ insert into t1 values (100); echo [ master status should be OFF ]; show status like 'Rpl_semi_sync_master_status'; show status like 'Rpl_semi_sync_master_no_tx'; ---replace_result 305 304 show status like 'Rpl_semi_sync_master_yes_tx'; --echo # @@ -274,9 +277,11 @@ connection master; # The master semi-sync status should be on again after slave catches up. echo [ master status should be ON again after slave catches up ]; -show status like 'Rpl_semi_sync_master_status'; + +let $status_var= Rpl_semi_sync_master_status; +let $status_var_value= ON; +source include/wait_for_status_var.inc; show status like 'Rpl_semi_sync_master_no_tx'; ---replace_result 305 304 show status like 'Rpl_semi_sync_master_yes_tx'; show status like 'Rpl_semi_sync_master_clients'; @@ -332,11 +337,7 @@ replace_result $engine_type ENGINE_TYPE; eval create table t1 (a int) engine = $engine_type; drop table t1; -##show status like 'Rpl_semi_sync_master_status'; - sync_slave_with_master; ---replace_column 2 # -show status like 'Rpl_relay%'; echo [ test reset master ]; connection master; @@ -353,19 +354,7 @@ source include/stop_slave.inc; reset slave; # Kill the dump thread on master for previous slave connection and -# wait for it to exit -connection master; -let $_tid= `select id from information_schema.processlist where command = 'Binlog Dump' limit 1`; -if ($_tid) -{ - --replace_result $_tid _tid - eval kill query $_tid; - - # After dump thread exit, Rpl_semi_sync_master_clients will be 0 - let $status_var= Rpl_semi_sync_master_clients; - let $status_var_value= 0; - source include/wait_for_status_var.inc; -} +--source include/kill_binlog_dump_threads.inc connection slave; source include/start_slave.inc; @@ -404,17 +393,7 @@ connection master; reset master; # Kill the dump thread on master for previous slave connection and wait for it to exit -let $_tid= `select id from information_schema.processlist where command = 'Binlog Dump' limit 1`; -if ($_tid) -{ - --replace_result $_tid _tid - eval kill query $_tid; - - # After dump thread exit, Rpl_semi_sync_master_clients will be 0 - let $status_var= Rpl_semi_sync_master_clients; - let $status_var_value= 0; - source include/wait_for_status_var.inc; -} +--source include/kill_binlog_dump_threads.inc # Do not binlog the following statement because it will generate # different events for ROW and STATEMENT format @@ -459,21 +438,16 @@ SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; connection master; # Kill the dump thread on master for previous slave connection and wait for it to exit -let $_tid= `select id from information_schema.processlist where command = 'Binlog Dump' limit 1`; -if ($_tid) -{ - --replace_result $_tid _tid - eval kill query $_tid; - - # After dump thread exit, Rpl_semi_sync_master_clients will be 0 - let $status_var= Rpl_semi_sync_master_clients; - let $status_var_value= 0; - source include/wait_for_status_var.inc; -} +--source include/kill_binlog_dump_threads.inc echo [ Semi-sync status on master should be ON ]; -show status like 'Rpl_semi_sync_master_clients'; +let $status_var= Rpl_semi_sync_master_clients; +let $status_var_value= 0; +source include/wait_for_status_var.inc; show status like 'Rpl_semi_sync_master_status'; +let $status_var= Rpl_semi_sync_master_status; +let $status_var_value= ON; +source include/wait_for_status_var.inc; set global rpl_semi_sync_master_enabled= 0; connection slave; diff --git a/mysql-test/suite/rpl/r/rpl_drop_temp_table_invaid_lex.result b/mysql-test/suite/rpl/r/rpl_drop_temp_table_invalid_lex.result index 49e9195ed35..f3c4cb4def2 100644 --- a/mysql-test/suite/rpl/r/rpl_drop_temp_table_invaid_lex.result +++ b/mysql-test/suite/rpl/r/rpl_drop_temp_table_invalid_lex.result @@ -4,5 +4,8 @@ connect con1,localhost,root,,; CREATE TEMPORARY TABLE tmp (a INT); CREATE TABLE non_existing_db.t SELECT 1 AS b; disconnect con1; -connection default; +connection master; +connection slave; +connection slave; +include/assert.inc ["Slave_open_temp_tables count should be 0"] include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync.result b/mysql-test/suite/rpl/r/rpl_semi_sync.result index 106efb555d3..d18bd1efda7 100644 --- a/mysql-test/suite/rpl/r/rpl_semi_sync.result +++ b/mysql-test/suite/rpl/r/rpl_semi_sync.result @@ -164,20 +164,15 @@ connection slave; connection slave; include/stop_slave.inc connection master; +include/kill_binlog_dump_threads.inc set global rpl_semi_sync_master_timeout= 5000; [ master status should be ON ] -show status like 'Rpl_semi_sync_master_status'; -Variable_name Value -Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 14 -show status like 'Rpl_semi_sync_master_clients'; -Variable_name Value -Rpl_semi_sync_master_clients 0 [ semi-sync replication of these transactions will fail ] insert into t1 values (500); [ master status should be OFF ] @@ -235,9 +230,6 @@ max(a) 500 connection master; [ master status should be ON again after slave catches up ] -show status like 'Rpl_semi_sync_master_status'; -Variable_name Value -Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 12 @@ -304,8 +296,6 @@ connection master; create table t1 (a int) engine = ENGINE_TYPE; drop table t1; connection slave; -show status like 'Rpl_relay%'; -Variable_name Value [ test reset master ] connection master; reset master; @@ -321,7 +311,7 @@ Rpl_semi_sync_master_yes_tx 0 connection slave; include/stop_slave.inc reset slave; -connection master; +include/kill_binlog_dump_threads.inc connection slave; include/start_slave.inc connection master; @@ -353,6 +343,7 @@ include/stop_slave.inc reset slave; connection master; reset master; +include/kill_binlog_dump_threads.inc set sql_log_bin=0; grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password'; flush privileges; @@ -403,10 +394,8 @@ SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status OFF connection master; +include/kill_binlog_dump_threads.inc [ Semi-sync status on master should be ON ] -show status like 'Rpl_semi_sync_master_clients'; -Variable_name Value -Rpl_semi_sync_master_clients 0 show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync.result b/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync.result index c61340f3967..f2240817489 100644 --- a/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync.result +++ b/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync.result @@ -165,20 +165,15 @@ connection slave; connection slave; include/stop_slave.inc connection master; +include/kill_binlog_dump_threads.inc set global rpl_semi_sync_master_timeout= 5000; [ master status should be ON ] -show status like 'Rpl_semi_sync_master_status'; -Variable_name Value -Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 16 -show status like 'Rpl_semi_sync_master_clients'; -Variable_name Value -Rpl_semi_sync_master_clients 0 [ semi-sync replication of these transactions will fail ] insert into t1 values (500); [ master status should be OFF ] @@ -236,9 +231,6 @@ max(a) 500 connection master; [ master status should be ON again after slave catches up ] -show status like 'Rpl_semi_sync_master_status'; -Variable_name Value -Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 12 @@ -305,8 +297,6 @@ connection master; create table t1 (a int) engine = ENGINE_TYPE; drop table t1; connection slave; -show status like 'Rpl_relay%'; -Variable_name Value [ test reset master ] connection master; reset master; @@ -322,7 +312,7 @@ Rpl_semi_sync_master_yes_tx 0 connection slave; include/stop_slave.inc reset slave; -connection master; +include/kill_binlog_dump_threads.inc connection slave; include/start_slave.inc connection master; @@ -354,6 +344,7 @@ include/stop_slave.inc reset slave; connection master; reset master; +include/kill_binlog_dump_threads.inc set sql_log_bin=0; grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password'; flush privileges; @@ -404,10 +395,8 @@ SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status OFF connection master; +include/kill_binlog_dump_threads.inc [ Semi-sync status on master should be ON ] -show status like 'Rpl_semi_sync_master_clients'; -Variable_name Value -Rpl_semi_sync_master_clients 0 show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync_row.result b/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync_row.result index 6a23f24b66d..fcced801d65 100644 --- a/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync_row.result +++ b/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync_row.result @@ -165,20 +165,15 @@ connection slave; connection slave; include/stop_slave.inc connection master; +include/kill_binlog_dump_threads.inc set global rpl_semi_sync_master_timeout= 5000; [ master status should be ON ] -show status like 'Rpl_semi_sync_master_status'; -Variable_name Value -Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 14 -show status like 'Rpl_semi_sync_master_clients'; -Variable_name Value -Rpl_semi_sync_master_clients 0 [ semi-sync replication of these transactions will fail ] insert into t1 values (500); [ master status should be OFF ] @@ -236,9 +231,6 @@ max(a) 500 connection master; [ master status should be ON again after slave catches up ] -show status like 'Rpl_semi_sync_master_status'; -Variable_name Value -Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 12 @@ -305,8 +297,6 @@ connection master; create table t1 (a int) engine = ENGINE_TYPE; drop table t1; connection slave; -show status like 'Rpl_relay%'; -Variable_name Value [ test reset master ] connection master; reset master; @@ -322,7 +312,7 @@ Rpl_semi_sync_master_yes_tx 0 connection slave; include/stop_slave.inc reset slave; -connection master; +include/kill_binlog_dump_threads.inc connection slave; include/start_slave.inc connection master; @@ -354,6 +344,7 @@ include/stop_slave.inc reset slave; connection master; reset master; +include/kill_binlog_dump_threads.inc set sql_log_bin=0; grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password'; flush privileges; @@ -404,10 +395,8 @@ SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status OFF connection master; +include/kill_binlog_dump_threads.inc [ Semi-sync status on master should be ON ] -show status like 'Rpl_semi_sync_master_clients'; -Variable_name Value -Rpl_semi_sync_master_clients 0 show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON diff --git a/mysql-test/suite/rpl/t/rpl_drop_temp_table_invaid_lex.test b/mysql-test/suite/rpl/t/rpl_drop_temp_table_invalid_lex.test index 2d3031da544..475afef12fc 100644 --- a/mysql-test/suite/rpl/t/rpl_drop_temp_table_invaid_lex.test +++ b/mysql-test/suite/rpl/t/rpl_drop_temp_table_invalid_lex.test @@ -17,15 +17,25 @@ # MDEV-19716: ASAN use-after-poison in Query_log_event::Query_log_event / # THD::log_events_and_free_tmp_shares +--source include/have_binlog_format_mixed_or_statement.inc --source include/master-slave.inc --connect (con1,localhost,root,,) - CREATE TEMPORARY TABLE tmp (a INT); --send CREATE TABLE non_existing_db.t SELECT 1 AS b --disconnect con1 +--source include/wait_until_disconnected.inc + +--connection master +--let $wait_binlog_event= DROP +--source include/wait_for_binlog_event.inc +sync_slave_with_master; ---connection default +--connection slave +--let $open_temp_tbl_count=query_get_value(show status like 'Slave_open_temp_tables', Value, 1) +--let $assert_cond= "open_temp_tbl_count" = 0 +--let $assert_text= "Slave_open_temp_tables count should be 0" +--source include/assert.inc --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_parallel_retry.test b/mysql-test/suite/rpl/t/rpl_parallel_retry.test index 55da54e3c8c..97a3a709eb5 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_retry.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_retry.test @@ -436,6 +436,9 @@ SET @@DEBUG_SYNC='now SIGNAL proceed_by_1000'; --connection spoiler_21 ROLLBACK; +--let $wait_condition= SELECT count(*)=1 FROM information_schema.processlist WHERE state LIKE '%debug sync point%'; +--source include/wait_condition.inc + --echo # Release the 2nd worker to proceed --connection spoiler_22 ROLLBACK; diff --git a/mysql-test/suite/sql_sequence/other.result b/mysql-test/suite/sql_sequence/other.result index abc101b3c00..5226ce78789 100644 --- a/mysql-test/suite/sql_sequence/other.result +++ b/mysql-test/suite/sql_sequence/other.result @@ -300,4 +300,62 @@ update t1 set p_first_name='Yunxi' where p_id=1; drop view v2; drop table t1,t2; drop sequence s1; +# +# MDEV-19273:Server crash in MDL_ticket::has_stronger_or_equal_type or +# Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE, +# table->db.str, table->table_name.str, MDL_SHARED)' failed +# in mysql_rm_table_no_locks +# +CREATE TABLE t1 (a INT); +CREATE TEMPORARY TABLE tmp (b INT); +LOCK TABLE t1 READ; +DROP SEQUENCE tmp; +ERROR 42S02: Unknown SEQUENCE: 'test.tmp' +DROP TEMPORARY SEQUENCE tmp; +ERROR 42S02: Unknown SEQUENCE: 'test.tmp' +DROP SEQUENCE t1; +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +DROP TEMPORARY SEQUENCE t1; +ERROR 42S02: Unknown SEQUENCE: 'test.t1' +UNLOCK TABLES; +DROP SEQUENCE t1; +ERROR 42S02: 'test.t1' is not a SEQUENCE +DROP TEMPORARY SEQUENCE t1; +ERROR 42S02: Unknown SEQUENCE: 'test.t1' +DROP TABLE t1; +CREATE TABLE t (a INT); +CREATE SEQUENCE s; +LOCK TABLE t WRITE; +DROP SEQUENCE s; +ERROR HY000: Table 's' was not locked with LOCK TABLES +DROP TEMPORARY SEQUENCE s; +ERROR 42S02: Unknown SEQUENCE: 'test.s' +UNLOCK TABLES; +CREATE TEMPORARY SEQUENCE s; +LOCK TABLE t WRITE; +DROP TEMPORARY SEQUENCE s; +UNLOCK TABLES; +DROP SEQUENCE s; +create table s(a INT); +CREATE TEMPORARY TABLE s (f INT); +LOCK TABLE t WRITE; +DROP TEMPORARY TABLE s; +CREATE TEMPORARY TABLE s (f INT); +DROP TABLE s; +DROP TABLE s; +ERROR HY000: Table 's' was not locked with LOCK TABLES +UNLOCK TABLES; +DROP TABLE s; +CREATE VIEW v1 as SELECT * FROM t; +CREATE SEQUENCE s; +DROP SEQUENCE IF EXISTS v1; +Warnings: +Note 1965 'test.v1' is a view +DROP VIEW IF EXISTS s; +Warnings: +Warning 1347 'test.s' is not of type 'VIEW' +Note 4092 Unknown VIEW: 'test.s' +DROP VIEW v1; +DROP SEQUENCE s; +DROP TABLE t; # End of 10.3 tests diff --git a/mysql-test/suite/sql_sequence/other.test b/mysql-test/suite/sql_sequence/other.test index 70c4efa40e5..639cc5c36b0 100644 --- a/mysql-test/suite/sql_sequence/other.test +++ b/mysql-test/suite/sql_sequence/other.test @@ -315,4 +315,66 @@ drop view v2; drop table t1,t2; drop sequence s1; +--echo # +--echo # MDEV-19273:Server crash in MDL_ticket::has_stronger_or_equal_type or +--echo # Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE, +--echo # table->db.str, table->table_name.str, MDL_SHARED)' failed +--echo # in mysql_rm_table_no_locks +--echo # + +CREATE TABLE t1 (a INT); +CREATE TEMPORARY TABLE tmp (b INT); +LOCK TABLE t1 READ; +--error ER_UNKNOWN_SEQUENCES +DROP SEQUENCE tmp; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE tmp; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +DROP SEQUENCE t1; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE t1; +UNLOCK TABLES; +--error ER_NOT_SEQUENCE2 +DROP SEQUENCE t1; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE t1; + +# Cleanup +DROP TABLE t1; + + +CREATE TABLE t (a INT); +CREATE SEQUENCE s; +LOCK TABLE t WRITE; +--error ER_TABLE_NOT_LOCKED +DROP SEQUENCE s; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE s; +UNLOCK TABLES; +CREATE TEMPORARY SEQUENCE s; +LOCK TABLE t WRITE; +DROP TEMPORARY SEQUENCE s; +UNLOCK TABLES; +DROP SEQUENCE s; + +create table s(a INT); +CREATE TEMPORARY TABLE s (f INT); +LOCK TABLE t WRITE; +DROP TEMPORARY TABLE s; +CREATE TEMPORARY TABLE s (f INT); +DROP TABLE s; +--error ER_TABLE_NOT_LOCKED +DROP TABLE s; +UNLOCK TABLES; +DROP TABLE s; + +CREATE VIEW v1 as SELECT * FROM t; +CREATE SEQUENCE s; + +DROP SEQUENCE IF EXISTS v1; +DROP VIEW IF EXISTS s; + +DROP VIEW v1; +DROP SEQUENCE s; +DROP TABLE t; --echo # End of 10.3 tests diff --git a/mysql-test/suite/sys_vars/r/max_sort_length_basic.result b/mysql-test/suite/sys_vars/r/max_sort_length_basic.result deleted file mode 100644 index b48b045897c..00000000000 --- a/mysql-test/suite/sys_vars/r/max_sort_length_basic.result +++ /dev/null @@ -1,199 +0,0 @@ -SET @start_global_value = @@global.max_sort_length; -SELECT @start_global_value; -@start_global_value -1024 -SET @start_session_value = @@session.max_sort_length; -SELECT @start_session_value; -@start_session_value -1024 -'#--------------------FN_DYNVARS_084_01-------------------------#' -SET @@global.max_sort_length = 1000; -SET @@global.max_sort_length = DEFAULT; -SELECT @@global.max_sort_length; -@@global.max_sort_length -1024 -SET @@session.max_sort_length = 1000; -SET @@session.max_sort_length = DEFAULT; -SELECT @@session.max_sort_length; -@@session.max_sort_length -1024 -'#--------------------FN_DYNVARS_084_02-------------------------#' -SET @@global.max_sort_length = DEFAULT; -SELECT @@global.max_sort_length = 1024; -@@global.max_sort_length = 1024 -1 -SET @@session.max_sort_length = DEFAULT; -SELECT @@session.max_sort_length = 1024; -@@session.max_sort_length = 1024 -1 -'#--------------------FN_DYNVARS_084_03-------------------------#' -SET @@global.max_sort_length = 8; -SELECT @@global.max_sort_length; -@@global.max_sort_length -8 -SET @@global.max_sort_length = 9; -SELECT @@global.max_sort_length; -@@global.max_sort_length -9 -SET @@global.max_sort_length = 8388608; -SELECT @@global.max_sort_length; -@@global.max_sort_length -8388608 -SET @@global.max_sort_length = 8388607; -SELECT @@global.max_sort_length; -@@global.max_sort_length -8388607 -SET @@global.max_sort_length = 65536; -SELECT @@global.max_sort_length; -@@global.max_sort_length -65536 -'#--------------------FN_DYNVARS_084_04-------------------------#' -SET @@session.max_sort_length = 8; -SELECT @@session.max_sort_length; -@@session.max_sort_length -8 -SET @@session.max_sort_length = 9; -SELECT @@session.max_sort_length; -@@session.max_sort_length -9 -SET @@session.max_sort_length = 8388608; -SELECT @@session.max_sort_length; -@@session.max_sort_length -8388608 -SET @@session.max_sort_length = 8388607; -SELECT @@session.max_sort_length; -@@session.max_sort_length -8388607 -SET @@session.max_sort_length = 65536; -SELECT @@session.max_sort_length; -@@session.max_sort_length -65536 -'#------------------FN_DYNVARS_084_05-----------------------#' -SET @@global.max_sort_length = -1024; -Warnings: -Warning 1292 Truncated incorrect max_sort_length value: '-1024' -SELECT @@global.max_sort_length; -@@global.max_sort_length -8 -SET @@global.max_sort_length = 3; -Warnings: -Warning 1292 Truncated incorrect max_sort_length value: '3' -SELECT @@global.max_sort_length; -@@global.max_sort_length -8 -SET @@global.max_sort_length = 8388609; -Warnings: -Warning 1292 Truncated incorrect max_sort_length value: '8388609' -SELECT @@global.max_sort_length; -@@global.max_sort_length -8388608 -SET @@global.max_sort_length = 0; -Warnings: -Warning 1292 Truncated incorrect max_sort_length value: '0' -SELECT @@global.max_sort_length; -@@global.max_sort_length -8 -SET @@global.max_sort_length = 65530.34; -ERROR 42000: Incorrect argument type to variable 'max_sort_length' -SELECT @@global.max_sort_length; -@@global.max_sort_length -8 -SET @@global.max_sort_length = test; -ERROR 42000: Incorrect argument type to variable 'max_sort_length' -SELECT @@global.max_sort_length; -@@global.max_sort_length -8 -SET @@session.max_sort_length = 8388610; -Warnings: -Warning 1292 Truncated incorrect max_sort_length value: '8388610' -SELECT @@session.max_sort_length; -@@session.max_sort_length -8388608 -SET @@session.max_sort_length = -1; -Warnings: -Warning 1292 Truncated incorrect max_sort_length value: '-1' -SELECT @@session.max_sort_length; -@@session.max_sort_length -8 -SET @@session.max_sort_length = 3; -Warnings: -Warning 1292 Truncated incorrect max_sort_length value: '3' -SELECT @@session.max_sort_length; -@@session.max_sort_length -8 -SET @@session.max_sort_length = 0; -Warnings: -Warning 1292 Truncated incorrect max_sort_length value: '0' -SELECT @@session.max_sort_length; -@@session.max_sort_length -8 -SET @@session.max_sort_length = 65530.34; -ERROR 42000: Incorrect argument type to variable 'max_sort_length' -SET @@session.max_sort_length = 10737418241; -Warnings: -Warning 1292 Truncated incorrect max_sort_length value: '10737418241' -SELECT @@session.max_sort_length; -@@session.max_sort_length -8388608 -SET @@session.max_sort_length = test; -ERROR 42000: Incorrect argument type to variable 'max_sort_length' -SELECT @@session.max_sort_length; -@@session.max_sort_length -8388608 -'#------------------FN_DYNVARS_084_06-----------------------#' -SELECT @@global.max_sort_length = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='max_sort_length'; -@@global.max_sort_length = VARIABLE_VALUE -1 -'#------------------FN_DYNVARS_084_07-----------------------#' -SELECT @@session.max_sort_length = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.SESSION_VARIABLES -WHERE VARIABLE_NAME='max_sort_length'; -@@session.max_sort_length = VARIABLE_VALUE -1 -'#------------------FN_DYNVARS_084_08-----------------------#' -SET @@global.max_sort_length = TRUE; -Warnings: -Warning 1292 Truncated incorrect max_sort_length value: '1' -SELECT @@global.max_sort_length; -@@global.max_sort_length -8 -SET @@global.max_sort_length = FALSE; -Warnings: -Warning 1292 Truncated incorrect max_sort_length value: '0' -SELECT @@global.max_sort_length; -@@global.max_sort_length -8 -'#---------------------FN_DYNVARS_084_09----------------------#' -SET @@global.max_sort_length = 2048; -SELECT @@max_sort_length = @@global.max_sort_length; -@@max_sort_length = @@global.max_sort_length -0 -'#---------------------FN_DYNVARS_084_10----------------------#' -SET @@max_sort_length = 100000; -SELECT @@max_sort_length = @@local.max_sort_length; -@@max_sort_length = @@local.max_sort_length -1 -SELECT @@local.max_sort_length = @@session.max_sort_length; -@@local.max_sort_length = @@session.max_sort_length -1 -'#---------------------FN_DYNVARS_084_11----------------------#' -SET max_sort_length = 1024; -SELECT @@max_sort_length; -@@max_sort_length -1024 -SELECT local.max_sort_length; -ERROR 42S02: Unknown table 'local' in field list -SELECT session.max_sort_length; -ERROR 42S02: Unknown table 'session' in field list -SELECT max_sort_length = @@session.max_sort_length; -ERROR 42S22: Unknown column 'max_sort_length' in 'field list' -SET @@global.max_sort_length = @start_global_value; -SELECT @@global.max_sort_length; -@@global.max_sort_length -1024 -SET @@session.max_sort_length = @start_session_value; -SELECT @@session.max_sort_length; -@@session.max_sort_length -1024 diff --git a/mysql-test/suite/sys_vars/r/max_sort_length_func.result b/mysql-test/suite/sys_vars/r/max_sort_length_func.result index 36f5518287a..3ec8faf1b7e 100644 --- a/mysql-test/suite/sys_vars/r/max_sort_length_func.result +++ b/mysql-test/suite/sys_vars/r/max_sort_length_func.result @@ -1,301 +1,279 @@ SET @start_value= @@global.max_sort_length; -SET @session_max_sort_length = @@Session.max_sort_length; -DROP TABLE IF EXISTS t; ** creating tables ** -CREATE TABLE t -( -id INT AUTO_INCREMENT PRIMARY KEY, -c TEXT(30) -); -CREATE TABLE t1 -( -id INT AUTO_INCREMENT PRIMARY KEY, -c BLOB(30) -); -CREATE TABLE t2 -( -id INT AUTO_INCREMENT PRIMARY KEY, -c TEXT(30) -); +CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, c TEXT); +CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, c BLOB); +CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY, c TEXT); '#--------------------FN_DYNVARS_098_01-------------------------#' connect test_con1,localhost,root,,; -connection test_con1; -SELECT @@global.max_sort_length = 10; -@@global.max_sort_length = 10 -0 -SELECT @@session.max_sort_length = 10; -@@session.max_sort_length = 10 -0 -** Setting value to 30 and inserting data ** -SET @@global.max_sort_length = 30; +** Setting value to 70 and inserting data ** +SET @@global.max_sort_length = 70; SELECT @@global.max_sort_length; @@global.max_sort_length -30 -INSERT INTO t set c = repeat('x',29); -INSERT INTO t set c = concat(repeat('x',28),'r','x'); -INSERT INTO t set c = concat(repeat('x',28),'s','y'); -INSERT INTO t set c = concat(repeat('x',28),'g','w'); +70 +INSERT INTO t set c = repeat('x',69); +INSERT INTO t set c = concat(repeat('x',68),'r','x'); +INSERT INTO t set c = concat(repeat('x',68),'s','y'); +INSERT INTO t set c = concat(repeat('x',68),'g','w'); SELECT c from t ORDER BY c, id; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** -SET @@session.max_sort_length = 29; +SET @@session.max_sort_length = 69; SELECT @@session.max_sort_length; @@session.max_sort_length -29 -INSERT INTO t set c = repeat('x',29); -INSERT INTO t set c = concat(repeat('x',28),'r','x'); -INSERT INTO t set c = concat(repeat('x',28),'s','y'); -INSERT INTO t set c = concat(repeat('x',28),'g','w'); +69 +INSERT INTO t set c = repeat('x',69); +INSERT INTO t set c = concat(repeat('x',68),'r','x'); +INSERT INTO t set c = concat(repeat('x',68),'s','y'); +INSERT INTO t set c = concat(repeat('x',68),'g','w'); SELECT c from t ORDER BY c, id; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** '#--------------------FN_DYNVARS_098_02-------------------------#' connect test_con2,localhost,root,,; -connection test_con2; -SET @@global.max_sort_length = 30; +SET @@global.max_sort_length = 70; SELECT @@global.max_sort_length; @@global.max_sort_length -30 -INSERT INTO t set c = repeat('x',29); -INSERT INTO t set c = concat(repeat('x',28),'r','x'); -INSERT INTO t set c = concat(repeat('x',28),'s','y'); -INSERT INTO t set c = concat(repeat('x',28),'g','w'); +70 +INSERT INTO t set c = repeat('x',69); +INSERT INTO t set c = concat(repeat('x',68),'r','x'); +INSERT INTO t set c = concat(repeat('x',68),'s','y'); +INSERT INTO t set c = concat(repeat('x',68),'g','w'); SELECT c from t ORDER BY c, id; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** -SET @@session.max_sort_length = 20; +SET @@session.max_sort_length = 64; SELECT @@session.max_sort_length; @@session.max_sort_length -20 -INSERT INTO t set c = repeat('x',29); -INSERT INTO t set c = concat(repeat('x',28),'r','x'); -INSERT INTO t set c = concat(repeat('x',28),'s','y'); -INSERT INTO t set c = concat(repeat('x',28),'g','w'); +64 +INSERT INTO t set c = repeat('x',69); +INSERT INTO t set c = concat(repeat('x',68),'r','x'); +INSERT INTO t set c = concat(repeat('x',68),'s','y'); +INSERT INTO t set c = concat(repeat('x',68),'g','w'); SELECT c from t ORDER BY c, id; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw ** Results should not be sorted ** '#--------------------FN_DYNVARS_098_03-------------------------#' -SET max_sort_length=20; -INSERT INTO t set c = repeat('x',29); -INSERT INTO t set c = concat(repeat('x',28),'r','x'); -INSERT INTO t set c = concat(repeat('x',28),'s','y'); -INSERT INTO t set c = concat(repeat('x',28),'g','w'); +SET max_sort_length=64; +INSERT INTO t set c = repeat('x',69); +INSERT INTO t set c = concat(repeat('x',68),'r','x'); +INSERT INTO t set c = concat(repeat('x',68),'s','y'); +INSERT INTO t set c = concat(repeat('x',68),'g','w'); SELECT c from t ORDER BY c, id; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw ** Results should not be sorted ** RESET QUERY CACHE; '#--------------------FN_DYNVARS_098_04-------------------------#' -SET max_sort_length=29; +SET max_sort_length=69; SELECT c from t ORDER BY c, id; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** '#--------------------FN_DYNVARS_098_05-------------------------#' -SET max_sort_length=30; +SET max_sort_length=70; SELECT c from t ORDER BY c, id; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** '#--------------------FN_DYNVARS_098_06-------------------------#' SET max_sort_length=default; +SELECT @@max_sort_length; +@@max_sort_length +70 SELECT c from t ORDER BY c, id; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** '#--------------------FN_DYNVARS_098_07-------------------------#' Testing type BLOB -SET @@global.max_sort_length = 30; -SELECT @@global.max_sort_length; -@@global.max_sort_length -30 -INSERT INTO t1 set c = repeat('x',29); -INSERT INTO t1 set c = concat(repeat('x',28),'r','x'); -INSERT INTO t1 set c = concat(repeat('x',28),'s','y'); -INSERT INTO t1 set c = concat(repeat('x',28),'g','w'); -SELECT c from t1 ORDER BY c, id; +SET @@max_sort_length = 70; +SELECT @@max_sort_length; +@@max_sort_length +70 +INSERT INTO t1 set c = repeat('x',69); +INSERT INTO t1 set c = concat(repeat('x',68),'r','x'); +INSERT INTO t1 set c = concat(repeat('x',68),'s','y'); +INSERT INTO t1 set c = concat(repeat('x',68),'g','w'); +SELECT c from t1 ORDER BY c, id DESC; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx ** Results should be sorted ** -SET @@session.max_sort_length = 20; +SET @@session.max_sort_length = 64; SELECT @@session.max_sort_length; @@session.max_sort_length -20 -INSERT INTO t1 set c = repeat('x',29); -INSERT INTO t1 set c = concat(repeat('x',28),'r','x'); -INSERT INTO t1 set c = concat(repeat('x',28),'s','y'); -INSERT INTO t1 set c = concat(repeat('x',28),'g','w'); +64 +INSERT INTO t1 set c = repeat('x',69); +INSERT INTO t1 set c = concat(repeat('x',68),'r','x'); +INSERT INTO t1 set c = concat(repeat('x',68),'s','y'); +INSERT INTO t1 set c = concat(repeat('x',68),'g','w'); SELECT c from t1 ORDER BY c, id; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw ** Results should not be sorted ** '#--------------------FN_DYNVARS_098_08-------------------------#' Testing type CHAR -SET @@global.max_sort_length = 30; +SET @@global.max_sort_length = 70; SELECT @@global.max_sort_length; @@global.max_sort_length -30 -INSERT INTO t2 set c = repeat('x',29); -INSERT INTO t2 set c = concat(repeat('x',28),'r','x'); -INSERT INTO t2 set c = concat(repeat('x',28),'s','y'); -INSERT INTO t2 set c = concat(repeat('x',28),'g','w'); +70 +INSERT INTO t2 set c = repeat('x',69); +INSERT INTO t2 set c = concat(repeat('x',68),'r','x'); +INSERT INTO t2 set c = concat(repeat('x',68),'s','y'); +INSERT INTO t2 set c = concat(repeat('x',68),'g','w'); SELECT c from t2 ORDER BY c, id; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw ** Results should not be sorted ** -SET @@session.max_sort_length = 20; +SET @@session.max_sort_length = 64; SELECT @@session.max_sort_length; @@session.max_sort_length -20 -INSERT INTO t2 set c = repeat('x',29); -INSERT INTO t2 set c = concat(repeat('x',28),'r','x'); -INSERT INTO t2 set c = concat(repeat('x',28),'s','y'); -INSERT INTO t2 set c = concat(repeat('x',28),'g','w'); +64 +INSERT INTO t2 set c = repeat('x',69); +INSERT INTO t2 set c = concat(repeat('x',68),'r','x'); +INSERT INTO t2 set c = concat(repeat('x',68),'s','y'); +INSERT INTO t2 set c = concat(repeat('x',68),'g','w'); SELECT c from t2 ORDER BY c, id; c -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw -xxxxxxxxxxxxxxxxxxxxxxxxxxxxx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx -xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy -xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy +xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw ** Results should not be sorted ** connection default; disconnect test_con1; disconnect test_con2; -SET @@SESSION.max_sort_length = @session_max_sort_length; -DROP TABLE IF EXISTS t; -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t2; +DROP TABLE t, t1, t2; SET @@global.max_sort_length= @start_value; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 7bfa2f626f0..0648284e580 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -1055,10 +1055,10 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME GROUP_CONCAT_MAX_LEN VARIABLE_SCOPE SESSION -VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_TYPE INT UNSIGNED VARIABLE_COMMENT The maximum length of the result of function GROUP_CONCAT() NUMERIC_MIN_VALUE 4 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MAX_VALUE 4294967295 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO @@ -1947,7 +1947,7 @@ VARIABLE_NAME MAX_SORT_LENGTH VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored) -NUMERIC_MIN_VALUE 8 +NUMERIC_MIN_VALUE 64 NUMERIC_MAX_VALUE 8388608 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 8854fab4209..78b8ca0263d 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -1075,10 +1075,10 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME GROUP_CONCAT_MAX_LEN VARIABLE_SCOPE SESSION -VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_TYPE INT UNSIGNED VARIABLE_COMMENT The maximum length of the result of function GROUP_CONCAT() NUMERIC_MIN_VALUE 4 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MAX_VALUE 4294967295 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO @@ -2107,7 +2107,7 @@ VARIABLE_NAME MAX_SORT_LENGTH VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored) -NUMERIC_MIN_VALUE 8 +NUMERIC_MIN_VALUE 64 NUMERIC_MAX_VALUE 8388608 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL diff --git a/mysql-test/suite/sys_vars/t/max_sort_length_basic.test b/mysql-test/suite/sys_vars/t/max_sort_length_basic.test deleted file mode 100644 index fcd6db017f1..00000000000 --- a/mysql-test/suite/sys_vars/t/max_sort_length_basic.test +++ /dev/null @@ -1,225 +0,0 @@ -############## mysql-test\t\max_sort_length_basic.test ############### -# # -# Variable Name: max_sort_length # -# Scope: GLOBAL | SESSION # -# Access Type: Dynamic # -# Data Type: numeric # -# Default Value: 1024 # -# Range: 4-8388608 # -# # -# # -# Creation Date: 2008-02-07 # -# Author: Salman # -# # -# Description: Test Cases of Dynamic System Variable max_sort_length # -# that checks the behavior of this variable in the following ways# -# * Default Value # -# * Valid & Invalid values # -# * Scope & Access method # -# * Data Integrity # -# # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # -# # -############################################################################### - ---source include/load_sysvars.inc - - -############################################ -# START OF max_sort_length TESTS # -############################################ - - -############################################################# -# Save initial value # -############################################################# - -SET @start_global_value = @@global.max_sort_length; -SELECT @start_global_value; -SET @start_session_value = @@session.max_sort_length; -SELECT @start_session_value; - - ---echo '#--------------------FN_DYNVARS_084_01-------------------------#' -####################################################### -# Display the DEFAULT value of max_sort_length # -####################################################### - -SET @@global.max_sort_length = 1000; -SET @@global.max_sort_length = DEFAULT; -SELECT @@global.max_sort_length; - - -SET @@session.max_sort_length = 1000; -SET @@session.max_sort_length = DEFAULT; -SELECT @@session.max_sort_length; - - ---echo '#--------------------FN_DYNVARS_084_02-------------------------#' -####################################################### -# Check the DEFAULT value of max_sort_length # -####################################################### - -SET @@global.max_sort_length = DEFAULT; -SELECT @@global.max_sort_length = 1024; - -SET @@session.max_sort_length = DEFAULT; -SELECT @@session.max_sort_length = 1024; - - - ---echo '#--------------------FN_DYNVARS_084_03-------------------------#' -######################################################################### -# Change the value of max_sort_length to a valid value for GLOBAL Scope # -######################################################################### - -SET @@global.max_sort_length = 8; -SELECT @@global.max_sort_length; -SET @@global.max_sort_length = 9; -SELECT @@global.max_sort_length; -SET @@global.max_sort_length = 8388608; -SELECT @@global.max_sort_length; -SET @@global.max_sort_length = 8388607; -SELECT @@global.max_sort_length; -SET @@global.max_sort_length = 65536; -SELECT @@global.max_sort_length; - ---echo '#--------------------FN_DYNVARS_084_04-------------------------#' -########################################################################## -# Change the value of max_sort_length to a valid value for SESSION Scope # -########################################################################## - -SET @@session.max_sort_length = 8; -SELECT @@session.max_sort_length; - -SET @@session.max_sort_length = 9; -SELECT @@session.max_sort_length; - -SET @@session.max_sort_length = 8388608; -SELECT @@session.max_sort_length; - -SET @@session.max_sort_length = 8388607; -SELECT @@session.max_sort_length; - -SET @@session.max_sort_length = 65536; -SELECT @@session.max_sort_length; - - ---echo '#------------------FN_DYNVARS_084_05-----------------------#' -########################################################### -# Change the value of max_sort_length to an invalid value # -########################################################### - -SET @@global.max_sort_length = -1024; -SELECT @@global.max_sort_length; -SET @@global.max_sort_length = 3; -SELECT @@global.max_sort_length; -SET @@global.max_sort_length = 8388609; -SELECT @@global.max_sort_length; -SET @@global.max_sort_length = 0; -SELECT @@global.max_sort_length; ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.max_sort_length = 65530.34; -SELECT @@global.max_sort_length; ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.max_sort_length = test; -SELECT @@global.max_sort_length; - -SET @@session.max_sort_length = 8388610; -SELECT @@session.max_sort_length; -SET @@session.max_sort_length = -1; -SELECT @@session.max_sort_length; -SET @@session.max_sort_length = 3; -SELECT @@session.max_sort_length; -SET @@session.max_sort_length = 0; -SELECT @@session.max_sort_length; ---Error ER_WRONG_TYPE_FOR_VAR -SET @@session.max_sort_length = 65530.34; -SET @@session.max_sort_length = 10737418241; -SELECT @@session.max_sort_length; ---Error ER_WRONG_TYPE_FOR_VAR -SET @@session.max_sort_length = test; -SELECT @@session.max_sort_length; - - ---echo '#------------------FN_DYNVARS_084_06-----------------------#' -#################################################################### -# Check if the value in GLOBAL Table matches value in variable # -#################################################################### - - -SELECT @@global.max_sort_length = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='max_sort_length'; - ---echo '#------------------FN_DYNVARS_084_07-----------------------#' -#################################################################### -# Check if the value in SESSION Table matches value in variable # -#################################################################### - -SELECT @@session.max_sort_length = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.SESSION_VARIABLES -WHERE VARIABLE_NAME='max_sort_length'; - - ---echo '#------------------FN_DYNVARS_084_08-----------------------#' -#################################################################### -# Check if TRUE and FALSE values can be used on variable # -#################################################################### - -SET @@global.max_sort_length = TRUE; -SELECT @@global.max_sort_length; -SET @@global.max_sort_length = FALSE; -SELECT @@global.max_sort_length; - - ---echo '#---------------------FN_DYNVARS_084_09----------------------#' -################################################################################# -# Check if accessing variable with and without GLOBAL point to same variable # -################################################################################# - -SET @@global.max_sort_length = 2048; -SELECT @@max_sort_length = @@global.max_sort_length; - - ---echo '#---------------------FN_DYNVARS_084_10----------------------#' -######################################################################################################## -# Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable # -######################################################################################################## - -SET @@max_sort_length = 100000; -SELECT @@max_sort_length = @@local.max_sort_length; -SELECT @@local.max_sort_length = @@session.max_sort_length; - - ---echo '#---------------------FN_DYNVARS_084_11----------------------#' -########################################################################## -# Check if max_sort_length can be accessed with and without @@ sign # -########################################################################## - - -SET max_sort_length = 1024; -SELECT @@max_sort_length; ---Error ER_UNKNOWN_TABLE -SELECT local.max_sort_length; ---Error ER_UNKNOWN_TABLE -SELECT session.max_sort_length; ---Error ER_BAD_FIELD_ERROR -SELECT max_sort_length = @@session.max_sort_length; - - -#################################### -# Restore initial value # -#################################### - -SET @@global.max_sort_length = @start_global_value; -SELECT @@global.max_sort_length; -SET @@session.max_sort_length = @start_session_value; -SELECT @@session.max_sort_length; - - -#################################################### -# END OF max_sort_length TESTS # -#################################################### - diff --git a/mysql-test/suite/sys_vars/t/max_sort_length_func.test b/mysql-test/suite/sys_vars/t/max_sort_length_func.test index fd0b87750a1..d54453a57b6 100644 --- a/mysql-test/suite/sys_vars/t/max_sort_length_func.test +++ b/mysql-test/suite/sys_vars/t/max_sort_length_func.test @@ -26,70 +26,40 @@ SET @start_value= @@global.max_sort_length; -SET @session_max_sort_length = @@Session.max_sort_length; - - ---disable_warnings -DROP TABLE IF EXISTS t; ---enable_warnings - ######################### # Creating new table # ######################### - --echo ** creating tables ** -CREATE TABLE t -( -id INT AUTO_INCREMENT PRIMARY KEY, -c TEXT(30) -); - -CREATE TABLE t1 -( -id INT AUTO_INCREMENT PRIMARY KEY, -c BLOB(30) -); - -CREATE TABLE t2 -( -id INT AUTO_INCREMENT PRIMARY KEY, -c TEXT(30) -); - - +CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, c TEXT); +CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, c BLOB); +CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY, c TEXT); --echo '#--------------------FN_DYNVARS_098_01-------------------------#' ########################################################## # Test behavior of variable on new connection # 01 # ########################################################## - connect (test_con1,localhost,root,,); -connection test_con1; - -# Value of session & global vairable here should be 10 -SELECT @@global.max_sort_length = 10; -SELECT @@session.max_sort_length = 10; # Setting global value of variable and inserting data in table ---echo ** Setting value to 30 and inserting data ** -SET @@global.max_sort_length = 30; +--echo ** Setting value to 70 and inserting data ** +SET @@global.max_sort_length = 70; SELECT @@global.max_sort_length; -INSERT INTO t set c = repeat('x',29); -INSERT INTO t set c = concat(repeat('x',28),'r','x'); -INSERT INTO t set c = concat(repeat('x',28),'s','y'); -INSERT INTO t set c = concat(repeat('x',28),'g','w'); +INSERT INTO t set c = repeat('x',69); +INSERT INTO t set c = concat(repeat('x',68),'r','x'); +INSERT INTO t set c = concat(repeat('x',68),'s','y'); +INSERT INTO t set c = concat(repeat('x',68),'g','w'); SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** # Setting session value of variable and inserting data in table -SET @@session.max_sort_length = 29; +SET @@session.max_sort_length = 69; SELECT @@session.max_sort_length; -INSERT INTO t set c = repeat('x',29); -INSERT INTO t set c = concat(repeat('x',28),'r','x'); -INSERT INTO t set c = concat(repeat('x',28),'s','y'); -INSERT INTO t set c = concat(repeat('x',28),'g','w'); +INSERT INTO t set c = repeat('x',69); +INSERT INTO t set c = concat(repeat('x',68),'r','x'); +INSERT INTO t set c = concat(repeat('x',68),'s','y'); +INSERT INTO t set c = concat(repeat('x',68),'g','w'); SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** @@ -99,72 +69,69 @@ SELECT c from t ORDER BY c, id; ########################################################## connect (test_con2,localhost,root,,); -connection test_con2; - ## Setting global value of variable and inserting data in table -SET @@global.max_sort_length = 30; +SET @@global.max_sort_length = 70; SELECT @@global.max_sort_length; -INSERT INTO t set c = repeat('x',29); -INSERT INTO t set c = concat(repeat('x',28),'r','x'); -INSERT INTO t set c = concat(repeat('x',28),'s','y'); -INSERT INTO t set c = concat(repeat('x',28),'g','w'); +INSERT INTO t set c = repeat('x',69); +INSERT INTO t set c = concat(repeat('x',68),'r','x'); +INSERT INTO t set c = concat(repeat('x',68),'s','y'); +INSERT INTO t set c = concat(repeat('x',68),'g','w'); SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** # Setting session value of variable and inserting data in table -SET @@session.max_sort_length = 20; +SET @@session.max_sort_length = 64; SELECT @@session.max_sort_length; -INSERT INTO t set c = repeat('x',29); -INSERT INTO t set c = concat(repeat('x',28),'r','x'); -INSERT INTO t set c = concat(repeat('x',28),'s','y'); -INSERT INTO t set c = concat(repeat('x',28),'g','w'); +INSERT INTO t set c = repeat('x',69); +INSERT INTO t set c = concat(repeat('x',68),'r','x'); +INSERT INTO t set c = concat(repeat('x',68),'s','y'); +INSERT INTO t set c = concat(repeat('x',68),'g','w'); SELECT c from t ORDER BY c, id; --echo ** Results should not be sorted ** --echo '#--------------------FN_DYNVARS_098_03-------------------------#' ######################################################### -#Check if sorting is applied with the max_sort_length=20# +#Check if sorting is applied with the max_sort_length=64# ######################################################### ########################################### # Setting new value for max_sort_length # ########################################### -SET max_sort_length=20; +SET max_sort_length=64; ################################### # Inserting values in table t # ################################### -INSERT INTO t set c = repeat('x',29); -INSERT INTO t set c = concat(repeat('x',28),'r','x'); -INSERT INTO t set c = concat(repeat('x',28),'s','y'); -INSERT INTO t set c = concat(repeat('x',28),'g','w'); +INSERT INTO t set c = repeat('x',69); +INSERT INTO t set c = concat(repeat('x',68),'r','x'); +INSERT INTO t set c = concat(repeat('x',68),'s','y'); +INSERT INTO t set c = concat(repeat('x',68),'g','w'); SELECT c from t ORDER BY c, id; --echo ** Results should not be sorted ** RESET QUERY CACHE; - --echo '#--------------------FN_DYNVARS_098_04-------------------------#' ######################################################### -#Check if sorting is applied with the max_sort_length=29# +#Check if sorting is applied with the max_sort_length=69# ######################################################### -SET max_sort_length=29; +SET max_sort_length=69; SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** --echo '#--------------------FN_DYNVARS_098_05-------------------------#' ######################################################### -#Check if sorting is applied with the max_sort_length=30# +#Check if sorting is applied with the max_sort_length=70# ######################################################### -SET max_sort_length=30; +SET max_sort_length=70; SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** @@ -173,34 +140,33 @@ SELECT c from t ORDER BY c, id; #Check if sorting is applied with the max_sort_length=Default# ############################################################## - SET max_sort_length=default; +SELECT @@max_sort_length; SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** - --echo '#--------------------FN_DYNVARS_098_07-------------------------#' ########################################### #Check if sorting is applied on BLOB type # ########################################### --echo Testing type BLOB # Setting global value of variable and inserting data in table -SET @@global.max_sort_length = 30; -SELECT @@global.max_sort_length; -INSERT INTO t1 set c = repeat('x',29); -INSERT INTO t1 set c = concat(repeat('x',28),'r','x'); -INSERT INTO t1 set c = concat(repeat('x',28),'s','y'); -INSERT INTO t1 set c = concat(repeat('x',28),'g','w'); -SELECT c from t1 ORDER BY c, id; +SET @@max_sort_length = 70; +SELECT @@max_sort_length; +INSERT INTO t1 set c = repeat('x',69); +INSERT INTO t1 set c = concat(repeat('x',68),'r','x'); +INSERT INTO t1 set c = concat(repeat('x',68),'s','y'); +INSERT INTO t1 set c = concat(repeat('x',68),'g','w'); +SELECT c from t1 ORDER BY c, id DESC; --echo ** Results should be sorted ** # Setting session value of variable and inserting data in table -SET @@session.max_sort_length = 20; +SET @@session.max_sort_length = 64; SELECT @@session.max_sort_length; -INSERT INTO t1 set c = repeat('x',29); -INSERT INTO t1 set c = concat(repeat('x',28),'r','x'); -INSERT INTO t1 set c = concat(repeat('x',28),'s','y'); -INSERT INTO t1 set c = concat(repeat('x',28),'g','w'); +INSERT INTO t1 set c = repeat('x',69); +INSERT INTO t1 set c = concat(repeat('x',68),'r','x'); +INSERT INTO t1 set c = concat(repeat('x',68),'s','y'); +INSERT INTO t1 set c = concat(repeat('x',68),'g','w'); SELECT c from t1 ORDER BY c, id; --echo ** Results should not be sorted ** @@ -210,26 +176,25 @@ SELECT c from t1 ORDER BY c, id; ########################################### --echo Testing type CHAR # Setting global value of variable and inserting data in table -SET @@global.max_sort_length = 30; +SET @@global.max_sort_length = 70; SELECT @@global.max_sort_length; -INSERT INTO t2 set c = repeat('x',29); -INSERT INTO t2 set c = concat(repeat('x',28),'r','x'); -INSERT INTO t2 set c = concat(repeat('x',28),'s','y'); -INSERT INTO t2 set c = concat(repeat('x',28),'g','w'); +INSERT INTO t2 set c = repeat('x',69); +INSERT INTO t2 set c = concat(repeat('x',68),'r','x'); +INSERT INTO t2 set c = concat(repeat('x',68),'s','y'); +INSERT INTO t2 set c = concat(repeat('x',68),'g','w'); SELECT c from t2 ORDER BY c, id; --echo ** Results should not be sorted ** # Setting session value of variable and inserting data in table -SET @@session.max_sort_length = 20; +SET @@session.max_sort_length = 64; SELECT @@session.max_sort_length; -INSERT INTO t2 set c = repeat('x',29); -INSERT INTO t2 set c = concat(repeat('x',28),'r','x'); -INSERT INTO t2 set c = concat(repeat('x',28),'s','y'); -INSERT INTO t2 set c = concat(repeat('x',28),'g','w'); +INSERT INTO t2 set c = repeat('x',69); +INSERT INTO t2 set c = concat(repeat('x',68),'r','x'); +INSERT INTO t2 set c = concat(repeat('x',68),'s','y'); +INSERT INTO t2 set c = concat(repeat('x',68),'g','w'); SELECT c from t2 ORDER BY c, id; --echo ** Results should not be sorted ** - # # Cleanup # @@ -239,12 +204,6 @@ connection default; disconnect test_con1; disconnect test_con2; -SET @@SESSION.max_sort_length = @session_max_sort_length; - ---disable_warnings -DROP TABLE IF EXISTS t; -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t2; ---enable_warnings +DROP TABLE t, t1, t2; SET @@global.max_sort_length= @start_value; diff --git a/mysql-test/suite/versioning/common.inc b/mysql-test/suite/versioning/common.inc index efb081a02e4..25adf15dd50 100644 --- a/mysql-test/suite/versioning/common.inc +++ b/mysql-test/suite/versioning/common.inc @@ -70,6 +70,11 @@ returns int deterministic return sys_trx_end = $sys_datatype_max; +eval create or replace function current_row_ts(sys_trx_end timestamp(6)) +returns int +deterministic + return convert_tz(sys_trx_end, '+00:00', @@time_zone) = TIMESTAMP'2038-01-19 03:14:07.999999'; + delimiter ~~; eval create or replace function check_row(row_start $sys_datatype_expl, row_end $sys_datatype_expl) returns varchar(255) @@ -86,4 +91,20 @@ begin end~~ delimiter ;~~ +delimiter ~~; +eval create or replace function check_row_ts(row_start timestamp(6), row_end timestamp(6)) +returns varchar(255) +deterministic +begin + if row_end < row_start then + return "ERROR: row_end < row_start"; + elseif row_end = row_start then + return "ERROR: row_end == row_start"; + elseif current_row_ts(row_end) then + return "CURRENT ROW"; + end if; + return "HISTORICAL ROW"; +end~~ +delimiter ;~~ + --enable_query_log diff --git a/mysql-test/suite/versioning/common_finish.inc b/mysql-test/suite/versioning/common_finish.inc index 61641c6c5ce..3c4e7b66ff3 100644 --- a/mysql-test/suite/versioning/common_finish.inc +++ b/mysql-test/suite/versioning/common_finish.inc @@ -4,5 +4,7 @@ drop procedure if exists verify_trt; drop procedure if exists verify_trt_dummy; drop function if exists current_row; drop function if exists check_row; +drop function if exists current_row_ts; +drop function if exists check_row_ts; --enable_warnings --enable_query_log diff --git a/mysql-test/suite/versioning/r/delete.result b/mysql-test/suite/versioning/r/delete.result index 5aa239b9cb8..0f9e2c22130 100644 --- a/mysql-test/suite/versioning/r/delete.result +++ b/mysql-test/suite/versioning/r/delete.result @@ -130,3 +130,22 @@ ERROR 42S02: Table 'test.xx' doesn't exist drop procedure pr; drop trigger tr; drop table t1; +# +# MDEV-21138 Assertion `col->ord_part' or `f.col->ord_part' failed in row_build_index_entry_low +# +create table t1 ( +f1 int, f2 text, f3 int, fulltext (f2), key(f1), key(f3), +foreign key r (f3) references t1 (f1) on delete set null) +with system versioning engine innodb; +insert into t1 values (1, repeat('a', 8193), 1), (1, repeat('b', 8193), 1); +select f1, f3, check_row_ts(row_start, row_end) from t1; +f1 f3 check_row_ts(row_start, row_end) +1 1 CURRENT ROW +1 1 CURRENT ROW +delete from t1; +select f1, f3, check_row_ts(row_start, row_end) from t1 for system_time all; +f1 f3 check_row_ts(row_start, row_end) +1 1 HISTORICAL ROW +1 NULL ERROR: row_end == row_start +1 1 HISTORICAL ROW +drop table t1; diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index 5234a1ad616..07e133e1eeb 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -400,6 +400,8 @@ Warning 1265 Data truncated for column 'f12' at row 7 SET timestamp = 9; REPLACE INTO t2 SELECT * FROM t2; DROP TABLE t1, t2; +set timestamp= default; +set time_zone='+00:00'; # # MDEV-16210 FK constraints on versioned tables use historical rows, which may cause constraint violation # @@ -430,6 +432,20 @@ insert into t2 values (1), (1); delete from t2; drop tables t2, t1; # +# MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table +# +create table t1 (pk int primary key, f1 int,f2 int, f3 text, +key(f1), fulltext(f3), key(f3(10)), +foreign key (f2) references t1 (f1) on delete set null +) engine=innodb with system versioning; +insert into t1 values (1, 8, 8, 'SHORT'), (2, 8, 8, repeat('LONG', 8071)); +delete from t1; +select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for system_time all order by pk; +pk f1 f2 left(f3, 4) check_row_ts(row_start, row_end) +1 8 8 SHOR HISTORICAL ROW +2 8 8 LONG HISTORICAL ROW +drop table t1; +# # MDEV-20729 Fix REFERENCES constraint in column definition # create or replace table t1( diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index cc9891a1f6e..99d1c01cc49 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -1080,6 +1080,13 @@ create table t1 (a int) with system versioning partition by system_time alter table t1 add partition (partition p2); ERROR HY000: Wrong partitioning type, expected type: `SYSTEM_TIME` drop table t1; +# +# MDEV-22178 Assertion `info->alias.str' failed in partition_info::check_partition_info instead of ER_VERS_WRONG_PARTS +# +create or replace table t1 (a int) with system versioning; +alter table t1 partition by system_time (partition pn current); +ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT +drop table t1; # End of 10.3 tests # # MDEV-22283 Server crashes in key_copy or unexpected error 156: The table already existed in the storage engine diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result index e8d550cefa6..b2bb59a4150 100644 --- a/mysql-test/suite/versioning/r/trx_id.result +++ b/mysql-test/suite/versioning/r/trx_id.result @@ -5,7 +5,15 @@ sys_trx_start bigint(20) unsigned as row start invisible, sys_trx_end bigint(20) unsigned as row end invisible, period for system_time (sys_trx_start, sys_trx_end) ) with system versioning; +# No history inside the transaction +start transaction; insert into t1 (x) values (1); +update t1 set x= x + 1; +update t1 set x= x + 1; +commit; +select *, sys_trx_start > 1, sys_trx_end from t1 for system_time all; +x sys_trx_start > 1 sys_trx_end +3 1 18446744073709551615 # ALTER ADD SYSTEM VERSIONING should write to mysql.transaction_registry set @@system_versioning_alter_history=keep; create or replace table t1 (x int); diff --git a/mysql-test/suite/versioning/r/update.result b/mysql-test/suite/versioning/r/update.result index cd26c341113..fbb9f541b06 100644 --- a/mysql-test/suite/versioning/r/update.result +++ b/mysql-test/suite/versioning/r/update.result @@ -241,6 +241,26 @@ B2 salary 1 2500 drop table t1; drop table t2; +# Ensure FTS retains correct history +create table t1 ( +x int, y text, fulltext (y), +row_start SYS_DATATYPE as row start invisible, +row_end SYS_DATATYPE as row end invisible, +period for system_time (row_start, row_end)) +with system versioning engine innodb; +insert into t1 values (1, repeat('LONG', 2048)); +update t1 set x= x + 1; +select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y; +x left(y, 4) length(y) check_row(row_start, row_end) +1 LONG 8192 HISTORICAL ROW +2 LONG 8192 CURRENT ROW +update t1 set y= 'SHORT'; +select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y; +x left(y, 4) length(y) check_row(row_start, row_end) +1 LONG 8192 HISTORICAL ROW +2 LONG 8192 HISTORICAL ROW +2 SHOR 5 CURRENT ROW +drop tables t1; ### Issue tempesta-tech/mariadb#365, bug 7 (duplicate of historical row) create or replace table t1 (a int primary key, b int) with system versioning engine myisam; @@ -350,3 +370,32 @@ insert into t1 (a) values (1), (2); update ignore t1 set a= 3; delete history from t1; drop table t1; +# +# MDEV-23446 UPDATE does not insert history row if the row is not changed +# +create table t1 ( +a int, +row_start SYS_DATATYPE as row start invisible, +row_end SYS_DATATYPE as row end invisible, +period for system_time (row_start, row_end)) with system versioning; +insert into t1 values (1); +update t1 set a= 1; +select *, check_row(row_start, row_end) from t1 for system_time all order by row_end; +a check_row(row_start, row_end) +1 HISTORICAL ROW +1 CURRENT ROW +# multi-update +create or replace table t2 like t1; +create or replace table t3 like t1; +insert into t2 values (1); +insert into t3 values (1); +update t2, t3 set t2.a= 1, t3.a= 1 where t2.a = t3.a; +select *, check_row(row_start, row_end) from t2 for system_time all order by row_end; +a check_row(row_start, row_end) +1 HISTORICAL ROW +1 CURRENT ROW +select *, check_row(row_start, row_end) from t2 for system_time all order by row_end; +a check_row(row_start, row_end) +1 HISTORICAL ROW +1 CURRENT ROW +drop tables t1, t2, t3; diff --git a/mysql-test/suite/versioning/t/delete.test b/mysql-test/suite/versioning/t/delete.test index 492463f9395..a5a0497fef2 100644 --- a/mysql-test/suite/versioning/t/delete.test +++ b/mysql-test/suite/versioning/t/delete.test @@ -94,4 +94,19 @@ drop procedure pr; drop trigger tr; drop table t1; +--echo # +--echo # MDEV-21138 Assertion `col->ord_part' or `f.col->ord_part' failed in row_build_index_entry_low +--echo # +create table t1 ( + f1 int, f2 text, f3 int, fulltext (f2), key(f1), key(f3), + foreign key r (f3) references t1 (f1) on delete set null) +with system versioning engine innodb; +insert into t1 values (1, repeat('a', 8193), 1), (1, repeat('b', 8193), 1); +select f1, f3, check_row_ts(row_start, row_end) from t1; +delete from t1; +select f1, f3, check_row_ts(row_start, row_end) from t1 for system_time all; + +# cleanup +drop table t1; + --source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test index 831ba518337..ed2ed4dd122 100644 --- a/mysql-test/suite/versioning/t/foreign.test +++ b/mysql-test/suite/versioning/t/foreign.test @@ -421,6 +421,8 @@ REPLACE INTO t2 SELECT * FROM t2; # Cleanup DROP TABLE t1, t2; +set timestamp= default; +set time_zone='+00:00'; --let $datadir= `select @@datadir` --remove_file $datadir/test/t1.data --remove_file $datadir/test/t1.data.2 @@ -459,6 +461,22 @@ delete from t2; drop tables t2, t1; --echo # +--echo # MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table +--echo # +create table t1 (pk int primary key, f1 int,f2 int, f3 text, + key(f1), fulltext(f3), key(f3(10)), + foreign key (f2) references t1 (f1) on delete set null +) engine=innodb with system versioning; + +insert into t1 values (1, 8, 8, 'SHORT'), (2, 8, 8, repeat('LONG', 8071)); + +delete from t1; +select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for system_time all order by pk; + +# cleanup +drop table t1; + +--echo # --echo # MDEV-20729 Fix REFERENCES constraint in column definition --echo # create or replace table t1( diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index a154cc37ae2..b4c2a058147 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -939,6 +939,15 @@ alter table t1 add partition (partition p2); # Cleanup drop table t1; +--echo # +--echo # MDEV-22178 Assertion `info->alias.str' failed in partition_info::check_partition_info instead of ER_VERS_WRONG_PARTS +--echo # +create or replace table t1 (a int) with system versioning; +--error ER_VERS_WRONG_PARTS +alter table t1 partition by system_time (partition pn current); +# Cleanup +drop table t1; + --echo # End of 10.3 tests --echo # diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test index 216f2082bed..db691a7ec19 100644 --- a/mysql-test/suite/versioning/t/trx_id.test +++ b/mysql-test/suite/versioning/t/trx_id.test @@ -14,7 +14,13 @@ create or replace table t1 ( period for system_time (sys_trx_start, sys_trx_end) ) with system versioning; +--echo # No history inside the transaction +start transaction; insert into t1 (x) values (1); +update t1 set x= x + 1; +update t1 set x= x + 1; +commit; +select *, sys_trx_start > 1, sys_trx_end from t1 for system_time all; --echo # ALTER ADD SYSTEM VERSIONING should write to mysql.transaction_registry set @@system_versioning_alter_history=keep; diff --git a/mysql-test/suite/versioning/t/update.test b/mysql-test/suite/versioning/t/update.test index 06f81ea9064..7f99e307942 100644 --- a/mysql-test/suite/versioning/t/update.test +++ b/mysql-test/suite/versioning/t/update.test @@ -147,6 +147,21 @@ select @tmp2 = sys_trx_start as B2, salary from t2; drop table t1; drop table t2; +--echo # Ensure FTS retains correct history +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create table t1 ( + x int, y text, fulltext (y), + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end)) +with system versioning engine innodb; +insert into t1 values (1, repeat('LONG', 2048)); +update t1 set x= x + 1; +select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y; +update t1 set y= 'SHORT'; +select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y; +drop tables t1; + --echo ### Issue tempesta-tech/mariadb#365, bug 7 (duplicate of historical row) create or replace table t1 (a int primary key, b int) with system versioning engine myisam; @@ -286,4 +301,29 @@ delete history from t1; # cleanup drop table t1; +--echo # +--echo # MDEV-23446 UPDATE does not insert history row if the row is not changed +--echo # +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create table t1 ( + a int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end)) with system versioning; +insert into t1 values (1); +update t1 set a= 1; +select *, check_row(row_start, row_end) from t1 for system_time all order by row_end; + +--echo # multi-update +create or replace table t2 like t1; +create or replace table t3 like t1; +insert into t2 values (1); +insert into t3 values (1); +update t2, t3 set t2.a= 1, t3.a= 1 where t2.a = t3.a; +select *, check_row(row_start, row_end) from t2 for system_time all order by row_end; +select *, check_row(row_start, row_end) from t2 for system_time all order by row_end; + +# cleanup +drop tables t1, t2, t3; + source suite/versioning/common_finish.inc; diff --git a/mysys/my_addr_resolve.c b/mysys/my_addr_resolve.c index bf2c89a9463..0603a50fac7 100644 --- a/mysys/my_addr_resolve.c +++ b/mysys/my_addr_resolve.c @@ -170,7 +170,6 @@ static pid_t pid; static char addr2line_binary[1024]; static char output[1024]; static struct pollfd poll_fds; -Dl_info info; int start_addr2line_fork(const char *binary_path) { diff --git a/plugin/feedback/sender_thread.cc b/plugin/feedback/sender_thread.cc index fd9ab65c0fa..19b68b70650 100644 --- a/plugin/feedback/sender_thread.cc +++ b/plugin/feedback/sender_thread.cc @@ -114,6 +114,7 @@ static int prepare_for_fill(TABLE_LIST *tables) tables->init_one_table(&INFORMATION_SCHEMA_NAME, &tbl_name, 0, TL_READ); tables->schema_table= i_s_feedback; + tables->schema_table_reformed= 1; tables->select_lex= thd->lex->first_select_lex(); DBUG_ASSERT(tables->select_lex); tables->table= create_schema_table(thd, tables); diff --git a/plugin/server_audit/server_audit.c b/plugin/server_audit/server_audit.c index 87f53b1db87..83799b4c714 100644 --- a/plugin/server_audit/server_audit.c +++ b/plugin/server_audit/server_audit.c @@ -2070,13 +2070,9 @@ static void update_connection_info(struct connection_info *cn, { case MYSQL_AUDIT_CONNECTION_CONNECT: setup_connection_connect(cn, event); - if (event->status == 0 && event->proxy_user && event->proxy_user[0]) - log_proxy(cn, event); break; case MYSQL_AUDIT_CONNECTION_CHANGE_USER: *after_action= AA_CHANGE_USER; - if (event->proxy_user && event->proxy_user[0]) - log_proxy(cn, event); break; default:; } @@ -2194,6 +2190,8 @@ void auditing(MYSQL_THD thd, unsigned int event_class, const void *ev) { case MYSQL_AUDIT_CONNECTION_CONNECT: log_connection(cn, event, event->status ? "FAILED_CONNECT": "CONNECT"); + if (event->status == 0 && event->proxy_user && event->proxy_user[0]) + log_proxy(cn, event); break; case MYSQL_AUDIT_CONNECTION_DISCONNECT: if (use_event_data_for_disconnect) @@ -2203,6 +2201,8 @@ void auditing(MYSQL_THD thd, unsigned int event_class, const void *ev) break; case MYSQL_AUDIT_CONNECTION_CHANGE_USER: log_connection(cn, event, "CHANGEUSER"); + if (event->proxy_user && event->proxy_user[0]) + log_proxy(cn, event); break; default:; } diff --git a/scripts/mysqld_safe.sh b/scripts/mysqld_safe.sh index 85c9363081c..126eb924814 100644 --- a/scripts/mysqld_safe.sh +++ b/scripts/mysqld_safe.sh @@ -126,7 +126,7 @@ log_generic () { case $logging in init) ;; # Just echo the message, don't save it anywhere file) - if [ -n "$helper" ]; then + if [ "$helper_exist" -eq "0" ]; then echo "$msg" | "$helper" "$user" log "$err_log" fi ;; @@ -150,7 +150,7 @@ eval_log_error () { local cmd="$1" case $logging in file) - if [ -n "$helper" ]; then + if [ "$helper_exist" -eq "0" ]; then cmd="$cmd 2>&1 | "`shell_quote_string "$helper"`" $user log "`shell_quote_string "$err_log"` fi ;; @@ -533,10 +533,9 @@ fi helper=`find_in_bin mysqld_safe_helper` print_defaults=`find_in_bin my_print_defaults` - # Check if helper exists -$helper --help >/dev/null 2>&1 || helper="" - +command -v $helper --help >/dev/null 2>&1 +helper_exist=$? # # Second, try to find the data directory # @@ -943,7 +942,6 @@ fi # Avoid 'nohup: ignoring input' warning test -n "$NOHUP_NICENESS" && cmd="$cmd < /dev/null" - log_notice "Starting $MYSQLD daemon with databases from $DATADIR" # variable to track the current number of "fast" (a.k.a. subsecond) restarts diff --git a/sql/field.cc b/sql/field.cc index 2173670572d..45b3a3c703e 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -8549,7 +8549,7 @@ int Field_blob::store(const char *from,size_t length,CHARSET_INFO *cs) DBUG_ASSERT(length <= max_data_length()); new_length= length; - copy_length= (size_t)MY_MIN(UINT_MAX,table->in_use->variables.group_concat_max_len); + copy_length= table->in_use->variables.group_concat_max_len; if (new_length > copy_length) { new_length= Well_formed_prefix(cs, diff --git a/sql/handler.cc b/sql/handler.cc index bdf6011c364..228c016e082 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3526,6 +3526,13 @@ int handler::update_auto_increment() (table->auto_increment_field_not_null && thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO)) { + + /* + There could be an error reported because value was truncated + when strict mode is enabled. + */ + if (thd->is_error()) + DBUG_RETURN(HA_ERR_AUTOINC_ERANGE); /* Update next_insert_id if we had already generated a value in this statement (case of INSERT VALUES(null),(3763),(null): diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index bfd415344ef..c93f4761fc9 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -5616,6 +5616,7 @@ void Item_func_like::print(String *str, enum_query_type query_type) longlong Item_func_like::val_int() { DBUG_ASSERT(fixed == 1); + DBUG_ASSERT(escape != -1); String* res= args[0]->val_str(&cmp_value1); if (args[0]->null_value) { @@ -5702,15 +5703,29 @@ bool fix_escape_item(THD *thd, Item *escape_item, String *tmp_str, bool escape_used_in_parsing, CHARSET_INFO *cmp_cs, int *escape) { - if (!escape_item->const_during_execution()) + /* + ESCAPE clause accepts only constant arguments and Item_param. + + Subqueries during context_analysis_only might decide they're + const_during_execution, but not quite const yet, not evaluate-able. + This is fine, as most of context_analysis_only modes will never + reach val_int(), so we won't need the value. + CONTEXT_ANALYSIS_ONLY_DERIVED being a notable exception here. + */ + if (!escape_item->const_during_execution() || + (!escape_item->const_item() && + !(thd->lex->context_analysis_only & ~CONTEXT_ANALYSIS_ONLY_DERIVED))) { my_error(ER_WRONG_ARGUMENTS,MYF(0),"ESCAPE"); return TRUE; } - + + IF_DBUG(*escape= -1,); + if (escape_item->const_item()) { /* If we are on execution stage */ + /* XXX is it safe to evaluate is_expensive() items here? */ String *escape_str= escape_item->val_str(tmp_str); if (escape_str) { diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index fa715badfc7..46412fe8ad6 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2809,6 +2809,13 @@ public: return this; } + bool walk(Item_processor processor, bool walk_subquery, void *arg) + { + return walk_args(processor, walk_subquery, arg) + || escape_item->walk(processor, walk_subquery, arg) + || (this->*processor)(arg); + } + bool find_selective_predicates_list_processor(void *arg); Item *get_copy(THD *thd) diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 5d0578df4ac..7106f72e237 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -3780,7 +3780,7 @@ int dump_leaf_key(void* key_arg, element_count count __attribute__((unused)), { Item_func_group_concat *item= (Item_func_group_concat *) item_arg; TABLE *table= item->table; - uint max_length= (uint)table->in_use->variables.group_concat_max_len; + uint max_length= table->in_use->variables.group_concat_max_len; String tmp((char *)table->record[1], table->s->reclength, default_charset_info); String tmp2; @@ -4109,7 +4109,7 @@ bool Item_func_group_concat::repack_tree(THD *thd) DBUG_ASSERT(tree->size_of_element == st.tree.size_of_element); st.table= table; st.len= 0; - st.maxlen= (size_t)thd->variables.group_concat_max_len; + st.maxlen= thd->variables.group_concat_max_len; tree_walk(tree, ©_to_tree, &st, left_root_right); if (st.len <= st.maxlen) // Copying aborted. Must be OOM { @@ -4131,7 +4131,7 @@ bool Item_func_group_concat::repack_tree(THD *thd) decreases up to N=10 (that is, factor=1024) and then starts to increase, again, very slowly. */ -#define GCONCAT_REPACK_FACTOR (1 << 10) +#define GCONCAT_REPACK_FACTOR 10 bool Item_func_group_concat::add(bool exclude_nulls) { @@ -4186,7 +4186,7 @@ bool Item_func_group_concat::add(bool exclude_nulls) { THD *thd= table->in_use; table->field[0]->store(row_str_len, FALSE); - if (tree_len > thd->variables.group_concat_max_len * GCONCAT_REPACK_FACTOR + if ((tree_len >> GCONCAT_REPACK_FACTOR) > thd->variables.group_concat_max_len && tree->elements_in_tree > 1) if (repack_tree(thd)) return 1; @@ -4240,9 +4240,9 @@ Item_func_group_concat::fix_fields(THD *thd, Item **ref) result.set_charset(collation.collation); result_field= 0; null_value= 1; - max_length= (uint32)(thd->variables.group_concat_max_len - / collation.collation->mbminlen - * collation.collation->mbmaxlen); + max_length= (uint32)MY_MIN(thd->variables.group_concat_max_len + / collation.collation->mbminlen + * collation.collation->mbmaxlen, UINT_MAX32); uint32 offset; if (separator->needs_conversion(separator->length(), separator->charset(), diff --git a/sql/opt_range.cc b/sql/opt_range.cc index cb5a0604733..f146fc25126 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -7801,6 +7801,30 @@ SEL_TREE *Item_func_in::get_func_mm_tree(RANGE_OPT_PARAM *param, if (array->count > NOT_IN_IGNORE_THRESHOLD || !value_item) DBUG_RETURN(0); + /* + If this is "unique_key NOT IN (...)", do not consider it sargable (for + any index, not just the unique one). The logic is as follows: + - if there are only a few constants, this condition is not selective + (unless the table is also very small in which case we won't gain + anything) + - If there are a lot of constants, the overhead of building and + processing enormous range list is not worth it. + */ + if (param->using_real_indexes) + { + key_map::Iterator it(field->key_start); + uint key_no; + while ((key_no= it++) != key_map::Iterator::BITMAP_END) + { + KEY *key_info= &field->table->key_info[key_no]; + if (key_info->user_defined_key_parts == 1 && + (key_info->flags & HA_NOSAME)) + { + DBUG_RETURN(0); + } + } + } + /* Get a SEL_TREE for "(-inf|NULL) < X < c_0" interval. */ uint i=0; do diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 3a086aef04c..6cb4a12e51f 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -1147,7 +1147,7 @@ bool JOIN_TAB::fix_splitting(SplM_plan_info *spl_plan, bool JOIN::fix_all_splittings_in_plan() { table_map prev_tables= 0; - table_map all_tables= (1 << table_count) - 1; + table_map all_tables= (table_map(1) << table_count) - 1; for (uint tablenr= 0; tablenr < table_count; tablenr++) { POSITION *cur_pos= &best_positions[tablenr]; diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 25e8aa42c7c..aed0ad02e4d 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -13201,15 +13201,6 @@ static bool find_mpvio_user(MPVIO_EXT *mpvio) ACL_USER *user= find_user_or_anon(sctx->host, sctx->user, sctx->ip); - if (user && user->password_errors >= max_password_errors && !ignore_max_password_errors(user)) - { - mysql_mutex_unlock(&acl_cache->lock); - my_error(ER_USER_IS_BLOCKED, MYF(0)); - general_log_print(mpvio->auth_info.thd, COM_CONNECT, - ER_THD(mpvio->auth_info.thd, ER_USER_IS_BLOCKED)); - DBUG_RETURN(1); - } - if (user) mpvio->acl_user= user->copy(mpvio->auth_info.thd->mem_root); @@ -13246,6 +13237,15 @@ static bool find_mpvio_user(MPVIO_EXT *mpvio) mpvio->make_it_fail= true; } + if (mpvio->acl_user->password_errors >= max_password_errors && + !ignore_max_password_errors(mpvio->acl_user)) + { + my_error(ER_USER_IS_BLOCKED, MYF(0)); + general_log_print(mpvio->auth_info.thd, COM_CONNECT, + ER_THD(mpvio->auth_info.thd, ER_USER_IS_BLOCKED)); + DBUG_RETURN(1); + } + /* user account requires non-default plugin and the client is too old */ if (mpvio->acl_user->auth->plugin.str != native_password_plugin_name.str && mpvio->acl_user->auth->plugin.str != old_password_plugin_name.str && @@ -14586,6 +14586,7 @@ static int native_password_get_salt(const char *hash, size_t hash_length, { DBUG_ASSERT(sizeof(invalid_password) > SCRAMBLE_LENGTH); DBUG_ASSERT(*out_length >= SCRAMBLE_LENGTH); + DBUG_ASSERT(*out_length >= sizeof(invalid_password)); if (hash_length == 0) { *out_length= 0; @@ -14596,14 +14597,27 @@ static int native_password_get_salt(const char *hash, size_t hash_length, { if (hash_length == 7 && strcmp(hash, "invalid") == 0) { - memcpy(out, invalid_password, SCRAMBLED_PASSWORD_CHAR_LENGTH); - *out_length= SCRAMBLED_PASSWORD_CHAR_LENGTH; + memcpy(out, invalid_password, sizeof(invalid_password)); + *out_length= sizeof(invalid_password); return 0; } my_error(ER_PASSWD_LENGTH, MYF(0), SCRAMBLED_PASSWORD_CHAR_LENGTH); return 1; } + for (const char *c= hash + 1; c < (hash + hash_length); c++) + { + /* If any non-hex characters are found, mark the password as invalid. */ + if (!(*c >= '0' && *c <= '9') && + !(*c >= 'A' && *c <= 'F') && + !(*c >= 'a' && *c <= 'f')) + { + memcpy(out, invalid_password, sizeof(invalid_password)); + *out_length= sizeof(invalid_password); + return 0; + } + } + *out_length= SCRAMBLE_LENGTH; get_salt_from_password(out, hash); return 0; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index d02d130c084..291cad89d79 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6151,6 +6151,7 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, #ifndef NO_EMBEDDED_ACCESS_CHECKS /* Check if there are sufficient access rights to the found field. */ if (check_privileges && + !table_list->is_derived() && check_column_grant_in_table_ref(thd, *actual_table, name, length, fld)) fld= WRONG_GRANT; else @@ -8020,36 +8021,23 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, #ifndef NO_EMBEDDED_ACCESS_CHECKS /* - Ensure that we have access rights to all fields to be inserted. Under - some circumstances, this check may be skipped. + Ensure that we have access rights to all fields to be inserted + the table 'tables'. Under some circumstances, this check may be skipped. - - If any_privileges is true, skip the check. + The check is skipped in the following cases: - - If the SELECT privilege has been found as fulfilled already for both - the TABLE and TABLE_LIST objects (and both of these exist, of - course), the check is skipped. + - any_privileges is true - - If the SELECT privilege has been found fulfilled for the TABLE object - and the TABLE_LIST represents a derived table other than a view (see - below), the check is skipped. + - the table is a derived table - - If the TABLE_LIST object represents a view, we may skip checking if - the SELECT privilege has been found fulfilled for it, regardless of - the TABLE object. + - the table is a view with SELECT privilege - - If there is no TABLE object, the test is skipped if either - * the TABLE_LIST does not represent a view, or - * the SELECT privilege has been found fulfilled. - - A TABLE_LIST that is not a view may be a subquery, an - information_schema table, or a nested table reference. See the comment - for TABLE_LIST. + - the table is a base table with SELECT privilege */ - if (!((table && tables->is_non_derived() && - (table->grant.privilege & SELECT_ACL)) || - ((!tables->is_non_derived() && - (tables->grant.privilege & SELECT_ACL)))) && - !any_privileges) + if (!any_privileges && + !tables->is_derived() && + !(tables->is_view() && (tables->grant.privilege & SELECT_ACL)) && + !(table && (table->grant.privilege & SELECT_ACL))) { field_iterator.set(tables); if (check_grant_all_columns(thd, SELECT_ACL, &field_iterator)) diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc index 35e3a7c5608..57070b038ca 100644 --- a/sql/sql_cache.cc +++ b/sql/sql_cache.cc @@ -1449,7 +1449,7 @@ void Query_cache::store_query(THD *thd, TABLE_LIST *tables_used) DBUG_PRINT("qcache", ("\ long %d, 4.1: %d, eof: %d, bin_proto: %d, more results %d, pkt_nr: %d, \ CS client: %u, CS result: %u, CS conn: %u, limit: %llu, TZ: %p, \ -sql mode: 0x%llx, sort len: %llu, conncat len: %llu, div_precision: %zu, \ +sql mode: 0x%llx, sort len: %llu, concat len: %u, div_precision: %zu, \ def_week_frmt: %zu, in_trans: %d, autocommit: %d", (int)flags.client_long_flag, (int)flags.client_protocol_41, @@ -1949,7 +1949,7 @@ Query_cache::send_result_to_client(THD *thd, char *org_sql, uint query_length) DBUG_PRINT("qcache", ("\ long %d, 4.1: %d, eof: %d, bin_proto: %d, more results %d, pkt_nr: %d, \ CS client: %u, CS result: %u, CS conn: %u, limit: %llu, TZ: %p, \ -sql mode: 0x%llx, sort len: %llu, conncat len: %llu, div_precision: %zu, \ +sql mode: 0x%llx, sort len: %llu, concat len: %u, div_precision: %zu, \ def_week_frmt: %zu, in_trans: %d, autocommit: %d", (int)flags.client_long_flag, (int)flags.client_protocol_41, diff --git a/sql/sql_cache.h b/sql/sql_cache.h index b92b3972512..196acf6fa2e 100644 --- a/sql/sql_cache.h +++ b/sql/sql_cache.h @@ -558,11 +558,11 @@ struct Query_cache_query_flags uint character_set_client_num; uint character_set_results_num; uint collation_connection_num; + uint group_concat_max_len; ha_rows limit; Time_zone *time_zone; sql_mode_t sql_mode; ulonglong max_sort_length; - ulonglong group_concat_max_len; size_t default_week_format; size_t div_precision_increment; MY_LOCALE *lc_time_names; diff --git a/sql/sql_class.h b/sql/sql_class.h index 1da82edc061..3c28d089e20 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -641,7 +641,6 @@ typedef struct system_variables ulonglong bulk_insert_buff_size; ulonglong join_buff_size; ulonglong sortbuff_size; - ulonglong group_concat_max_len; ulonglong default_regex_flags; ulonglong max_mem_used; @@ -734,6 +733,8 @@ typedef struct system_variables uint32 gtid_domain_id; uint64 gtid_seq_no; + uint group_concat_max_len; + /** Default transaction access mode. READ ONLY (true) or READ WRITE (false). */ @@ -6140,10 +6141,15 @@ class select_union_recursive :public select_unit public: /* The temporary table with the new records generated by one iterative step */ TABLE *incr_table; + /* The TMP_TABLE_PARAM structure used to create incr_table */ + TMP_TABLE_PARAM incr_table_param; /* One of tables from the list rec_tables (determined dynamically) */ TABLE *first_rec_table_to_update; - /* The temporary tables used for recursive table references */ - List<TABLE> rec_tables; + /* + The list of all recursive table references to the CTE for whose + specification this select_union_recursive was created + */ + List<TABLE_LIST> rec_table_refs; /* The count of how many times cleanup() was called with cleaned==false for the unit specifying the recursive CTE for which this object was created @@ -6153,7 +6159,8 @@ class select_union_recursive :public select_unit select_union_recursive(THD *thd_arg): select_unit(thd_arg), - incr_table(0), first_rec_table_to_update(0), cleanup_count(0) {}; + incr_table(0), first_rec_table_to_update(0), cleanup_count(0) + { incr_table_param.init(); }; int send_data(List<Item> &items); bool create_result_table(THD *thd, List<Item> *column_types, diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 5bf9930ef21..c32ea8c0238 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -253,6 +253,8 @@ With_element *With_clause::find_table_def(TABLE_LIST *table, !table->is_fqtn) { table->set_derived(); + table->db.str= empty_c_string; + table->db.length= 0; return with_elem; } } @@ -890,8 +892,6 @@ st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, goto err; spec_tables_tail= tbl; } - if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) - goto err; if (spec_tables) { if (with_table->next_global) @@ -917,6 +917,22 @@ st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, with_select)); if (check_dependencies_in_with_clauses(lex->with_clauses_list)) res= NULL; + /* + Resolve references to CTE from the spec_tables list that has not + been resolved yet. + */ + for (TABLE_LIST *tbl= spec_tables; + tbl; + tbl= tbl->next_global) + { + if (!tbl->with) + tbl->with= with_select->find_table_def_in_with_clauses(tbl); + if (tbl == spec_tables_tail) + break; + } + if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) + goto err; + lex->sphead= NULL; // in order not to delete lex->sphead lex_end(lex); err: @@ -1521,10 +1537,11 @@ void With_element::print(THD *thd, String *str, enum_query_type query_type) bool With_element::instantiate_tmp_tables() { - List_iterator_fast<TABLE> li(rec_result->rec_tables); - TABLE *rec_table; - while ((rec_table= li++)) + List_iterator_fast<TABLE_LIST> li(rec_result->rec_table_refs); + TABLE_LIST *rec_tbl; + while ((rec_tbl= li++)) { + TABLE *rec_table= rec_tbl->table; if (!rec_table->is_created() && instantiate_tmp_table(rec_table, rec_table->s->key_info, diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 01f67476688..94d5ceb309d 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -281,7 +281,15 @@ int TABLE::delete_row() store_record(this, record[1]); vers_update_end(); - return file->ha_update_row(record[1], record[0]); + int err= file->ha_update_row(record[1], record[0]); + /* + MDEV-23644: we get HA_ERR_FOREIGN_DUPLICATE_KEY iff we already got history + row with same trx_id which is the result of foreign key action, so we + don't need one more history row. + */ + if (err == HA_ERR_FOREIGN_DUPLICATE_KEY) + return file->ha_delete_row(record[0]); + return err; } diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index a9155f361b5..90d7ce8fc2c 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -710,7 +710,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) if (derived->is_with_table_recursive_reference()) { /* Here 'derived" is a secondary recursive table reference */ - unit->with_element->rec_result->rec_tables.push_back(derived->table); + unit->with_element->rec_result->rec_table_refs.push_back(derived); } } DBUG_ASSERT(derived->table || res); @@ -808,17 +808,17 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) derived->fill_me= FALSE; - if (!(derived->derived_result= new (thd->mem_root) select_unit(thd))) + if ((!derived->is_with_table_recursive_reference() || + !derived->derived_result) && + !(derived->derived_result= new (thd->mem_root) select_unit(thd))) DBUG_RETURN(TRUE); // out of memory - lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED; // st_select_lex_unit::prepare correctly work for single select if ((res= unit->prepare(derived, derived->derived_result, 0))) goto exit; if (derived->with && (res= derived->with->process_columns_of_derived_unit(thd, unit))) goto exit; - lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED; if ((res= check_duplicate_names(thd, unit->types, 0))) goto exit; @@ -827,7 +827,8 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) Depending on the result field translation will or will not be created. */ - if (derived->init_derived(thd, FALSE)) + if (!derived->is_with_table_recursive_reference() && + derived->init_derived(thd, FALSE)) goto exit; /* diff --git a/sql/sql_help.cc b/sql/sql_help.cc index e31e51d0316..ebda874d1fe 100644 --- a/sql/sql_help.cc +++ b/sql/sql_help.cc @@ -741,6 +741,9 @@ static bool mysqld_help_internal(THD *thd, const char *mask) &name, &description, &example); delete select; + if (thd->is_error()) + goto error; + if (count_topics == 0) { int UNINIT_VAR(key_id); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index f09d7140d3b..9211625c804 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -5847,6 +5847,14 @@ mysql_execute_command(THD *thd) break; } case SQLCOM_XA_START: +#ifdef WITH_WSREP + if (WSREP(thd)) + { + my_error(ER_NOT_SUPPORTED_YET, MYF(0), + "XA transactions with Galera replication"); + break; + } +#endif /* WITH_WSREP */ if (trans_xa_start(thd)) goto error; my_ok(thd); @@ -6893,6 +6901,9 @@ check_access(THD *thd, privilege_t want_access, bool check_single_table_access(THD *thd, privilege_t privilege, TABLE_LIST *tables, bool no_errors) { + if (tables->derived) + return 0; + Switch_to_definer_security_ctx backup_sctx(thd, tables); const char *db_name; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 808d49d4067..5422346884d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -13399,10 +13399,6 @@ void JOIN_TAB::cleanup() { DBUG_ENTER("JOIN_TAB::cleanup"); - if (tab_list && tab_list->is_with_table_recursive_reference() && - tab_list->with->is_cleaned()) - DBUG_VOID_RETURN; - DBUG_PRINT("enter", ("tab: %p table %s.%s", this, (table ? table->s->db.str : "?"), diff --git a/sql/sql_show.cc b/sql/sql_show.cc index ca3e2aaa97e..87d41756684 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -9472,7 +9472,8 @@ ST_FIELD_INFO check_constraints_fields_info[]= Column("CONSTRAINT_SCHEMA", Name(), NOT_NULL, OPEN_FULL_TABLE), Column("TABLE_NAME", Name(), NOT_NULL, OPEN_FULL_TABLE), Column("CONSTRAINT_NAME", Name(), NOT_NULL, OPEN_FULL_TABLE), - Column("CHECK_CLAUSE", Name(), NOT_NULL, OPEN_FULL_TABLE), + Column("CHECK_CLAUSE", Longtext(MAX_FIELD_VARCHARLENGTH), + NOT_NULL, OPEN_FULL_TABLE), CEnd() }; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 4e1ff93c576..6825fbc8e24 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2311,8 +2311,8 @@ int mysql_rm_table_no_locks(THD *thd, TABLE_LIST *tables, bool if_exists, Table_type table_type; size_t path_length= 0; char *path_end= 0; - error= 0; + DBUG_PRINT("table", ("table_l: '%s'.'%s' table: %p s: %p", db.str, table_name.str, table->table, table->table ? table->table->s : NULL)); @@ -2327,9 +2327,40 @@ int mysql_rm_table_no_locks(THD *thd, TABLE_LIST *tables, bool if_exists, thd->find_temporary_table(table) && table->mdl_request.ticket != NULL)); + if (drop_sequence && table->table && + table->table->s->table_type != TABLE_TYPE_SEQUENCE) + { + if (if_exists) + { + char buff[FN_REFLEN]; + String tbl_name(buff, sizeof(buff), system_charset_info); + tbl_name.length(0); + tbl_name.append(&db); + tbl_name.append('.'); + tbl_name.append(&table->table_name); + push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, + ER_NOT_SEQUENCE2, ER_THD(thd, ER_NOT_SEQUENCE2), + tbl_name.c_ptr_safe()); + + /* + Our job is done here. This statement was added to avoid executing + unnecessary code farther below which in some strange corner cases + caused the server to crash (see MDEV-17896). + */ + continue; + } + /* "DROP SEQUENCE" but a sequence table was not found */ + unknown_tables.append(&db); + unknown_tables.append('.'); + unknown_tables.append(&table_name); + unknown_tables.append(','); + error= ENOENT; + not_found_errors++; + continue; + } + /* First try to delete temporary tables and temporary sequences */ - if ((table->open_type != OT_BASE_ONLY && is_temporary_table(table)) && - (!drop_sequence || table->table->s->table_type == TABLE_TYPE_SEQUENCE)) + if ((table->open_type != OT_BASE_ONLY && is_temporary_table(table))) { table_creation_was_logged= table->table->s->table_creation_was_logged; if (thd->drop_temporary_table(table->table, &is_trans, true)) @@ -10516,6 +10547,7 @@ do_continue:; tmp_disable_binlog(thd); create_info->options|=HA_CREATE_TMP_ALTER; + create_info->alias= alter_ctx.table_name; error= create_table_impl(thd, alter_ctx.db, alter_ctx.table_name, alter_ctx.new_db, alter_ctx.tmp_name, alter_ctx.get_tmp_path(), diff --git a/sql/sql_type.cc b/sql/sql_type.cc index 6c5f72c2ce2..5a31b39c7b6 100644 --- a/sql/sql_type.cc +++ b/sql/sql_type.cc @@ -9289,11 +9289,18 @@ LEX_CSTRING Charset::collation_specific_name() const for character sets and collations, so a collation name not necessarily starts with the character set name. */ + LEX_CSTRING retval; size_t csname_length= strlen(m_charset->csname); if (strncmp(m_charset->name, m_charset->csname, csname_length)) - return {NULL, 0}; + { + retval.str= NULL; + retval.length= 0; + return retval; + } const char *ptr= m_charset->name + csname_length; - return {ptr, strlen(ptr) }; + retval.str= ptr; + retval.length= strlen(ptr); + return retval; } diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 6da1175e709..b88d78c0db3 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -362,7 +362,10 @@ select_union_recursive::create_result_table(THD *thd_arg, hidden)) return true; - if (! (incr_table= create_tmp_table(thd_arg, &tmp_table_param, *column_types, + incr_table_param.init(); + incr_table_param.field_count= column_types->elements; + incr_table_param.bit_fields_as_long= bit_fields_as_long; + if (! (incr_table= create_tmp_table(thd_arg, &incr_table_param, *column_types, (ORDER*) 0, false, 1, options, HA_POS_ERROR, &empty_clex_str, true, keep_row_order))) @@ -372,20 +375,6 @@ select_union_recursive::create_result_table(THD *thd_arg, for (uint i=0; i < table->s->fields; i++) incr_table->field[i]->flags &= ~(PART_KEY_FLAG | PART_INDIRECT_KEY_FLAG); - TABLE *rec_table= 0; - if (! (rec_table= create_tmp_table(thd_arg, &tmp_table_param, *column_types, - (ORDER*) 0, false, 1, - options, HA_POS_ERROR, alias, - true, keep_row_order))) - return true; - - rec_table->keys_in_use_for_query.clear_all(); - for (uint i=0; i < table->s->fields; i++) - rec_table->field[i]->flags &= ~(PART_KEY_FLAG | PART_INDIRECT_KEY_FLAG); - - if (rec_tables.push_back(rec_table)) - return true; - return false; } @@ -908,23 +897,25 @@ void select_union_recursive::cleanup() free_tmp_table(thd, incr_table); } - List_iterator<TABLE> it(rec_tables); - TABLE *tab; - while ((tab= it++)) + List_iterator<TABLE_LIST> it(rec_table_refs); + TABLE_LIST *tbl; + while ((tbl= it++)) { + TABLE *tab= tbl->table; if (tab->is_created()) { tab->file->extra(HA_EXTRA_RESET_STATE); tab->file->ha_delete_all_rows(); } - /* + /* The table will be closed later in close_thread_tables(), because it might be used in the statements like ANALYZE WITH r AS (...) SELECT * from r - where r is defined through recursion. + where r is defined through recursion. */ tab->next= thd->rec_tables; thd->rec_tables= tab; + tbl->derived_result= 0; } } @@ -1630,9 +1621,33 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, } if (!derived_arg->table) { - derived_arg->table= with_element->rec_result->rec_tables.head(); - if (derived_arg->derived_result) - derived_arg->derived_result->table= derived_arg->table; + bool res= false; + + if ((!derived_arg->is_with_table_recursive_reference() || + !derived_arg->derived_result) && + !(derived_arg->derived_result= + new (thd->mem_root) select_unit(thd))) + goto err; // out of memory + thd->create_tmp_table_for_derived= TRUE; + + res= derived_arg->derived_result->create_result_table(thd, + &types, + FALSE, + create_options, + &derived_arg->alias, + FALSE, FALSE, + FALSE, 0); + thd->create_tmp_table_for_derived= FALSE; + if (res) + goto err; + derived_arg->derived_result->set_unit(this); + derived_arg->table= derived_arg->derived_result->table; + if (derived_arg->is_with_table_recursive_reference()) + { + /* Here 'derived_arg' is the primary recursive table reference */ + derived_arg->with->rec_result-> + rec_table_refs.push_back(derived_arg); + } } with_element->mark_as_with_prepared_anchor(); is_rec_result_table_created= true; @@ -2428,11 +2443,11 @@ bool st_select_lex_unit::exec_recursive() TABLE *incr_table= with_element->rec_result->incr_table; st_select_lex *end= NULL; bool is_unrestricted= with_element->is_unrestricted(); - List_iterator_fast<TABLE> li(with_element->rec_result->rec_tables); + List_iterator_fast<TABLE_LIST> li(with_element->rec_result->rec_table_refs); TMP_TABLE_PARAM *tmp_table_param= &with_element->rec_result->tmp_table_param; ha_rows examined_rows= 0; bool was_executed= executed; - TABLE *rec_table; + TABLE_LIST *rec_tbl; DBUG_ENTER("st_select_lex_unit::exec_recursive"); @@ -2510,8 +2525,9 @@ bool st_select_lex_unit::exec_recursive() else with_element->level++; - while ((rec_table= li++)) + while ((rec_tbl= li++)) { + TABLE *rec_table= rec_tbl->table; saved_error= incr_table->insert_all_rows_into_tmp_table(thd, rec_table, tmp_table_param, diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 9e6a1cd3bb4..d0956ec8bd1 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -51,8 +51,9 @@ compare_record(TABLE*). */ bool records_are_comparable(const TABLE *table) { - return ((table->file->ha_table_flags() & HA_PARTIAL_COLUMN_READ) == 0) || - bitmap_is_subset(table->write_set, table->read_set); + return !table->versioned(VERS_TRX_ID) && + (((table->file->ha_table_flags() & HA_PARTIAL_COLUMN_READ) == 0) || + bitmap_is_subset(table->write_set, table->read_set)); } @@ -1084,20 +1085,9 @@ update_begin: } else if (likely(!error)) { - if (has_vers_fields && table->versioned()) - { - if (table->versioned(VERS_TIMESTAMP)) - { - store_record(table, record[2]); - table->mark_columns_per_binlog_row_image(); - error= vers_insert_history_row(table); - restore_record(table, record[2]); - } - if (likely(!error)) - rows_inserted++; - } - if (likely(!error)) - updated++; + if (has_vers_fields && table->versioned(VERS_TRX_ID)) + rows_inserted++; + updated++; } if (likely(!error) && !record_was_same && table_list->has_period()) @@ -1113,6 +1103,19 @@ update_begin: if (unlikely(error) && (!ignore || table->file->is_fatal_error(error, HA_CHECK_ALL))) { + goto error; + } + } + + if (likely(!error) && has_vers_fields && table->versioned(VERS_TIMESTAMP)) + { + store_record(table, record[2]); + table->mark_columns_per_binlog_row_image(); + error= vers_insert_history_row(table); + restore_record(table, record[2]); + if (unlikely(error)) + { +error: /* If (ignore && error is ignorable) we don't have to do anything; otherwise... @@ -1123,10 +1126,11 @@ update_begin: flags|= ME_FATAL; /* Other handler errors are fatal */ prepare_record_for_error_message(error, table); - table->file->print_error(error,MYF(flags)); - error= 1; - break; - } + table->file->print_error(error,MYF(flags)); + error= 1; + break; + } + rows_inserted++; } if (table->triggers && @@ -1851,7 +1855,11 @@ int mysql_multi_update_prepare(THD *thd) During prepare phase acquire only S metadata locks instead of SW locks to keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE and global read lock. + + Don't evaluate any subqueries even if constant, because + tables aren't locked yet. */ + lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED; if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI) { if (open_tables(thd, &table_list, &table_count, @@ -1873,6 +1881,8 @@ int mysql_multi_update_prepare(THD *thd) lock_tables(thd, table_list, table_count, 0)) DBUG_RETURN(TRUE); + lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED; + (void) read_statistics_for_tables_if_needed(thd, table_list); /* @todo: downgrade the metadata locks here. */ @@ -2551,6 +2561,7 @@ int multi_update::send_data(List<Item> ¬_used_values) if (!ignore || table->file->is_fatal_error(error, HA_CHECK_ALL)) { +error: /* If (ignore && error == is ignorable) we don't have to do anything; otherwise... @@ -2572,19 +2583,8 @@ int multi_update::send_data(List<Item> ¬_used_values) error= 0; updated--; } - else if (has_vers_fields && table->versioned()) + else if (has_vers_fields && table->versioned(VERS_TRX_ID)) { - if (table->versioned(VERS_TIMESTAMP)) - { - store_record(table, record[2]); - if (vers_insert_history_row(table)) - { - restore_record(table, record[2]); - error= 1; - break; - } - restore_record(table, record[2]); - } updated_sys_ver++; } /* non-transactional or transactional table got modified */ @@ -2598,6 +2598,17 @@ int multi_update::send_data(List<Item> ¬_used_values) } } } + if (has_vers_fields && table->versioned(VERS_TIMESTAMP)) + { + store_record(table, record[2]); + if (vers_insert_history_row(table)) + { + restore_record(table, record[2]); + goto error; + } + restore_record(table, record[2]); + updated_sys_ver++; + } if (table->triggers && unlikely(table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, TRUE))) diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 617254244e1..cfd43bd13ab 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -292,6 +292,8 @@ bool create_view_precheck(THD *thd, TABLE_LIST *tables, TABLE_LIST *view, { for (tbl= sl->get_table_list(); tbl; tbl= tbl->next_local) { + if (!tbl->with && tbl->select_lex) + tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl); /* Ensure that we have some privileges on this table, more strict check will be done on column level after preparation, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 0b6f099b1b0..4fb9419d2f8 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -341,12 +341,12 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); */ /* Start SQL_MODE_DEFAULT_SPECIFIC */ -%expect 53 +%expect 46 /* End SQL_MODE_DEFAULT_SPECIFIC */ /* Start SQL_MODE_ORACLE_SPECIFIC -%expect 56 +%expect 49 End SQL_MODE_ORACLE_SPECIFIC */ @@ -1445,7 +1445,7 @@ End SQL_MODE_ORACLE_SPECIFIC */ %type <item> literal insert_ident order_ident temporal_literal simple_ident expr sum_expr in_sum_expr - variable variable_aux bool_pri + variable variable_aux predicate bit_expr parenthesized_expr table_wild simple_expr column_default_non_parenthesized_expr udf_expr primary_expr string_factor_expr mysql_concatenation_expr @@ -9364,23 +9364,19 @@ expr: if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | bool_pri - ; - -bool_pri: - bool_pri EQUAL_SYM predicate %prec EQUAL_SYM + | expr EQUAL_SYM predicate %prec EQUAL_SYM { $$= new (thd->mem_root) Item_func_equal(thd, $1, $3); if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | bool_pri comp_op predicate %prec '=' + | expr comp_op predicate %prec '=' { $$= (*$2)(0)->create(thd, $1, $3); if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | bool_pri comp_op all_or_any '(' subselect ')' %prec '=' + | expr comp_op all_or_any '(' subselect ')' %prec '=' { $$= all_any_subquery_creator(thd, $1, $2, $3, $5); if (unlikely($$ == NULL)) diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index d0eff0adada..44d29c5da0b 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2465,7 +2465,7 @@ static Sys_var_ulong Sys_max_sort_length( "the first max_sort_length bytes of each value are used; the rest " "are ignored)", SESSION_VAR(max_sort_length), CMD_LINE(REQUIRED_ARG), - VALID_RANGE(8, 8192*1024L), DEFAULT(1024), BLOCK_SIZE(1)); + VALID_RANGE(64, 8192*1024L), DEFAULT(1024), BLOCK_SIZE(1)); static Sys_var_ulong Sys_max_sp_recursion_depth( "max_sp_recursion_depth", @@ -4785,11 +4785,11 @@ static Sys_var_ulong Sys_default_week_format( SESSION_VAR(default_week_format), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, 7), DEFAULT(0), BLOCK_SIZE(1)); -static Sys_var_ulonglong Sys_group_concat_max_len( +static Sys_var_uint Sys_group_concat_max_len( "group_concat_max_len", "The maximum length of the result of function GROUP_CONCAT()", SESSION_VAR(group_concat_max_len), CMD_LINE(REQUIRED_ARG), - VALID_RANGE(4, SIZE_T_MAX), DEFAULT(1024*1024), BLOCK_SIZE(1)); + VALID_RANGE(4, UINT_MAX32), DEFAULT(1024*1024), BLOCK_SIZE(1)); static char *glob_hostname_ptr; static Sys_var_charptr Sys_hostname( diff --git a/sql/wsrep_binlog.cc b/sql/wsrep_binlog.cc index da899321ba8..5595f263fa5 100644 --- a/sql/wsrep_binlog.cc +++ b/sql/wsrep_binlog.cc @@ -358,7 +358,7 @@ void wsrep_register_for_group_commit(THD *thd) DBUG_VOID_RETURN; } - DBUG_ASSERT(thd->wsrep_trx().state() == wsrep::transaction::s_committing); + DBUG_ASSERT(thd->wsrep_trx().ordered()); wait_for_commit *wfc= thd->wait_for_commit_ptr= &thd->wsrep_wfc; diff --git a/sql/wsrep_high_priority_service.cc b/sql/wsrep_high_priority_service.cc index 3116315b9c7..6136a459d16 100644 --- a/sql/wsrep_high_priority_service.cc +++ b/sql/wsrep_high_priority_service.cc @@ -159,11 +159,12 @@ Wsrep_high_priority_service::Wsrep_high_priority_service(THD* thd) /* Disable general logging on applier threads */ thd->variables.option_bits |= OPTION_LOG_OFF; - /* Enable binlogging if opt_log_slave_updates is set */ - if (opt_log_slave_updates) - thd->variables.option_bits|= OPTION_BIN_LOG; - else - thd->variables.option_bits&= ~(OPTION_BIN_LOG); + + /* enable binlogging regardless of log_slave_updates setting + this is for ensuring that both local and applier transaction go through + same commit ordering algorithm in group commit control + */ + thd->variables.option_bits|= OPTION_BIN_LOG; thd->net.vio= 0; thd->reset_db(&db_str); @@ -462,8 +463,24 @@ int Wsrep_high_priority_service::log_dummy_write_set(const wsrep::ws_handle& ws_ cs.before_rollback(); cs.after_rollback(); } + + if (!WSREP_EMULATE_BINLOG(m_thd)) + { + wsrep_register_for_group_commit(m_thd); + ret = ret || cs.provider().commit_order_leave(ws_handle, ws_meta, err); + m_thd->wait_for_prior_commit(); + } + wsrep_set_SE_checkpoint(ws_meta.gtid(), wsrep_gtid_server.gtid()); - ret= ret || cs.provider().commit_order_leave(ws_handle, ws_meta, err); + + if (!WSREP_EMULATE_BINLOG(m_thd)) + { + wsrep_unregister_from_group_commit(m_thd); + } + else + { + ret= ret || cs.provider().commit_order_leave(ws_handle, ws_meta, err); + } cs.after_applying(); } DBUG_RETURN(ret); diff --git a/sql/wsrep_mysqld.cc b/sql/wsrep_mysqld.cc index 284df936160..50650280a2a 100644 --- a/sql/wsrep_mysqld.cc +++ b/sql/wsrep_mysqld.cc @@ -2649,10 +2649,12 @@ static my_bool kill_remaining_threads(THD *thd, THD *caller_thd) if (is_client_connection(thd) && !abort_replicated(thd) && !is_replaying_connection(thd) && + thd_is_connection_alive(thd) && thd != caller_thd) { + WSREP_INFO("killing local connection: %lld", (longlong) thd->thread_id); - close_connection(thd, 0); + close_connection(thd); } #endif return 0; diff --git a/storage/connect/plugutil.cpp b/storage/connect/plugutil.cpp index e45feb31bea..522366dda9c 100644 --- a/storage/connect/plugutil.cpp +++ b/storage/connect/plugutil.cpp @@ -378,9 +378,9 @@ char *PlugReadMessage(PGLOBAL g, int mid, char *m) if (atoi(buff) == mid) break; - if (sscanf(buff, " %*d %-.256s \"%[^\"]", msgid, stmsg) < 2) { + if (sscanf(buff, " %*d %.31s \"%.255[^\"]", msgid, stmsg) < 2) { // Old message file - if (!sscanf(buff, " %*d \"%[^\"]", stmsg)) { + if (!sscanf(buff, " %*d \"%.255[^\"]", stmsg)) { sprintf(stmsg, "Bad message file for %d %-.256s", mid, SVP(m)); goto fin; } else diff --git a/storage/federatedx/federatedx_io_mysql.cc b/storage/federatedx/federatedx_io_mysql.cc index 4fa4b2b6ae1..d420de738b9 100644 --- a/storage/federatedx/federatedx_io_mysql.cc +++ b/storage/federatedx/federatedx_io_mysql.cc @@ -64,7 +64,6 @@ struct mysql_position class federatedx_io_mysql :public federatedx_io { MYSQL mysql; /* MySQL connection */ - MYSQL_ROWS *current; DYNAMIC_ARRAY savepoints; bool requested_autocommit; bool actual_autocommit; @@ -108,7 +107,8 @@ public: virtual void free_result(FEDERATEDX_IO_RESULT *io_result); virtual unsigned int get_num_fields(FEDERATEDX_IO_RESULT *io_result); virtual my_ulonglong get_num_rows(FEDERATEDX_IO_RESULT *io_result); - virtual FEDERATEDX_IO_ROW *fetch_row(FEDERATEDX_IO_RESULT *io_result); + virtual FEDERATEDX_IO_ROW *fetch_row(FEDERATEDX_IO_RESULT *io_result, + FEDERATEDX_IO_ROWS **current= NULL); virtual ulong *fetch_lengths(FEDERATEDX_IO_RESULT *io_result); virtual const char *get_column_data(FEDERATEDX_IO_ROW *row, unsigned int column); @@ -117,7 +117,7 @@ public: virtual size_t get_ref_length() const; virtual void mark_position(FEDERATEDX_IO_RESULT *io_result, - void *ref); + void *ref, FEDERATEDX_IO_ROWS *current); virtual int seek_position(FEDERATEDX_IO_RESULT **io_result, const void *ref); virtual void set_thd(void *thd); @@ -517,10 +517,12 @@ my_ulonglong federatedx_io_mysql::get_num_rows(FEDERATEDX_IO_RESULT *io_result) } -FEDERATEDX_IO_ROW *federatedx_io_mysql::fetch_row(FEDERATEDX_IO_RESULT *io_result) +FEDERATEDX_IO_ROW *federatedx_io_mysql::fetch_row(FEDERATEDX_IO_RESULT *io_result, + FEDERATEDX_IO_ROWS **current) { MYSQL_RES *result= (MYSQL_RES*)io_result; - current= result->data_cursor; + if (current) + *current= (FEDERATEDX_IO_ROWS *) result->data_cursor; return (FEDERATEDX_IO_ROW *) mysql_fetch_row(result); } @@ -628,11 +630,11 @@ size_t federatedx_io_mysql::get_ref_length() const void federatedx_io_mysql::mark_position(FEDERATEDX_IO_RESULT *io_result, - void *ref) + void *ref, FEDERATEDX_IO_ROWS *current) { mysql_position& pos= *reinterpret_cast<mysql_position*>(ref); pos.result= (MYSQL_RES *) io_result; - pos.offset= current; + pos.offset= (MYSQL_ROW_OFFSET) current; } int federatedx_io_mysql::seek_position(FEDERATEDX_IO_RESULT **io_result, diff --git a/storage/federatedx/federatedx_io_null.cc b/storage/federatedx/federatedx_io_null.cc index 1976f22124a..b1058dbd2f5 100644 --- a/storage/federatedx/federatedx_io_null.cc +++ b/storage/federatedx/federatedx_io_null.cc @@ -90,7 +90,8 @@ public: virtual void free_result(FEDERATEDX_IO_RESULT *io_result); virtual unsigned int get_num_fields(FEDERATEDX_IO_RESULT *io_result); virtual my_ulonglong get_num_rows(FEDERATEDX_IO_RESULT *io_result); - virtual FEDERATEDX_IO_ROW *fetch_row(FEDERATEDX_IO_RESULT *io_result); + virtual FEDERATEDX_IO_ROW *fetch_row(FEDERATEDX_IO_RESULT *io_result, + FEDERATEDX_IO_ROWS **current= NULL); virtual ulong *fetch_lengths(FEDERATEDX_IO_RESULT *io_result); virtual const char *get_column_data(FEDERATEDX_IO_ROW *row, unsigned int column); @@ -98,7 +99,7 @@ public: unsigned int column) const; virtual size_t get_ref_length() const; virtual void mark_position(FEDERATEDX_IO_RESULT *io_result, - void *ref); + void *ref, FEDERATEDX_IO_ROWS *current); virtual int seek_position(FEDERATEDX_IO_RESULT **io_result, const void *ref); }; @@ -242,7 +243,8 @@ my_ulonglong federatedx_io_null::get_num_rows(FEDERATEDX_IO_RESULT *) } -FEDERATEDX_IO_ROW *federatedx_io_null::fetch_row(FEDERATEDX_IO_RESULT *) +FEDERATEDX_IO_ROW *federatedx_io_null::fetch_row(FEDERATEDX_IO_RESULT *, + FEDERATEDX_IO_ROWS **current) { return NULL; } @@ -288,7 +290,7 @@ size_t federatedx_io_null::get_ref_length() const void federatedx_io_null::mark_position(FEDERATEDX_IO_RESULT *io_result, - void *ref) + void *ref, FEDERATEDX_IO_ROWS *current) { } diff --git a/storage/federatedx/ha_federatedx.cc b/storage/federatedx/ha_federatedx.cc index dfcbf156f88..f41ab1f9174 100644 --- a/storage/federatedx/ha_federatedx.cc +++ b/storage/federatedx/ha_federatedx.cc @@ -2948,7 +2948,7 @@ int ha_federatedx::read_next(uchar *buf, FEDERATEDX_IO_RESULT *result) DBUG_RETURN(retval); /* Fetch a row, insert it back in a row format. */ - if (!(row= io->fetch_row(result))) + if (!(row= io->fetch_row(result, ¤t))) DBUG_RETURN(HA_ERR_END_OF_FILE); if (!(retval= convert_row_to_internal_format(buf, row, result))) @@ -2992,7 +2992,7 @@ void ha_federatedx::position(const uchar *record __attribute__ ((unused))) if (txn->acquire(share, ha_thd(), TRUE, &io)) DBUG_VOID_RETURN; - io->mark_position(stored_result, ref); + io->mark_position(stored_result, ref, current); position_called= TRUE; diff --git a/storage/federatedx/ha_federatedx.h b/storage/federatedx/ha_federatedx.h index 333028da587..473d3ecd3e2 100644 --- a/storage/federatedx/ha_federatedx.h +++ b/storage/federatedx/ha_federatedx.h @@ -131,6 +131,7 @@ typedef struct st_federatedx_share { typedef struct st_federatedx_result FEDERATEDX_IO_RESULT; typedef struct st_federatedx_row FEDERATEDX_IO_ROW; +typedef struct st_federatedx_rows FEDERATEDX_IO_ROWS; typedef ptrdiff_t FEDERATEDX_IO_OFFSET; class federatedx_io @@ -207,7 +208,8 @@ public: virtual void free_result(FEDERATEDX_IO_RESULT *io_result)=0; virtual unsigned int get_num_fields(FEDERATEDX_IO_RESULT *io_result)=0; virtual my_ulonglong get_num_rows(FEDERATEDX_IO_RESULT *io_result)=0; - virtual FEDERATEDX_IO_ROW *fetch_row(FEDERATEDX_IO_RESULT *io_result)=0; + virtual FEDERATEDX_IO_ROW *fetch_row(FEDERATEDX_IO_RESULT *io_result, + FEDERATEDX_IO_ROWS **current= NULL)=0; virtual ulong *fetch_lengths(FEDERATEDX_IO_RESULT *io_result)=0; virtual const char *get_column_data(FEDERATEDX_IO_ROW *row, unsigned int column)=0; @@ -216,7 +218,7 @@ public: virtual size_t get_ref_length() const=0; virtual void mark_position(FEDERATEDX_IO_RESULT *io_result, - void *ref)=0; + void *ref, FEDERATEDX_IO_ROWS *current)=0; virtual int seek_position(FEDERATEDX_IO_RESULT **io_result, const void *ref)=0; virtual void set_thd(void *thd) { } @@ -269,6 +271,7 @@ class ha_federatedx final : public handler federatedx_txn *txn; federatedx_io *io; FEDERATEDX_IO_RESULT *stored_result; + FEDERATEDX_IO_ROWS *current; /** Array of all stored results we get during a query execution. */ diff --git a/storage/innobase/data/data0data.cc b/storage/innobase/data/data0data.cc index 1341136d8d6..d1fcdb53baa 100644 --- a/storage/innobase/data/data0data.cc +++ b/storage/innobase/data/data0data.cc @@ -769,7 +769,7 @@ void dtuple_convert_back_big_rec( /*========================*/ dict_index_t* index MY_ATTRIBUTE((unused)), /*!< in: index */ - dtuple_t* entry, /*!< in: entry whose data was put to vector */ + dtuple_t* entry, /*!< in/out: entry whose data was put to vector */ big_rec_t* vector) /*!< in, own: big rec vector; it is freed in this function */ { diff --git a/storage/innobase/dict/dict0stats.cc b/storage/innobase/dict/dict0stats.cc index d6fbb9b7937..8386cc80d81 100644 --- a/storage/innobase/dict/dict0stats.cc +++ b/storage/innobase/dict/dict0stats.cc @@ -178,8 +178,8 @@ dict_stats_persistent_storage_check( {"table_name", DATA_VARMYSQL, DATA_NOT_NULL, 597}, - {"last_update", DATA_FIXBINARY, - DATA_NOT_NULL, 4}, + {"last_update", DATA_INT, + DATA_NOT_NULL | DATA_UNSIGNED, 4}, {"n_rows", DATA_INT, DATA_NOT_NULL | DATA_UNSIGNED, 8}, @@ -209,8 +209,8 @@ dict_stats_persistent_storage_check( {"index_name", DATA_VARMYSQL, DATA_NOT_NULL, 192}, - {"last_update", DATA_FIXBINARY, - DATA_NOT_NULL, 4}, + {"last_update", DATA_INT, + DATA_NOT_NULL | DATA_UNSIGNED, 4}, {"stat_name", DATA_VARMYSQL, DATA_NOT_NULL, 64*3}, diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 3f3f164e505..227b63674b9 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -3087,10 +3087,12 @@ ha_innobase::init_table_handle_for_HANDLER(void) reset_template(); } -/** Free tablespace resources allocated. */ -void innobase_space_shutdown() +/*********************************************************************//** +Free any resources that were allocated and return failure. +@return always return 1 */ +static int innodb_init_abort() { - DBUG_ENTER("innobase_space_shutdown"); + DBUG_ENTER("innodb_init_abort"); if (fil_system.temp_space) { fil_system.temp_space->close(); @@ -3105,16 +3107,6 @@ void innobase_space_shutdown() #ifdef WITH_INNODB_DISALLOW_WRITES os_event_destroy(srv_allow_writes_event); #endif /* WITH_INNODB_DISALLOW_WRITES */ - - DBUG_VOID_RETURN; -} - -/** Free any resources that were allocated and return failure. -@return always return 1 */ -static int innodb_init_abort() -{ - DBUG_ENTER("innodb_init_abort"); - innobase_space_shutdown(); DBUG_RETURN(1); } @@ -3941,9 +3933,6 @@ static int innodb_init(void* p) } #endif /* MYSQL_DYNAMIC_PLUGIN */ - /* Currently, monitor counter information are not persistent. */ - memset(monitor_set_tbl, 0, sizeof monitor_set_tbl); - memset(innodb_counter_value, 0, sizeof innodb_counter_value); /* Do this as late as possible so server is fully starts up, @@ -3997,7 +3986,6 @@ innobase_end(handlerton*, ha_panic_function) innodb_shutdown(); - innobase_space_shutdown(); mysql_mutex_destroy(&pending_checkpoint_mutex); } @@ -8337,6 +8325,18 @@ ha_innobase::update_row( MySQL that the row is not really updated and it should not increase the count of updated rows. This is fix for http://bugs.mysql.com/29157 */ + if (m_prebuilt->versioned_write + && thd_sql_command(m_user_thd) != SQLCOM_ALTER_TABLE + /* Multiple UPDATE of same rows in single transaction create + historical rows only once. */ + && trx->id != table->vers_start_id()) { + error = row_insert_for_mysql((byte*) old_row, + m_prebuilt, + ROW_INS_HISTORICAL); + if (error != DB_SUCCESS) { + goto func_exit; + } + } DBUG_RETURN(HA_ERR_RECORD_IS_THE_SAME); } else { const bool vers_set_fields = m_prebuilt->versioned_write diff --git a/storage/innobase/handler/ha_innodb.h b/storage/innobase/handler/ha_innodb.h index c5c5e8cba31..b47acae01f2 100644 --- a/storage/innobase/handler/ha_innodb.h +++ b/storage/innobase/handler/ha_innodb.h @@ -970,6 +970,3 @@ which is in the prepared state @return 0 or error number */ int innobase_rollback_by_xid(handlerton* hton, XID* xid); - -/** Free tablespace resources allocated. */ -void innobase_space_shutdown(); diff --git a/storage/innobase/include/data0data.h b/storage/innobase/include/data0data.h index 04ddf5b0a42..fc774b6ee60 100644 --- a/storage/innobase/include/data0data.h +++ b/storage/innobase/include/data0data.h @@ -544,6 +544,17 @@ struct dtuple_t { @param[in] index index possibly with instantly added columns */ void trim(const dict_index_t& index); + bool vers_history_row() const + { + for (ulint i = 0; i < n_fields; i++) { + const dfield_t* field = &fields[i]; + if (field->type.vers_sys_end()) { + return field->vers_history_row(); + } + } + return false; + } + /** @param info_bits the info_bits of a data tuple @return whether this is a hidden metadata record diff --git a/storage/innobase/include/row0ins.h b/storage/innobase/include/row0ins.h index 34427dc6dc7..9a16394a052 100644 --- a/storage/innobase/include/row0ins.h +++ b/storage/innobase/include/row0ins.h @@ -206,6 +206,7 @@ struct ins_node_t if this is NULL, entry list should be created and buffers for sys fields in row allocated */ void vers_update_end(row_prebuilt_t *prebuilt, bool history_row); + bool vers_history_row() const; /* true if 'row' is historical */ }; /** Create an insert object. diff --git a/storage/innobase/include/srv0mon.h b/storage/innobase/include/srv0mon.h index 33d8c57a744..20365ffc7a7 100644 --- a/storage/innobase/include/srv0mon.h +++ b/storage/innobase/include/srv0mon.h @@ -481,23 +481,23 @@ enum mon_option_t { /** This "monitor_set_tbl" is a bitmap records whether a particular monitor counter has been turned on or off */ -extern ulint monitor_set_tbl[(NUM_MONITOR + NUM_BITS_ULINT - 1) / - NUM_BITS_ULINT]; +extern Atomic_relaxed<ulint> + monitor_set_tbl[(NUM_MONITOR + NUM_BITS_ULINT - 1) / NUM_BITS_ULINT]; /** Macros to turn on/off the control bit in monitor_set_tbl for a monitor counter option. */ -#define MONITOR_ON(monitor) \ - (monitor_set_tbl[unsigned(monitor) / NUM_BITS_ULINT] |= \ - (ulint(1) << (unsigned(monitor) % NUM_BITS_ULINT))) +#define MONITOR_ON(monitor) \ + (monitor_set_tbl[unsigned(monitor) / NUM_BITS_ULINT].fetch_or( \ + (ulint(1) << (unsigned(monitor) % NUM_BITS_ULINT)))) -#define MONITOR_OFF(monitor) \ - (monitor_set_tbl[unsigned(monitor) / NUM_BITS_ULINT] &= \ - ~(ulint(1) << (unsigned(monitor) % NUM_BITS_ULINT))) +#define MONITOR_OFF(monitor) \ + (monitor_set_tbl[unsigned(monitor) / NUM_BITS_ULINT].fetch_and( \ + ~(ulint(1) << (unsigned(monitor) % NUM_BITS_ULINT)))) /** Check whether the requested monitor is turned on/off */ -#define MONITOR_IS_ON(monitor) \ - (monitor_set_tbl[unsigned(monitor) / NUM_BITS_ULINT] & \ - (ulint(1) << (unsigned(monitor) % NUM_BITS_ULINT))) +#define MONITOR_IS_ON(monitor) \ + (monitor_set_tbl[unsigned(monitor) / NUM_BITS_ULINT] & \ + (ulint(1) << (unsigned(monitor) % NUM_BITS_ULINT))) /** The actual monitor counter array that records each monintor counter value */ diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc index 5e41d037b6c..4aed14cc059 100644 --- a/storage/innobase/row/row0ins.cc +++ b/storage/innobase/row/row0ins.cc @@ -892,7 +892,7 @@ row_ins_foreign_fill_virtual( update->old_vrow = row_build( ROW_COPY_DATA, index, rec, offsets, index->table, NULL, NULL, - &ext, cascade->heap); + &ext, update->heap); n_diff = update->n_fields; if (index->table->vc_templ == NULL) { @@ -2373,6 +2373,18 @@ row_ins_duplicate_error_in_clust( duplicate: trx->error_info = cursor->index; err = DB_DUPLICATE_KEY; + if (cursor->index->table->versioned() + && entry->vers_history_row()) + { + ulint trx_id_len; + byte *trx_id = rec_get_nth_field( + rec, offsets, n_unique, + &trx_id_len); + ut_ad(trx_id_len == DATA_TRX_ID_LEN); + if (trx->id == trx_read_trx_id(trx_id)) { + err = DB_FOREIGN_DUPLICATE_KEY; + } + } goto func_exit; } } @@ -3585,6 +3597,16 @@ row_ins_get_row_from_select( } } +inline +bool ins_node_t::vers_history_row() const +{ + if (!table->versioned()) + return false; + dfield_t* row_end = dtuple_get_nth_field(row, table->vers_end); + return row_end->vers_history_row(); +} + + /***********************************************************//** Inserts a row to a table. @return DB_SUCCESS if operation successfully completed, else error @@ -3623,12 +3645,31 @@ row_ins( ut_ad(node->state == INS_NODE_INSERT_ENTRIES); while (node->index != NULL) { - if (node->index->type != DICT_FTS) { + dict_index_t *index = node->index; + /* + We do not insert history rows into FTS_DOC_ID_INDEX because + it is unique by FTS_DOC_ID only and we do not want to add + row_end to unique key. Fulltext field works the way new + FTS_DOC_ID is created on every fulltext UPDATE, so holding only + FTS_DOC_ID for history is enough. + */ + const unsigned type = index->type; + if (index->type & DICT_FTS) { + } else if (!(type & DICT_UNIQUE) || index->n_uniq > 1 + || !node->vers_history_row()) { + dberr_t err = row_ins_index_entry_step(node, thr); if (err != DB_SUCCESS) { DBUG_RETURN(err); } + } else { + /* Unique indexes with system versioning must contain + the version end column. The only exception is a hidden + FTS_DOC_ID_INDEX that InnoDB may create on a hidden or + user-created FTS_DOC_ID column. */ + ut_ad(!strcmp(index->name, FTS_DOC_ID_INDEX_NAME)); + ut_ad(!strcmp(index->fields[0].name, FTS_DOC_ID_COL_NAME)); } node->index = dict_table_get_next_index(node->index); diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc index 33aa578873f..9d1dbca0b18 100644 --- a/storage/innobase/row/row0mysql.cc +++ b/storage/innobase/row/row0mysql.cc @@ -2095,10 +2095,18 @@ row_mysql_unfreeze_data_dictionary( @param buf Buffer to hold start time data */ void thd_get_query_start_data(THD *thd, char *buf); -/** Function restores btr_pcur_t, creates dtuple_t from rec_t, -sets row_end = CURRENT_TIMESTAMP/trx->id, inserts it to a table and updates -table statistics. -This is used in UPDATE CASCADE/SET NULL of a system versioning table. +/** Insert history row when evaluating foreign key referential action. + +1. Create new dtuple_t 'row' from node->historical_row; +2. Update its row_end to current timestamp; +3. Insert it to a table; +4. Update table statistics. + +This is used in UPDATE CASCADE/SET NULL of a system versioned referenced table. + +node->historical_row: dtuple_t containing pointers of row changed by refertial +action. + @param[in] thr current query thread @param[in] node a node which just updated a row in a foreign table @return DB_SUCCESS or some error */ @@ -2108,11 +2116,19 @@ static dberr_t row_update_vers_insert(que_thr_t* thr, upd_node_t* node) dfield_t* row_end; char row_end_data[8]; dict_table_t* table = node->table; + const unsigned zip_size = table->space->zip_size(); ut_ad(table->versioned()); - dtuple_t* row = node->historical_row; - ut_ad(row); - node->historical_row = NULL; + dtuple_t* row; + const ulint n_cols = dict_table_get_n_cols(table); + const ulint n_v_cols = dict_table_get_n_v_cols(table); + + ut_ad(n_cols == dtuple_get_n_fields(node->historical_row)); + ut_ad(n_v_cols == dtuple_get_n_v_fields(node->historical_row)); + + row = dtuple_create_with_vcol(node->historical_heap, n_cols, n_v_cols); + + dict_table_copy_types(row, table); ins_node_t* insert_node = ins_node_create(INS_DIRECT, table, node->historical_heap); @@ -2125,6 +2141,40 @@ static dberr_t row_update_vers_insert(que_thr_t* thr, upd_node_t* node) insert_node->common.parent = thr; ins_node_set_new_row(insert_node, row); + ut_ad(n_cols > DATA_N_SYS_COLS); + // Exclude DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR + for (ulint i = 0; i < n_cols - DATA_N_SYS_COLS; i++) { + dfield_t *src= dtuple_get_nth_field(node->historical_row, i); + dfield_t *dst= dtuple_get_nth_field(row, i); + dfield_copy(dst, src); + if (dfield_is_ext(src)) { + byte *field_data + = static_cast<byte*>(dfield_get_data(src)); + ulint ext_len; + ulint field_len = dfield_get_len(src); + + ut_a(field_len >= BTR_EXTERN_FIELD_REF_SIZE); + + ut_a(memcmp(field_data + field_len + - BTR_EXTERN_FIELD_REF_SIZE, + field_ref_zero, + BTR_EXTERN_FIELD_REF_SIZE)); + + byte *data = btr_copy_externally_stored_field( + &ext_len, field_data, zip_size, field_len, + node->historical_heap); + dfield_set_data(dst, data, ext_len); + } + } + + for (ulint i = 0; i < n_v_cols; i++) { + dfield_t *dst= dtuple_get_nth_v_field(row, i); + dfield_t *src= dtuple_get_nth_v_field(node->historical_row, i); + dfield_copy(dst, src); + } + + node->historical_row = NULL; + row_end = dtuple_get_nth_field(row, table->vers_end); if (dict_table_get_nth_col(table, table->vers_end)->vers_native()) { mach_write_to_8(row_end_data, trx->id); diff --git a/storage/innobase/row/row0row.cc b/storage/innobase/row/row0row.cc index aec5377ce14..39cefb463e4 100644 --- a/storage/innobase/row/row0row.cc +++ b/storage/innobase/row/row0row.cc @@ -295,12 +295,14 @@ row_build_index_entry_low( continue; } + ut_ad(!(index->type & DICT_FTS)); + ulint len = dfield_get_len(dfield); if (f.prefix_len == 0 && (!dfield_is_ext(dfield) || dict_index_is_clust(index))) { - /* The dfield_copy() above suffices for + /* The *dfield = *dfield2 above suffices for columns that are stored in-page, or for clustered index record columns that are not part of a column prefix in the PRIMARY KEY. */ diff --git a/storage/innobase/srv/srv0mon.cc b/storage/innobase/srv/srv0mon.cc index c98be4dc682..901a35425e2 100644 --- a/storage/innobase/srv/srv0mon.cc +++ b/storage/innobase/srv/srv0mon.cc @@ -1342,8 +1342,8 @@ monitor_value_t innodb_counter_value[NUM_MONITOR]; /* monitor_set_tbl is used to record and determine whether a monitor has been turned on/off. */ -ulint monitor_set_tbl[(NUM_MONITOR + NUM_BITS_ULINT - - 1) / NUM_BITS_ULINT]; +Atomic_relaxed<ulint> + monitor_set_tbl[(NUM_MONITOR + NUM_BITS_ULINT - 1) / NUM_BITS_ULINT]; /****************************************************************//** Get a monitor's "monitor_info" by its monitor id (index into the diff --git a/storage/innobase/srv/srv0start.cc b/storage/innobase/srv/srv0start.cc index b7c4baa0ef7..b0bc3d3ede9 100644 --- a/storage/innobase/srv/srv0start.cc +++ b/storage/innobase/srv/srv0start.cc @@ -2101,6 +2101,17 @@ void innodb_shutdown() buf_pool.close(); sync_check_close(); + srv_sys_space.shutdown(); + if (srv_tmp_space.get_sanity_check_status()) { + fil_system.temp_space->close(); + srv_tmp_space.delete_files(); + } + srv_tmp_space.shutdown(); + +#ifdef WITH_INNODB_DISALLOW_WRITES + os_event_destroy(srv_allow_writes_event); +#endif /* WITH_INNODB_DISALLOW_WRITES */ + if (srv_was_started && srv_print_verbose_log) { ib::info() << "Shutdown completed; log sequence number " << srv_shutdown_lsn diff --git a/storage/innobase/ut/ut0ut.cc b/storage/innobase/ut/ut0ut.cc index 3376698dba1..fa04af6de13 100644 --- a/storage/innobase/ut/ut0ut.cc +++ b/storage/innobase/ut/ut0ut.cc @@ -38,6 +38,9 @@ Created 5/11/1994 Heikki Tuuri #include <string> #include "log.h" #include "my_cpu.h" +#ifndef DBUG_OFF +#include "rem0rec.h" +#endif /**********************************************************//** Returns the number of milliseconds since some epoch. The @@ -597,4 +600,49 @@ fatal_or_error::~fatal_or_error() } // namespace ib +#ifndef DBUG_OFF +static char dbug_print_buf[1024]; + +const char * dbug_print_rec(const rec_t* rec, const rec_offs* offsets) +{ + rec_printer r(rec, offsets); + strmake(dbug_print_buf, r.str().c_str(), sizeof(dbug_print_buf) - 1); + return dbug_print_buf; +} + +const char * dbug_print_rec(const rec_t* rec, ulint info, const rec_offs* offsets) +{ + rec_printer r(rec, info, offsets); + strmake(dbug_print_buf, r.str().c_str(), sizeof(dbug_print_buf) - 1); + return dbug_print_buf; +} + +const char * dbug_print_rec(const dtuple_t* tuple) +{ + rec_printer r(tuple); + strmake(dbug_print_buf, r.str().c_str(), sizeof(dbug_print_buf) - 1); + return dbug_print_buf; +} + +const char * dbug_print_rec(const dfield_t* field, ulint n) +{ + rec_printer r(field, n); + strmake(dbug_print_buf, r.str().c_str(), sizeof(dbug_print_buf) - 1); + return dbug_print_buf; +} + +const char * dbug_print_rec(const rec_t* rec, dict_index_t* index) +{ + rec_offs offsets_[REC_OFFS_NORMAL_SIZE]; + rec_offs* offsets = offsets_; + rec_offs_init(offsets_); + mem_heap_t* tmp_heap = NULL; + offsets = rec_get_offsets(rec, index, offsets, true, + ULINT_UNDEFINED, &tmp_heap); + rec_printer r(rec, offsets); + strmake(dbug_print_buf, r.str().c_str(), sizeof(dbug_print_buf) - 1); + return dbug_print_buf; +} +#endif /* !DBUG_OFF */ + #endif /* !UNIV_INNOCHECKSUM */ diff --git a/zlib/CMakeLists.txt b/zlib/CMakeLists.txt index 810ec8ae0cb..ee79bc0bd5f 100644 --- a/zlib/CMakeLists.txt +++ b/zlib/CMakeLists.txt @@ -13,12 +13,6 @@ # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA -cmake_minimum_required(VERSION 2.4.4) -set(CMAKE_ALLOW_LOOSE_LOOP_CONSTRUCTS ON) - -project(zlib C) - -set(VERSION "1.2.11") include(CheckTypeSize) include(CheckFunctionExists) @@ -110,40 +104,7 @@ set(ZLIB_SRCS zutil.c ) -if(NOT MINGW) - set(ZLIB_DLL_SRCS - win32/zlib1.rc # If present will override custom build rule below. - ) -endif() - -# parse the full version number from zlib.h and include in ZLIB_FULL_VERSION -file(READ ${CMAKE_CURRENT_SOURCE_DIR}/zlib.h _zlib_h_contents) -string(REGEX REPLACE ".*#define[ \t]+ZLIB_VERSION[ \t]+\"([-0-9A-Za-z.]+)\".*" - "\\1" ZLIB_FULL_VERSION ${_zlib_h_contents}) - ADD_CONVENIENCE_LIBRARY(zlib STATIC ${ZLIB_SRCS} ${ZLIB_PUBLIC_HDRS} ${ZLIB_PRIVATE_HDRS}) RESTRICT_SYMBOL_EXPORTS(zlib) - -if(NOT CYGWIN) - # This property causes shared libraries on Linux to have the full version - # encoded into their final filename. We disable this on Cygwin because - # it causes cygz-${ZLIB_FULL_VERSION}.dll to be created when cygz.dll - # seems to be the default. - # - # This has no effect with MSVC, on that platform the version info for - # the DLL comes from the resource file win32/zlib1.rc - set_target_properties(zlib PROPERTIES VERSION ${ZLIB_FULL_VERSION}) -endif() - -if(CMAKE_SYSTEM_NAME MATCHES "SunOS") - # On unix-like platforms the library is almost always called libz - set_target_properties(zlib PROPERTIES OUTPUT_NAME z) -elseif(UNIX) - # On unix-like platforms the library is almost always called libz - set_target_properties(zlib PROPERTIES OUTPUT_NAME z) -endif() - - -RESTRICT_SYMBOL_EXPORTS(zlib) |