From 51d80960ea52e1c3c99bf6287f331a58e1c2b11c Mon Sep 17 00:00:00 2001 From: "ingo@mysql.com" <> Date: Tue, 31 May 2005 11:08:14 +0200 Subject: Bug#10224 - ANALYZE TABLE crashing with simultaneous CREATE ... SELECT statement. 1.) Added a new option to mysql_lock_tables() for ignoring FLUSH TABLES. Used the new option in create_table_from_items(). It is necessary to prevent the SELECT table from being reopend. It would get new storage assigned for its fields, while the SELECT part of the command would still use the old (freed) storage. 2.) Protected the CREATE TABLE and CREATE TABLE ... SELECT commands against a global read lock. This prevents a deadlock in CREATE TABLE ... SELECT in conjunction with FLUSH TABLES WITH READ LOCK and avoids the creation of new tables during a global read lock. 3.) Replaced set_protect_against_global_read_lock() and unset_protect_against_global_read_lock() by wait_if_global_read_lock() and start_waiting_global_read_lock() in the INSERT DELAYED handling. --- sql/lock.cc | 69 +++++++++++++++++------------------------------------- sql/mysql_priv.h | 7 ++++-- sql/sql_acl.cc | 4 ++-- sql/sql_base.cc | 6 ++--- sql/sql_handler.cc | 2 +- sql/sql_insert.cc | 24 ++++++++++++++----- sql/sql_parse.cc | 37 ++++++++++++++++++++++++++--- sql/sql_table.cc | 2 +- 8 files changed, 85 insertions(+), 66 deletions(-) (limited to 'sql') diff --git a/sql/lock.cc b/sql/lock.cc index 951b0e2ff6a..4c6d7c75ae2 100644 --- a/sql/lock.cc +++ b/sql/lock.cc @@ -79,8 +79,24 @@ static int unlock_external(THD *thd, TABLE **table,uint count); static void print_lock_error(int error); -MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, uint count, - bool ignore_global_read_lock) +/* + Lock tables. + + SYNOPSIS + mysql_lock_tables() + thd The current thread. + tables An array of pointers to the tables to lock. + count The number of tables to lock. + flags Options: + MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK Ignore a global read lock + MYSQL_LOCK_IGNORE_FLUSH Ignore a flush tables. + + RETURN + A lock structure pointer on success. + NULL on error. +*/ + +MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, uint count, uint flags) { MYSQL_LOCK *sql_lock; TABLE *write_lock_used; @@ -91,7 +107,8 @@ MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, uint count, if (!(sql_lock = get_lock_data(thd,tables,count, 0,&write_lock_used))) break; - if (global_read_lock && write_lock_used && ! ignore_global_read_lock) + if (global_read_lock && write_lock_used && + ! (flags & MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK)) { /* Someone has issued LOCK ALL TABLES FOR READ and we want a write lock @@ -125,7 +142,7 @@ MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, uint count, thd->some_tables_deleted=1; // Try again sql_lock->lock_count=0; // Locks are alread freed } - else if (!thd->some_tables_deleted) + else if (!thd->some_tables_deleted || (flags & MYSQL_LOCK_IGNORE_FLUSH)) { thd->locked=0; break; @@ -868,47 +885,3 @@ void make_global_read_lock_block_commit(THD *thd) } -/* - Set protection against global read lock. - - SYNOPSIS - set_protect_against_global_read_lock() - void - - RETURN - FALSE OK, no global read lock exists. - TRUE Error, global read lock exists already. -*/ - -my_bool set_protect_against_global_read_lock(void) -{ - my_bool global_read_lock_exists; - - pthread_mutex_lock(&LOCK_open); - if (! (global_read_lock_exists= test(global_read_lock))) - protect_against_global_read_lock++; - pthread_mutex_unlock(&LOCK_open); - return global_read_lock_exists; -} - - -/* - Unset protection against global read lock. - - SYNOPSIS - unset_protect_against_global_read_lock() - void - - RETURN - void -*/ - -void unset_protect_against_global_read_lock(void) -{ - pthread_mutex_lock(&LOCK_open); - protect_against_global_read_lock--; - pthread_mutex_unlock(&LOCK_open); - pthread_cond_broadcast(&COND_refresh); -} - - diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 6a25536b3f1..75ec3482d8d 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -766,8 +766,11 @@ extern pthread_t signal_thread; extern struct st_VioSSLAcceptorFd * ssl_acceptor_fd; #endif /* HAVE_OPENSSL */ -MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **table, uint count, - bool ignore_global_read_lock= FALSE); +MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **table, uint count, uint flags); +/* mysql_lock_tables() flags bits */ +#define MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK 0x0001 +#define MYSQL_LOCK_IGNORE_FLUSH 0x0002 + void mysql_unlock_tables(THD *thd, MYSQL_LOCK *sql_lock); void mysql_unlock_read_tables(THD *thd, MYSQL_LOCK *sql_lock); void mysql_unlock_some_tables(THD *thd, TABLE **table,uint count); diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index acdd07b2fc5..46b29f252a6 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -177,7 +177,7 @@ my_bool acl_init(THD *org_thd, bool dont_read_acl_tables) ptr[0]= tables[0].table; ptr[1]= tables[1].table; ptr[2]= tables[2].table; - if (!(lock=mysql_lock_tables(thd,ptr,3))) + if (! (lock= mysql_lock_tables(thd, ptr, 3, 0))) { sql_print_error("Fatal error: Can't lock privilege tables: %s", thd->net.last_error); @@ -2514,7 +2514,7 @@ my_bool grant_init(THD *org_thd) TABLE *ptr[2]; // Lock tables for quick update ptr[0]= tables[0].table; ptr[1]= tables[1].table; - if (!(lock=mysql_lock_tables(thd,ptr,2))) + if (! (lock= mysql_lock_tables(thd, ptr, 2, 0))) goto end; t_table = tables[0].table; c_table = tables[1].table; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index c8eb2338294..9c5f73697fd 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1161,7 +1161,7 @@ bool reopen_tables(THD *thd,bool get_locks,bool in_refresh) MYSQL_LOCK *lock; /* We should always get these locks */ thd->some_tables_deleted=0; - if ((lock=mysql_lock_tables(thd,tables,(uint) (tables_ptr-tables)))) + if ((lock= mysql_lock_tables(thd, tables, (uint) (tables_ptr-tables), 0))) { thd->locked_tables=mysql_lock_merge(thd->locked_tables,lock); } @@ -1602,7 +1602,7 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type) else { if ((table->reginfo.lock_type= lock_type) != TL_UNLOCK) - if (!(thd->lock=mysql_lock_tables(thd,&table_list->table,1))) + if (! (thd->lock= mysql_lock_tables(thd, &table_list->table, 1, 0))) table= 0; } } @@ -1653,7 +1653,7 @@ int lock_tables(THD *thd,TABLE_LIST *tables) return -1; for (table = tables ; table ; table=table->next) *(ptr++)= table->table; - if (!(thd->lock=mysql_lock_tables(thd,start,count))) + if (! (thd->lock= mysql_lock_tables(thd, start, count, 0))) return -1; /* purecov: inspected */ } else diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc index 5bfcc897fc7..fcdb2aeb668 100644 --- a/sql/sql_handler.cc +++ b/sql/sql_handler.cc @@ -448,7 +448,7 @@ int mysql_ha_read(THD *thd, TABLE_LIST *tables, send_fields(thd,list,1); HANDLER_TABLES_HACK(thd); - lock= mysql_lock_tables(thd, &tables->table, 1); + lock= mysql_lock_tables(thd, &tables->table, 1, 0); HANDLER_TABLES_HACK(thd); byte *key; diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index c6b7b1d6c15..b61c766120e 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -674,10 +674,13 @@ static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list) Avoid that a global read lock steps in while we are creating the new thread. It would block trying to open the table. Hence, the DI thread and this thread would wait until after the global - readlock is gone. If the read lock exists already, we leave with - no table and then switch to non-delayed insert. + readlock is gone. Since the insert thread needs to wait for a + global read lock anyway, we do it right now. Note that + wait_if_global_read_lock() sets a protection against a new + global read lock when it succeeds. This needs to be released by + start_waiting_global_read_lock(). */ - if (set_protect_against_global_read_lock()) + if (wait_if_global_read_lock(thd, 0, 1)) goto err; if (!(tmp=new delayed_insert())) { @@ -719,7 +722,11 @@ static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list) pthread_cond_wait(&tmp->cond_client,&tmp->mutex); } pthread_mutex_unlock(&tmp->mutex); - unset_protect_against_global_read_lock(); + /* + Release the protection against the global read lock and wake + everyone, who might want to set a global read lock. + */ + start_waiting_global_read_lock(thd); thd->proc_info="got old table"; if (tmp->thd.killed) { @@ -755,7 +762,11 @@ static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list) err1: thd->fatal_error= 1; - unset_protect_against_global_read_lock(); + /* + Release the protection against the global read lock and wake + everyone, who might want to set a global read lock. + */ + start_waiting_global_read_lock(thd); err: pthread_mutex_unlock(&LOCK_delayed_create); DBUG_RETURN(0); // Continue with normal insert @@ -1105,7 +1116,8 @@ extern "C" pthread_handler_decl(handle_delayed_insert,arg) handler will close the table and finish when the outstanding inserts are done. */ - if (! (thd->lock= mysql_lock_tables(thd, &di->table, 1, TRUE))) + if (! (thd->lock= mysql_lock_tables(thd, &di->table, 1, + MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK))) { di->dead=thd->killed=1; // Fatal error } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 8f56b7cfcff..aac442d35a2 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1673,6 +1673,24 @@ mysql_execute_command(void) break; } #endif + /* + The create-select command will open and read-lock the select table + and then create, open and write-lock the new table. If a global + read lock steps in, we get a deadlock. The write lock waits for + the global read lock, while the global read lock waits for the + select table to be closed. So we wait until the global readlock is + gone before starting both steps. Note that + wait_if_global_read_lock() sets a protection against a new global + read lock when it succeeds. This needs to be released by + start_waiting_global_read_lock(). We protect the normal CREATE + TABLE in the same way. That way we avoid that a new table is + created during a gobal read lock. + */ + if (wait_if_global_read_lock(thd, 0, 1)) + { + res= -1; + break; + } if (select_lex->item_list.elements) // With select { select_result *result; @@ -1681,7 +1699,7 @@ mysql_execute_command(void) check_dup(tables->db, tables->real_name, tables->next)) { net_printf(&thd->net,ER_INSERT_TABLE_USED,tables->real_name); - DBUG_VOID_RETURN; + goto error1; } if (lex->create_info.used_fields & HA_CREATE_USED_UNION) { @@ -1692,7 +1710,7 @@ mysql_execute_command(void) (TABLE_LIST*)lex->create_info.merge_list.first)) { net_printf(&thd->net, ER_INSERT_TABLE_USED, tab->real_name); - DBUG_VOID_RETURN; + goto error1; } } } @@ -1700,7 +1718,7 @@ mysql_execute_command(void) { TABLE_LIST *table; if (check_table_access(thd, SELECT_ACL, tables->next)) - goto error; // Error message is given + goto error1; // Error message is given /* TODO: Delete the following loop when locks is set by sql_yacc */ for (table = tables->next ; table ; table=table->next) table->lock_type= lex->lock_option; @@ -1737,6 +1755,11 @@ mysql_execute_command(void) if (!res) send_ok(&thd->net); } + /* + Release the protection against the global read lock and wake + everyone, who might want to set a global read lock. + */ + start_waiting_global_read_lock(thd); break; } case SQLCOM_CREATE_INDEX: @@ -2674,6 +2697,14 @@ error: thd->lock= 0; } DBUG_VOID_RETURN; + + error1: + /* + Release the protection against the global read lock and wake + everyone, who might want to set a global read lock. + */ + start_waiting_global_read_lock(thd); + DBUG_VOID_RETURN; } diff --git a/sql/sql_table.cc b/sql/sql_table.cc index e275f902abd..a9af8144df0 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -896,7 +896,7 @@ TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info, if (!table) DBUG_RETURN(0); table->reginfo.lock_type=TL_WRITE; - if (!((*lock)=mysql_lock_tables(thd,&table,1))) + if (! ((*lock)= mysql_lock_tables(thd, &table, 1, MYSQL_LOCK_IGNORE_FLUSH))) { VOID(pthread_mutex_lock(&LOCK_open)); hash_delete(&open_cache,(byte*) table); -- cgit v1.2.1 From c4f37eda6573e4c63d9d839b8a6c9396e729bb67 Mon Sep 17 00:00:00 2001 From: "acurtis@xiphis.org" <> Date: Tue, 31 May 2005 18:06:54 +0100 Subject: Bug#10413 - Invalid column name is not rejected Stop ignoring name parts and check for validity --- sql/sql_yacc.yy | 26 +++++++++++++++++++++++++- 1 file changed, 25 insertions(+), 1 deletion(-) (limited to 'sql') diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index d8a2c997bf8..7585f0b10a0 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -5016,7 +5016,31 @@ simple_ident: field_ident: ident { $$=$1;} - | ident '.' ident { $$=$3;} /* Skip schema name in create*/ + | ident '.' ident '.' ident + { + TABLE_LIST *table= (TABLE_LIST*) Select->table_list.first; + if (my_strcasecmp(table_alias_charset, $1.str, table->db)) + { + net_printf(YYTHD, ER_WRONG_DB_NAME, $1.str); + YYABORT; + } + if (my_strcasecmp(table_alias_charset, $3.str, table->real_name)) + { + net_printf(YYTHD, ER_WRONG_TABLE_NAME, $3.str); + YYABORT; + } + $$=$5; + } + | ident '.' ident + { + TABLE_LIST *table= (TABLE_LIST*) Select->table_list.first; + if (my_strcasecmp(table_alias_charset, $1.str, table->alias)) + { + net_printf(YYTHD, ER_WRONG_TABLE_NAME, $1.str); + YYABORT; + } + $$=$3; + } | '.' ident { $$=$2;} /* For Delphi */; table_ident: -- cgit v1.2.1