diff options
| author | unknown <joerg@trift2.> | 2007-08-15 12:52:47 +0200 |
|---|---|---|
| committer | unknown <joerg@trift2.> | 2007-08-15 12:52:47 +0200 |
| commit | 582b682a622cee869849daada8e3e35609b47137 (patch) | |
| tree | 7cdf83a378da771ea2323797cb9a29b0d87a2f2c | |
| parent | 4cfe994ebcc9c9f1ff710694916a1d6b1e22af7f (diff) | |
| parent | 9d819b7d7f748b41e137b1eb94495156041a2593 (diff) | |
| download | mariadb-git-582b682a622cee869849daada8e3e35609b47137.tar.gz | |
Merge trift2.:/MySQL/M51/mysql-5.1
into trift2.:/MySQL/M51/push-5.1
62 files changed, 1313 insertions, 409 deletions
diff --git a/client/mysqldump.c b/client/mysqldump.c index c56c4423470..566760d2db3 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -1236,6 +1236,125 @@ static int switch_character_set_results(MYSQL *mysql, const char *cs_name) return mysql_real_query(mysql, query_buffer, query_length); } +/** + Rewrite CREATE TRIGGER statement, enclosing DEFINER clause in + version-specific comment. + + This function parses the CREATE TRIGGER statement and encloses + DEFINER-clause in version-specific comment: + input query: CREATE DEFINER=a@b TRIGGER ... + rewritten query: CREATE * / / *!50017 DEFINER=a@b * / / *!50003 TRIGGER ... + + @note This function will go away when WL#3995 is implemented. + + @param[in] trigger_def_str CREATE TRIGGER statement string. + @param[in] trigger_def_length length of the trigger_def_str. + + @return pointer to the new allocated query string. +*/ + +static char *cover_definer_clause_in_trigger(const char *trigger_def_str, + uint trigger_def_length) +{ + char *query_str= NULL; + char *definer_begin= my_case_str(trigger_def_str, trigger_def_length, + C_STRING_WITH_LEN(" DEFINER")); + char *definer_end; + + if (!definer_begin) + return NULL; + + definer_end= my_case_str(definer_begin, strlen(definer_begin), + C_STRING_WITH_LEN(" TRIGGER")); + + if (definer_end) + { + char *query_str_tail; + + /* + Allocate memory for new query string: original string + from SHOW statement and version-specific comments. + */ + query_str= alloc_query_str(trigger_def_length + 23); + + query_str_tail= strnmov(query_str, + trigger_def_str, + definer_begin - trigger_def_str); + + query_str_tail= strmov(query_str_tail, + "*/ /*!50017"); + + query_str_tail= strnmov(query_str_tail, + definer_begin, + definer_end - definer_begin); + + query_str_tail= strxmov(query_str_tail, + "*/ /*!50003", + definer_end, + NullS); + } + + return query_str; +} + +/** + Rewrite CREATE FUNCTION or CREATE PROCEDURE statement, enclosing DEFINER + clause in version-specific comment. + + This function parses the CREATE FUNCTION | PROCEDURE statement and + encloses DEFINER-clause in version-specific comment: + input query: CREATE DEFINER=a@b FUNCTION ... + rewritten query: CREATE * / / *!50020 DEFINER=a@b * / / *!50003 FUNCTION ... + + @note This function will go away when WL#3995 is implemented. + + @param[in] def_str CREATE FUNCTION|PROCEDURE statement string. + @param[in] def_length length of the def_str. + + @return pointer to the new allocated query string. +*/ + +static char *cover_definer_clause_in_sp(const char *def_str, + uint def_str_length) +{ + char *query_str= NULL; + char *definer_begin= my_case_str(def_str, def_str_length, + C_STRING_WITH_LEN(" DEFINER")); + char *definer_end; + + if (!definer_begin) + return NULL; + + definer_end= my_case_str(definer_begin, strlen(definer_begin), + C_STRING_WITH_LEN(" PROCEDURE")); + + if (!definer_end) + { + definer_end= my_case_str(definer_begin, strlen(definer_begin), + C_STRING_WITH_LEN(" FUNCTION")); + } + + if (definer_end) + { + char *query_str_tail; + + /* + Allocate memory for new query string: original string + from SHOW statement and version-specific comments. + */ + query_str= alloc_query_str(def_str_length + 23); + + query_str_tail= strnmov(query_str, def_str, definer_begin - def_str); + query_str_tail= strmov(query_str_tail, "*/ /*!50020"); + query_str_tail= strnmov(query_str_tail, definer_begin, + definer_end - definer_begin); + query_str_tail= strxmov(query_str_tail, "*/ /*!50003", + definer_end, NullS); + } + + return query_str; +} + /* Open a new .sql file to dump the table or view into @@ -1710,7 +1829,7 @@ static uint dump_events_for_db(char *db) MYSQL_ROW row, event_list_row; char db_cl_name[MY_CS_NAME_SIZE]; - int db_cl_altered; + int db_cl_altered= FALSE; DBUG_ENTER("dump_events_for_db"); DBUG_PRINT("enter", ("db: '%s'", db)); @@ -1775,16 +1894,36 @@ static uint dump_events_for_db(char *db) fprintf(sql_file, "DELIMITER %s\n", delimiter); - if (switch_db_collation(sql_file, db_name_buff, delimiter, db_cl_name, - row[6], &db_cl_altered)) + if (mysql_num_fields(event_res) >= 7) { - DBUG_RETURN(1); - } + if (switch_db_collation(sql_file, db_name_buff, delimiter, + db_cl_name, row[6], &db_cl_altered)) + { + DBUG_RETURN(1); + } - switch_cs_variables(sql_file, delimiter, - row[4], /* character_set_client */ - row[4], /* character_set_results */ - row[5]); /* collation_connection */ + switch_cs_variables(sql_file, delimiter, + row[4], /* character_set_client */ + row[4], /* character_set_results */ + row[5]); /* collation_connection */ + } + else + { + /* + mysqldump is being run against the server, that does not + provide character set information in SHOW CREATE + statements. + + NOTE: the dump may be incorrect, since character set + information is required in order to restore event properly. + */ + + fprintf(sql_file, + "--\n" + "-- WARNING: old server version. " + "The following dump may be incomplete.\n" + "--\n"); + } switch_sql_mode(sql_file, delimiter, row[1]); @@ -1797,13 +1936,17 @@ static uint dump_events_for_db(char *db) restore_time_zone(sql_file, delimiter); restore_sql_mode(sql_file, delimiter); - restore_cs_variables(sql_file, delimiter); - if (db_cl_altered) + if (mysql_num_fields(event_res) >= 7) { - if (restore_db_collation(sql_file, db_name_buff, delimiter, - db_cl_name)) - DBUG_RETURN(1); + restore_cs_variables(sql_file, delimiter); + + if (db_cl_altered) + { + if (restore_db_collation(sql_file, db_name_buff, delimiter, + db_cl_name)) + DBUG_RETURN(1); + } } } } /* end of event printing */ @@ -1871,7 +2014,7 @@ static uint dump_routines_for_db(char *db) MYSQL_ROW row, routine_list_row; char db_cl_name[MY_CS_NAME_SIZE]; - int db_cl_altered; + int db_cl_altered= FALSE; DBUG_ENTER("dump_routines_for_db"); DBUG_PRINT("enter", ("db: '%s'", db)); @@ -1938,74 +2081,45 @@ static uint dump_routines_for_db(char *db) } else if (strlen(row[2])) { - char *query_str= NULL; - char *definer_begin; - + char *query_str; if (opt_drop) fprintf(sql_file, "/*!50003 DROP %s IF EXISTS %s */;\n", routine_type[i], routine_name); - /* - Cover DEFINER-clause in version-specific comments. - - TODO: this is definitely a BAD IDEA to parse SHOW CREATE output. - However, we can not use INFORMATION_SCHEMA instead: - 1. INFORMATION_SCHEMA provides data in UTF8, but here we - need data in the original character set; - 2. INFORMATION_SCHEMA does not provide information about - routine parameters now. - */ - - definer_begin= my_case_str(row[2], strlen(row[2]), - C_STRING_WITH_LEN(" DEFINER")); + query_str= cover_definer_clause_in_sp(row[2], strlen(row[2])); - if (definer_begin) + if (mysql_num_fields(routine_res) >= 6) { - char *definer_end= my_case_str(definer_begin, - strlen(definer_begin), - C_STRING_WITH_LEN(" PROCEDURE")); - - if (!definer_end) + if (switch_db_collation(sql_file, db_name_buff, ";", + db_cl_name, row[5], &db_cl_altered)) { - definer_end= my_case_str(definer_begin, strlen(definer_begin), - C_STRING_WITH_LEN(" FUNCTION")); + DBUG_RETURN(1); } - if (definer_end) - { - char *query_str_tail; - - /* - Allocate memory for new query string: original string - from SHOW statement and version-specific comments. - */ - query_str= alloc_query_str(strlen(row[2]) + 23); - - query_str_tail= strnmov(query_str, row[2], - definer_begin - row[2]); - query_str_tail= strmov(query_str_tail, "*/ /*!50020"); - query_str_tail= strnmov(query_str_tail, definer_begin, - definer_end - definer_begin); - query_str_tail= strxmov(query_str_tail, "*/ /*!50003", - definer_end, NullS); - } + switch_cs_variables(sql_file, ";", + row[3], /* character_set_client */ + row[3], /* character_set_results */ + row[4]); /* collation_connection */ } - - /* - we need to change sql_mode only for the CREATE - PROCEDURE/FUNCTION otherwise we may need to re-quote routine_name - */ - - if (switch_db_collation(sql_file, db_name_buff, ";", - db_cl_name, row[5], &db_cl_altered)) + else { - DBUG_RETURN(1); + /* + mysqldump is being run against the server, that does not + provide character set information in SHOW CREATE + statements. + + NOTE: the dump may be incorrect, since character set + information is required in order to restore stored + procedure/function properly. + */ + + fprintf(sql_file, + "--\n" + "-- WARNING: old server version. " + "The following dump may be incomplete.\n" + "--\n"); } - switch_cs_variables(sql_file, ";", - row[3], /* character_set_client */ - row[3], /* character_set_results */ - row[4]); /* collation_connection */ switch_sql_mode(sql_file, ";", row[1]); @@ -2016,12 +2130,16 @@ static uint dump_routines_for_db(char *db) (const char *) (query_str != NULL ? query_str : row[2])); restore_sql_mode(sql_file, ";"); - restore_cs_variables(sql_file, ";"); - if (db_cl_altered) + if (mysql_num_fields(routine_res) >= 6) { - if (restore_db_collation(sql_file, db_name_buff, ";", db_cl_name)) - DBUG_RETURN(1); + restore_cs_variables(sql_file, ";"); + + if (db_cl_altered) + { + if (restore_db_collation(sql_file, db_name_buff, ";", db_cl_name)) + DBUG_RETURN(1); + } } my_free(query_str, MYF(MY_ALLOW_ZERO_PTR)); @@ -2560,153 +2678,211 @@ continue_xml: DBUG_RETURN((uint) num_fields); } /* get_table_structure */ +static void dump_trigger_old(MYSQL_RES *show_triggers_rs, + MYSQL_ROW *show_trigger_row, + const char *table_name) +{ + FILE *sql_file= md_result_file; -/* + char quoted_table_name_buf[NAME_LEN * 2 + 3]; + char *quoted_table_name= quote_name(table_name, quoted_table_name_buf, 1); + + char name_buff[NAME_LEN * 4 + 3]; + + DBUG_ENTER("dump_trigger_old"); - dump_triggers_for_table + fprintf(sql_file, + "--\n" + "-- WARNING: old server version. " + "The following dump may be incomplete.\n" + "--\n"); + + if (opt_compact) + fprintf(sql_file, "/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n"); - Dumps the triggers given a table/db name. This should be called after - the tables have been dumped in case a trigger depends on the existence - of a table + fprintf(sql_file, + "DELIMITER ;;\n" + "/*!50003 SET SESSION SQL_MODE=\"%s\" */;;\n" + "/*!50003 CREATE */ ", + (*show_trigger_row)[6]); + + if (mysql_num_fields(show_triggers_rs) > 7) + { + /* + mysqldump can be run against the server, that does not support + definer in triggers (there is no DEFINER column in SHOW TRIGGERS + output). So, we should check if we have this column before + accessing it. + */ + size_t user_name_len; + char user_name_str[USERNAME_LENGTH + 1]; + char quoted_user_name_str[USERNAME_LENGTH * 2 + 3]; + size_t host_name_len; + char host_name_str[HOSTNAME_LENGTH + 1]; + char quoted_host_name_str[HOSTNAME_LENGTH * 2 + 3]; + + parse_user((*show_trigger_row)[7], + strlen((*show_trigger_row)[7]), + user_name_str, &user_name_len, + host_name_str, &host_name_len); + + fprintf(sql_file, + "/*!50017 DEFINER=%s@%s */ ", + quote_name(user_name_str, quoted_user_name_str, FALSE), + quote_name(host_name_str, quoted_host_name_str, FALSE)); + } + + fprintf(sql_file, + "/*!50003 TRIGGER %s %s %s ON %s FOR EACH ROW%s%s */;;\n" + "DELIMITER ;\n", + quote_name((*show_trigger_row)[0], name_buff, 0), /* Trigger */ + (*show_trigger_row)[4], /* Timing */ + (*show_trigger_row)[1], /* Event */ + quoted_table_name, + (strchr(" \t\n\r", *((*show_trigger_row)[3]))) ? "" : " ", + (*show_trigger_row)[3] /* Statement */); + + if (opt_compact) + fprintf(sql_file, "/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;\n"); + + DBUG_VOID_RETURN; +} + +static int dump_trigger(MYSQL_RES *show_create_trigger_rs, + const char *db_name, + const char *db_cl_name) +{ + FILE *sql_file= md_result_file; + MYSQL_ROW row; + int db_cl_altered= FALSE; + + DBUG_ENTER("dump_trigger"); + + while ((row= mysql_fetch_row(show_create_trigger_rs))) + { + char *query_str= cover_definer_clause_in_trigger(row[2], strlen(row[2])); + + + if (switch_db_collation(sql_file, db_name, ";", + db_cl_name, row[5], &db_cl_altered)) + DBUG_RETURN(TRUE); + + switch_cs_variables(sql_file, ";", + row[3], /* character_set_client */ + row[3], /* character_set_results */ + row[4]); /* collation_connection */ + + switch_sql_mode(sql_file, ";", row[1]); + + fprintf(sql_file, + "DELIMITER ;;\n" + "/*!50003 %s */;;\n" + "DELIMITER ;\n", + (const char *) (query_str != NULL ? query_str : row[2])); + + restore_sql_mode(sql_file, ";"); + restore_cs_variables(sql_file, ";"); + + if (db_cl_altered) + { + if (restore_db_collation(sql_file, db_name, ";", db_cl_name)) + DBUG_RETURN(TRUE); + } + + my_free(query_str, MYF(MY_ALLOW_ZERO_PTR)); + } + + DBUG_RETURN(FALSE); +} + +/** + Dump the triggers for a given table. + + This should be called after the tables have been dumped in case a trigger + depends on the existence of a table. + + @param[in] table_name + @param[in] db_name + + @return Error status. + @retval TRUE error has occurred. + @retval FALSE operation succeed. */ -static void dump_triggers_for_table(char *table, char *db_name) +static int dump_triggers_for_table(char *table_name, char *db_name) { - char *result_table; - char name_buff[NAME_LEN*4+3], table_buff[NAME_LEN*2+3]; + char name_buff[NAME_LEN*4+3]; char query_buff[QUERY_LENGTH]; - uint old_opt_compatible_mode=opt_compatible_mode; - FILE *sql_file= md_result_file; - MYSQL_RES *result; + uint old_opt_compatible_mode= opt_compatible_mode; + MYSQL_RES *show_triggers_rs; MYSQL_ROW row; char db_cl_name[MY_CS_NAME_SIZE]; - int db_cl_altered; DBUG_ENTER("dump_triggers_for_table"); - DBUG_PRINT("enter", ("db: %s, table: %s", db_name, table)); + DBUG_PRINT("enter", ("db: %s, table_name: %s", db_name, table_name)); /* Do not use ANSI_QUOTES on triggers in dump */ opt_compatible_mode&= ~MASK_ANSI_QUOTES; - result_table= quote_name(table, table_buff, 1); - - my_snprintf(query_buff, sizeof(query_buff), - "SHOW TRIGGERS LIKE %s", - quote_for_like(table, name_buff)); - - if (mysql_query_with_error_report(mysql, &result, query_buff)) - { - if (path) - my_fclose(sql_file, MYF(MY_WME)); - DBUG_VOID_RETURN; - } /* Get database collation. */ + if (switch_character_set_results(mysql, "binary")) + DBUG_RETURN(TRUE); + if (fetch_db_collation(db_name, db_cl_name, sizeof (db_cl_name))) - DBUG_VOID_RETURN; + DBUG_RETURN(TRUE); - if (switch_character_set_results(mysql, "binary")) - DBUG_VOID_RETURN; + /* Get list of triggers. */ + + my_snprintf(query_buff, sizeof(query_buff), + "SHOW TRIGGERS LIKE %s", + quote_for_like(table_name, name_buff)); + + if (mysql_query_with_error_report(mysql, &show_triggers_rs, query_buff)) + DBUG_RETURN(TRUE); /* Dump triggers. */ - while ((row= mysql_fetch_row(result))) + while ((row= mysql_fetch_row(show_triggers_rs))) { - MYSQL_RES *res2; my_snprintf(query_buff, sizeof (query_buff), "SHOW CREATE TRIGGER %s", quote_name(row[0], name_buff, TRUE)); - if (mysql_query_with_error_report(mysql, &res2, query_buff)) + if (mysql_query(mysql, query_buff)) { - if (path) - my_fclose(sql_file, MYF(MY_WME)); - maybe_exit(EX_MYSQLERR); - DBUG_VOID_RETURN; - } - - while ((row= mysql_fetch_row(res2))) - { - char *query_str= NULL; - char *definer_begin; - /* - Cover DEFINER-clause in version-specific comments. - - TODO: this is definitely a BAD IDEA to parse SHOW CREATE output. - However, we can not use INFORMATION_SCHEMA instead: - 1. INFORMATION_SCHEMA provides data in UTF8, but here we - need data in the original character set; - 2. INFORMATION_SCHEMA does not provide information about - routine parameters now. - */ - - definer_begin= my_case_str(row[2], strlen(row[2]), - C_STRING_WITH_LEN(" DEFINER")); + mysqldump is being run against old server, that does not support + SHOW CREATE TRIGGER statement. We should use SHOW TRIGGERS output. - if (definer_begin) - { - char *definer_end= my_case_str(definer_begin, strlen(definer_begin), - C_STRING_WITH_LEN(" TRIGGER")); - - if (definer_end) - { - char *query_str_tail; - - /* - Allocate memory for new query string: original string - from SHOW statement and version-specific comments. - */ - query_str= alloc_query_str(strlen(row[2]) + 23); - - query_str_tail= strnmov(query_str, row[2], - definer_begin - row[2]); - query_str_tail= strmov(query_str_tail, "*/ /*!50017"); - query_str_tail= strnmov(query_str_tail, definer_begin, - definer_end - definer_begin); - query_str_tail= strxmov(query_str_tail, "*/ /*!50003", - definer_end, NullS); - } - } - - if (switch_db_collation(sql_file, db_name, ";", - db_cl_name, row[5], &db_cl_altered)) - DBUG_VOID_RETURN; - - switch_cs_variables(sql_file, ";", - row[3], /* character_set_client */ - row[3], /* character_set_results */ - row[4]); /* collation_connection */ - - switch_sql_mode(sql_file, ";", row[1]); - - fprintf(sql_file, - "DELIMITER ;;\n" - "/*!50003 %s */;;\n" - "DELIMITER ;\n", - (const char *) (query_str != NULL ? query_str : row[2])); + NOTE: the dump may be incorrect, as old SHOW TRIGGERS does not + provide all the necessary information to restore trigger properly. + */ - restore_sql_mode(sql_file, ";"); - restore_cs_variables(sql_file, ";"); + dump_trigger_old(show_triggers_rs, &row, table_name); + } + else + { + MYSQL_RES *show_create_trigger_rs= mysql_store_result(mysql); - if (db_cl_altered) + if (!show_create_trigger_rs || + dump_trigger(show_create_trigger_rs, db_name, db_cl_name)) { - if (restore_db_collation(sql_file, db_name, ";", db_cl_name)) - DBUG_VOID_RETURN; + DBUG_RETURN(TRUE); } - my_free(query_str, MYF(MY_ALLOW_ZERO_PTR)); + mysql_free_result(show_create_trigger_rs); } - mysql_free_result(res2); + } - mysql_free_result(result); + mysql_free_result(show_triggers_rs); if (switch_character_set_results(mysql, default_charset)) - DBUG_VOID_RETURN; + DBUG_RETURN(TRUE); /* make sure to set back opt_compatible mode to @@ -2714,7 +2890,7 @@ static void dump_triggers_for_table(char *table, char *db_name) */ opt_compatible_mode=old_opt_compatible_mode; - DBUG_VOID_RETURN; + DBUG_RETURN(FALSE); } static void add_load_option(DYNAMIC_STRING *str, const char *option, @@ -3737,7 +3913,7 @@ static int init_dumping(char *database, int init_func(char*)) /* Return 1 if we should copy the table */ -my_bool include_table(uchar* hash_key, uint len) +my_bool include_table(const char* hash_key, uint len) { return !hash_search(&ignore_table, (uchar*) hash_key, len); } @@ -3795,7 +3971,14 @@ static int dump_all_tables_in_db(char *database) order_by= 0; if (opt_dump_triggers && ! opt_xml && mysql_get_server_version(mysql) >= 50009) - dump_triggers_for_table(table, database); + { + if (dump_triggers_for_table(table, database)) + { + if (path) + my_fclose(md_result_file, MYF(MY_WME)); + maybe_exit(EX_MYSQLERR); + } + } } } if (opt_events && !opt_xml && @@ -4021,7 +4204,14 @@ static int dump_selected_tables(char *db, char **table_names, int tables) dump_table(*pos, db); if (opt_dump_triggers && mysql_get_server_version(mysql) >= 50009) - dump_triggers_for_table(*pos, db); + { + if (dump_triggers_for_table(*pos, db)) + { + if (path) + my_fclose(md_result_file, MYF(MY_WME)); + maybe_exit(EX_MYSQLERR); + } + } } /* Dump each selected view */ diff --git a/mysql-test/r/binlog_unsafe.result b/mysql-test/r/binlog_unsafe.result index 624c7feec1f..fb89631f30b 100644 --- a/mysql-test/r/binlog_unsafe.result +++ b/mysql-test/r/binlog_unsafe.result @@ -5,9 +5,9 @@ CREATE TABLE t3 (b INT AUTO_INCREMENT PRIMARY KEY); CREATE VIEW v1(a,b) AS SELECT a,b FROM t2,t3; INSERT INTO t1 SELECT UUID(); Warnings: -Warning 1589 Statement is not safe to log in statement format. +Warning 1590 Statement is not safe to log in statement format. SHOW WARNINGS; Level Warning -Code 1589 +Code 1590 Message Statement is not safe to log in statement format. DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 002dbc6ccb5..795d8956a08 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -209,16 +209,16 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT distinct t1.a from t1 order by a desc limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index explain SELECT distinct a from t3 order by a desc limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL a 5 NULL 204 Using index +1 SIMPLE t3 index NULL a 5 NULL 40 Using index explain SELECT distinct a,b from t3 order by a+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort -explain SELECT distinct a,b from t3 order by a limit 10; +explain SELECT distinct a,b from t3 order by a limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL a 5 NULL 204 Using temporary +1 SIMPLE t3 index NULL a 5 NULL 2 Using temporary explain SELECT a,b from t3 group by a,b order by a+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result index 6fb33dee826..9c8d12362c4 100644 --- a/mysql-test/r/endspace.result +++ b/mysql-test/r/endspace.result @@ -154,7 +154,7 @@ teststring teststring explain select * from t1 order by text1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL key1 34 NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort alter table t1 modify text1 char(32) binary not null; select * from t1 order by text1; text1 diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index 1bfa0e84f72..2557fcb3b28 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -31,7 +31,7 @@ create event e_55 on schedule at 10000101000000 do drop table t; ERROR HY000: Incorrect AT value: '10000101000000' create event e_55 on schedule at 20000101000000 do drop table t; Warnings: -Note 1585 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1586 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. show events; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; @@ -457,22 +457,22 @@ CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' DO SELECT 1; Warnings: -Note 1585 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1586 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' DISABLE DO SELECT 1; Warnings: -Note 1585 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1586 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO SELECT 1; Warnings: -Note 1585 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1586 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE DO SELECT 1; Warnings: -Note 1585 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1586 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci @@ -482,19 +482,19 @@ The following should succeed giving a warning. ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE; Warnings: -Note 1541 Event execution time is in the past. Event has been disabled +Note 1542 Event execution time is in the past. Event has been disabled CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DO SELECT 1; Warnings: -Note 1541 Event execution time is in the past. Event has been disabled +Note 1542 Event execution time is in the past. Event has been disabled CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00' ON COMPLETION PRESERVE DO SELECT 1; Warnings: -Note 1541 Event execution time is in the past. Event has been disabled +Note 1542 Event execution time is in the past. Event has been disabled The following should succeed without warnings. ALTER EVENT e2 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'; ALTER EVENT e3 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' diff --git a/mysql-test/r/events_trans.result b/mysql-test/r/events_trans.result index 1f87bcea68e..dce08d3b9f9 100644 --- a/mysql-test/r/events_trans.result +++ b/mysql-test/r/events_trans.result @@ -63,7 +63,7 @@ begin work; insert into t1 (a) values ("OK: create event if not exists"); create event if not exists e1 on schedule every 2 day do select 2; Warnings: -Note 1534 Event 'e1' already exists +Note 1535 Event 'e1' already exists rollback work; select * from t1; a diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index ca936cd5fde..8f1fe20ea3b 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -1920,6 +1920,20 @@ a b 2 Curly drop table federated.t1; drop table federated.t1; + +Bug#18287 create federated table always times out, error 1159 ' ' + +Test that self-references work + +create table federated.t1 (a int primary key); +create table federated.t2 (a int primary key) +ENGINE=FEDERATED +connection='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; +insert into federated.t1 (a) values (1); +select * from federated.t2; +a +1 +drop table federated.t1, federated.t2; CREATE TABLE federated.t1 (a INT PRIMARY KEY) DEFAULT CHARSET=utf8; CREATE TABLE federated.t1 (a INT PRIMARY KEY) ENGINE=FEDERATED diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index faa1bc1d661..13ddccaee92 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1161,7 +1161,7 @@ CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 5 NULL 4 +1 SIMPLE t2 index NULL a 5 NULL 2 EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index b6bf7260dc2..02b1459afd0 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -1963,20 +1963,20 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort explain select a1,a2,count(a2) from t1 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using index +1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 128 75.00 Using where; Using index +1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where (`test`.`t1`.`a1` > _latin1'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 128 75.00 Using where; Using index +1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index Warnings: Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > _latin1'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain select distinct(a1) from t1 where ord(a2) = 98; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index +1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index select distinct(a1) from t1 where ord(a2) = 98; a1 a @@ -2256,7 +2256,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index -2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 804c4b81c17..dce5e1a9a35 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -947,7 +947,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL # explain select * from t1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 4 NULL # +1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort explain select * from t1 order by c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 26000fa27b7..ae442acd1b9 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -879,13 +879,13 @@ EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; id 1 select_type SIMPLE table t1 -type range +type index possible_keys bkey -key bkey -key_len 5 +key PRIMARY +key_len 4 ref NULL -rows 16 -Extra Using where; Using index; Using filesort +rows 32 +Extra Using where SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; a b 1 2 @@ -974,13 +974,13 @@ EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a; id 1 select_type SIMPLE table t2 -type ref +type index possible_keys bkey -key bkey -key_len 5 -ref const -rows 8 -Extra Using where; Using index; Using filesort +key PRIMARY +key_len 4 +ref NULL +rows 16 +Extra Using where; Using index SELECT * FROM t2 WHERE b=1 ORDER BY a; a b c 1 1 1 diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result index e2000a9ec91..6152e403637 100644 --- a/mysql-test/r/lock.result +++ b/mysql-test/r/lock.result @@ -96,4 +96,74 @@ ERROR HY000: You can't combine write-locking of system tables with other tables LOCK TABLES mysql.time_zone READ, mysql.proc WRITE; ERROR HY000: You can't combine write-locking of system tables with other tables or lock types DROP TABLE t1; + +Bug#5719 impossible to lock VIEW + +Just covering existing behaviour with tests. +Consistency has not been found here. + +drop view if exists v_bug5719; +drop table if exists t1, t2, t3; +create table t1 (a int); +create temporary table t2 (a int); +create table t3 (a int); +create view v_bug5719 as select 1; +lock table v_bug5719 write; +select * from t1; +ERROR HY000: Table 't1' was not locked with LOCK TABLES + +Allowed to select from a temporary talbe under LOCK TABLES + +select * from t2; +a +select * from t3; +ERROR HY000: Table 't3' was not locked with LOCK TABLES +select * from v_bug5719; +1 +1 +drop view v_bug5719; + +sic: did not left LOCK TABLES mode automatically + +select * from t1; +ERROR HY000: Table 't1' was not locked with LOCK TABLES +unlock tables; +create view v_bug5719 as select * from t1; +lock tables v_bug5719 write; +select * from v_bug5719; +a + +Allowed to use an underlying table under LOCK TABLES <view> + +select * from t1; +a + +Allowed to select from a temporary table under LOCK TABLES + +select * from t2; +a +select * from t3; +ERROR HY000: Table 't3' was not locked with LOCK TABLES +drop table t1; + +sic: left LOCK TABLES mode + +select * from t3; +a +select * from v_bug5719; +ERROR HY000: View 'test.v_bug5719' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +unlock tables; +drop view v_bug5719; + +When limitation to use temporary tables in views is removed, please +add a test that shows what happens under LOCK TABLES when a view +references a temporary table, is locked, and the underlying table +is dropped. + +create view v_bug5719 as select * from t2; +ERROR HY000: View's SELECT refers to a temporary table 't2' + +Cleanup. + +drop table t2, t3; End of 5.1 tests. diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 2445b3e0c69..a3f7ab4505c 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -95,3 +95,13 @@ alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; // unlock tables; drop table t1; +create table t1 (i int); +lock table t1 read; +update t1 set i= 10;; +select * from t1;; +kill query ID; +i +ERROR 70100: Query execution was interrupted +unlock tables; +drop table t1; +End of 5.1 tests diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result index 0c6be16b9b7..3a3ef584ce3 100644 --- a/mysql-test/r/log_state.result +++ b/mysql-test/r/log_state.result @@ -37,14 +37,14 @@ set session long_query_time=1; select sleep(2); sleep(2) 0 -select * from mysql.slow_log; +select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text set global slow_query_log= ON; set session long_query_time=1; select sleep(2); sleep(2) 0 -select * from mysql.slow_log; +select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text TIMESTAMP USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 select sleep(2) show global variables diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index bb4fc654b38..5aa4288500c 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -86,7 +86,7 @@ a b 19 Testing explain select a from t3 order by a desc limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL a 4 NULL 1131 Using index +1 SIMPLE t3 index NULL a 4 NULL 10 Using index select a from t3 order by a desc limit 10; a 699 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 25fbeadf21b..ff4882d6cd8 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1073,3 +1073,61 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY,b b 5 const 1 1 SIMPLE t2 ref a a 5 const 2 Using where; Using index DROP TABLE t1,t2; +CREATE TABLE t1( +id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3)); +INSERT INTO t1 (c2,c3) VALUES +(31,34),(35,38),(34,31),(32,35),(31,39), +(11,14),(15,18),(14,11),(12,15),(11,19); +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +UPDATE t1 SET c2=20 WHERE id%100 = 0; +SELECT COUNT(*) FROM t1; +COUNT(*) +40960 +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 SELECT * FROM t1 ORDER BY id; +EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index k2 k3 5 NULL 111 Using where +EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref k2 k2 5 const 7341 Using where; Using filesort +EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index k2 k3 5 NULL 73 Using where +EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range k2 k2 5 NULL 386 Using where; Using filesort +SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; +id c3 +6 14 +16 14 +26 14 +36 14 +46 14 +56 14 +66 14 +76 14 +86 14 +96 14 +106 14 +116 14 +126 14 +136 14 +146 14 +156 14 +166 14 +176 14 +186 14 +196 14 +DROP TABLE t1,t2; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index a148d1029df..1ebed6d17d5 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3980,4 +3980,14 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE f1 index inx inx 10 NULL 7 Using where; Using index 1 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index DROP TABLE t1; +CREATE TABLE t1 (c1 INT, c2 INT); +INSERT INTO t1 VALUES (1,11), (2,22), (2,22); +EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2)))))))))))))))))))))))))))))))) > 0; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +31 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +32 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) > 0; +ERROR HY000: Too high level of nesting for select +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result index 7abd65beb46..7896f8a9f4e 100644 --- a/mysql-test/r/select_found.result +++ b/mysql-test/r/select_found.result @@ -84,7 +84,7 @@ UNIQUE KEY e_n (email,name) EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system PRIMARY,kid NULL NULL NULL 0 const row not found -1 SIMPLE t2 index NULL e_n 104 NULL 200 +1 SIMPLE t2 index NULL e_n 104 NULL 10 SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; email email1 diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index ebdab4a3f89..163bbb4aab4 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5670,7 +5670,7 @@ drop function if exists pi; create function pi() returns varchar(50) return "pie, my favorite desert."; Warnings: -Note 1582 This function 'pi' has the same name as a native function +Note 1583 This function 'pi' has the same name as a native function SET @save_sql_mode=@@sql_mode; SET SQL_MODE='IGNORE_SPACE'; select pi(), pi (); @@ -5719,15 +5719,15 @@ use test; create function `database`() returns varchar(50) return "Stored function database"; Warnings: -Note 1582 This function 'database' has the same name as a native function +Note 1583 This function 'database' has the same name as a native function create function `current_user`() returns varchar(50) return "Stored function current_user"; Warnings: -Note 1582 This function 'current_user' has the same name as a native function +Note 1583 This function 'current_user' has the same name as a native function create function md5(x varchar(50)) returns varchar(50) return "Stored function md5"; Warnings: -Note 1582 This function 'md5' has the same name as a native function +Note 1583 This function 'md5' has the same name as a native function SET SQL_MODE='IGNORE_SPACE'; select database(), database (); database() database () diff --git a/mysql-test/r/sp_gis.result b/mysql-test/r/sp_gis.result index b4fe0872d64..c640e5c46f2 100644 --- a/mysql-test/r/sp_gis.result +++ b/mysql-test/r/sp_gis.result @@ -7,11 +7,11 @@ return 1; create function x() returns int return 2; Warnings: -Note 1582 This function 'x' has the same name as a native function +Note 1583 This function 'x' has the same name as a native function create function y() returns int return 3; Warnings: -Note 1582 This function 'y' has the same name as a native function +Note 1583 This function 'y' has the same name as a native function select a(); a() 1 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 40b9e489577..a25183a0e6d 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3419,7 +3419,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 9 Using filesort +2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 Using filesort DROP TABLE t1; create table t1( f1 int,f2 int); insert into t1 values (1,1),(2,2); diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result index 8d7843cc0b1..e6641cab7f6 100644 --- a/mysql-test/r/type_bit.result +++ b/mysql-test/r/type_bit.result @@ -618,6 +618,30 @@ bit_field int_field 2 handler t1 close; drop table t1; +CREATE TABLE t1 (b BIT(2), a VARCHAR(5)); +INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z"); +SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; +b+0 COUNT(DISTINCT a) +0 1 +1 1 +3 2 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), b BIT(2)); +INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z"); +SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; +b+0 COUNT(DISTINCT a) +0 1 +1 1 +3 2 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b BIT(2)); +INSERT INTO t1 (b, a) VALUES (1, 1), (3, 2), (0, 3), (3, 4); +SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; +b+0 COUNT(DISTINCT a) +0 1 +1 1 +3 2 +DROP TABLE t1; End of 5.0 tests create table t1(a bit(7)); insert into t1 values(0x40); diff --git a/mysql-test/r/xml.result b/mysql-test/r/xml.result index 95ad19a886f..aac7c4e850a 100644 --- a/mysql-test/r/xml.result +++ b/mysql-test/r/xml.result @@ -647,32 +647,32 @@ select extractValue('<a>a','/a'); extractValue('<a>a','/a') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 5: unexpected END-OF-INPUT' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 5: unexpected END-OF-INPUT' select extractValue('<a>a<','/a'); extractValue('<a>a<','/a') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 6: END-OF-INPUT unexpected (ident or '/' wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 6: END-OF-INPUT unexpected (ident or '/' wanted)' select extractValue('<a>a</','/a'); extractValue('<a>a</','/a') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 7: END-OF-INPUT unexpected (ident wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 7: END-OF-INPUT unexpected (ident wanted)' select extractValue('<a>a</a','/a'); extractValue('<a>a</a','/a') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 8: END-OF-INPUT unexpected ('>' wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 8: END-OF-INPUT unexpected ('>' wanted)' select extractValue('<a>a</a></b>','/a'); extractValue('<a>a</a></b>','/a') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 12: '</b>' unexpected (END-OF-INPUT wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 12: '</b>' unexpected (END-OF-INPUT wanted)' select extractValue('<a b=>a</a>','/a'); extractValue('<a b=>a</a>','/a') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 7: '>' unexpected (ident or string wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 7: '>' unexpected (ident or string wanted)' select extractValue('<e>1</e>','position()'); ERROR HY000: XPATH syntax error: '' select extractValue('<e>1</e>','last()'); @@ -723,17 +723,17 @@ select extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//* extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//*') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 13: unknown token unexpected (ident or '/' wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 13: unknown token unexpected (ident or '/' wanted)' select extractValue('<.>test</.>','//*'); extractValue('<.>test</.>','//*') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' select extractValue('<->test</->','//*'); extractValue('<->test</->','//*') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' select extractValue('<:>test</:>','//*'); extractValue('<:>test</:>','//*') test diff --git a/mysql-test/suite/ndb/r/ndb_dd_basic.result b/mysql-test/suite/ndb/r/ndb_dd_basic.result index bc1762ce407..b23d178acde 100644 --- a/mysql-test/suite/ndb/r/ndb_dd_basic.result +++ b/mysql-test/suite/ndb/r/ndb_dd_basic.result @@ -5,20 +5,20 @@ INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=MYISAM; Warnings: -Error 1475 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' +Error 1476 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' ALTER LOGFILE GROUP lg1 ADD UNDOFILE 'undofile02.dat' INITIAL_SIZE = 4M ENGINE=XYZ; Warnings: Warning 1286 Unknown table engine 'XYZ' -Error 1475 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' +Error 1476 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' CREATE TABLESPACE ts1 ADD DATAFILE 'datafile.dat' USE LOGFILE GROUP lg1 INITIAL_SIZE 12M; Warnings: -Error 1475 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' +Error 1476 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' set storage_engine=ndb; CREATE LOGFILE GROUP lg1 ADD UNDOFILE 'undofile.dat' diff --git a/mysql-test/suite/ndb/r/ndb_dd_ddl.result b/mysql-test/suite/ndb/r/ndb_dd_ddl.result index 55f1f56fd0c..33536038b1b 100644 --- a/mysql-test/suite/ndb/r/ndb_dd_ddl.result +++ b/mysql-test/suite/ndb/r/ndb_dd_ddl.result @@ -16,7 +16,7 @@ ERROR HY000: Failed to create LOGFILE GROUP SHOW WARNINGS; Level Code Message Error 1296 Got error 1514 'Currently there is a limit of one logfile group' from NDB -Error 1525 Failed to create LOGFILE GROUP +Error 1526 Failed to create LOGFILE GROUP CREATE LOGFILE GROUP lg1 ADD UNDOFILE 'undofile.dat' INITIAL_SIZE 1M diff --git a/mysql-test/suite/ndb/r/ndb_gis.result b/mysql-test/suite/ndb/r/ndb_gis.result index 54f1fc23489..1d593f471d1 100644 --- a/mysql-test/suite/ndb/r/ndb_gis.result +++ b/mysql-test/suite/ndb/r/ndb_gis.result @@ -463,7 +463,7 @@ drop table t1; End of 4.1 tests CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY); Warnings: -Error 1475 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' +Error 1476 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); @@ -1013,7 +1013,7 @@ drop table t1; End of 4.1 tests CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY); Warnings: -Error 1475 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' +Error 1476 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); diff --git a/mysql-test/suite/ndb/r/ndb_row_format.result b/mysql-test/suite/ndb/r/ndb_row_format.result index 37c239986f6..44880e28c83 100644 --- a/mysql-test/suite/ndb/r/ndb_row_format.result +++ b/mysql-test/suite/ndb/r/ndb_row_format.result @@ -8,7 +8,7 @@ ENGINE=NDB; ERROR HY000: Can't create table 'test.t1' (errno: 138) SHOW WARNINGS; Level Code Message -Error 1475 Table storage engine 'ndbcluster' does not support the create option 'Row format FIXED incompatible with variable sized attribute' +Error 1476 Table storage engine 'ndbcluster' does not support the create option 'Row format FIXED incompatible with variable sized attribute' Error 1005 Can't create table 'test.t1' (errno: 138) CREATE TABLE t1 ( a INT KEY, diff --git a/mysql-test/suite/ndb/r/ndb_single_user.result b/mysql-test/suite/ndb/r/ndb_single_user.result index f916422a509..11e8f098416 100644 --- a/mysql-test/suite/ndb/r/ndb_single_user.result +++ b/mysql-test/suite/ndb/r/ndb_single_user.result @@ -11,7 +11,7 @@ ERROR HY000: Failed to create LOGFILE GROUP show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1525 Failed to create LOGFILE GROUP +Error 1526 Failed to create LOGFILE GROUP create table t1 (a int key, b int unique, c int) engine ndb; CREATE LOGFILE GROUP lg1 ADD UNDOFILE 'undofile.dat' @@ -27,14 +27,14 @@ ERROR HY000: Failed to create TABLESPACE show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1525 Failed to create TABLESPACE +Error 1526 Failed to create TABLESPACE DROP LOGFILE GROUP lg1 ENGINE =NDB; ERROR HY000: Failed to drop LOGFILE GROUP show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1526 Failed to drop LOGFILE GROUP +Error 1527 Failed to drop LOGFILE GROUP CREATE TABLESPACE ts1 ADD DATAFILE 'datafile.dat' USE LOGFILE GROUP lg1 @@ -47,7 +47,7 @@ ERROR HY000: Failed to alter: DROP DATAFILE show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1530 Failed to alter: DROP DATAFILE +Error 1531 Failed to alter: DROP DATAFILE ALTER TABLESPACE ts1 DROP DATAFILE 'datafile.dat' ENGINE NDB; @@ -57,7 +57,7 @@ ERROR HY000: Failed to drop TABLESPACE show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1526 Failed to drop TABLESPACE +Error 1527 Failed to drop TABLESPACE DROP TABLESPACE ts1 ENGINE NDB; DROP LOGFILE GROUP lg1 diff --git a/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result b/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result index c3159987b30..b14c8c2a725 100644 --- a/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result @@ -72,7 +72,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -90,7 +90,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -134,7 +134,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -152,7 +152,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -196,7 +196,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 Skip_Counter 0 Exec_Master_Log_Pos # @@ -214,7 +214,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -257,7 +257,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -275,7 +275,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; *** Drop t6 *** @@ -369,7 +369,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 Skip_Counter 0 Exec_Master_Log_Pos # @@ -387,7 +387,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -430,7 +430,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 Skip_Counter 0 Exec_Master_Log_Pos # @@ -448,7 +448,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -755,7 +755,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 Skip_Counter 0 Exec_Master_Log_Pos # @@ -773,7 +773,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result index 9d6c3ed5d33..c13bd79c621 100644 --- a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result @@ -72,7 +72,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -90,7 +90,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -134,7 +134,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -152,7 +152,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -196,7 +196,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 Skip_Counter 0 Exec_Master_Log_Pos # @@ -214,7 +214,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -257,7 +257,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -275,7 +275,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; *** Drop t6 *** @@ -369,7 +369,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 Skip_Counter 0 Exec_Master_Log_Pos # @@ -387,7 +387,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -430,7 +430,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 Skip_Counter 0 Exec_Master_Log_Pos # @@ -448,7 +448,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -755,7 +755,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 Skip_Counter 0 Exec_Master_Log_Pos # @@ -773,7 +773,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_incident.result b/mysql-test/suite/rpl/r/rpl_incident.result index 84fd9c0ee4f..c3cec758d89 100644 --- a/mysql-test/suite/rpl/r/rpl_incident.result +++ b/mysql-test/suite/rpl/r/rpl_incident.result @@ -44,7 +44,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1587 +Last_Errno 1588 Last_Error The incident LOST_EVENTS occured on the master. Message: <none> Skip_Counter 0 Exec_Master_Log_Pos # @@ -62,7 +62,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1587 +Last_SQL_Errno 1588 Last_SQL_Error The incident LOST_EVENTS occured on the master. Message: <none> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result b/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result index 6c73c275ff0..0216ef65570 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result +++ b/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result @@ -65,7 +65,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1590 +Last_Errno 1591 Last_Error Fatal error: Not enough memory Skip_Counter 0 Exec_Master_Log_Pos 325 @@ -83,7 +83,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1590 +Last_SQL_Errno 1591 Last_SQL_Error Fatal error: Not enough memory SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result index 816fba54bd8..07ac657584c 100644 --- a/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result @@ -214,7 +214,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -232,7 +232,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -257,7 +257,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -275,7 +275,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -300,7 +300,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -318,7 +318,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result b/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result index 0c5d57c1d00..a17d495a116 100644 --- a/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result @@ -214,7 +214,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -232,7 +232,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -257,7 +257,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -275,7 +275,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -300,7 +300,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -318,7 +318,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_timezone.result b/mysql-test/suite/rpl/r/rpl_timezone.result index 47ef16b9d49..cd71dbe628e 100644 --- a/mysql-test/suite/rpl/r/rpl_timezone.result +++ b/mysql-test/suite/rpl/r/rpl_timezone.result @@ -105,3 +105,21 @@ t n 2005-01-01 08:00:00 17 drop table t1, t2; set global time_zone= @my_time_zone; +End of 4.1 tests +CREATE TABLE t1 (a INT, b TIMESTAMP); +INSERT INTO t1 VALUES (1, NOW()); +SET @@session.time_zone='Japan'; +UPDATE t1 SET b= '1970-01-01 08:59:59' WHERE a= 1; +Warnings: +Warning 1264 Out of range value for column 'b' at row 1 +SELECT * FROM t1 ORDER BY a; +a b +1 0000-00-00 00:00:00 +SET @@session.time_zone='Japan'; +SELECT * FROM t1 ORDER BY a; +a b +1 0000-00-00 00:00:00 +SET @@session.time_zone = default; +DROP TABLE t1; +SET @@session.time_zone = default; +End of 5.0 tests diff --git a/mysql-test/suite/rpl/r/rpl_udf.result b/mysql-test/suite/rpl/r/rpl_udf.result index d21cfd2539b..1fa9cb3ffc2 100644 --- a/mysql-test/suite/rpl/r/rpl_udf.result +++ b/mysql-test/suite/rpl/r/rpl_udf.result @@ -182,19 +182,19 @@ CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=MyISAM; affected rows: 0 INSERT INTO t1 VALUES(myfunc_int(100), myfunc_double(50.00)); Warnings: -Warning 1589 Statement is not safe to log in statement format. +Warning 1590 Statement is not safe to log in statement format. affected rows: 1 INSERT INTO t1 VALUES(myfunc_int(10), myfunc_double(5.00)); Warnings: -Warning 1589 Statement is not safe to log in statement format. +Warning 1590 Statement is not safe to log in statement format. affected rows: 1 INSERT INTO t1 VALUES(myfunc_int(200), myfunc_double(25.00)); Warnings: -Warning 1589 Statement is not safe to log in statement format. +Warning 1590 Statement is not safe to log in statement format. affected rows: 1 INSERT INTO t1 VALUES(myfunc_int(1), myfunc_double(500.00)); Warnings: -Warning 1589 Statement is not safe to log in statement format. +Warning 1590 Statement is not safe to log in statement format. affected rows: 1 SELECT * FROM t1 ORDER BY sum; sum price diff --git a/mysql-test/suite/rpl/t/rpl_relayspace.test b/mysql-test/suite/rpl/t/rpl_relayspace.test index 70315c14f34..d4ef2fe59bd 100644 --- a/mysql-test/suite/rpl/t/rpl_relayspace.test +++ b/mysql-test/suite/rpl/t/rpl_relayspace.test @@ -14,6 +14,22 @@ connection slave; reset slave; start slave io_thread; # Give the I/O thread time to block. +let $run= 1; +let $counter= 300; +while ($run) +{ + let $io_state= query_get_value("SHOW SLAVE STATUS", Slave_IO_State, 1); + if (`SELECT '$io_state' = 'Waiting for the slave SQL thread to free enough relay log space'`){ + let $run= 0; + } + sleep 0.1; + if (!$counter){ + --echo "Failed while waiting for slave IO thread block" + SHOW SLAVE STATUS; + exit; + } + dec $counter; +} sleep 2; # A bug caused the I/O thread to refuse stopping. stop slave io_thread; diff --git a/mysql-test/suite/rpl/t/rpl_timezone.test b/mysql-test/suite/rpl/t/rpl_timezone.test index 4b8c8152c82..dac21000a62 100644 --- a/mysql-test/suite/rpl/t/rpl_timezone.test +++ b/mysql-test/suite/rpl/t/rpl_timezone.test @@ -140,3 +140,30 @@ sync_slave_with_master; # Restore original timezone connection master; set global time_zone= @my_time_zone; + +--echo End of 4.1 tests + +# +# Bug #29536: timestamp inconsistent in replication around 1970 +# +connection master; + +CREATE TABLE t1 (a INT, b TIMESTAMP); +INSERT INTO t1 VALUES (1, NOW()); + +SET @@session.time_zone='Japan'; +UPDATE t1 SET b= '1970-01-01 08:59:59' WHERE a= 1; +SELECT * FROM t1 ORDER BY a; + +sync_slave_with_master; +SET @@session.time_zone='Japan'; +# must procdure the same result as the SELECT on the master +SELECT * FROM t1 ORDER BY a; + +SET @@session.time_zone = default; +connection master; +DROP TABLE t1; +SET @@session.time_zone = default; + + +--echo End of 5.0 tests diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result index edb4dfbf392..0d17720678d 100644 --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result @@ -72,7 +72,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -90,7 +90,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -134,7 +134,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -152,7 +152,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -196,7 +196,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 Skip_Counter 0 Exec_Master_Log_Pos # @@ -214,7 +214,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -257,7 +257,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -275,7 +275,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; *** Drop t6 *** @@ -369,7 +369,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 Skip_Counter 0 Exec_Master_Log_Pos # @@ -387,7 +387,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -430,7 +430,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 Skip_Counter 0 Exec_Master_Log_Pos # @@ -448,7 +448,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -756,7 +756,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1532 +Last_Errno 1533 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 Skip_Counter 0 Exec_Master_Log_Pos # @@ -774,7 +774,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1532 +Last_SQL_Errno 1533 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 7310f98cd16..bfdb5f8b9f8 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -97,7 +97,7 @@ explain SELECT t1.a from t1 group by a order by a desc; explain SELECT distinct t1.a from t1 order by a desc limit 1; explain SELECT distinct a from t3 order by a desc limit 2; explain SELECT distinct a,b from t3 order by a+1; -explain SELECT distinct a,b from t3 order by a limit 10; +explain SELECT distinct a,b from t3 order by a limit 2; explain SELECT a,b from t3 group by a,b order by a+1; drop table t1,t2,t3,t4; diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index 6597c77e798..90f1fa36bb4 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1725,6 +1725,20 @@ drop table federated.t1; connection slave; drop table federated.t1; +--echo +--echo Bug#18287 create federated table always times out, error 1159 ' ' +--echo +--echo Test that self-references work +--echo +connection slave; +create table federated.t1 (a int primary key); +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval create table federated.t2 (a int primary key) + ENGINE=FEDERATED + connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; +insert into federated.t1 (a) values (1); +select * from federated.t2; +drop table federated.t1, federated.t2; # # BUG#29875 Disable support for transactions diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test index 2b8b430f096..6069bbf7018 100644 --- a/mysql-test/t/lock.test +++ b/mysql-test/t/lock.test @@ -148,5 +148,70 @@ LOCK TABLES mysql.time_zone READ, mysql.proc WRITE; DROP TABLE t1; +--echo +--echo Bug#5719 impossible to lock VIEW +--echo +--echo Just covering existing behaviour with tests. +--echo Consistency has not been found here. +--echo +--disable_warnings +drop view if exists v_bug5719; +drop table if exists t1, t2, t3; +--enable_warnings +create table t1 (a int); +create temporary table t2 (a int); +create table t3 (a int); +create view v_bug5719 as select 1; +lock table v_bug5719 write; +--error ER_TABLE_NOT_LOCKED +select * from t1; +--echo +--echo Allowed to select from a temporary talbe under LOCK TABLES +--echo +select * from t2; +--error ER_TABLE_NOT_LOCKED +select * from t3; +select * from v_bug5719; +drop view v_bug5719; +--echo +--echo sic: did not left LOCK TABLES mode automatically +--echo +--error ER_TABLE_NOT_LOCKED +select * from t1; +unlock tables; +create view v_bug5719 as select * from t1; +lock tables v_bug5719 write; +select * from v_bug5719; +--echo +--echo Allowed to use an underlying table under LOCK TABLES <view> +--echo +select * from t1; +--echo +--echo Allowed to select from a temporary table under LOCK TABLES +--echo +select * from t2; +--error ER_TABLE_NOT_LOCKED +select * from t3; +drop table t1; +--echo +--echo sic: left LOCK TABLES mode +--echo +select * from t3; +--error ER_VIEW_INVALID +select * from v_bug5719; +unlock tables; +drop view v_bug5719; +--echo +--echo When limitation to use temporary tables in views is removed, please +--echo add a test that shows what happens under LOCK TABLES when a view +--echo references a temporary table, is locked, and the underlying table +--echo is dropped. +--echo +--error ER_VIEW_SELECT_TMPTABLE +create view v_bug5719 as select * from t2; +--echo +--echo Cleanup. +--echo +drop table t2, t3; --echo End of 5.1 tests. diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 4a6b4ff5e56..b7c406f9637 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -270,3 +270,38 @@ drop table t1; # End of 5.0 tests + +# +# Bug #21281 "Pending write lock is incorrectly removed when its +# statement being KILLed" +# +create table t1 (i int); +connection locker; +lock table t1 read; +connection writer; +--send update t1 set i= 10; +connection reader; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Locked" and info = "update t1 set i= 10"; +--source include/wait_condition.inc +--send select * from t1; +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Locked" and info = "select * from t1"; +--source include/wait_condition.inc +let $ID= `select id from information_schema.processlist where state = "Locked" and info = "update t1 set i= 10"`; +--replace_result $ID ID +eval kill query $ID; +connection reader; +--reap +connection writer; +--error ER_QUERY_INTERRUPTED +--reap +connection locker; +unlock tables; +connection default; +drop table t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/log_state.test b/mysql-test/t/log_state.test index b0bb818b783..c67da261ef1 100644 --- a/mysql-test/t/log_state.test +++ b/mysql-test/t/log_state.test @@ -28,7 +28,7 @@ connection con1; set session long_query_time=1; select sleep(2); --replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME -select * from mysql.slow_log; +select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; connection default; set global slow_query_log= ON; @@ -36,7 +36,7 @@ connection con1; set session long_query_time=1; select sleep(2); --replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME -select * from mysql.slow_log; +select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; connection default; show global variables where Variable_name = 'log' or Variable_name = 'log_slow_queries' or diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 1e520da9f00..37398616299 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -739,3 +739,43 @@ INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2); EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b; DROP TABLE t1,t2; + +# End of 5.0 + +# +# Bug #28404: query with ORDER BY and ref access +# + +CREATE TABLE t1( + id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3)); + +INSERT INTO t1 (c2,c3) VALUES + (31,34),(35,38),(34,31),(32,35),(31,39), + (11,14),(15,18),(14,11),(12,15),(11,19); + +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +UPDATE t1 SET c2=20 WHERE id%100 = 0; +SELECT COUNT(*) FROM t1; + +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 SELECT * FROM t1 ORDER BY id; + +EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; +EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; +EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; +EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; + +SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 1f8a00409e6..b513d17bfa0 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3359,4 +3359,34 @@ EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2 WHERE 1 AND f1.b NOT IN (100,2232,3343,51111); DROP TABLE t1; +# +# Bug #27352: Incorrect result of nested selects instead of error reporting +# + +CREATE TABLE t1 (c1 INT, c2 INT); +INSERT INTO t1 VALUES (1,11), (2,22), (2,22); + +let $n= 31; +let $q= COUNT(c2); +while ($n) +{ + let $q= (SELECT $q); + dec $n; +} +--disable_warnings +eval EXPLAIN SELECT c1 FROM t1 WHERE $q > 0; +--enable_warnings + +let $n= 64; +let $q= COUNT(c2); +while ($n) +{ + let $q= (SELECT $q); + dec $n; +} +--error ER_TOO_HIGH_LEVEL_OF_NESTING_FOR_SELECT +eval EXPLAIN SELECT c1 FROM t1 WHERE $q > 0; + +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/type_bit.test b/mysql-test/t/type_bit.test index ae725b3b235..f51b2c1d612 100644 --- a/mysql-test/t/type_bit.test +++ b/mysql-test/t/type_bit.test @@ -272,6 +272,25 @@ handler t1 read a=(1); handler t1 close; drop table t1; +# +# Bug #30219: GROUP BY a column of the BIT type +# + +CREATE TABLE t1 (b BIT(2), a VARCHAR(5)); +INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z"); +SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), b BIT(2)); +INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z"); +SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; +DROP TABLE t1; + +CREATE TABLE t1 (a INT, b BIT(2)); +INSERT INTO t1 (b, a) VALUES (1, 1), (3, 2), (0, 3), (3, 4); +SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; +DROP TABLE t1; + --echo End of 5.0 tests # diff --git a/mysys/thr_lock.c b/mysys/thr_lock.c index 20edffb49c2..a81ed925562 100644 --- a/mysys/thr_lock.c +++ b/mysys/thr_lock.c @@ -384,6 +384,9 @@ static inline my_bool have_specific_lock(THR_LOCK_DATA *data, } +static void wake_up_waiters(THR_LOCK *lock); + + static enum enum_thr_lock_result wait_for_lock(struct st_lock_list *wait, THR_LOCK_DATA *data, my_bool in_wait_list) @@ -445,8 +448,13 @@ wait_for_lock(struct st_lock_list *wait, THR_LOCK_DATA *data, else wait->last=data->prev; data->type= TL_UNLOCK; /* No lock */ + check_locks(data->lock, "killed or timed out wait_for_lock", 1); + wake_up_waiters(data->lock); + } + else + { + check_locks(data->lock, "aborted wait_for_lock", 0); } - check_locks(data->lock,"failed wait_for_lock",0); } else { @@ -776,6 +784,26 @@ void thr_unlock(THR_LOCK_DATA *data) lock->read_no_write_count--; data->type=TL_UNLOCK; /* Mark unlocked */ check_locks(lock,"after releasing lock",1); + wake_up_waiters(lock); + pthread_mutex_unlock(&lock->mutex); + DBUG_VOID_RETURN; +} + + +/** + @brief Wake up all threads which pending requests for the lock + can be satisfied. + + @param lock Lock for which threads should be woken up + +*/ + +static void wake_up_waiters(THR_LOCK *lock) +{ + THR_LOCK_DATA *data; + enum thr_lock_type lock_type; + + DBUG_ENTER("wake_up_waiters"); if (!lock->write.data) /* If no active write locks */ { @@ -827,11 +855,7 @@ void thr_unlock(THR_LOCK_DATA *data) data=lock->write_wait.data; /* Free this too */ } if (data->type >= TL_WRITE_LOW_PRIORITY) - { - check_locks(lock,"giving write lock",0); - pthread_mutex_unlock(&lock->mutex); - DBUG_VOID_RETURN; - } + goto end; /* Release possible read locks together with the write lock */ } if (lock->read_wait.data) @@ -886,8 +910,7 @@ void thr_unlock(THR_LOCK_DATA *data) free_all_read_locks(lock,0); } end: - check_locks(lock,"thr_unlock",0); - pthread_mutex_unlock(&lock->mutex); + check_locks(lock, "after waking up waiters", 0); DBUG_VOID_RETURN; } @@ -1101,6 +1124,7 @@ my_bool thr_abort_locks_for_thread(THR_LOCK *lock, my_thread_id thread_id) lock->write_wait.last= data->prev; } } + wake_up_waiters(lock); pthread_mutex_unlock(&lock->mutex); DBUG_RETURN(found); } diff --git a/sql/field.cc b/sql/field.cc index acaf1576a8f..4af5dd5ebd6 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -4655,6 +4655,7 @@ longlong Field_timestamp::val_int(void) MYSQL_TIME time_tmp; THD *thd= table ? table->in_use : current_thd; + thd->time_zone_used= 1; #ifdef WORDS_BIGENDIAN if (table && table->s->db_low_byte_first) temp=uint4korr(ptr); @@ -4666,7 +4667,6 @@ longlong Field_timestamp::val_int(void) return(0); /* purecov: inspected */ thd->variables.time_zone->gmt_sec_to_TIME(&time_tmp, (my_time_t)temp); - thd->time_zone_used= 1; return time_tmp.year * LL(10000000000) + time_tmp.month * LL(100000000) + time_tmp.day * 1000000L + time_tmp.hour * 10000L + @@ -4686,6 +4686,7 @@ String *Field_timestamp::val_str(String *val_buffer, String *val_ptr) to= (char*) val_buffer->ptr(); val_buffer->length(field_length); + thd->time_zone_used= 1; #ifdef WORDS_BIGENDIAN if (table && table->s->db_low_byte_first) temp=uint4korr(ptr); @@ -4701,7 +4702,6 @@ String *Field_timestamp::val_str(String *val_buffer, String *val_ptr) val_buffer->set_charset(&my_charset_bin); // Safety thd->variables.time_zone->gmt_sec_to_TIME(&time_tmp,(my_time_t)temp); - thd->time_zone_used= 1; temp= time_tmp.year % 100; if (temp < YY_PART_YEAR - 1) @@ -4751,6 +4751,7 @@ bool Field_timestamp::get_date(MYSQL_TIME *ltime, uint fuzzydate) { long temp; THD *thd= table ? table->in_use : current_thd; + thd->time_zone_used= 1; #ifdef WORDS_BIGENDIAN if (table && table->s->db_low_byte_first) temp=uint4korr(ptr); @@ -4766,7 +4767,6 @@ bool Field_timestamp::get_date(MYSQL_TIME *ltime, uint fuzzydate) else { thd->variables.time_zone->gmt_sec_to_TIME(ltime, (my_time_t)temp); - thd->time_zone_used= 1; } return 0; } diff --git a/sql/field.h b/sql/field.h index 60f6fc19d76..fa17f44a55b 100644 --- a/sql/field.h +++ b/sql/field.h @@ -275,9 +275,9 @@ public: if (null_ptr) null_ptr=ADD_TO_PTR(null_ptr,ptr_diff,uchar*); } - inline void get_image(uchar *buff,uint length, CHARSET_INFO *cs) + virtual void get_image(uchar *buff, uint length, CHARSET_INFO *cs) { memcpy(buff,ptr,length); } - inline void set_image(const uchar *buff,uint length, CHARSET_INFO *cs) + virtual void set_image(const uchar *buff,uint length, CHARSET_INFO *cs) { memcpy(ptr,buff,length); } @@ -1586,7 +1586,10 @@ public: virtual bool str_needs_quotes() { return TRUE; } my_decimal *val_decimal(my_decimal *); int cmp(const uchar *a, const uchar *b) - { return cmp_binary(a, b); } + { + DBUG_ASSERT(ptr == a); + return Field_bit::key_cmp(b, bytes_in_rec+test(bit_len)); + } int cmp_binary_offset(uint row_offset) { return cmp_offset(row_offset); } int cmp_max(const uchar *a, const uchar *b, uint max_length); @@ -1594,6 +1597,10 @@ public: { return cmp_binary((uchar *) a, (uchar *) b); } int key_cmp(const uchar *str, uint length); int cmp_offset(uint row_offset); + void get_image(uchar *buff, uint length, CHARSET_INFO *cs) + { get_key_image(buff, length, itRAW); } + void set_image(const uchar *buff,uint length, CHARSET_INFO *cs) + { Field_bit::store((char *) buff, length, cs); } uint get_key_image(uchar *buff, uint length, imagetype type); void set_key_image(const uchar *buff, uint length) { Field_bit::store((char*) buff, length, &my_charset_bin); } diff --git a/sql/item.cc b/sql/item.cc index 006595a39a9..76aad708d22 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1705,7 +1705,7 @@ void Item_ident_for_show::make_field(Send_field *tmp_field) tmp_field->type=field->type(); tmp_field->flags= field->table->maybe_null ? (field->flags & ~NOT_NULL_FLAG) : field->flags; - tmp_field->decimals= 0; + tmp_field->decimals= field->decimals(); } /**********************************************/ diff --git a/sql/mysqld.cc b/sql/mysqld.cc index fe4f83dd79e..6c8fa7e7ac3 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -5073,6 +5073,7 @@ enum options_mysqld OPT_PLUGIN_DIR, OPT_LOG_OUTPUT, OPT_PORT_OPEN_TIMEOUT, + OPT_KEEP_FILES_ON_CREATE, OPT_GENERAL_LOG, OPT_SLOW_LOG, OPT_THREAD_HANDLING, @@ -5950,6 +5951,11 @@ log and this option does nothing anymore.", (uchar**) &max_system_variables.join_buff_size, 0, GET_ULONG, REQUIRED_ARG, 128*1024L, IO_SIZE*2+MALLOC_OVERHEAD, ~0L, MALLOC_OVERHEAD, IO_SIZE, 0}, + {"keep_files_on_create", OPT_KEEP_FILES_ON_CREATE, + "Don't overwrite stale .MYD and .MYI even if no directory is specified.", + (uchar**) &global_system_variables.keep_files_on_create, + (uchar**) &max_system_variables.keep_files_on_create, + 0, GET_BOOL, OPT_ARG, 0, 0, 0, 0, 0, 0}, {"key_buffer_size", OPT_KEY_BUFFER_SIZE, "The size of the buffer used for index blocks for MyISAM tables. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly runs MySQL is quite common.", (uchar**) &dflt_key_cache_var.param_buff_size, diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt index 966e841ccbf..8ecba1d4be0 100644 --- a/sql/share/errmsg.txt +++ b/sql/share/errmsg.txt @@ -5654,6 +5654,8 @@ ER_NON_INSERTABLE_TABLE ger "Die Zieltabelle %-.100s von %s ist nicht einfügbar" ER_ADMIN_WRONG_MRG_TABLE eng "Table '%-.64s' is differently defined or of non-MyISAM type or doesn't exist" +ER_TOO_HIGH_LEVEL_OF_NESTING_FOR_SELECT + eng "Too high level of nesting for select" ER_FOREIGN_SERVER_EXISTS eng "The foreign server, %s, you are trying to create already exists." ER_FOREIGN_SERVER_DOESNT_EXIST diff --git a/sql/sql_class.h b/sql/sql_class.h index ef4869fa244..df79d82732f 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -424,6 +424,8 @@ typedef struct system_status_var #define last_system_status_var com_stmt_close +void mark_transaction_to_rollback(THD *thd, bool all); + #ifdef MYSQL_SERVER void free_tmp_table(THD *thd, TABLE *entry); @@ -2482,6 +2484,7 @@ public: /* Functions in sql_class.cc */ void add_to_status(STATUS_VAR *to_var, STATUS_VAR *from_var); + void add_diff_to_status(STATUS_VAR *to_var, STATUS_VAR *from_var, STATUS_VAR *dec_var); void mark_transaction_to_rollback(THD *thd, bool all); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index d38c9bd8643..0d7158a4eee 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -5251,6 +5251,11 @@ mysql_new_select(LEX *lex, bool move_down) select_lex->init_query(); select_lex->init_select(); lex->nest_level++; + if (lex->nest_level > (int) MAX_SELECT_NESTING) + { + my_error(ER_TOO_HIGH_LEVEL_OF_NESTING_FOR_SELECT,MYF(0),MAX_SELECT_NESTING); + DBUG_RETURN(1); + } select_lex->nest_level= lex->nest_level; /* Don't evaluate this subquery during statement prepare even if diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0da3b4af547..6f3851b66a1 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6448,6 +6448,7 @@ void JOIN_TAB::cleanup() quick= 0; x_free(cache.buff); cache.buff= 0; + limit= 0; if (table) { if (table->key_read) @@ -12586,9 +12587,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, { int ref_key; uint ref_key_parts; + int order_direction; + uint used_key_parts; TABLE *table=tab->table; SQL_SELECT *select=tab->select; key_map usable_keys; + QUICK_SELECT_I *save_quick= 0; DBUG_ENTER("test_if_skip_sort_order"); LINT_INIT(ref_key_parts); @@ -12623,6 +12627,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, else if (select && select->quick) // Range found by opt_range { int quick_type= select->quick->get_type(); + save_quick= select->quick; /* assume results are not ordered when index merge is used TODO: sergeyp: Results of all index merge selects actually are ordered @@ -12642,8 +12647,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, /* We come here when there is a REF key. */ - int order_direction; - uint used_key_parts; if (!usable_keys.is_set(ref_key)) { /* @@ -12704,63 +12707,33 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, } /* Check if we get the rows in requested sorted order by using the key */ if (usable_keys.is_set(ref_key) && - (order_direction = test_if_order_by_key(order,table,ref_key, - &used_key_parts))) - { - if (order_direction == -1) // If ORDER BY ... DESC - { - if (select && select->quick) - { - /* - Don't reverse the sort order, if it's already done. - (In some cases test_if_order_by_key() can be called multiple times - */ - if (!select->quick->reverse_sorted()) - { - QUICK_SELECT_DESC *tmp; - int quick_type= select->quick->get_type(); - if (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE || - quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT || - quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || - quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) - DBUG_RETURN(0); // Use filesort - - /* ORDER BY range_key DESC */ - tmp= new QUICK_SELECT_DESC((QUICK_RANGE_SELECT*)(select->quick), - used_key_parts); - if (!tmp || tmp->error) - { - delete tmp; - DBUG_RETURN(0); // Reverse sort not supported - } - select->quick=tmp; - } - DBUG_RETURN(1); - } - if (tab->ref.key_parts < used_key_parts) - { - /* - SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC - - Use a traversal function that starts by reading the last row - with key part (A) and then traverse the index backwards. - */ - tab->read_first_record= join_read_last_key; - tab->read_record.read_record= join_read_prev_same; - /* fall through */ - } - } - else if (select && select->quick) - select->quick->sorted= 1; - DBUG_RETURN(1); /* No need to sort */ - } + (order_direction= test_if_order_by_key(order,table,ref_key, + &used_key_parts))) + goto check_reverse_order; } - else { - /* check if we can use a key to resolve the group */ - /* Tables using JT_NEXT are handled here */ + /* + Check whether there is an index compatible with the given order + usage of which is cheaper than usage of the ref_key index (ref_key>=0) + or a table scan. + It may be the case if ORDER/GROUP BY is used with LIMIT. + */ uint nr; key_map keys; + uint best_key_parts; + int best_key_direction; + ha_rows best_records; + double read_time; + int best_key= -1; + bool is_best_covering= FALSE; + double fanout= 1; + JOIN *join= tab->join; + uint tablenr= tab - join->join_tab; + ha_rows table_records= table->file->stats.records; + bool group= join->group && order == join->group_list; + LINT_INIT(best_key_parts); + LINT_INIT(best_key_direction); + LINT_INIT(best_records); /* filesort() and join cache are usually faster than reading in @@ -12773,7 +12746,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, resolved with a key; This is because filesort() is usually faster than retrieving all rows through an index. */ - if (select_limit >= table->file->stats.records) + if (select_limit >= table_records) { keys= *table->file->keys_to_use_for_scanning(); keys.merge(table->covering_keys); @@ -12784,38 +12757,227 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, This is to allow users to use index in ORDER BY. */ if (table->force_index) - keys.merge(table->keys_in_use_for_query); + keys.merge(group ? table->keys_in_use_for_group_by : + table->keys_in_use_for_order_by); keys.intersect(usable_keys); } else keys= usable_keys; + read_time= join->best_positions[tablenr].read_time; + for (uint i= tablenr+1; i < join->tables; i++) + fanout*= join->best_positions[i].records_read; // fanout is always >= 1 + for (nr=0; nr < table->s->keys ; nr++) { - uint not_used; - if (keys.is_set(nr)) + int direction; + if (keys.is_set(nr) && + (direction= test_if_order_by_key(order, table, nr, &used_key_parts))) { - int flag; - if ((flag= test_if_order_by_key(order, table, nr, ¬_used))) + bool is_covering= table->covering_keys.is_set(nr) || + nr == table->s->primary_key && + table->file->primary_key_is_clustered(); + + /* + Don't use an index scan with ORDER BY without limit. + For GROUP BY without limit always use index scan + if there is a suitable index. + Why we hold to this asymmetry hardly can be explained + rationally. It's easy to demonstrate that using + temporary table + filesort could be cheaper for grouping + queries too. + */ + if (is_covering || + select_limit != HA_POS_ERROR || + ref_key < 0 && (group || table->force_index)) + { + double rec_per_key; + double index_scan_time; + KEY *keyinfo= tab->table->key_info+nr; + if (select_limit == HA_POS_ERROR) + select_limit= table_records; + if (group) + { + rec_per_key= keyinfo->rec_per_key[used_key_parts-1]; + set_if_bigger(rec_per_key, 1); + /* + With a grouping query each group containing on average + rec_per_key records produces only one row that will + be included into the result set. + */ + if (select_limit > table_records/rec_per_key) + select_limit= table_records; + else + select_limit= (ha_rows) (select_limit*rec_per_key); + } + /* + If tab=tk is not the last joined table tn then to get first + L records from the result set we can expect to retrieve + only L/fanout(tk,tn) where fanout(tk,tn) says how many + rows in the record set on average will match each row tk. + Usually our estimates for fanouts are too pessimistic. + So the estimate for L/fanout(tk,tn) will be too optimistic + and as result we'll choose an index scan when using ref/range + access + filesort will be cheaper. + */ + select_limit= (ha_rows) (select_limit < fanout ? + 1 : select_limit/fanout); + /* + We assume that each of the tested indexes is not correlated + with ref_key. Thus, to select first N records we have to scan + N/selectivity(ref_key) index entries. + selectivity(ref_key) = #scanned_records/#table_records = + table->quick_condition_rows/table_records. + In any case we can't select more than #table_records. + N/(table->quick_condition_rows/table_records) > table_records + <=> N > table->quick_condition_rows. + */ + if (select_limit > table->quick_condition_rows) + select_limit= table_records; + else + select_limit= (ha_rows) (select_limit * + (double) table_records / + table->quick_condition_rows); + rec_per_key= keyinfo->rec_per_key[keyinfo->key_parts-1]; + set_if_bigger(rec_per_key, 1); + /* + Here we take into account the fact that rows are + accessed in sequences rec_per_key records in each. + Rows in such a sequence are supposed to be ordered + by rowid/primary key. When reading the data + in a sequence we'll touch not more pages than the + table file contains. + TODO. Use the formula for a disk sweep sequential access + to calculate the cost of accessing data rows for one + index entry. + */ + index_scan_time= select_limit/rec_per_key * + min(rec_per_key, table->file->scan_time()); + if (is_covering || + ref_key < 0 && (group || table->force_index) || + index_scan_time < read_time) + { + ha_rows quick_records= table_records; + if (is_best_covering && !is_covering) + continue; + if (table->quick_keys.is_set(nr)) + quick_records= table->quick_rows[nr]; + if (best_key < 0 || + (select_limit <= min(quick_records,best_records) ? + keyinfo->key_parts < best_key_parts : + quick_records < best_records)) + { + best_key= nr; + best_key_parts= keyinfo->key_parts; + best_records= quick_records; + is_best_covering= is_covering; + best_key_direction= direction; + } + } + } + } + } + if (best_key >= 0) + { + bool quick_created= FALSE; + if (table->quick_keys.is_set(best_key) && best_key != ref_key) + { + key_map map; + map.clear_all(); // Force the creation of quick select + map.set_bit(best_key); // only best_key. + quick_created= + select->test_quick_select(join->thd, map, 0, + join->select_options & OPTION_FOUND_ROWS ? + HA_POS_ERROR : + join->unit->select_limit_cnt, + 0) > 0; + } + if (!no_changes) + { + if (!quick_created) { - if (!no_changes) - { - tab->index=nr; - tab->read_first_record= (flag > 0 ? join_read_first: - join_read_last); - tab->type=JT_NEXT; // Read with index_first(), index_next() - if (table->covering_keys.is_set(nr)) - { - table->key_read=1; - table->file->extra(HA_EXTRA_KEYREAD); - } - } - DBUG_RETURN(1); + tab->index= best_key; + tab->read_first_record= best_key_direction > 0 ? + join_read_first:join_read_last; + tab->type=JT_NEXT; // Read with index_first(), index_next() + if (table->covering_keys.is_set(best_key)) + { + table->key_read=1; + table->file->extra(HA_EXTRA_KEYREAD); + } + table->file->ha_index_or_rnd_end(); + if (join->select_options & SELECT_DESCRIBE) + { + tab->ref.key= -1; + tab->ref.key_parts= 0; + if (select && select->quick) + { + delete select->quick; + select->quick= 0; + } + if (select_limit < table_records) + tab->limit= select_limit; + } + } + } + used_key_parts= best_key_parts; + order_direction= best_key_direction; + } + else + DBUG_RETURN(0); + } + +check_reverse_order: + if (order_direction == -1) // If ORDER BY ... DESC + { + if (select && select->quick) + { + /* + Don't reverse the sort order, if it's already done. + (In some cases test_if_order_by_key() can be called multiple times + */ + if (!select->quick->reverse_sorted()) + { + QUICK_SELECT_DESC *tmp; + int quick_type= select->quick->get_type(); + if (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE || + quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT || + quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || + quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) + { + tab->limit= 0; + select->quick= save_quick; + DBUG_RETURN(0); // Use filesort + } + + /* ORDER BY range_key DESC */ + tmp= new QUICK_SELECT_DESC((QUICK_RANGE_SELECT*)(select->quick), + used_key_parts); + if (!tmp || tmp->error) + { + delete tmp; + select->quick= save_quick; + tab->limit= 0; + DBUG_RETURN(0); // Reverse sort not supported } + select->quick=tmp; } } + else if (tab->ref.key >= 0 && tab->ref.key_parts < used_key_parts) + { + /* + SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC + + Use a traversal function that starts by reading the last row + with key part (A) and then traverse the index backwards. + */ + tab->read_first_record= join_read_last_key; + tab->read_record.read_record= join_read_prev_same; + } } - DBUG_RETURN(0); // Can't use index. + else if (select && select->quick) + select->quick->sorted= 1; + DBUG_RETURN(1); } @@ -15554,7 +15716,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, if (tab->select && tab->select->quick) examined_rows= tab->select->quick->records; else if (tab->type == JT_NEXT || tab->type == JT_ALL) - examined_rows= tab->table->file->records(); + examined_rows= tab->limit ? tab->limit : tab->table->file->records(); else examined_rows=(ha_rows)join->best_positions[i].records_read; diff --git a/sql/sql_select.h b/sql/sql_select.h index d1acad1c5d6..efa92432e2b 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -194,6 +194,12 @@ typedef struct st_join_table { enum join_type type; bool cached_eq_ref_table,eq_ref_table,not_used_in_distinct; bool sorted; + /* + If it's not 0 the number stored this field indicates that the index + scan has been chosen to access the table data and we expect to scan + this number of rows for the table. + */ + ha_rows limit; TABLE_REF ref; JOIN_CACHE cache; JOIN *join; diff --git a/sql/table.cc b/sql/table.cc index 12fffe1dde7..27f9ccc418e 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -2484,6 +2484,7 @@ void update_create_info_from_table(HA_CREATE_INFO *create_info, TABLE *table) create_info->row_type= share->row_type; create_info->default_table_charset= share->table_charset; create_info->table_charset= 0; + create_info->comment= share->comment; DBUG_VOID_RETURN; } diff --git a/sql/time.cc b/sql/time.cc index c552d085f5e..a6619cf4cee 100644 --- a/sql/time.cc +++ b/sql/time.cc @@ -263,11 +263,11 @@ my_time_t TIME_to_timestamp(THD *thd, const MYSQL_TIME *t, my_bool *in_dst_time_ my_time_t timestamp; *in_dst_time_gap= 0; + thd->time_zone_used= 1; timestamp= thd->variables.time_zone->TIME_to_gmt_sec(t, in_dst_time_gap); if (timestamp) { - thd->time_zone_used= 1; return timestamp; } diff --git a/sql/unireg.h b/sql/unireg.h index 232ea5e70e7..b368eee6f0e 100644 --- a/sql/unireg.h +++ b/sql/unireg.h @@ -85,6 +85,8 @@ #define MAX_FIELDS 4096 /* Limit in the .frm file */ #define MAX_PARTITIONS 1024 +#define MAX_SELECT_NESTING (sizeof(nesting_map)*8-1) + #define MAX_SORT_MEMORY (2048*1024-MALLOC_OVERHEAD) #define MIN_SORT_MEMORY (32*1024-MALLOC_OVERHEAD) diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index f606134578a..05447f75e95 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -604,7 +604,6 @@ convert_error_code_to_mysql( thd_mark_transaction_to_rollback(thd, TRUE); return(HA_ERR_LOCK_DEADLOCK); - } else if (error == (int) DB_LOCK_WAIT_TIMEOUT) { /* Starting from 5.0.13, we let MySQL just roll back the diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index eac1ad413ea..3656364820e 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -16077,7 +16077,6 @@ static void test_bug24179() /* Bug#28075 "COM_DEBUG crashes mysqld" - Note: Test disabled because of failure in PushBuild. */ static void test_bug28075() @@ -16106,7 +16105,7 @@ static void test_bug27876() int rc; MYSQL_RES *result; - char utf8_func[] = + unsigned char utf8_func[] = { 0xd1, 0x84, 0xd1, 0x83, 0xd0, 0xbd, 0xd0, 0xba, 0xd1, 0x86, 0xd0, 0xb8, 0xd0, 0xb9, 0xd0, 0xba, @@ -16114,7 +16113,7 @@ static void test_bug27876() 0x00 }; - char utf8_param[] = + unsigned char utf8_param[] = { 0xd0, 0xbf, 0xd0, 0xb0, 0xd1, 0x80, 0xd0, 0xb0, 0xd0, 0xbc, 0xd0, 0xb5, 0xd1, 0x82, 0xd1, 0x8a, @@ -16334,7 +16333,54 @@ static void test_bug29692() mysql_close(conn); } +/** + Bug#29306 Truncated data in MS Access with decimal (3,1) columns in a VIEW +*/ + +static void test_bug29306() +{ + MYSQL_FIELD *field; + int rc; + MYSQL_RES *res; + + DBUG_ENTER("test_bug29306"); + myheader("test_bug29306"); + + rc= mysql_query(mysql, "DROP TABLE IF EXISTS tab17557"); + myquery(rc); + rc= mysql_query(mysql, "DROP VIEW IF EXISTS view17557"); + myquery(rc); + rc= mysql_query(mysql, "CREATE TABLE tab17557 (dd decimal (3,1))"); + myquery(rc); + rc= mysql_query(mysql, "CREATE VIEW view17557 as SELECT dd FROM tab17557"); + myquery(rc); + rc= mysql_query(mysql, "INSERT INTO tab17557 VALUES (7.6)"); + myquery(rc); + + /* Checking the view */ + res= mysql_list_fields(mysql, "view17557", NULL); + while ((field= mysql_fetch_field(res))) + { + if (! opt_silent) + { + printf("field name %s\n", field->name); + printf("field table %s\n", field->table); + printf("field decimals %d\n", field->decimals); + if (field->decimals < 1) + printf("Error! No decimals! \n"); + printf("\n\n"); + } + DIE_UNLESS(field->decimals == 1); + } + mysql_free_result(res); + + rc= mysql_query(mysql, "DROP TABLE tab17557"); + myquery(rc); + rc= mysql_query(mysql, "DROP VIEW view17557"); + myquery(rc); + DBUG_VOID_RETURN; +} /* Read and parse arguments and MySQL options from my.cnf */ @@ -16626,6 +16672,7 @@ static struct my_tests_st my_tests[]= { { "test_bug27592", test_bug27592 }, { "test_bug29687", test_bug29687 }, { "test_bug29692", test_bug29692 }, + { "test_bug29306", test_bug29306 }, { 0, 0 } }; |