diff options
84 files changed, 1292 insertions, 376 deletions
diff --git a/CMakeLists.txt b/CMakeLists.txt index 5c4e0037588..c3bb7bef9e2 100644 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@ -1,5 +1,5 @@ -# Copyright (c) 2006, 2014, Oracle and/or its affiliates. -# Copyright (c) 2008, 2014, Monty Program Ab +# Copyright (c) 2006, 2017, Oracle and/or its affiliates. +# Copyright (c) 2008, 2017, MariaDB # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -220,11 +220,9 @@ IF(SECURITY_HARDENED) MY_CHECK_AND_SET_COMPILER_FLAG("-D_FORTIFY_SOURCE=2" RELEASE RELWITHDEBINFO) ENDIF() -OPTION(ENABLE_DEBUG_SYNC "Enable debug sync (debug builds only)" ON) -IF(ENABLE_DEBUG_SYNC) - SET(CMAKE_CXX_FLAGS_DEBUG "${CMAKE_CXX_FLAGS_DEBUG} -DENABLED_DEBUG_SYNC") - SET(CMAKE_C_FLAGS_DEBUG "${CMAKE_C_FLAGS_DEBUG} -DENABLED_DEBUG_SYNC") -ENDIF() +# Always enable debug sync for debug builds. +SET(CMAKE_CXX_FLAGS_DEBUG "${CMAKE_CXX_FLAGS_DEBUG} -DENABLED_DEBUG_SYNC") +SET(CMAKE_C_FLAGS_DEBUG "${CMAKE_C_FLAGS_DEBUG} -DENABLED_DEBUG_SYNC") OPTION(ENABLE_GCOV "Enable gcov (debug, Linux builds only)" OFF) IF (ENABLE_GCOV) diff --git a/client/client_priv.h b/client/client_priv.h index e96e187fb34..ba1a1fddfae 100644 --- a/client/client_priv.h +++ b/client/client_priv.h @@ -120,4 +120,3 @@ enum options_client Name of the performance schema database. */ #define PERFORMANCE_SCHEMA_DB_NAME "performance_schema" - diff --git a/client/mysql.cc b/client/mysql.cc index 69924010a15..22f518ed70f 100644 --- a/client/mysql.cc +++ b/client/mysql.cc @@ -1,6 +1,6 @@ /* Copyright (c) 2000, 2014, Oracle and/or its affiliates. - Copyright (c) 2009, 2016, MariaDB + Copyright (c) 2009, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by diff --git a/client/mysql_upgrade.c b/client/mysql_upgrade.c index ee63fda2e39..9ac438ff6ea 100644 --- a/client/mysql_upgrade.c +++ b/client/mysql_upgrade.c @@ -1,6 +1,6 @@ /* Copyright (c) 2006, 2013, Oracle and/or its affiliates. - Copyright (c) 2010, 2016, MariaDB + Copyright (c) 2010, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by diff --git a/client/mysqladmin.cc b/client/mysqladmin.cc index 7491458c161..1eba3f25ee8 100644 --- a/client/mysqladmin.cc +++ b/client/mysqladmin.cc @@ -1,6 +1,6 @@ /* Copyright (c) 2000, 2014, Oracle and/or its affiliates. - Copyright (c) 2010, 2016, MariaDB + Copyright (c) 2010, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by diff --git a/client/mysqlcheck.c b/client/mysqlcheck.c index c1ea002515b..42785b6dc7c 100644 --- a/client/mysqlcheck.c +++ b/client/mysqlcheck.c @@ -1,6 +1,6 @@ /* Copyright (c) 2001, 2013, Oracle and/or its affiliates. - Copyright (c) 2010, 2016, MariaDB + Copyright (c) 2010, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by diff --git a/client/mysqldump.c b/client/mysqldump.c index 99fcbdb4250..8235090ccf3 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -2211,7 +2211,6 @@ static void print_comment(FILE *sql_file, my_bool is_error, const char *format, print_xml_comment(sql_file, strlen(comment_buff), comment_buff); } - /* create_delimiter Generate a new (null-terminated) string that does not exist in query @@ -2556,7 +2555,7 @@ static uint dump_routines_for_db(char *db) query_buff); print_comment(sql_file, 1, "-- does %s have permissions on mysql.proc?\n\n", - current_user); + fix_for_comment(current_user)); maybe_die(EX_MYSQLERR,"%s has insufficent privileges to %s!", current_user, query_buff); } diff --git a/client/mysqlimport.c b/client/mysqlimport.c index 688789ec436..092ae555318 100644 --- a/client/mysqlimport.c +++ b/client/mysqlimport.c @@ -1,6 +1,6 @@ /* Copyright (c) 2000, 2015, Oracle and/or its affiliates. - Copyright (c) 2011, 2016, MariaDB + Copyright (c) 2011, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by diff --git a/client/mysqlshow.c b/client/mysqlshow.c index e3e30abc426..46494311f7d 100644 --- a/client/mysqlshow.c +++ b/client/mysqlshow.c @@ -1,6 +1,6 @@ /* Copyright (c) 2000, 2015, Oracle and/or its affiliates. - Copyright (c) 2010, 2016, MariaDB + Copyright (c) 2010, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by diff --git a/client/mysqlslap.c b/client/mysqlslap.c index b3229980e77..a78bf35d51b 100644 --- a/client/mysqlslap.c +++ b/client/mysqlslap.c @@ -1,6 +1,6 @@ /* Copyright (c) 2005, 2015, Oracle and/or its affiliates. - Copyright (c) 2010, 2016, MariaDB + Copyright (c) 2010, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by diff --git a/client/mysqltest.cc b/client/mysqltest.cc index 6c77275569c..d38147f8ac2 100644 --- a/client/mysqltest.cc +++ b/client/mysqltest.cc @@ -1,5 +1,5 @@ /* Copyright (c) 2000, 2013, Oracle and/or its affiliates. - Copyright (c) 2009, 2016, Monty Program Ab. + Copyright (c) 2009, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -1063,7 +1063,7 @@ void do_eval(DYNAMIC_STRING *query_eval, const char *query, if (!(v= var_get(p, &p, 0, 0))) { report_or_die( "Bad variable in eval"); - return; + DBUG_VOID_RETURN; } dynstr_append_mem(query_eval, v->str_val, v->str_val_len); } @@ -1776,7 +1776,7 @@ static int run_command(char* cmd, if (!(res_file= popen(cmd, "r"))) { report_or_die("popen(\"%s\", \"r\") failed", cmd); - return -1; + DBUG_RETURN(-1); } while (fgets(buf, sizeof(buf), res_file)) @@ -2873,7 +2873,7 @@ void var_set_query_get_value(struct st_command *command, VAR *var) dynstr_free(&ds_query); dynstr_free(&ds_col); eval_expr(var, "", 0); - return; + DBUG_VOID_RETURN; } { @@ -2898,7 +2898,7 @@ void var_set_query_get_value(struct st_command *command, VAR *var) ds_col.str, ds_query.str); dynstr_free(&ds_query); dynstr_free(&ds_col); - return; + DBUG_VOID_RETURN; } DBUG_PRINT("info", ("Found column %d with name '%s'", i, fields[i].name)); @@ -3344,7 +3344,7 @@ void do_exec(struct st_command *command) if (!*cmd) { report_or_die("Missing argument in exec"); - return; + DBUG_VOID_RETURN; } command->last_argument= command->end; @@ -3384,7 +3384,7 @@ void do_exec(struct st_command *command) dynstr_free(&ds_cmd); if (command->abort_on_error) report_or_die("popen(\"%s\", \"r\") failed", command->first_argument); - return; + DBUG_VOID_RETURN; } ds_result= &ds_res; @@ -3433,7 +3433,7 @@ void do_exec(struct st_command *command) ds_cmd.str, error, status, errno, ds_res.str); dynstr_free(&ds_cmd); - return; + DBUG_VOID_RETURN; } DBUG_PRINT("info", @@ -3572,7 +3572,7 @@ void do_system(struct st_command *command) if (strlen(command->first_argument) == 0) { report_or_die("Missing arguments to system, nothing to do!"); - return; + DBUG_VOID_RETURN; } init_dynamic_string(&ds_cmd, 0, command->query_len + 64, 256); @@ -4659,7 +4659,7 @@ void do_perl(struct st_command *command) if (command->abort_on_error) die("popen(\"%s\", \"r\") failed", buf); dynstr_free(&ds_delimiter); - return; + DBUG_VOID_RETURN; } while (fgets(buf, sizeof(buf), res_file)) @@ -9978,25 +9978,39 @@ bool parse_re_part(char *start_re, char *end_re, Returns: st_replace_regex struct with pairs of substitutions */ +void append_replace_regex(char*, char*, struct st_replace_regex*, char**); struct st_replace_regex* init_replace_regex(char* expr) { + char *expr_end, *buf_p; struct st_replace_regex* res; - char* buf,*expr_end; - char* p, start_re, end_re= 1; - char* buf_p; uint expr_len= strlen(expr); - struct st_regex reg; /* my_malloc() will die on fail with MY_FAE */ res=(struct st_replace_regex*)my_malloc( - sizeof(*res)+expr_len ,MYF(MY_FAE+MY_WME)); + sizeof(*res)+8192 ,MYF(MY_FAE+MY_WME)); my_init_dynamic_array(&res->regex_arr,sizeof(struct st_regex), 128, 128, MYF(0)); - buf= (char*)res + sizeof(*res); expr_end= expr + expr_len; + buf_p= (char*)res + sizeof(*res); + append_replace_regex(expr, expr_end, res, &buf_p); + + res->odd_buf_len= res->even_buf_len= 8192; + res->even_buf= (char*)my_malloc(res->even_buf_len,MYF(MY_WME+MY_FAE)); + res->odd_buf= (char*)my_malloc(res->odd_buf_len,MYF(MY_WME+MY_FAE)); + res->buf= res->even_buf; + + return res; +} + + +void append_replace_regex(char* expr, char *expr_end, struct st_replace_regex* res, + char **buf_p) +{ + char* p, start_re, end_re= 1; + struct st_regex reg; + p= expr; - buf_p= buf; /* for each regexp substitution statement */ while (p < expr_end) @@ -10015,13 +10029,34 @@ struct st_replace_regex* init_replace_regex(char* expr) } start_re= 0; - reg.pattern= buf_p; - if (parse_re_part(&start_re, &end_re, &p, expr_end, &buf_p)) - goto err; + reg.pattern= *buf_p; - reg.replace= buf_p; - if (parse_re_part(&start_re, &end_re, &p, expr_end, &buf_p)) - goto err; + /* Allow variable for the *entire* list of replacements */ + if (*p == '$') + { + const char *v_end; + VAR *val= var_get(p, &v_end, 0, 1); + + if (val) + { + char *expr, *expr_end; + expr= val->str_val; + expr_end= expr + val->str_val_len; + append_replace_regex(expr, expr_end, res, buf_p); + } + + p= (char *) v_end + 1; + continue; + } + else + { + if (parse_re_part(&start_re, &end_re, &p, expr_end, buf_p)) + goto err; + + reg.replace= *buf_p; + if (parse_re_part(&start_re, &end_re, &p, expr_end, buf_p)) + goto err; + } /* Check if we should do matching case insensitive */ if (p < expr_end && *p == 'i') @@ -10034,17 +10069,12 @@ struct st_replace_regex* init_replace_regex(char* expr) if (insert_dynamic(&res->regex_arr, ®)) die("Out of memory"); } - res->odd_buf_len= res->even_buf_len= 8192; - res->even_buf= (char*)my_malloc(res->even_buf_len,MYF(MY_WME+MY_FAE)); - res->odd_buf= (char*)my_malloc(res->odd_buf_len,MYF(MY_WME+MY_FAE)); - res->buf= res->even_buf; - return res; + return; err: my_free(res); die("Error parsing replace_regex \"%s\"", expr); - return 0; } /* @@ -10124,12 +10154,6 @@ void do_get_replace_regex(struct st_command *command) { char *expr= command->first_argument; free_replace_regex(); - /* Allow variable for the *entire* list of replacements */ - if (*expr == '$') - { - VAR *val= var_get(expr, NULL, 0, 1); - expr= val ? val->str_val : NULL; - } if (expr && *expr && !(glob_replace_regex=init_replace_regex(expr))) die("Could not init replace_regex"); command->last_argument= command->end; diff --git a/cmake/pcre.cmake b/cmake/pcre.cmake index 45d9bc01ddb..894bde38974 100644 --- a/cmake/pcre.cmake +++ b/cmake/pcre.cmake @@ -5,7 +5,7 @@ MACRO (CHECK_PCRE) IF(WITH_PCRE STREQUAL "system" OR WITH_PCRE STREQUAL "auto") CHECK_LIBRARY_EXISTS(pcre pcre_stack_guard "" HAVE_PCRE) ENDIF() - IF(NOT HAVE_PCRE) + IF(NOT HAVE_PCRE OR WITH_PCRE STREQUAL "bundled") IF (WITH_PCRE STREQUAL "system") MESSAGE(FATAL_ERROR "system pcre is not found or unusable") ENDIF() diff --git a/extra/mysqld_safe_helper.c b/extra/mysqld_safe_helper.c index 09e507c6e1c..2ccff20b910 100644 --- a/extra/mysqld_safe_helper.c +++ b/extra/mysqld_safe_helper.c @@ -59,7 +59,7 @@ int main(int argc, char *argv[]) MY_INIT(argv[0]); if (argc < 3) - do_usage(argv[0]); + do_usage(); user_info= my_check_user(argv[1], MYF(0)); if (user_info ? my_set_user(argv[1], user_info, MYF(MY_WME)) diff --git a/include/mysql/psi/mysql_file.h b/include/mysql/psi/mysql_file.h index 2f388c285bf..be9d7116b9b 100644 --- a/include/mysql/psi/mysql_file.h +++ b/include/mysql/psi/mysql_file.h @@ -1423,4 +1423,3 @@ inline_mysql_file_sync( /** @} (end of group File_instrumentation) */ #endif - diff --git a/include/welcome_copyright_notice.h b/include/welcome_copyright_notice.h index e9891856221..cd7cd6692be 100644 --- a/include/welcome_copyright_notice.h +++ b/include/welcome_copyright_notice.h @@ -1,5 +1,5 @@ -/* Copyright (c) 2011, 2016, Oracle and/or its affiliates. - Copyright (c) 2011, 2016, MariaDB +/* Copyright (c) 2011, 2017, Oracle and/or its affiliates. + Copyright (c) 2011, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -17,7 +17,7 @@ #ifndef _welcome_copyright_notice_h_ #define _welcome_copyright_notice_h_ -#define COPYRIGHT_NOTICE_CURRENT_YEAR "2016" +#define COPYRIGHT_NOTICE_CURRENT_YEAR "2017" /* This define specifies copyright notice which is displayed by every MySQL diff --git a/man/galera_new_cluster.1 b/man/galera_new_cluster.1 index 72c46edc54a..9765141a20c 100644 --- a/man/galera_new_cluster.1 +++ b/man/galera_new_cluster.1 @@ -11,6 +11,26 @@ .SH NAME galera_new_cluster \- starting a new Galera cluster .SH DESCRIPTION -Use: Starting a new Galera Cluster\. +Used to bootstrap a new Galera Cluster when all nodes are down\&. +Run galera_new_cluster on the first node only\&. +On the remaining nodes simply run 'service @DAEMON_NAME@ start'\&. .PP -For more information, please refer to the MariaDB Knowledge Base, available online at https://mariadb.com/kb/ +.RS 4 +.ie n \{\ +\h'-04'\(bu\h'+03'\c +.\} +.el \{\ +.sp -1 +.IP \(bu 2.3 +.\} +.\" galera_new_cluster: help option +.\" help option: galera_new_cluster +\fB\-\-help\fR, +\fB\-h\fR +.sp +Display a help message and exit\&. +.PP +.sp +.SH "SEE ALSO" +For more information on configuration and usage see +https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluster/ diff --git a/mysql-test/disabled.def b/mysql-test/disabled.def index e82ddcf92d1..b489139a59f 100644 --- a/mysql-test/disabled.def +++ b/mysql-test/disabled.def @@ -12,7 +12,6 @@ tablespace : disabled in MariaDB (no TABLESPACE table attribute) events_time_zone : Test is not predictable as it depends on precise timing. read_many_rows_innodb : Bug#11748886 2010-11-15 mattiasj report already exists -log_tables-big : Bug#11756699 2010-11-15 mattiasj report already exists mysql_embedded : Bug#12561297 2011-05-14 Anitha Dependent on PB2 changes - eventum#41836 #show_explain : Psergey: random timeout in range-checked-for-each record query. ssl_crl_clients_valid : broken upstream diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 42fd0ff7397..088dd3aae2c 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -2792,13 +2792,10 @@ sub mysql_server_start($) { if (! $opt_start_dirty) # If dirty, keep possibly grown system db { # Copy datadir from installed system db - for my $path ( "$opt_vardir", "$opt_vardir/..") { - my $install_db= "$path/install.db"; - copytree($install_db, $datadir) - if -d $install_db; - } - mtr_error("Failed to copy system db to '$datadir'") - unless -d $datadir; + my $path= ($opt_parallel == 1) ? "$opt_vardir" : "$opt_vardir/.."; + my $install_db= "$path/install.db"; + copytree($install_db, $datadir) if -d $install_db; + mtr_error("Failed to copy system db to '$datadir'") unless -d $datadir; } } else diff --git a/mysql-test/r/events_2.result b/mysql-test/r/events_2.result index ab5d47b5118..6d2ebda03d0 100644 --- a/mysql-test/r/events_2.result +++ b/mysql-test/r/events_2.result @@ -2,10 +2,10 @@ set sql_mode=""; drop database if exists events_test; create database events_test; use events_test; -create event e_26 on schedule at '2027-01-01 00:00:00' disable do set @a = 5; +create event e_26 on schedule at '2037-01-01 00:00:00' disable do set @a = 5; select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event; db name body definer convert_tz(execute_at, 'UTC', 'SYSTEM') on_completion -events_test e_26 set @a = 5 root@localhost 2027-01-01 00:00:00 DROP +events_test e_26 set @a = 5 root@localhost 2037-01-01 00:00:00 DROP drop event e_26; create event e_26 on schedule at NULL disable do set @a = 5; ERROR HY000: Incorrect AT value: 'NULL' diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result index 1af67cf6a4e..1e95e983253 100644 --- a/mysql-test/r/explain_json.result +++ b/mysql-test/r/explain_json.result @@ -825,7 +825,7 @@ EXPLAIN "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", - "attached_condition": "t2.b <> outer_t1.a and trigcond(<cache>(outer_t1.a) = t1.a or t1.a is null)" + "attached_condition": "t2.b <> outer_t1.a" } } } @@ -1573,7 +1573,7 @@ EXPLAIN "access_type": "ALL", "rows": 10, "filtered": 100, - "attached_condition": "t0.a < 5 and t1.b < t0.a" + "attached_condition": "t1.b < t0.a" } } } diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index b9ee2a9136f..31335dc2af6 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1702,7 +1702,19 @@ SELECT ASTEXT(p) FROM v1; ASTEXT(p) POINT(1 1) DROP VIEW v1; -End of 5.5 tests +# +# Start of 10.0 tests +# +# +# MDEV-12495 Conditional jump depends on uninitialised value for: SELECT NULL UNION geom_expression +# +SELECT AsText(g) FROM (SELECT NULL AS g UNION SELECT Point(1,1)) AS t1; +AsText(g) +NULL +POINT(1 1) +# +# End 10.0 tests +# SHOW CREATE TABLE information_schema.geometry_columns; Table Create Table GEOMETRY_COLUMNS CREATE TEMPORARY TABLE `GEOMETRY_COLUMNS` ( diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result index 207317c1add..8a58a769ed9 100644 --- a/mysql-test/r/innodb_icp.result +++ b/mysql-test/r/innodb_icp.result @@ -432,7 +432,7 @@ WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using where -2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); pk i diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index ffeddc989ec..468aa3b2dc0 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1743,7 +1743,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using temporary; Using filesort 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`pk` AS `pk`,<expr_cache><`test`.`t2`.`pk`>((select `test`.`t3`.`pk` + if(`test`.`t4`.`pk` is null,0,`test`.`t4`.`pk`) from `test`.`t3` left join `test`.`t4` on(`test`.`t4`.`pk` = `test`.`t3`.`pk`) where `test`.`t3`.`pk` = `test`.`t2`.`pk` + 1000 limit 1)) AS `t` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`pk` + 1000 and `test`.`t1`.`pk` > 1000 group by `test`.`t2`.`pk` diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 4ffca891061..bcb8fb21947 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1754,7 +1754,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using temporary; Using filesort 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`pk` AS `pk`,<expr_cache><`test`.`t2`.`pk`>((select `test`.`t3`.`pk` + if(`test`.`t4`.`pk` is null,0,`test`.`t4`.`pk`) from `test`.`t3` left join `test`.`t4` on(`test`.`t4`.`pk` = `test`.`t3`.`pk`) where `test`.`t3`.`pk` = `test`.`t2`.`pk` + 1000 limit 1)) AS `t` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`pk` + 1000 and `test`.`t1`.`pk` > 1000 group by `test`.`t2`.`pk` diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index b91dd1b87ae..4f59e9cdbe3 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -340,7 +340,7 @@ FIELDS ESCAPED BY '\\' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (c0, c2); -ERROR HY000: Invalid column reference (v2.c0) in LOAD DATA +ERROR HY000: Column 'c0' is not updatable LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v3 FIELDS ESCAPED BY '\\' diff --git a/mysql-test/r/loadxml.result b/mysql-test/r/loadxml.result index 711fea9e854..34719a27afc 100644 --- a/mysql-test/r/loadxml.result +++ b/mysql-test/r/loadxml.result @@ -129,8 +129,8 @@ DROP TABLE t1; CREATE TABLE t1 (c1 TEXT); CREATE VIEW v1 AS SELECT CONCAT(c1,'') AS c1, NULL AS c2 FROM t1; LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c1); -ERROR HY000: Invalid column reference (v1.c1) in LOAD DATA +ERROR HY000: Column 'c1' is not updatable LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c2); -ERROR HY000: Invalid column reference (v1.c2) in LOAD DATA +ERROR HY000: Column 'c2' is not updatable DROP VIEW v1; DROP TABLE t1; diff --git a/mysql-test/r/log_tables-big.result b/mysql-test/r/log_tables-big.result index 9b81127c825..1e189a7726f 100644 --- a/mysql-test/r/log_tables-big.result +++ b/mysql-test/r/log_tables-big.result @@ -1,29 +1,31 @@ +set @@global.log_output = 'TABLE'; set session long_query_time=10; select get_lock('bug27638', 1); get_lock('bug27638', 1) 1 set session long_query_time=1; -truncate table mysql.slow_log; select get_lock('bug27638', 2); get_lock('bug27638', 2) 0 -select if (query_time between '00:00:01' and '00:00:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; +select if (query_time >= '00:00:01', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +where sql_text = 'select get_lock(\'bug27638\', 2)'; qt sql_text OK select get_lock('bug27638', 2) -truncate table mysql.slow_log; select get_lock('bug27638', 60); get_lock('bug27638', 60) 0 -select if (query_time between '00:00:59' and '00:01:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; +select if (query_time >= '00:00:59', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +where sql_text = 'select get_lock(\'bug27638\', 60)'; qt sql_text OK select get_lock('bug27638', 60) -truncate table mysql.slow_log; select get_lock('bug27638', 101); get_lock('bug27638', 101) 0 -select if (query_time between '00:01:40' and '00:01:50', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; +select if (query_time >= '00:01:40', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +where sql_text = 'select get_lock(\'bug27638\', 101)'; qt sql_text OK select get_lock('bug27638', 101) select release_lock('bug27638'); release_lock('bug27638') 1 +set @@global.log_output=default; diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result index 458d0195a19..6a70521cd72 100644 --- a/mysql-test/r/myisam_icp.result +++ b/mysql-test/r/myisam_icp.result @@ -430,7 +430,7 @@ WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition -2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); pk i @@ -804,7 +804,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where 1 PRIMARY t2 ref g g 5 test.t.c 19 Using where 2 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 64 Using where; Using index -2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index condition; Using where +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where SELECT COUNT(*) FROM t1 AS t, t2 WHERE c = g AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 66b501e8278..5a8c1e7a8ae 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -5330,6 +5330,69 @@ DROP TABLE t1; DROP TABLE t2; DROP DATABASE db_20772273; USE test; +# +# Bug #25717383: MYSQLDUMP MAY EXECUTE ANY ARBITRARY QUERY +# +CREATE DATABASE bug25717383; +use bug25717383; +CREATE TABLE `tab +one` (a int); +CREATE VIEW `view +one` as SELECT * FROM `tab +one`; +CREATE PROCEDURE `proc +one`() SELECT * from `tab +one`; +CREATE TEMPORARY TABLE `temp +one` (id INT); +CREATE TRIGGER `trig +one` BEFORE INSERT ON `tab +one` FOR EACH ROW SET NEW.a = 1; +CREATE EVENT `event +one` ON SCHEDULE AT '2030-01-01 00:00:00' DO SET @a=5; +SHOW TABLES FROM bug25717383; +Tables_in_bug25717383 +tab +one +view +one +SHOW TRIGGERS FROM bug25717383; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trig +one INSERT tab +one SET NEW.a = 1 BEFORE # root@localhost utf8 utf8_general_ci latin1_swedish_ci +SHOW EVENTS FROM bug25717383; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +bug25717383 event +one root@localhost SYSTEM ONE TIME # NULL NULL NULL NULL ENABLED 1 utf8 utf8_general_ci latin1_swedish_ci +SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE' + ORDER BY ROUTINE_NAME; +ROUTINE_NAME +proc +one +SHOW TABLES FROM bug25717383; +Tables_in_bug25717383 +tab +one +view +one +SHOW TRIGGERS FROM bug25717383; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trig +one INSERT tab +one SET NEW.a = 1 BEFORE # root@localhost utf8 utf8_general_ci latin1_swedish_ci +SHOW EVENTS FROM bug25717383; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +bug25717383 event +one root@localhost SYSTEM ONE TIME # NULL NULL NULL NULL ENABLED 1 utf8 utf8_general_ci latin1_swedish_ci +SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE' + ORDER BY ROUTINE_NAME; +ROUTINE_NAME +proc +one +DROP DATABASE bug25717383; Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result index f8f0b8e379d..237f04d6d28 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -684,7 +684,9 @@ drop table t1; y txt b is b and more is more -txt +txt2 +b is b or more is more +txt3 a is a and less is more sflfdt 'ABCDfF bbddff h' bs txt; txt diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 673b53b9c04..9ed067c0bdc 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -267,7 +267,7 @@ from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key -2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where `test`.`t1`.`c` = `test`.`t2`.`oref` and trigcond(trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null)) and trigcond(trigcond(<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b` or `test`.`t1`.`b` is null)) having trigcond(`test`.`t1`.`a` is null) and trigcond(`test`.`t1`.`b` is null)))) AS `Z` from `test`.`t2` @@ -1242,19 +1242,19 @@ explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) explain select straight_join * from t2 X, t2 Y where X.a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where 1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) create table t0 (a int, b int); insert into t0 values(1,1); explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 06a5e2c5aa4..2a1fc0df957 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -277,7 +277,7 @@ from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key -2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where `test`.`t1`.`c` = `test`.`t2`.`oref` and trigcond(trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null)) and trigcond(trigcond(<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b` or `test`.`t1`.`b` is null)) having trigcond(`test`.`t1`.`a` is null) and trigcond(`test`.`t1`.`b` is null)))) AS `Z` from `test`.`t2` @@ -1252,19 +1252,19 @@ explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) explain select straight_join * from t2 X, t2 Y where X.a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where 1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) create table t0 (a int, b int); insert into t0 values(1,1); explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 3d0dfd189a8..699a1b27b78 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -19,7 +19,7 @@ SELECT 1 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY count(*); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL a 5 NULL 2 Using where; Using index +1 PRIMARY t1 index NULL a 5 NULL 2 Using index 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found # should not crash the next statement @@ -1409,7 +1409,7 @@ GROUP BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 FROM t2 JOIN t1 ON t1.f3 @@ -1425,7 +1425,7 @@ ORDER BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 FROM t2 JOIN t1 ON t1.f3 @@ -2435,9 +2435,11 @@ WHERE EXISTS ( )"; EXECUTE stmt; i +4 6 EXECUTE stmt; i +4 6 drop table t1, t2, t3; # diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result index 07b5557506b..d5e61d4beca 100644 --- a/mysql-test/r/subselect_exists2in.result +++ b/mysql-test/r/subselect_exists2in.result @@ -884,5 +884,55 @@ a deallocate prepare stmt; drop view v1; drop table t1,t2; +# +#MDEV-10053: EXIST to IN transformation turned down +# +CREATE TABLE t1 ( +pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) +ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); +set @optimizer_switch_save=@@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +explain extended SELECT STRAIGHT_JOIN sq1.f2 +FROM ( SELECT * FROM t1 ) AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +3 DEPENDENT SUBQUERY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 +3 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +4 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1276 Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1 +Note 1003 select straight_join `test`.`t1`.`f2` AS `f2` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`,`test`.`t1`.`pk`>(exists(select 1 from `test`.`t1` `sq2` semi join (`test`.`t1`) where `test`.`sq2`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`pk` = `test`.`t1`.`f1`)) +SELECT STRAIGHT_JOIN sq1.f2 +FROM ( SELECT * FROM t1 ) AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set optimizer_switch='exists_to_in=on'; +explain extended SELECT STRAIGHT_JOIN sq1.f2 +FROM ( SELECT * FROM t1 ) AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +3 DEPENDENT SUBQUERY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 +3 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +4 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1276 Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1 +Note 1003 select straight_join `test`.`t1`.`f2` AS `f2` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`f1`,<exists>(select `test`.`sq2`.`f1` from `test`.`t1` `sq2` semi join (`test`.`t1`) where `test`.`t1`.`pk` = `test`.`t1`.`f1` and <cache>(`test`.`t1`.`f1`) = `test`.`sq2`.`f1`))) +SELECT STRAIGHT_JOIN sq1.f2 +FROM ( SELECT * FROM t1 ) AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set optimizer_switch= @optimizer_switch_save; +DROP TABLE t1; # End of 10.0 tests set optimizer_switch=default; diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 11221c797ff..01257c33361 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -312,7 +312,7 @@ EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where 2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY,d PRIMARY 1 func 1 Using where -3 DEPENDENT SUBQUERY t2 index NULL d 2 NULL 1 Using where; Using index +3 DEPENDENT SUBQUERY t2 index NULL d 2 NULL 1 Using index DROP TABLE t2; CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB; INSERT INTO t2 VALUES (1, 1); @@ -509,6 +509,20 @@ set join_cache_level = default; drop view v1; drop table t1,t2; # +# MDEV-10693: cost-based choice between materialization and in-to-exists +# for a subquery from the expression used in ref access +# +CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (i2 INT) ENGINE=InnoDB; +CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t3 VALUES (3); +SELECT * FROM t1 +WHERE NULL IN ( SELECT i2 FROM t2 +WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 ); +i1 +DROP TABLE t1,t2,t3; +# # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding # create table t1(a int) engine=innodb; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 59f9d3c1876..ede18083b85 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2192,6 +2192,117 @@ execute stmt; a 0 drop table t1; +# +# MDEV-12429: IN subquery used in WHERE of EXISTS subquery +# +CREATE TABLE t1 ( +pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +EXPLAIN +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY sq1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +2 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +# this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1; +# +# MDEV-12145: IN subquery used in WHERE of EXISTS subquery +# +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(7),(1); +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +f1 +6 +EXPLAIN EXTENDED +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 DEPENDENT SUBQUERY t2 index i2 i2 5 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t3 ref i3 i3 5 test.t2.i2 2 100.00 Using index +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 100.00 +Warnings: +Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`f1` = `test`.`t3`.`f3`)) +# this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +f1 +6 +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2,t3; +# +# MDEV-9686: IN subquery used in WHERE of a subquery from select list +# +CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT); +INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3); +CREATE TABLE t2 (f2 INT); +INSERT INTO t2 VALUES (1),(2),(3),(4),(5); +# t1.pk is always IN ( SELECT f2 FROM t2 ), +# so the IN condition should be true for every row, +# and thus COUNT(*) should always return 5 +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +pk f1 sq +1 4 5 +2 3 5 +3 3 5 +4 6 5 +5 3 5 +EXPLAIN EXTENDED +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 +Warnings: +Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` semi join (`test`.`t2`) where `test`.`t1`.`pk` = `test`.`t2`.`f2`)) AS `sq` from `test`.`t1` +# this checks the result set above +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +pk f1 sq +1 4 5 +2 3 5 +3 3 5 +4 6 5 +5 3 5 +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index c7b0f0d46c6..08a5d019bab 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -400,3 +400,45 @@ id select_type table type possible_keys key key_len ref rows Extra select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); c1 c2 drop table t1, t2; +# +# MDEV-12673: cost-based choice between materialization and in-to-exists +# +CREATE TABLE t1 ( +pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo'); +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo'); +SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ) +0 +SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ) +1 +EXPLAIN +SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 const PRIMARY,a2 PRIMARY 4 const 1 +2 SUBQUERY t1 ref a1,b1 b1 6 const 1 Using where +DROP TABLE t1,t2; +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'abc'),(2,'foo'); +CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1,'foo'),(2,'bar'); +SELECT * FROM t1 WHERE i1 NOT IN ( +SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 +); +i1 +1 +EXPLAIN +SELECT * FROM t1 WHERE i1 NOT IN ( +SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY t2 index NULL i2 11 NULL 2 Using where; Using index +DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 91d05a31dfa..9ee5d8d2806 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -178,7 +178,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) where t1.a < 5; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 09d88c9996f..d20c6251f85 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -191,7 +191,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) 2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) 2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) -2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) +2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) where t1.a < 5; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index a3f3cc4213c..72f875cf8a7 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2232,6 +2232,117 @@ execute stmt; a 0 drop table t1; +# +# MDEV-12429: IN subquery used in WHERE of EXISTS subquery +# +CREATE TABLE t1 ( +pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +EXPLAIN +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY sq1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +2 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +# this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1; +# +# MDEV-12145: IN subquery used in WHERE of EXISTS subquery +# +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(7),(1); +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +f1 +6 +EXPLAIN EXTENDED +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 DEPENDENT SUBQUERY t2 index i2 i2 5 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t3 ref i3 i3 5 test.t2.i2 2 100.00 Using index +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 100.00 +Warnings: +Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`f1` = `test`.`t3`.`f3`)) +# this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +f1 +6 +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2,t3; +# +# MDEV-9686: IN subquery used in WHERE of a subquery from select list +# +CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT); +INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3); +CREATE TABLE t2 (f2 INT); +INSERT INTO t2 VALUES (1),(2),(3),(4),(5); +# t1.pk is always IN ( SELECT f2 FROM t2 ), +# so the IN condition should be true for every row, +# and thus COUNT(*) should always return 5 +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +pk f1 sq +1 4 5 +2 3 5 +3 3 5 +4 6 5 +5 3 5 +EXPLAIN EXTENDED +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 +Warnings: +Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` semi join (`test`.`t2`) where `test`.`t1`.`pk` = `test`.`t2`.`f2`)) AS `sq` from `test`.`t1` +# this checks the result set above +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +pk f1 sq +1 4 5 +2 3 5 +3 3 5 +4 6 5 +5 3 5 +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 2385e3887c4..1764ea6a7dd 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -226,3 +226,14 @@ t2 CREATE TABLE `t2` ( PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop tables t1, t2; +# +# Test for bug #25514146 DB_NAME IS IGNORED WHEN CREATING TABLE +# WITH DATA DIRECTORY +# +# Make sure we have no current database +CREATE DATABASE x; +USE x; +DROP DATABASE x; +CREATE TABLE test.t1(id INT(11)) ENGINE MYISAM +DATA DIRECTORY "MYSQLTEST_VARDIR/tmp"; +DROP TABLE test.t1; diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 0ebdd726b4e..d22e705e6de 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -432,7 +432,7 @@ WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition -2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); pk i @@ -806,7 +806,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where 1 PRIMARY t2 ref g g 5 test.t.c 19 Using where 2 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 64 Using where; Using index -2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index condition; Using where +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where SELECT COUNT(*) FROM t1 AS t, t2 WHERE c = g AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) diff --git a/mysql-test/suite/sys_vars/r/sysvars_wsrep.result b/mysql-test/suite/sys_vars/r/sysvars_wsrep.result index 802ee3b1c6d..68ab8bd27a0 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_wsrep.result +++ b/mysql-test/suite/sys_vars/r/sysvars_wsrep.result @@ -367,7 +367,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME WSREP_PATCH_VERSION SESSION_VALUE NULL -GLOBAL_VALUE wsrep_25.19 +GLOBAL_VALUE wsrep_MAJVER.MINVER GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE NULL VARIABLE_SCOPE GLOBAL diff --git a/mysql-test/suite/sys_vars/t/sysvars_wsrep.test b/mysql-test/suite/sys_vars/t/sysvars_wsrep.test index 700b129fd62..c127115efe1 100644 --- a/mysql-test/suite/sys_vars/t/sysvars_wsrep.test +++ b/mysql-test/suite/sys_vars/t/sysvars_wsrep.test @@ -5,7 +5,7 @@ --replace_result $datadir DATADIR --let $hostname_regex=/^$hostname\$/HOSTNAME/ ---replace_regex $hostname_regex +--replace_regex $hostname_regex /wsrep_[0-9]{2}\.[0-9]{1,2}/wsrep_MAJVER.MINVER/ --vertical_results select * from information_schema.system_variables where variable_name like 'wsrep%' diff --git a/mysql-test/t/events_2.test b/mysql-test/t/events_2.test index 20f17966b29..12ce9210b0a 100644 --- a/mysql-test/t/events_2.test +++ b/mysql-test/t/events_2.test @@ -15,7 +15,7 @@ use events_test; # mysql.event intact checking end # -create event e_26 on schedule at '2027-01-01 00:00:00' disable do set @a = 5; +create event e_26 on schedule at '2037-01-01 00:00:00' disable do set @a = 5; select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event; drop event e_26; --error ER_WRONG_VALUE diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 3bb0044c7fb..0c8bbbfc819 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1453,7 +1453,20 @@ SHOW CREATE VIEW v1; SELECT ASTEXT(p) FROM v1; DROP VIEW v1; ---echo End of 5.5 tests +# --echo End of 5.5 tests + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-12495 Conditional jump depends on uninitialised value for: SELECT NULL UNION geom_expression +--echo # +SELECT AsText(g) FROM (SELECT NULL AS g UNION SELECT Point(1,1)) AS t1; + +--echo # +--echo # End 10.0 tests +--echo # SHOW CREATE TABLE information_schema.geometry_columns; SHOW CREATE TABLE information_schema.spatial_ref_sys; diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 88c4a363a57..4e355e5c3a7 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -294,7 +294,7 @@ SELECT * FROM v2; DELETE FROM t1; --echo ---error ER_LOAD_DATA_INVALID_COLUMN +--error ER_NONUPDATEABLE_COLUMN LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v2 FIELDS ESCAPED BY '\\' TERMINATED BY ',' diff --git a/mysql-test/t/loadxml.test b/mysql-test/t/loadxml.test index b26a2eacc00..0bd97a81649 100644 --- a/mysql-test/t/loadxml.test +++ b/mysql-test/t/loadxml.test @@ -137,9 +137,9 @@ DROP TABLE t1; CREATE TABLE t1 (c1 TEXT); CREATE VIEW v1 AS SELECT CONCAT(c1,'') AS c1, NULL AS c2 FROM t1; ---error ER_LOAD_DATA_INVALID_COLUMN +--error ER_NONUPDATEABLE_COLUMN LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c1); ---error ER_LOAD_DATA_INVALID_COLUMN, +--error ER_NONUPDATEABLE_COLUMN LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c2); DROP VIEW v1; DROP TABLE t1; diff --git a/mysql-test/t/log_tables-big.test b/mysql-test/t/log_tables-big.test index 8c956fa6f55..8936a163d73 100644 --- a/mysql-test/t/log_tables-big.test +++ b/mysql-test/t/log_tables-big.test @@ -7,6 +7,8 @@ # check that CSV engine was compiled in --source include/have_csv.inc +set @@global.log_output = 'TABLE'; + connect (con1,localhost,root,,); connect (con2,localhost,root,,); @@ -18,18 +20,20 @@ set session long_query_time=10; select get_lock('bug27638', 1); connection con2; set session long_query_time=1; -truncate table mysql.slow_log; select get_lock('bug27638', 2); -select if (query_time between '00:00:01' and '00:00:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; -truncate table mysql.slow_log; +select if (query_time >= '00:00:01', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log + where sql_text = 'select get_lock(\'bug27638\', 2)'; select get_lock('bug27638', 60); -select if (query_time between '00:00:59' and '00:01:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; -truncate table mysql.slow_log; +select if (query_time >= '00:00:59', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log + where sql_text = 'select get_lock(\'bug27638\', 60)'; select get_lock('bug27638', 101); -select if (query_time between '00:01:40' and '00:01:50', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; +select if (query_time >= '00:01:40', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log + where sql_text = 'select get_lock(\'bug27638\', 101)'; connection con1; select release_lock('bug27638'); connection default; disconnect con1; disconnect con2; + +set @@global.log_output=default; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 6d4e0e3f2b9..9f74f4ad9c0 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2490,6 +2490,58 @@ DROP TABLE t2; DROP DATABASE db_20772273; USE test; +--echo # +--echo # Bug #25717383: MYSQLDUMP MAY EXECUTE ANY ARBITRARY QUERY +--echo # + + +CREATE DATABASE bug25717383; +use bug25717383; + +CREATE TABLE `tab +one` (a int); +CREATE VIEW `view +one` as SELECT * FROM `tab +one`; + +CREATE PROCEDURE `proc +one`() SELECT * from `tab +one`; + +CREATE TEMPORARY TABLE `temp +one` (id INT); + +CREATE TRIGGER `trig +one` BEFORE INSERT ON `tab +one` FOR EACH ROW SET NEW.a = 1; + +CREATE EVENT `event +one` ON SCHEDULE AT '2030-01-01 00:00:00' DO SET @a=5; + +SHOW TABLES FROM bug25717383; +--replace_column 6 # +SHOW TRIGGERS FROM bug25717383; +--replace_column 6 # +SHOW EVENTS FROM bug25717383; + +SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES + WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE' + ORDER BY ROUTINE_NAME; + +--exec $MYSQL_DUMP --triggers --events --routines --add-drop-database --databases bug25717383 > $MYSQLTEST_VARDIR/tmp/bug25717383.sql + +SHOW TABLES FROM bug25717383; +--replace_column 6 # +SHOW TRIGGERS FROM bug25717383; +--replace_column 6 # +SHOW EVENTS FROM bug25717383; + +SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES + WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE' + ORDER BY ROUTINE_NAME; + +DROP DATABASE bug25717383; + # # MDEV-6091 mysqldump goes in a loop and segfaults if --dump-slave is specified and it cannot connect to the server # diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 4cc480b473e..2f1296560d0 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -2081,9 +2081,11 @@ drop table t1; --let $patt= /a /b / /less/more/ --replace_regex $patt select "a is a and less is more" as txt; +--replace_regex $patt /and /or / +select "a is a and less is more" as txt2; --let $patt= --replace_regex $patt -select "a is a and less is more" as txt; +select "a is a and less is more" as txt3; --enable_query_log # diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test index 9450ef71494..a4fdbe5c50b 100644 --- a/mysql-test/t/subselect_exists2in.test +++ b/mysql-test/t/subselect_exists2in.test @@ -758,6 +758,34 @@ deallocate prepare stmt; drop view v1; drop table t1,t2; +--echo # +--echo #MDEV-10053: EXIST to IN transformation turned down +--echo # + +CREATE TABLE t1 ( + pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) +ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); + +let $q= +SELECT STRAIGHT_JOIN sq1.f2 + FROM ( SELECT * FROM t1 ) AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +set @optimizer_switch_save=@@optimizer_switch; + +set optimizer_switch='exists_to_in=off'; +eval explain extended $q; +eval $q; +set optimizer_switch='exists_to_in=on'; +eval explain extended $q; +eval $q; + +set optimizer_switch= @optimizer_switch_save; + +DROP TABLE t1; + --echo # End of 10.0 tests #restore defaults diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index b26c5036f3f..2451bc60fee 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -503,6 +503,27 @@ drop view v1; drop table t1,t2; --echo # +--echo # MDEV-10693: cost-based choice between materialization and in-to-exists +--echo # for a subquery from the expression used in ref access +--echo # + +--source include/have_innodb.inc + +CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (i2 INT) ENGINE=InnoDB; + +CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t3 VALUES (3); + +SELECT * FROM t1 + WHERE NULL IN ( SELECT i2 FROM t2 + WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 ); + +DROP TABLE t1,t2,t3; + +--echo # --echo # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding --echo # create table t1(a int) engine=innodb; diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test index 8205e94b203..316ac707bef 100644 --- a/mysql-test/t/subselect_mat_cost_bugs.test +++ b/mysql-test/t/subselect_mat_cost_bugs.test @@ -424,3 +424,42 @@ explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); drop table t1, t2; + +--echo # +--echo # MDEV-12673: cost-based choice between materialization and in-to-exists +--echo # + +CREATE TABLE t1 ( + pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo'); + +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo'); + +SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +EXPLAIN +SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); + +DROP TABLE t1,t2; + +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'abc'),(2,'foo'); + +CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1,'foo'),(2,'bar'); + +SELECT * FROM t1 WHERE i1 NOT IN ( + SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 +); + +EXPLAIN +SELECT * FROM t1 WHERE i1 NOT IN ( + SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 +); + +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index e20291cfa67..b3c603728ee 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1857,6 +1857,99 @@ execute stmt; drop table t1; +--echo # +--echo # MDEV-12429: IN subquery used in WHERE of EXISTS subquery +--echo # + +CREATE TABLE t1 ( + pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); + +SELECT sq1.f2 FROM t1 AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +set @save_optimizer_switch= @@optimizer_switch; + +set optimizer_switch='exists_to_in=off'; +EXPLAIN +SELECT sq1.f2 FROM t1 AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +--echo # this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT sq1.f2 FROM t1 AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1; + +--echo # +--echo # MDEV-12145: IN subquery used in WHERE of EXISTS subquery +--echo # + +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); + +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(7),(1); + +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); + +set @save_optimizer_switch= @@optimizer_switch; + +set optimizer_switch='exists_to_in=off'; +SELECT * FROM t1 + WHERE EXISTS ( SELECT * FROM t2, t3 + WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +EXPLAIN EXTENDED +SELECT * FROM t1 + WHERE EXISTS ( SELECT * FROM t2, t3 + WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); + +--echo # this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT * FROM t1 + WHERE EXISTS ( SELECT * FROM t2, t3 + WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); + +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-9686: IN subquery used in WHERE of a subquery from select list +--echo # + +CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT); +INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3); + +CREATE TABLE t2 (f2 INT); +INSERT INTO t2 VALUES (1),(2),(3),(4),(5); + +--echo # t1.pk is always IN ( SELECT f2 FROM t2 ), +--echo # so the IN condition should be true for every row, +--echo # and thus COUNT(*) should always return 5 + +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 + WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +EXPLAIN EXTENDED +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 + WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; + +--echo # this checks the result set above +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 + WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1,t2; + --echo # End of 5.5 tests --echo # --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 065be302237..e17ea07ca3c 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -300,3 +300,19 @@ select create_options from information_schema.tables where table_name='t1'; create table t2 like t1; show create table t2; drop tables t1, t2; + +--echo # +--echo # Test for bug #25514146 DB_NAME IS IGNORED WHEN CREATING TABLE +--echo # WITH DATA DIRECTORY +--echo # + +--echo # Make sure we have no current database +CREATE DATABASE x; +USE x; +DROP DATABASE x; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE test.t1(id INT(11)) ENGINE MYISAM +DATA DIRECTORY "$MYSQLTEST_VARDIR/tmp"; + +DROP TABLE test.t1; diff --git a/mysql-test/unstable-tests b/mysql-test/unstable-tests index 4812456839d..091aeff6b7c 100644 --- a/mysql-test/unstable-tests +++ b/mysql-test/unstable-tests @@ -23,88 +23,79 @@ # ############################################################################## -main.alter_table : Modified in 10.1.22 +main.alter_table : MDEV-12625 - Valgrind warnings, also modified in 10.1.22 main.alter_table_trans : MDEV-11805 - timeout main.analyze_format_json : MDEV-11866 - Wrong result; also uses analyze-format.inc modified in 10.1.22 main.analyze_stmt_orderby : MDEV-11866 - Wrong result; also uses analyze-format.inc modified in 10.1.22 -main.analyze_stmt_slow_query_log : MDEV-12237 - Wrong result; also modified in 10.1.21 -main.cast : Modified in 10.1.21 -main.create : Modified in 10.1.21 +main.analyze_stmt_slow_query_log : MDEV-12237 - Wrong result main.create_delayed : MDEV-10605 - failed with timeout -main.ctype_ucs : Modified in 10.1.21 -main.ctype_ucs2_def : Modified in 10.1.21 -main.ctype_ucs2_query_cache : Modified in 10.1.21 -main.ctype_utf16 : Modified in 10.1.21 -main.ctype_utf16_def : Modified in 10.1.21 +main.ctype_upgrade : Modified in 10.1.23 main.ctype_utf16le : MDEV-10675: timeout or extra warnings -main.ctype_utf32 : Modified in 10.1.21 main.derived : Modified in 10.1.22 main.drop_bad_db_type : Modified in 10.1.22 +main.drop-no_root : MDEV-12633 - Valgrind warnings +main.events_2 : Modified in 10.1.23 main.events_restart : MDEV-12236 - Server shutdown problem -main.events_slowlog : Added in 10.1.21 main.explain_json : Uses analyze-format.inc modified in 10.1.22 main.explain_json_format_partitions : Uses analyze-format.inc modified in 10.1.22 -main.func_time : Modified in 10.1.21 -main.gis : Modified in 10.1.22 +main.gis : Modified in 10.1.23 main.grant : Modified in 10.1.22 main.host_cache_size_functionality : MDEV-10606 - sporadic failure on shutdown main.index_intersect_innodb : MDEV-10643 - failed with timeout -main.index_merge_innodb : MDEV-7142 - Wrong execution plan, also modified in 10.1.21 -main.information_schema_part : Modified in 10.1.21 +main.index_merge_innodb : MDEV-7142 - Wrong execution plan main.innodb_mysql_lock : MDEV-7861 - sporadic lock detection failure -main.join_cache : Modified in 10.1.21 -main.join_nested : MOdified in 10.1.22 +main.join_nested : Modified in 10.1.22 main.kill_processlist-6619 : MDEV-10793 - wrong result in processlist -main.loaddata : Modified in 10.1.21 -main.log_slow : Modified in 10.1.21 +main.loaddata : Modified in 10.1.23 +main.log_tables-big : Modified in 10.1.23 main.mdev-504 : MDEV-10607 - sporadic "can't connect" main.mdev375 : MDEV-10607 - sporadic "can't connect" main.merge : MDEV-10607 - sporadic "can't connect" +main.mysqlcheck : MDEV-12633 - Valgrind warnings main.mysqldump : Modified in 10.1.22 +main.mysqlhotcopy_myisam : Uses mysqlhotcopy.inc modified in 10.1.23 main.mysqlslap : MDEV-11801 - timeout main.mysqltest : MDEV-9269 - fails on Alpha +main.mysql_client_test : MDEV-12633 - Valgrind warnings +main.mysql_client_test_comp : MDEV-12633 - Valgrind warnings +main.mysql_client_test_nonblock : MDEV-12633 - Valgrind warnings main.mysql_upgrade_noengine : MDEV-12233 - Wrong result; added in 10.1.22 -main.order_by : Modified in 10.1.21 +main.mysqldump : Modified in 10.1.23 +main.mysqltest : Modified in 10.1.23 main.order_by_optimizer_innodb : MDEV-10683 - wrong execution plan main.partition_column : Modified in 10.1.22 main.partition_innodb : Modified in 10.1.22 main.partition_myisam : Modified in 10.1.22 -main.pool_of_threads : Modified in 10.1.21 main.ps : MDEV-11017 - sporadic wrong Prepared_stmt_count; also modified in 10.1.22 main.range_vs_index_merge : Modified in 10.1.22 +main.range_vs_index_merge_innodb : MDEV-12637 - Timeout main.repair_symlink-5543 : MDEV-12215 - Wrong error codes; also modified in 10.1.22 +main.show_check : MDEV-12633 - Valgrind warnings main.show_explain : MDEV-10674 - sporadic failure -main.signal_demo3 : MDEV-11720 - Thread stack overrun on labrador main.sp : Modified in 10.1.22 -main.sp-prelocking : Modified in 10.1.21 main.sp-security : MDEV-10607 - sporadic "can't connect" main.stat_tables_par : Modified in 10.1.22 main.status : MDEV-8510 - sporadic wrong result -main.subselect : Modified in 10.1.21 -main.subselect2 : Modified in 10.1.21 -main.subselect4 : Modified in 10.1.22 +main.subselect4 : Modified in 10.1.23 main.subselect_cache : Modified in 10.1.22 -main.subselect_innodb : MDEV-10614 - sporadic wrong results; also modified in 10.1.22 -main.subselect_no_exists_to_in : Uses subselect.test modified in 10.1.21 -main.subselect_no_mat : Uses subselect.test modified in 10.1.21 -main.subselect_no_opts : Uses subselect.test modified in 10.1.21 -main.subselect_no_scache : Uses subselect.test modified in 10.1.21 -main.subselect_no_semijoin : Uses subselect.test modified in 10.1.21 +main.subselect_exists2in : Modified in 10.1.23 +main.subselect_innodb : MDEV-10614 - sporadic wrong results; also modified in 10.1.23 +main.subselect_sj_mat : Modified in 10.1.23 +main.symlink : Modified in 10.1.23 main.symlink-aria-11902 : MDEV-12215 - Unexpected errors; also added in 10.1.22 -main.symlink-myisam-11902 : MDEV-12215 - Unexpected errors; also added in 10.1.22 +main.symlink-myisam-11902 : MDEV-12215 - Unexpected errors; modified in 10.1.23 main.table_elim : Modified in 10.1.22 -main.trigger_null-8605 : Modified in 10.1.21 main.trigger_no_defaults-11698 : Modified in 10.1.22 main.type_datetime_hires : MDEV-10687 - timeout main.type_newdecimal : Modified in 10.1.22 -main.union : Modified in 10.1.21 main.update_innodb : Modified in 10.1.22 -main.view : Modified in 10.1.22 +main.view : Modified in 10.1.23 #---------------------------------------------------------------- archive.archive-big : MDEV-10615 - table is marked as crashed -archive.discover : MDEV-10510 - table is marked as crashed; modified in 10.1.21 +archive.discover : MDEV-10510 - table is marked as crashed +archive.mysqlhotcopy_archive : Uses mysqlhotcopy.inc modified in 10.1.23 #---------------------------------------------------------------- @@ -117,44 +108,47 @@ binlog.binlog_xa_recover : MDEV-8517 - Extra checkpoint binlog_encryption.* : Added in 10.1.20, still unstable (valgrind errors and such) binlog_encryption.rpl_parallel : MDEV-10653 - Timeout -binlog_encryption.rpl_semi_sync : MDEV-11220 - Wrong result +binlog_encryption.rpl_semi_sync : MDEV-11220 - Wrong result, MDEV-11673 - Valgrind warning binlog_encryption.rpl_ssl : MDEV-11542 - Server crash #---------------------------------------------------------------- -connect.jdbc : Modified in 10.1.21 -connect.jdbc_new : Modified in 10.1.21 +connect.secure_file_priv : Modified in 10.1.23 connect.tbl : MDEV-9844, MDEV-10179 - sporadic crashes, valgrind warnings, wrong results connect.xml_zip : Added in 10.1.22 -connect.zip : Added in 10.1.22 +connect.zip : MDEV-12631 - Valgrind warnings; added in 10.1.22 #---------------------------------------------------------------- encryption.create_or_replace : MDEV-9359 - Assertion failure -encryption.encrypt_and_grep : MDEV-11222 - InnoDB error -encryption.innodb-bad-key-change : Modified in 10.1.22 -encryption.innodb-bad-key-change2 : Modified in 10.1.22 -encryption.innodb-bad-key-change3 : Modified in 10.1.21 -encryption.innodb-bad-key-change4 : Modified in 10.1.22 -encryption.innodb-bad-key-change5 : Modified in 10.1.22 -encryption.innodb-bad-key-shutdown : MDEV-9105 - valgrind warnings, assertion failures; also modified in 10.1.22 -encryption.innodb-discard-import : Modified in 10.1.21 -encryption.innodb-discard-import-change : Modified in 10.1.21 -encryption.innodb-encryption-disable : Modified in 10.1.22 -encryption.innodb_encryption_discard_import : MDEV-11218 - wrong result, also modified in 10.1.21 +encryption.debug_key_management : Modified in 10.1.23 +encryption.encrypt_and_grep : Modified in 10.1.23 +encryption.innodb-bad-key-change : Modified in 10.1.23 +encryption.innodb-bad-key-change2 : MDEV-12632 - Valgrind warnings, modified in 10.1.23 +encryption.innodb-bad-key-change4 : Modified in 10.1.23 +encryption.innodb-compressed-blob : Added in 10.1.23 +encryption.innodb-discard-import-change : MDEV-12632 - Valgrind warnings +encryption.innodb_encryption : MDEV-12623 - Assertion failure +encryption.innodb-encryption-disable : Modified in 10.1.23 +encryption.innodb_encryption_discard_import : MDEV-11218 - wrong result encryption.innodb_encryption_filekeys : MDEV-9962 - timeouts -encryption.innodb_encryption-page-compression : MDEV-11420 - Trying to access missing tablespace -encryption.innodb_encryption_tables : MDEV-9359 - Assertion failure +encryption.innodb_encryption-page-compression : MDEV-11420 - Trying to access missing tablespace, MDEV-12630 - assertion failure, modified in 10.1.23 +encryption.innodb_encryption_tables : MDEV-9359 - Assertion failure, MDEV-12623 - assertion failure, MDEV-12624 - timeout encryption.innodb_first_page : MDEV-10689 - crashes -encryption.innodb-log-encrypt : Modified in 10.1.21 -encryption.innodb_lotoftables : MDEV-11531 - InnoDB error, also modified in 10.1.21 -encryption.innodb-missing-key : MDEV-9359 - assertion failure; also modified in 10.1.22 -encryption.innodb_onlinealter_encryption : MDEV-10099 - wrong results +encryption.innodb-force-corrupt : Added in 10.1.23 +encryption.innodb-key-rotation-disable : Added in 10.1.23 +encryption.innodb_lotoftables : MDEV-11531 - InnoDB error +encryption.innodb-missing-key : MDEV-9359 - assertion failure; also modified in 10.1.23 +encryption.innodb_onlinealter_encryption : MDEV-10099 - wrong results; also modified in 10.1.23 encryption.innodb-page_encryption : MDEV-10641 - mutex problem +encryption.innodb-page_encryption_compression : Modified in 10.1.23 +encryption.innodb-page_encryption_log_encryption : Modified in 10.1.23 +encryption.innodb-redo-badkey : Added in 10.1.23 +encryption.innodb-redo-nokeys : Added in 10.1.23 encryption.innodb-read-only : Added in 10.1.22 -encryption.innodb_scrub : MDEV-8139, also was modified in 10.1.21 -encryption.innodb_scrub_background : MDEV_8139, also was modified in 10.1.21 -encryption.innodb_scrub_compressed : MDEV-8139; also was modified and re-enabled in 10.1.21 +encryption.innodb_scrub : MDEV-8139 - scrubbing tests need fixing +encryption.innodb_scrub_background : MDEV-8139 - scrubbing tests need fixing +encryption.innodb_scrub_compressed : MDEV-8139 - scrubbing tests need fixing #---------------------------------------------------------------- @@ -174,7 +168,6 @@ federated.federatedx : MDEV-10617 - Wrong checksum, timeouts funcs_1.is_columns_mysql : Modified in 10.1.22 funcs_1.processlist_val_no_prot : MDEV-11223 - Wrong result -funcs_2.innodb_charset : Modified in 10.1.21 funcs_2.memory_charset : MDEV-10290 - Timeout funcs_2.myisam_charset : MDEV-11535 - Timeout @@ -182,40 +175,78 @@ funcs_2.myisam_charset : MDEV-11535 - Timeout galera.* : suite.pm modified in 10.1.22 +galera.ev51914 : Added in 10.1.23 +galera.GAL-401 : Added in 10.1.23 +galera.GAL-419 : Added in 10.1.23 +galera.GAL-480 : Added in 10.1.23 +galera.galera_account_management : Modified in 10.1.23 +galera.galera_admin : Added in 10.1.23 +galera.galera_bf_abort : Modified in 10.1.23 +galera.galera_desync_overlapped : Added in 10.1.23 +galera.galera_gcache_recover : Added in 10.1.23 +galera.galera_gcache_recover_full_gcache : Added in 10.1.23 +galera.galera_gcache_recover_manytrx : Added in 10.1.23 +galera.galera_many_tables_pk : Modified in 10.1.23 galera.galera_mdev_10812 : Added in 10.1.22 -galera.galera_var_cluster_address : Modified in 10.1.21 -galera.galera_var_dirty_reads : Modified in 10.1.21 -galera.MW-284 : Modified in 10.1.21 -galera.galera_split_brain : Modified in 10.1.21 +galera.galera_restart_on_unknown_option : Added in 10.1.23 +galera.galera_roles : Modified in 10.1.23 +galera.galera_toi_ddl_fk_update : Added in 10.1.23 +galera.galera_toi_drop_database : Added in 10.1.23 +galera.galera_toi_truncate : Added in 10.1.23 galera.galera_var_certify_nonPK_off : Modified in 10.1.22 +galera.galera_var_cluster_address : Modified in 10.1.23 galera.galera_var_max_ws_rows : Modified in 10.1.22 +galera.galera_var_retry_autocommit : Added in 10.1.23 +galera.galera_var_slave_threads : Modified in 10.1.23 +galera.galera_wsrep_log_conficts : Added in 10.1.23 +galera.galera_wsrep_provider_options_syntax : Added in 10.1.23 +galera.galera#414 : Modified in 10.1.23 +galera.lp1376747 : Added in 10.1.23 +galera.lp1376747-2 : Added in 10.1.23 +galera.lp1376747-3 : Added in 10.1.23 +galera.lp1376747-4 : Added in 10.1.23 +galera.MW-258 : Modified in 10.1.23 +galera.MW-313 : Added in 10.1.23 +galera.MW-328A : Added in 10.1.23 +galera.MW-328B : Added in 10.1.23 +galera.MW-328C : Added in 10.1.23 +galera.MW-328D : Added in 10.1.23 +galera.MW-328E : Added in 10.1.23 +galera.MW-329 : Added in 10.1.23 +galera.pxc-421 : Added in 10.1.23 galera_3nodes.* : MDEV-11490 - Warnings not suppressed +galera_3nodes.galera_safe_to_bootstrap : Added in 10.1.23 + #---------------------------------------------------------------- innodb.101_compatibility : Modified in 10.1.22 innodb.alter_key_block_size-11757 : Added in 10.1.22 +innodb.autoinc_debug : Added in 10.1.23 innodb.binlog_consistent : MDEV-10618 - Server fails to start innodb.doublewrite : Modified in 10.1.22 -innodb.group_commit_crash : Modified in 10.1.21 -innodb.group_commit_crash_no_optimize_thread : Modified in 10.1.21 innodb.innodb-32k-crash : Modified in 10.1.22 innodb.innodb-64k-crash : Modified in 10.1.22 +innodb.innodb-alter-debug : Added in 10.1.23 +innodb.innodb-alter-nullable : Added in 10.1.23 innodb.innodb-alter-table : MDEV-10619 - Testcase timeout innodb.innodb-blob : MDEV-12053 - Client crash; also modified in 10.1.22 innodb.innodb_blob_unrecoverable_crash : Modified in 10.1.22 innodb.innodb-bug-14068765 : MDEV-9105 - valgrind warnings, assertion failures innodb.innodb-bug-14084530 : MDEV-9105 - valgrind warnings, assertion failures innodb.innodb_bug11754376 : Modified in 10.1.22 -innodb.innodb_bug14147491 : MDEV-11808, also modified in 10.1.22 -innodb.innodb_bug14676111 : MDEV-11802 - wrong result; also modified in 10.1.22 +innodb.innodb_bug14147491 : MDEV-11808, MDEV-12628 - wrong error codes, also modified in 10.1.23 +innodb.innodb_bug14676111 : Modified in 10.1.23 innodb.innodb_bug30423 : MDEV-7311 - Wrong number of rows in the plan +innodb.innodb_bug53290 : MDEV-12634 - Valgrind warnings innodb.innodb_bug53756 : Modified in 10.1.22 innodb.innodb_bug56947 : Modified in 10.1.22 innodb.innodb_bug59641 : Modified in 10.1.22 -innodb.innodb-change-buffer-recovery : Modified in 10.1.21 +innodb.innodb_defragment : Modified in 10.1.23 +innodb.innodb_defragment_small : Added in 10.1.23 innodb.innodb-get-fk : Modified in 10.1.22 +innodb.innodb-index-online-norebuild : Added in 10.1.23 innodb.innodb-page_compression_default : Added in 10.1.22 innodb.innodb-page_compression_zip : MDEV-10641 - mutex problem innodb.innodb_stats : MDEV-10682 - wrong result @@ -224,17 +255,30 @@ innodb.innodb-wl5522 : MDEV-9105 - valgrind warnings, assertion innodb.innodb-wl5522-1 : MDEV-9105 - valgrind warnings, assertion failures innodb.innodb-wl5522-debug : Modified in 10.1.22 innodb.innodb-wl5522-debug-zip : Modified in 10.1.22 -innodb.log_data_file_size : Added in 10.1.21 -innodb.log_file_size : Added in 10.1.22 +innodb.log_file_size : MDEV-12635 - Valgrind warnings, added in 10.1.22 innodb.read_only_recovery : Added in 10.1.22 innodb.xa_recovery : Modified in 10.1.22 innodb_fts.crash_recovery : Added in 10.1.22 +innodb_fts.create : MDEV-12625 - Valgrind warnings +innodb_fts.fulltext_misc : MDEV-12636 - Valgrind warnings +innodb_fts.innodb-fts-ddl : MDEV-12625 - Valgrind warnings +innodb_fts.innodb-fts-fic : MDEV-12625 - Valgrind warnings +innodb_fts.innodb_fts_misc : MDEV-12625 - Valgrind warnings +innodb_fts.innodb_fts_misc_1 : MDEV-12625 - Valgrind warnings +innodb_fts.innodb_fts_multiple_index : MDEV-12625 - Valgrind warnings +innodb_fts.innodb_fts_proximity : MDEV-12625 - Valgrind warnings innodb_fts.innodb_fts_result_cache_limit : Modified in 10.1.22 +innodb_fts.innodb_fts_stopword_charset : MDEV-12625 - Valgrind warnings; modified in 10.1.23 +innodb_fts.innodb_fts_transaction : MDEV-12625 - Valgrind warnings innodb_fts.misc_debug : Added in 10.1.22 #---------------------------------------------------------------- +mariabackup.* : Added in 10.1.23 + +#---------------------------------------------------------------- + mroonga/storage.column_datetime_32bit_2038 : Wrong result on Alpha mroonga/storage.column_datetime_32bit_before_unix_epoch : Wrong result on Alpha mroonga/storage.column_datetime_32bit_max : Wrong result on Alpha @@ -247,7 +291,7 @@ mroonga/storage.repair_table_no_index_file : MDEV-9364 - #---------------------------------------------------------------- multi_source.gtid : MDEV-10417 - Fails on Mips -multi_source.info_logs : MDEV-10042 - wrong result +multi_source.info_logs : MDEV-10042 - Wrong result, MDEV-12629 - Valgrind warnings multi_source.multisource : MDEV-10417 - Fails on Mips multi_source.reset_slave : MDEV-10690 - wrong result multi_source.simple : MDEV-4633 - Wrong slave status output @@ -287,11 +331,17 @@ perfschema.merge_table_io : Uses table_io_result_helper.inc m perfschema.multi_table_io : Uses table_io_result_helper.inc modified in 10.1.22 perfschema.myisam_table_io : Uses table_io_result_helper.inc modified in 10.1.22 perfschema.part_table_io : Uses table_io_result_helper.inc modified in 10.1.22 +perfschema.pfs_upgrade_event : Modified in 10.1.23 +perfschema.pfs_upgrade_func : Modified in 10.1.23 +perfschema.pfs_upgrade_proc : Modified in 10.1.23 +perfschema.pfs_upgrade_table : Modified in 10.1.23 +perfschema.pfs_upgrade_view : Modified in 10.1.23 perfschema.privilege_table_io : Uses table_io_result_helper.inc modified in 10.1.22 perfschema.rollback_table_io : Uses table_io_result_helper.inc modified in 10.1.22 perfschema.setup_actors : MDEV-10679 - rare crash perfschema.socket_summary_by_event_name_func : MDEV-10622 - Socket summary tables do not match perfschema.stage_mdl_global : MDEV-11803 - wrong result on slow builders +perfschema.stage_mdl_table : MDEV-12639 - Wrong result perfschema.table_name : Modified in 10.1.22 perfschema.temp_table_io : Uses table_io_result_helper.inc modified in 10.1.22 perfschema.threads_mysql : MDEV-10677 - sporadic wrong result; also modified in 10.1.22 @@ -303,6 +353,7 @@ perfschema.view_table_io : Uses table_io_result_helper.inc m plugins.auth_ed25519 : Added in 10.1.22 plugins.cracklib_password_check : MDEV-11650 - valgrind warnings plugins.feedback_plugin_send : MDEV-7932 - ssl failed for url +plugins.pam : MDEV-10940 - valgrind warnings plugins.server_audit : MDEV-9562 - crashes on sol10-sparc plugins.thread_pool_server_audit : MDEV-9562 - crashes on sol10-sparc plugins.two_password_validations : MDEV-11650 - valgrind warnings @@ -310,7 +361,6 @@ plugins.two_password_validations : MDEV-11650 - valgrind warnings #---------------------------------------------------------------- rpl.last_insert_id : MDEV-10625 - warnings in error log -rpl.rpl_alter_extra_persistent : Added in 10.1.21 rpl.rpl_auto_increment : MDEV-10417 - Fails on Mips rpl.rpl_auto_increment_bug45679 : MDEV-10417 - Fails on Mips rpl.rpl_auto_increment_update_failure : MDEV-10625 - warnings in error log @@ -321,7 +371,7 @@ rpl.rpl_domain_id_filter_restart : MDEV-10684 - Wrong result rpl.rpl_gtid_basic : MDEV-10681 - server startup problem rpl.rpl_gtid_crash : MDEV-9501 - Warning: failed registering on master rpl.rpl_gtid_mdev9033 : MDEV-10680 - warnings -rpl.rpl_gtid_stop_start : MDEV-10629 - Crash on shutdown +rpl.rpl_gtid_stop_start : MDEV-10629 - Crash on shutdown, MDEV-12629 - Valgrind warnings rpl.rpl_gtid_until : MDEV-10625 - warnings in error log rpl.rpl_heartbeat_basic : Modified in 10.1.22 rpl.rpl_innodb_bug30888 : MDEV-10417 - Fails on Mips @@ -331,6 +381,7 @@ rpl.rpl_invoked_features : MDEV-10417 - Fails on Mips rpl.rpl_mariadb_slave_capability : MDEV-11018 - sporadic wrong events in binlog rpl.rpl_mdev6020 : MDEV-10630, MDEV-10417 - Timeouts, fails on Mips rpl.rpl_mdev6386 : Modified in 10.1.22 +rpl.rpl_mysql_upgrade : Modified in 10.1.23 rpl.rpl_parallel : MDEV-10653 - Timeouts rpl.rpl_parallel_optimistic : MDEV-10511 - timeout rpl.rpl_parallel_retry : MDEV-11119 - Server crash @@ -338,7 +389,6 @@ rpl.rpl_parallel_temptable : MDEV-10356 - Crash in close_thread_table rpl.rpl_partition_innodb : MDEV-10417 - Fails on Mips rpl.rpl_password_boundaries : MDEV-11534 - Slave IO warnings rpl.rpl_row_log_innodb : MDEV-10688 - Wrong result -rpl.rpl_row_mysqlbinlog : Modified in 10.1.21 rpl.rpl_row_sp001 : MDEV-9329 - Fails on Ubuntu/s390x rpl.rpl_semi_sync : MDEV-11220 - Wrong result rpl.rpl_semi_sync_event_after_sync : MDEV-11806 - warnings @@ -347,10 +397,8 @@ rpl.rpl_semi_sync_wait_point : MDEV-11807 - timeout in wait condition rpl.rpl_show_slave_hosts : MDEV-10681 - server startup problem rpl.rpl_skip_replication : MDEV-9268 - Fails with timeout in sync_slave_with_master on Alpha rpl.rpl_slave_grp_exec : MDEV-10514 - Unexpected deadlock -rpl.rpl_special_charset : Modified in 10.1.21 rpl.rpl_sync : MDEV-10633 - Database page corruption rpl.rpl_temporary_error2 : MDEV-10634 - Wrong number of retries -rpl.sec_behind_master-5114 : Modified in 10.1.21 #---------------------------------------------------------------- @@ -361,6 +409,7 @@ spider/bg.direct_aggregate_part : MDEV-7098 - Trying to unlock mutex that wasn't spider/bg.ha : MDEV-7914, MDEV-9329 - Crash, failures on s390x spider/bg.ha_part : MDEV-9329 - Fails on Ubuntu/s390x spider/bg.spider_fixes : MDEV-7098, MDEV-9329 - Mutex problem, failures on s390x +spider/bg.spider3_fixes : MDEV-12639 - Packets out of order spider/bg.vp_fixes : MDEV-9329 - Fails on Ubuntu/s390x spider/handler.* : MDEV-10987, MDEV-10990 - Tests have not been maintained @@ -389,17 +438,24 @@ sys_vars.rpl_init_slave_func : MDEV-10149 - wrong results sys_vars.secure_file_priv : Modified in 10.1.22 sys_vars.sysvars_innodb : MDEV-6958 - error-prone rdiffs sys_vars.sysvars_server_embedded : MDEV-6958 - error-prone rdiffs +sys_vars.sysvars_wsrep : MDEV-12522 - Dependency on specific wsrep_provider #---------------------------------------------------------------- +tokudb.change_column_all_1000_10 : MDEV-12640 - Crash +tokudb.change_column_bin : MDEV-12640 - Crash tokudb.cluster_filter : MDEV-10678 - Wrong execution plan tokudb.cluster_filter_hidden : MDEV-10678 - Wrong execution plan tokudb.cluster_filter_unpack_varchar : MDEV-10636 - Wrong execution plan tokudb.dir_per_db : MDEV-11537 - Wrong result tokudb.dir_per_db_rename_to_nonexisting_schema : Added in 10.1.22 tokudb.gap_lock_error : Added in 10.1.22 +tokudb.hotindex-insert-bigchar : MDEV-12640 - Crash +tokudb.hotindex-update-1 : MDEV-12640 - Crash tokudb.locks-select-update-3 : Modified in 10.1.22 tokudb.percona_kill_idle_trx_tokudb : Modified in 10.1.22 +tokudb.rows-32m-rand-insert : MDEV-12640 - Crash +tokudb.rows-32m-seq-insert : MDEV-12640 - Crash tokudb_backup.* : suite.opt modified in 10.1.22 @@ -421,6 +477,7 @@ vcol.vcol_select_myisam : Modified in 10.1.22 wsrep.binlog_format : MDEV-11532 - WSREP has not yet prepared node wsrep.pool_of_threads : MDEV-12234 - Library problem on Power +wsrep.wsrep_rpl : Modified in 10.1.23 wsrep_info.* : suite.pm changed in 10.1.22 wsrep_info.plugin : MDEV-12232 - Crash on Power diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index eb03eae6045..96a6e2ea1c2 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -1,5 +1,5 @@ # Copyright (c) 2005, 2015, Oracle and/or its affiliates. -# Copyright (c) 2008, 2016, MariaDB +# Copyright (c) 2008, 2017, MariaDB # # This program is free software; you can redistribute it and/or # modify it under the terms of the GNU Library General Public diff --git a/mysys/mysys_priv.h b/mysys/mysys_priv.h index 471862a6d0b..da11b86e768 100644 --- a/mysys/mysys_priv.h +++ b/mysys/mysys_priv.h @@ -108,9 +108,13 @@ size_t sf_malloc_usable_size(void *ptr, my_bool *is_thread_specific); void my_error_unregister_all(void); -#if !defined(O_PATH) && defined(O_EXEC) /* FreeBSD */ +#ifndef O_PATH /* not Linux */ +#if defined(O_SEARCH) /* Illumos */ +#define O_PATH O_SEARCH +#elif defined(O_EXEC) /* FreeBSD */ #define O_PATH O_EXEC #endif +#endif #ifdef O_PATH #define HAVE_OPEN_PARENT_DIR_NOSYMLINKS diff --git a/scripts/galera_new_cluster.sh b/scripts/galera_new_cluster.sh index cf81bd0b95a..b873192cf31 100755 --- a/scripts/galera_new_cluster.sh +++ b/scripts/galera_new_cluster.sh @@ -5,6 +5,22 @@ # the Free Software Foundation; either version 2.1 of the License, or # (at your option) any later version. +if [ "${1}" == "-h" -o "${1}" == "--help" ]; then + cat <<EOF + +Usage: ${0} + + The script galera_new_cluster is used to bootstrap new Galera Cluster, + when all the nodes are down. Run galera_new_cluster on the first node only. + On the remaining nodes simply run 'service @DAEMON_NAME@ start'. + + For more information on Galera Cluster configuration and usage see: + https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluster/ + +EOF + exit 0 +fi + VERSION="@VERSION@@MYSQL_SERVER_SUFFIX@" COMPILATION_COMMENT="@COMPILATION_COMMENT@" diff --git a/scripts/mysql_secure_installation.sh b/scripts/mysql_secure_installation.sh index 2c3b2f8dc8c..57e4d43dfad 100644 --- a/scripts/mysql_secure_installation.sh +++ b/scripts/mysql_secure_installation.sh @@ -1,6 +1,6 @@ #!/bin/sh -# Copyright (c) 2002, 2012, Oracle and/or its affiliates. +# Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -194,6 +194,20 @@ set_echo_compat() { esac } +validate_reply () { + ret=0 + if [ -z "$1" ]; then + reply=y + return $ret + fi + case $1 in + y|Y|yes|Yes|YES) reply=y ;; + n|N|no|No|NO) reply=n ;; + *) ret=1 ;; + esac + return $ret +} + prepare() { touch $config $command chmod 600 $config $command @@ -407,15 +421,18 @@ echo "Setting the root password ensures that nobody can log into the MariaDB" echo "root user without the proper authorisation." echo -if [ $hadpass -eq 0 ]; then - echo $echo_n "Set root password? [Y/n] $echo_c" -else - echo "You already have a root password set, so you can safely answer 'n'." - echo - echo $echo_n "Change the root password? [Y/n] $echo_c" -fi +while true ; do + if [ $hadpass -eq 0 ]; then + echo $echo_n "Set root password? [Y/n] $echo_c" + else + echo "You already have a root password set, so you can safely answer 'n'." + echo + echo $echo_n "Change the root password? [Y/n] $echo_c" + fi + read reply + validate_reply $reply && break +done -read reply if [ "$reply" = "n" ]; then echo " ... skipping." else @@ -439,9 +456,11 @@ echo "go a bit smoother. You should remove them before moving into a" echo "production environment." echo -echo $echo_n "Remove anonymous users? [Y/n] $echo_c" - -read reply +while true ; do + echo $echo_n "Remove anonymous users? [Y/n] $echo_c" + read reply + validate_reply $reply && break +done if [ "$reply" = "n" ]; then echo " ... skipping." else @@ -457,9 +476,11 @@ echo echo "Normally, root should only be allowed to connect from 'localhost'. This" echo "ensures that someone cannot guess at the root password from the network." echo - -echo $echo_n "Disallow root login remotely? [Y/n] $echo_c" -read reply +while true ; do + echo $echo_n "Disallow root login remotely? [Y/n] $echo_c" + read reply + validate_reply $reply && break +done if [ "$reply" = "n" ]; then echo " ... skipping." else @@ -477,8 +498,12 @@ echo "access. This is also intended only for testing, and should be removed" echo "before moving into a production environment." echo -echo $echo_n "Remove test database and access to it? [Y/n] $echo_c" -read reply +while true ; do + echo $echo_n "Remove test database and access to it? [Y/n] $echo_c" + read reply + validate_reply $reply && break +done + if [ "$reply" = "n" ]; then echo " ... skipping." else @@ -495,8 +520,12 @@ echo "Reloading the privilege tables will ensure that all changes made so far" echo "will take effect immediately." echo -echo $echo_n "Reload privilege tables now? [Y/n] $echo_c" -read reply +while true ; do + echo $echo_n "Reload privilege tables now? [Y/n] $echo_c" + read reply + validate_reply $reply && break +done + if [ "$reply" = "n" ]; then echo " ... skipping." else diff --git a/sql-common/client.c b/sql-common/client.c index 47a68651e05..76dca42e14c 100644 --- a/sql-common/client.c +++ b/sql-common/client.c @@ -1679,8 +1679,8 @@ mysql_ssl_set(MYSQL *mysql __attribute__((unused)) , mysql_options(mysql, MYSQL_OPT_SSL_CAPATH, capath) | mysql_options(mysql, MYSQL_OPT_SSL_CIPHER, cipher) ? 1 : 0); - mysql->options.use_ssl= TRUE; #endif /* HAVE_OPENSSL && !EMBEDDED_LIBRARY */ + mysql->options.use_ssl= TRUE; DBUG_RETURN(result); } @@ -2542,7 +2542,6 @@ static int send_client_reply_packet(MCPVIO_EXT *mpvio, int3store(buff+2, net->max_packet_size); end= buff+5; } -#ifdef HAVE_OPENSSL /* If client uses ssl and client also has to verify the server @@ -2560,6 +2559,7 @@ static int send_client_reply_packet(MCPVIO_EXT *mpvio, goto error; } +#ifdef HAVE_OPENSSL if (mysql->client_flag & CLIENT_SSL) { /* Do the SSL layering. */ diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt index c0a123a0c74..acb83ea494a 100644 --- a/sql/CMakeLists.txt +++ b/sql/CMakeLists.txt @@ -68,6 +68,7 @@ ENDIF() ADD_CUSTOM_COMMAND( OUTPUT ${CMAKE_CURRENT_BINARY_DIR}/lex_token.h COMMAND gen_lex_token > lex_token.h + DEPENDS gen_lex_token ) ADD_DEFINITIONS(-DMYSQL_SERVER -DHAVE_EVENT_SCHEDULER) @@ -327,6 +328,7 @@ ENDIF() ADD_CUSTOM_COMMAND( OUTPUT ${CMAKE_CURRENT_BINARY_DIR}/lex_hash.h COMMAND gen_lex_hash > lex_hash.h + DEPENDS gen_lex_hash ) MYSQL_ADD_EXECUTABLE(mysql_tzinfo_to_sql tztime.cc COMPONENT Server) @@ -444,7 +446,7 @@ IF(WIN32) ${CMAKE_CURRENT_BINARY_DIR}/my_bootstrap.sql mysql_bootstrap_sql.c WORKING_DIRECTORY ${CMAKE_CURRENT_BINARY_DIR} - DEPENDS ${my_bootstrap_sql} + DEPENDS comp_sql ${my_bootstrap_sql} ) MYSQL_ADD_EXECUTABLE(mysql_install_db diff --git a/sql/item.cc b/sql/item.cc index 579b6539fb4..7f29915c965 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -10036,7 +10036,8 @@ void Item_cache_row::set_null() Item_type_holder::Item_type_holder(THD *thd, Item *item) :Item(thd, item), Type_handler_hybrid_real_field_type(get_real_type(item)), - enum_set_typelib(0) + enum_set_typelib(0), + geometry_type(Field::GEOM_GEOMETRY) { DBUG_ASSERT(item->fixed); maybe_null= item->maybe_null; diff --git a/sql/item.h b/sql/item.h index 2f33d72136c..42e9fd94331 100644 --- a/sql/item.h +++ b/sql/item.h @@ -439,19 +439,6 @@ typedef struct replace_equal_field_arg struct st_join_table *context_tab; } REPLACE_EQUAL_FIELD_ARG; - -class Load_data_out_param -{ -public: - Load_data_out_param() { } - virtual ~Load_data_out_param() { } - virtual void load_data_set_null_value(CHARSET_INFO *cs) = 0; - virtual void load_data_set_value(const char *str, uint length, - CHARSET_INFO *cs) = 0; - virtual void load_data_print(THD *thd, String *str) = 0; -}; - - class Settable_routine_parameter { public: @@ -1802,14 +1789,6 @@ public: delete this; } - virtual Load_data_out_param *get_load_data_out_param() { return 0; } - Load_data_out_param *get_load_data_out_param_or_error() - { - Load_data_out_param *res= get_load_data_out_param(); - if (!res) - my_error(ER_LOAD_DATA_INVALID_COLUMN, MYF(0), full_name()); - return res; - } virtual Item_splocal *get_item_splocal() { return 0; } virtual Rewritable_query_parameter *get_rewritable_query_parameter() { return 0; } diff --git a/sql/item_func.cc b/sql/item_func.cc index ba31f2794be..903d792db75 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -5675,15 +5675,14 @@ bool Item_user_var_as_out_param::fix_fields(THD *thd, Item **ref) } -void Item_user_var_as_out_param::load_data_set_null_value(CHARSET_INFO* cs) +void Item_user_var_as_out_param::set_null_value(CHARSET_INFO* cs) { ::update_hash(entry, TRUE, 0, 0, STRING_RESULT, cs, 0 /* unsigned_arg */); } -void Item_user_var_as_out_param::load_data_set_value(const char *str, - uint length, - CHARSET_INFO* cs) +void Item_user_var_as_out_param::set_value(const char *str, uint length, + CHARSET_INFO* cs) { ::update_hash(entry, FALSE, (void*)str, length, STRING_RESULT, cs, 0 /* unsigned_arg */); @@ -5718,7 +5717,7 @@ my_decimal* Item_user_var_as_out_param::val_decimal(my_decimal *decimal_buffer) } -void Item_user_var_as_out_param::load_data_print(THD *thd, String *str) +void Item_user_var_as_out_param::print_for_load(THD *thd, String *str) { str->append('@'); append_identifier(thd, str, name.str, name.length); diff --git a/sql/item_func.h b/sql/item_func.h index 37d3d4b1122..133074122dd 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -2006,8 +2006,7 @@ public: in List<Item> and desire to place this code somewhere near other functions working with user variables. */ -class Item_user_var_as_out_param :public Item, - public Load_data_out_param +class Item_user_var_as_out_param :public Item { LEX_STRING name; user_var_entry *entry; @@ -2022,10 +2021,9 @@ public: my_decimal *val_decimal(my_decimal *decimal_buffer); /* fix_fields() binds variable name with its entry structure */ bool fix_fields(THD *thd, Item **ref); - Load_data_out_param *get_load_data_out_param() { return this; } - void load_data_print(THD *thd, String *str); - void load_data_set_null_value(CHARSET_INFO* cs); - void load_data_set_value(const char *str, uint length, CHARSET_INFO* cs); + void print_for_load(THD *thd, String *str); + void set_null_value(CHARSET_INFO* cs); + void set_value(const char *str, uint length, CHARSET_INFO* cs); enum_field_types field_type() const { return MYSQL_TYPE_DOUBLE; } Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return get_item_copy<Item_user_var_as_out_param>(thd, mem_root, this); } diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index b34f9fd1b58..78382740a07 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -1,6 +1,6 @@ /* - Copyright (c) 2000, 2013, Oracle and/or its affiliates. - Copyright (c) 2009, 2013, Monty Program Ab. + Copyright (c) 2000, 2017, Oracle and/or its affiliates. + Copyright (c) 2009, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -3898,6 +3898,7 @@ String *Item_func_quote::val_str(String *str) 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 }; + ulong max_allowed_packet= current_thd->variables.max_allowed_packet; char *from, *to, *end, *start; String *arg= args[0]->val_str(str); uint arg_length, new_length; @@ -3916,11 +3917,14 @@ String *Item_func_quote::val_str(String *str) new_length= arg_length + 2; /* for beginning and ending ' signs */ for (from= (char*) arg->ptr(), end= from + arg_length; from < end; from++) new_length+= get_esc_bit(escmask, (uchar) *from); + if (new_length > max_allowed_packet) + goto toolong; } else { new_length= (arg_length * 2) + /* For string characters */ (2 * collation.collation->mbmaxlen); /* For quotes */ + set_if_smaller(new_length, max_allowed_packet); } if (tmp_value.alloc(new_length)) @@ -3936,7 +3940,7 @@ String *Item_func_quote::val_str(String *str) /* Put leading quote */ if ((mblen= cs->cset->wc_mb(cs, '\'', (uchar *) to, to_end)) <= 0) - goto null; + goto toolong; to+= mblen; for (start= (char*) arg->ptr(), end= start + arg_length; start < end; ) @@ -3956,17 +3960,17 @@ String *Item_func_quote::val_str(String *str) if (escape) { if ((mblen= cs->cset->wc_mb(cs, '\\', (uchar*) to, to_end)) <= 0) - goto null; + goto toolong; to+= mblen; } if ((mblen= cs->cset->wc_mb(cs, wc, (uchar*) to, to_end)) <= 0) - goto null; + goto toolong; to+= mblen; } /* Put trailing quote */ if ((mblen= cs->cset->wc_mb(cs, '\'', (uchar *) to, to_end)) <= 0) - goto null; + goto toolong; to+= mblen; new_length= to - tmp_value.ptr(); goto ret; @@ -4010,6 +4014,11 @@ ret: null_value= 0; return &tmp_value; +toolong: + push_warning_printf(current_thd, Sql_condition::WARN_LEVEL_WARN, + ER_WARN_ALLOWED_PACKET_OVERFLOWED, + ER_THD(current_thd, ER_WARN_ALLOWED_PACKET_OVERFLOWED), + func_name(), max_allowed_packet); null: null_value= 1; return 0; diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 83bd9ed0bdf..72ea63612f0 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -724,6 +724,13 @@ public: in_strategy= (SUBS_STRATEGY_CHOSEN | strategy); DBUG_VOID_RETURN; } + + bool walk(Item_processor processor, bool walk_subquery, void *arg) + { + return left_expr->walk(processor, walk_subquery, arg) || + Item_subselect::walk(processor, walk_subquery, arg); + } + bool exists2in_processor(void *opt_arg __attribute__((unused))) { return 0; diff --git a/sql/item_xmlfunc.cc b/sql/item_xmlfunc.cc index 4d848a0f737..10dda8f20e8 100644 --- a/sql/item_xmlfunc.cc +++ b/sql/item_xmlfunc.cc @@ -1,4 +1,5 @@ -/* Copyright (c) 2005, 2013, Oracle and/or its affiliates. +/* Copyright (c) 2005, 2016, Oracle and/or its affiliates. + Copyright (c) 2009, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -2836,9 +2837,9 @@ int xml_enter(MY_XML_PARSER *st,const char *attr, size_t len) node.parent= data->parent; // Set parent for the new node to old parent data->parent= numnodes; // Remember current node as new parent - DBUG_ASSERT(data->level <= MAX_LEVEL); + DBUG_ASSERT(data->level < MAX_LEVEL); data->pos[data->level]= numnodes; - if (data->level < MAX_LEVEL) + if (data->level < MAX_LEVEL - 1) node.level= data->level++; else return MY_XML_ERROR; diff --git a/sql/sp.cc b/sql/sp.cc index a1662cefb9e..383eef9f5ce 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -1,6 +1,6 @@ /* - Copyright (c) 2002, 2015, Oracle and/or its affiliates. - Copyright (c) 2009, 2015, MariaDB + Copyright (c) 2002, 2016, Oracle and/or its affiliates. + Copyright (c) 2009, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 43419472026..81ded19dd87 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -1938,7 +1938,7 @@ bool THD::notify_shared_lock(MDL_context_owner *ctx_in_use, if (!thd_table->needs_reopen()) { signalled|= mysql_lock_abort_for_thread(this, thd_table); - if (this && WSREP(this) && wsrep_thd_is_BF(this, FALSE)) + if (WSREP(this) && wsrep_thd_is_BF(this, FALSE)) { WSREP_DEBUG("remove_table_from_cache: %llu", (unsigned long long) this->real_id); diff --git a/sql/sql_const.h b/sql/sql_const.h index c8e60305eab..95960ac131a 100644 --- a/sql/sql_const.h +++ b/sql/sql_const.h @@ -69,6 +69,7 @@ #define RAND_TABLE_BIT (((table_map) 1) << (sizeof(table_map)*8-1)) #define PSEUDO_TABLE_BITS (PARAM_TABLE_BIT | OUTER_REF_TABLE_BIT | \ RAND_TABLE_BIT) +#define CONNECT_STRING_MAXLEN 65535 /* stored in 2 bytes in .frm */ #define MAX_FIELDS 4096 /* Limit in the .frm file */ #define MAX_PARTITIONS 8192 diff --git a/sql/sql_load.cc b/sql/sql_load.cc index 95da65a1c63..06eb86dd694 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -431,7 +431,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, else tot_length+= field->field_length; } - else if (item->get_load_data_out_param()) + else if (item->type() == Item::STRING_ITEM) use_vars= 1; } if (use_blobs && !ex->line_term->length() && !field_term->length()) @@ -814,7 +814,11 @@ static bool write_execute_load_query_log_event(THD *thd, sql_exchange* ex, if (item->real_type() == Item::FIELD_ITEM) append_identifier(thd, &query_str, item->name, strlen(item->name)); else - item->get_load_data_out_param()->load_data_print(thd, &query_str); + { + /* Actually Item_user_var_as_out_param despite claiming STRING_ITEM. */ + DBUG_ASSERT(item->type() == Item::STRING_ITEM); + ((Item_user_var_as_out_param *)item)->print_for_load(thd, &query_str); + } } query_str.append(")"); } @@ -1057,8 +1061,7 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, { uint length; uchar *pos; - Item *real_item; - Load_data_out_param *out_param; + Item_field *real_item; if (read_info.read_field()) break; @@ -1070,16 +1073,26 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, pos=read_info.row_start; length=(uint) (read_info.row_end-pos); - real_item= item->real_item(); + real_item= item->field_for_view_update(); if ((!read_info.enclosed && (enclosed_length && length == 4 && !memcmp(pos, STRING_WITH_LEN("NULL")))) || (length == 1 && read_info.found_null)) { - if (real_item->type() == Item::FIELD_ITEM) + if (item->type() == Item::STRING_ITEM) + { + ((Item_user_var_as_out_param *)item)->set_null_value( + read_info.read_charset); + } + else if (!real_item) { - Field *field= ((Item_field *)real_item)->field; + my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), item->name); + DBUG_RETURN(1); + } + else + { + Field *field= real_item->field; if (field->reset()) { my_error(ER_WARN_NULL_TO_NOTNULL, MYF(0), field->field_name, @@ -1101,17 +1114,24 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, } /* Do not auto-update this field. */ field->set_has_explicit_value(); - } - else if ((out_param= item->get_load_data_out_param_or_error())) - out_param->load_data_set_null_value(read_info.read_charset); - else - DBUG_RETURN(1); + } + continue; } - if (real_item->type() == Item::FIELD_ITEM) + if (item->type() == Item::STRING_ITEM) { - Field *field= ((Item_field *)real_item)->field; + ((Item_user_var_as_out_param *)item)->set_value((char*) pos, length, + read_info.read_charset); + } + else if (!real_item) + { + my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), item->name); + DBUG_RETURN(1); + } + else + { + Field *field= real_item->field; field->set_notnull(); read_info.row_end[0]=0; // Safe to change end marker if (field == table->next_number_field) @@ -1119,11 +1139,6 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, field->store((char*) pos, length, read_info.read_charset); field->set_has_explicit_value(); } - else if ((out_param= item->get_load_data_out_param_or_error())) - out_param->load_data_set_value((const char *) pos, length, - read_info.read_charset); - else - DBUG_RETURN(1); } if (thd->is_error()) @@ -1143,11 +1158,20 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, break; for (; item ; item= it++) { - Load_data_out_param *out_param; - Item *real_item= item->real_item(); - if (real_item->type() == Item::FIELD_ITEM) + Item_field *real_item= item->field_for_view_update(); + if (item->type() == Item::STRING_ITEM) + { + ((Item_user_var_as_out_param *)item)->set_null_value( + read_info.read_charset); + } + else if (!real_item) { - Field *field= ((Item_field *)real_item)->field; + my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), item->name); + DBUG_RETURN(1); + } + else + { + Field *field= real_item->field; if (field->reset()) { my_error(ER_WARN_NULL_TO_NOTNULL, MYF(0),field->field_name, @@ -1169,10 +1193,6 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, ER_THD(thd, ER_WARN_TOO_FEW_RECORDS), thd->get_stmt_da()->current_row_for_warning()); } - else if ((out_param= item->get_load_data_out_param_or_error())) - out_param->load_data_set_null_value(read_info.read_charset); - else - DBUG_RETURN(1); } } @@ -1268,7 +1288,6 @@ read_xml_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, while ((item= it++)) { - Load_data_out_param *out_param; /* If this line is to be skipped we don't want to fill field or var */ if (skip_lines) continue; @@ -1280,11 +1299,19 @@ read_xml_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, while(tag && strcmp(tag->field.c_ptr(), item->name) != 0) tag= xmlit++; + Item_field *real_item= item->field_for_view_update(); if (!tag) // found null { - if (item->type() == Item::FIELD_ITEM) + if (item->type() == Item::STRING_ITEM) + ((Item_user_var_as_out_param *) item)->set_null_value(cs); + else if (!real_item) + { + my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), item->name); + DBUG_RETURN(1); + } + else { - Field *field= ((Item_field *) item)->field; + Field *field= real_item->field; field->reset(); field->set_null(); if (field == table->next_number_field) @@ -1300,15 +1327,21 @@ read_xml_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, /* Do not auto-update this field. */ field->set_has_explicit_value(); } - else if ((out_param= item->get_load_data_out_param_or_error())) - out_param->load_data_set_null_value(cs); - else - DBUG_RETURN(1); continue; } - if (item->type() == Item::FIELD_ITEM) + if (item->type() == Item::STRING_ITEM) + ((Item_user_var_as_out_param *) item)->set_value( + (char *) tag->value.ptr(), + tag->value.length(), cs); + else if (!real_item) + { + my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), item->name); + DBUG_RETURN(1); + } + else { + Field *field= ((Item_field *)item)->field; field->set_notnull(); if (field == table->next_number_field) @@ -1316,12 +1349,6 @@ read_xml_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, field->store((char *) tag->value.ptr(), tag->value.length(), cs); field->set_has_explicit_value(); } - else if ((out_param= item->get_load_data_out_param_or_error())) - out_param->load_data_set_value((const char *) tag->value.ptr(), - tag->value.length(), cs); - else - DBUG_RETURN(1); - } if (read_info.error) @@ -1341,8 +1368,15 @@ read_xml_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, for ( ; item; item= it++) { - Load_data_out_param *out_param; - if (item->type() == Item::FIELD_ITEM) + Item_field *real_item= item->field_for_view_update(); + if (item->type() == Item::STRING_ITEM) + ((Item_user_var_as_out_param *)item)->set_null_value(cs); + else if (!real_item) + { + my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), item->name); + DBUG_RETURN(1); + } + else { /* QQ: We probably should not throw warning for each field. @@ -1356,10 +1390,6 @@ read_xml_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, ER_THD(thd, ER_WARN_TOO_FEW_RECORDS), thd->get_stmt_da()->current_row_for_warning()); } - else if ((out_param= item->get_load_data_out_param_or_error())) - out_param->load_data_set_null_value(cs); - else - DBUG_RETURN(1); } } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 590d2dfe681..a041fd0faaf 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1,5 +1,5 @@ -/* Copyright (c) 2000, 2015, Oracle and/or its affiliates. - Copyright (c) 2008, 2016, MariaDB Corporation +/* Copyright (c) 2000, 2017, Oracle and/or its affiliates. + Copyright (c) 2008, 2017, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c703e58d237..2d819d3207c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8780,8 +8780,6 @@ bool JOIN::get_best_combination() full_join=0; hash_join= FALSE; - used_tables= OUTER_REF_TABLE_BIT; // Outer row is already read - fix_semijoin_strategies_for_picked_join_order(this); JOIN_TAB_RANGE *root_range; @@ -8845,7 +8843,6 @@ bool JOIN::get_best_combination() j->bush_root_tab= sjm_nest_root; form= table[tablenr]= j->table; - used_tables|= form->map; form->reginfo.join_tab=j; DBUG_PRINT("info",("type: %d", j->type)); if (j->type == JT_CONST) @@ -8872,9 +8869,6 @@ bool JOIN::get_best_combination() best_positions[tablenr].loosescan_picker.loosescan_key); j->index= best_positions[tablenr].loosescan_picker.loosescan_key; }*/ - - if (keyuse && create_ref_for_key(this, j, keyuse, TRUE, used_tables)) - DBUG_RETURN(TRUE); // Something went wrong if ((j->type == JT_REF || j->type == JT_EQ_REF) && is_hash_join_key_no(j->ref.key)) @@ -8900,6 +8894,23 @@ bool JOIN::get_best_combination() } root_range->end= j; + used_tables= OUTER_REF_TABLE_BIT; // Outer row is already read + for (j=join_tab, tablenr=0 ; tablenr < table_count ; tablenr++,j++) + { + if (j->bush_children) + j= j->bush_children->start; + + used_tables|= j->table->map; + if (j->type != JT_CONST && j->type != JT_SYSTEM) + { + if ((keyuse= best_positions[tablenr].key) && + create_ref_for_key(this, j, keyuse, TRUE, used_tables)) + DBUG_RETURN(TRUE); // Something went wrong + } + if (j->last_leaf_in_bush) + j= j->bush_root_tab; + } + top_join_tab_count= join_tab_ranges.head()->end - join_tab_ranges.head()->start; @@ -9752,7 +9763,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5 */ if (tab == join->join_tab + join->top_join_tab_count - 1) - current_map|= OUTER_REF_TABLE_BIT | RAND_TABLE_BIT; + current_map|= RAND_TABLE_BIT; used_tables|=current_map; if (tab->type == JT_REF && tab->quick && diff --git a/sql/sql_table.cc b/sql/sql_table.cc index f5f2ffb0b63..84e4018c3d8 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -3236,6 +3236,21 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, bool tmp_table= create_table_mode == C_ALTER_TABLE; DBUG_ENTER("mysql_prepare_create_table"); + LEX_STRING* connect_string = &create_info->connect_string; + if (connect_string->length != 0 && + connect_string->length > CONNECT_STRING_MAXLEN && + (system_charset_info->cset->charpos(system_charset_info, + connect_string->str, + (connect_string->str + + connect_string->length), + CONNECT_STRING_MAXLEN) + < connect_string->length)) + { + my_error(ER_WRONG_STRING_LENGTH, MYF(0), + connect_string->str, "CONNECTION", CONNECT_STRING_MAXLEN); + DBUG_RETURN(TRUE); + } + select_field_pos= alter_info->create_list.elements - select_field_count; null_fields=blob_columns=0; create_info->varchar= 0; diff --git a/storage/innobase/fil/fil0crypt.cc b/storage/innobase/fil/fil0crypt.cc index c7d740bc6af..fef05a7ebfc 100644 --- a/storage/innobase/fil/fil0crypt.cc +++ b/storage/innobase/fil/fil0crypt.cc @@ -1782,11 +1782,6 @@ fil_crypt_rotate_page( kv, key_state->key_version, key_state->rotate_key_age)) { - /* page can be "fresh" i.e never written in case - * kv == 0 or it should have a key version at least - * as big as the space minimum key version*/ - ut_a(kv == 0 || kv >= crypt_data->min_key_version); - modified = true; /* force rotation by dummy updating page */ @@ -1798,9 +1793,6 @@ fil_crypt_rotate_page( state->crypt_stat.pages_modified++; } else { if (crypt_data->is_encrypted()) { - ut_a(kv >= crypt_data->min_key_version || - (kv == 0 && key_state->key_version == 0)); - if (kv < state->min_key_version_found) { state->min_key_version_found = kv; } @@ -2075,6 +2067,11 @@ fil_crypt_complete_rotate_space( crypt_data->rotate_state.flushing = false; mutex_exit(&crypt_data->mutex); } + } else { + mutex_enter(&crypt_data->mutex); + ut_a(crypt_data->rotate_state.active_threads > 0); + crypt_data->rotate_state.active_threads--; + mutex_exit(&crypt_data->mutex); } } diff --git a/storage/maria/ma_pagecache.c b/storage/maria/ma_pagecache.c index afb5fb8bdff..1eebfac03f8 100644 --- a/storage/maria/ma_pagecache.c +++ b/storage/maria/ma_pagecache.c @@ -493,7 +493,8 @@ error: #define FLUSH_CACHE 2000 /* sort this many blocks at once */ -static void free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block); +static my_bool free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block, + my_bool abort_if_pinned); static void unlink_hash(PAGECACHE *pagecache, PAGECACHE_HASH_LINK *hash_link); #ifndef DBUG_OFF static void test_key_cache(PAGECACHE *pagecache, @@ -1945,7 +1946,7 @@ restart: removed from the cache as we set the PCBLOCK_REASSIGNED flag (see the code below that handles reading requests). */ - free_block(pagecache, block); + free_block(pagecache, block, 0); return 0; } /* Wait until the page is flushed on disk */ @@ -1956,7 +1957,7 @@ restart: /* Invalidate page in the block if it has not been done yet */ DBUG_ASSERT(block->status); /* Should always be true */ if (block->status) - free_block(pagecache, block); + free_block(pagecache, block, 0); return 0; } @@ -1981,8 +1982,13 @@ restart: } else { - DBUG_ASSERT(hash_link->requests > 0); - hash_link->requests--; + /* + When we come here either PCBLOCK_REASSIGNED or PCBLOCK_IN_SWITCH are + active. In both cases wqueue_release_queue() is called when the + state changes. + */ + DBUG_ASSERT(block->hash_link == hash_link); + remove_reader(block); KEYCACHE_DBUG_PRINT("find_block", ("request waiting for old page to be saved")); { @@ -3638,7 +3644,7 @@ static my_bool pagecache_delete_internal(PAGECACHE *pagecache, DBUG_ASSERT(block->hash_link->requests > 0); page_link->requests--; /* See NOTE for pagecache_unlock() about registering requests. */ - free_block(pagecache, block); + free_block(pagecache, block, 0); dec_counter_for_resize_op(pagecache); return 0; @@ -4219,7 +4225,8 @@ end: and add it to the free list. */ -static void free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block) +static my_bool free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block, + my_bool abort_if_pinned) { uint status= block->status; KEYCACHE_THREAD_TRACE("free block"); @@ -4233,11 +4240,27 @@ static void free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block) /* While waiting for readers to finish, new readers might request the block. But since we set block->status|= PCBLOCK_REASSIGNED, they - will wait on block->wqueue[COND_FOR_SAVED]. They must be signalled + will wait on block->wqueue[COND_FOR_SAVED]. They must be signaled later. */ block->status|= PCBLOCK_REASSIGNED; wait_for_readers(pagecache, block); + if (unlikely(abort_if_pinned) && unlikely(block->pins)) + { + /* + Block got pinned while waiting for readers. + This can only happens when called from flush_pagecache_blocks_int() + when flushing blocks as part of prepare for maria_close() or from + flush_cached_blocks() + */ + block->status&= ~PCBLOCK_REASSIGNED; + unreg_request(pagecache, block, 0); + + /* All pending requests for this page must be resubmitted. */ + if (block->wqueue[COND_FOR_SAVED].last_thread) + wqueue_release_queue(&block->wqueue[COND_FOR_SAVED]); + return 1; + } unlink_hash(pagecache, block->hash_link); } @@ -4288,6 +4311,8 @@ static void free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block) /* All pending requests for this page must be resubmitted. */ if (block->wqueue[COND_FOR_SAVED].last_thread) wqueue_release_queue(&block->wqueue[COND_FOR_SAVED]); + + return 0; } @@ -4423,9 +4448,16 @@ static int flush_cached_blocks(PAGECACHE *pagecache, if (! (type == FLUSH_KEEP || type == FLUSH_KEEP_LAZY || type == FLUSH_FORCE_WRITE)) { - pagecache->blocks_changed--; - pagecache->global_blocks_changed--; - free_block(pagecache, block); + if (!free_block(pagecache, block, 1)) + { + pagecache->blocks_changed--; + pagecache->global_blocks_changed--; + } + else + { + block->status&= ~PCBLOCK_IN_FLUSH; + link_to_file_list(pagecache, block, file, 1); + } } else { @@ -4663,7 +4695,7 @@ restart: /* It's a temporary file */ pagecache->blocks_changed--; pagecache->global_blocks_changed--; - free_block(pagecache, block); + free_block(pagecache, block, 0); } } else if (type != FLUSH_KEEP_LAZY) @@ -4733,11 +4765,12 @@ restart: #endif next= block->next_changed; if (block->hash_link->file.file == file->file && + !block->pins && (! (block->status & PCBLOCK_CHANGED) || type == FLUSH_IGNORE_CHANGED)) { reg_requests(pagecache, block, 1); - free_block(pagecache, block); + free_block(pagecache, block, 1); } } } diff --git a/storage/myisam/mi_delete_table.c b/storage/myisam/mi_delete_table.c index d766fb2547f..5ad63a5cc87 100644 --- a/storage/myisam/mi_delete_table.c +++ b/storage/myisam/mi_delete_table.c @@ -38,11 +38,9 @@ int mi_delete_table(const char *name) mysql_file_delete_with_symlink(mi_key_file_dfile, name, MI_NAME_DEXT, MYF(MY_WME))) DBUG_RETURN(my_errno); - // optionally present: mysql_file_delete_with_symlink(mi_key_file_dfile, name, ".OLD", MYF(0)); mysql_file_delete_with_symlink(mi_key_file_dfile, name, ".TMD", MYF(0)); DBUG_RETURN(0); } - diff --git a/storage/xtradb/fil/fil0crypt.cc b/storage/xtradb/fil/fil0crypt.cc index a7373f451bc..6a1b2f310fb 100644 --- a/storage/xtradb/fil/fil0crypt.cc +++ b/storage/xtradb/fil/fil0crypt.cc @@ -1976,11 +1976,6 @@ fil_crypt_rotate_page( kv, key_state->key_version, key_state->rotate_key_age)) { - /* page can be "fresh" i.e never written in case - * kv == 0 or it should have a key version at least - * as big as the space minimum key version*/ - ut_a(kv == 0 || kv >= crypt_data->min_key_version); - modified = true; /* force rotation by dummy updating page */ @@ -1992,9 +1987,6 @@ fil_crypt_rotate_page( state->crypt_stat.pages_modified++; } else { if (crypt_data->is_encrypted()) { - ut_a(kv >= crypt_data->min_key_version || - (kv == 0 && key_state->key_version == 0)); - if (kv < state->min_key_version_found) { state->min_key_version_found = kv; } @@ -2266,6 +2258,11 @@ fil_crypt_complete_rotate_space( crypt_data->rotate_state.flushing = false; mutex_exit(&crypt_data->mutex); } + } else { + mutex_enter(&crypt_data->mutex); + ut_a(crypt_data->rotate_state.active_threads > 0); + crypt_data->rotate_state.active_threads--; + mutex_exit(&crypt_data->mutex); } } @@ -2551,8 +2548,9 @@ fil_space_crypt_close_tablespace( if (now >= last + 30) { ib_logf(IB_LOG_LEVEL_WARN, - "Waited %ld seconds to drop space: %s(" ULINTPF ").", - now - start, space->name, space->id); + "Waited %ld seconds to drop space: %s (" ULINTPF + ") active threads %u flushing=%d.", + now - start, space->name, space->id, cnt, flushing); last = now; } } diff --git a/support-files/CMakeLists.txt b/support-files/CMakeLists.txt index c9addecafa5..71e9b3fad3d 100644 --- a/support-files/CMakeLists.txt +++ b/support-files/CMakeLists.txt @@ -1,5 +1,5 @@ -# Copyright (c) 2006, 2014, Oracle and/or its affiliates. -# Copyright (c) 2012, 2014, SkySQL Ab. +# Copyright (c) 2006, 2016, Oracle and/or its affiliates. +# Copyright (c) 2012, 2017, MariaDB # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by diff --git a/support-files/mysql.server.sh b/support-files/mysql.server.sh index 9162cf7d716..1ebf2922279 100644 --- a/support-files/mysql.server.sh +++ b/support-files/mysql.server.sh @@ -156,8 +156,7 @@ parse_server_arguments() { # Get arguments from the my.cnf file, # the only group, which is read from now on is [mysqld] -if test -x $bindir/my_print_defaults -then +if test -x "$bindir/my_print_defaults"; then print_defaults="$bindir/my_print_defaults" else # Try to find basedir in /etc/my.cnf @@ -175,11 +174,6 @@ else print_defaults="$d/bin/my_print_defaults" break fi - if test -x "$d/bin/mysql_print_defaults" - then - print_defaults="$d/bin/mysql_print_defaults" - break - fi done fi @@ -384,7 +378,7 @@ case "$mode" in fi else # Try to find appropriate mysqld process - mysqld_pid=`pidof $libexecdir/mysqld` + mysqld_pid=`pgrep $libexecdir/mysqld` # test if multiple pids exist pid_count=`echo $mysqld_pid | wc -w` diff --git a/tests/fork_big.pl b/tests/fork_big.pl index 6e78e779d11..ec1f131d453 100755 --- a/tests/fork_big.pl +++ b/tests/fork_big.pl @@ -1,4 +1,5 @@ #!/usr/bin/perl -w +use strict; # Copyright (c) 2001, 2006 MySQL AB # Use is subject to license terms @@ -21,7 +22,7 @@ # # Tested a lot with: --threads=30 -$opt_loop_count=500000; # Change this to make test harder/easier +my $opt_loop_count=500000; # Change this to make test harder/easier ##################### Standard benchmark inits ############################## @@ -31,6 +32,13 @@ use Benchmark; package main; +our ($opt_skip_create,$opt_skip_in,$opt_verbose,$opt_fast_insert); +our ($opt_lock_tables,$opt_debug,$opt_skip_delete,$opt_fast,$opt_force); +our ($opt_threads); +our ($opt_host,$opt_user,$opt_password,$opt_db); +my (@testtables, $abort_table, $numtables, $start_time, $end_time); +my ($dbh); + $opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert= $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0; $opt_threads=5; @@ -94,6 +102,8 @@ $|= 1; # Autoflush #### Start the tests #### +my ($i, $pid, %work); + for ($i=0 ; $i < $opt_threads ; $i ++) { test_insert() if (($pid=fork()) == 0); $work{$pid}="insert"; @@ -118,10 +128,13 @@ test_alter() if (($pid=fork()) == 0); $work{$pid}="alter"; print "Started " . ($opt_threads*2+4) . " threads\n"; +my ($errors, $running_insert_threads); + $errors=0; $running_insert_threads=$opt_threads+$numtables; while (($pid=wait()) != -1) { + my ($ret); $ret=$?/256; print "thread '" . $work{$pid} . "' finished with exit code $ret\n"; if ($work{$pid} =~ /^insert/) @@ -203,7 +216,7 @@ sub test_insert sub test_select { - my ($dbh, $i, $j, $count, $loop); + my ($dbh, $i, $j, $count, $loop, $count_query, $row_counts); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, @@ -270,7 +283,7 @@ sub test_select_count sub test_join { - my ($dbh, $i, $j, $count, $loop); + my ($dbh, $i, $j, $count, $loop, $count_query, $row_counts); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, @@ -389,7 +402,7 @@ sub test_update sub test_check { - my ($dbh, $row, $i, $j, $type, $table); + my ($dbh, $sth, $row, $i, $j, $type, $table); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; @@ -397,7 +410,7 @@ sub test_check $type= "check"; for ($i=$j=0 ; !test_if_abort($dbh) ; $i++) { - sleep(1000); + sleep(200); $table=$testtables[$j]->[0]; $sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n"; $sth->execute || die $DBI::errstr; @@ -426,7 +439,7 @@ sub test_check sub test_repair { - my ($dbh, $row, $i, $type, $table); + my ($dbh, $sth, $row, $i, $type, $table); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; @@ -434,7 +447,7 @@ sub test_repair $type= "repair"; for ($i=0 ; !test_if_abort($dbh) ; $i++) { - sleep(2000); + sleep(100); $table=$testtables[0]->[0]; $sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n"; $sth->execute || die $DBI::errstr; @@ -470,7 +483,7 @@ sub test_flush $count=0; while (!test_if_abort($dbh)) { - sleep(3000); + sleep(300); $dbh->do("flush tables $tables") || die "Got error on flush $DBI::errstr\n"; $count++; @@ -488,7 +501,7 @@ sub test_flush sub test_database { my ($database) = @_; - my ($dbh, $row, $i, $type, $tables); + my ($dbh, $sth, $row, $i, $type, $tables); $dbh = DBI->connect("DBI:mysql:$database:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; @@ -521,7 +534,7 @@ sub test_database sub test_alter { - my ($dbh, $row, $i, $type, $table); + my ($dbh, $sth, $row, $i, $type, $table); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; @@ -559,6 +572,7 @@ sub signal_abort sub test_if_abort() { my ($dbh)=@_; + my ($row); $row=simple_query($dbh,"select * from $opt_db.$abort_table"); return (defined($row) && defined($row->[0]) != 0) ? 1 : 0; } @@ -567,7 +581,7 @@ sub test_if_abort() sub make_count_query { my ($table_count)= @_; - my ($tables, $count_query, $i, $tables_def); + my ($tables, $count_query, $i, $table_def); $tables=""; $count_query="select high_priority "; $table_count--; |