diff options
| -rw-r--r-- | mysql-test/r/view.result | 25 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 11 | ||||
| -rw-r--r-- | sql/mysqld.cc | 12 | ||||
| -rw-r--r-- | sql/set_var.cc | 15 | ||||
| -rw-r--r-- | sql/sql_class.h | 2 | ||||
| -rw-r--r-- | sql/sql_view.cc | 42 | ||||
| -rw-r--r-- | sql/sql_view.h | 2 |
7 files changed, 50 insertions, 59 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 7fc81ddf0fa..be5514d2a35 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -753,31 +753,28 @@ create table t1 (a int, b int, c int, primary key(a,b)); insert into t1 values (10,2,-1), (20,3,-2), (30,4,-3), (40,5,-4), (50,10,-5); create view v1 (x,y) as select a, b from t1; create view v2 (x,y) as select a, c from t1; -set sql_updatable_view_key=YES; +set updatable_views_with_limit=NO; update v1 set x=x+1; update v2 set x=x+1; -ERROR HY000: The target table v2 of the UPDATE is not updatable -set sql_updatable_view_key=LIMIT1; -update v1 set x=x+1; -update v2 set x=x+1; -Warnings: -Note 1355 View being updated does not have complete key of underlying table in it update v1 set x=x+1 limit 1; update v2 set x=x+1 limit 1; ERROR HY000: The target table v2 of the UPDATE is not updatable -set sql_updatable_view_key=NO; +set updatable_views_with_limit=YES; update v1 set x=x+1 limit 1; update v2 set x=x+1 limit 1; Warnings: Note 1355 View being updated does not have complete key of underlying table in it -set sql_updatable_view_key=DEFAULT; +set updatable_views_with_limit=DEFAULT; +show variables like "updatable_views_with_limit"; +Variable_name Value +updatable_views_with_limit YES select * from t1; a b c -16 2 -1 -23 3 -2 -33 4 -3 -43 5 -4 -53 10 -5 +15 2 -1 +22 3 -2 +32 4 -3 +42 5 -4 +52 10 -5 drop table t1; drop view v1,v2; create table t1 (a int, b int, c int, primary key(a,b)); diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index da696820828..456b62971ba 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -628,20 +628,17 @@ create table t1 (a int, b int, c int, primary key(a,b)); insert into t1 values (10,2,-1), (20,3,-2), (30,4,-3), (40,5,-4), (50,10,-5); create view v1 (x,y) as select a, b from t1; create view v2 (x,y) as select a, c from t1; -set sql_updatable_view_key=YES; -update v1 set x=x+1; --- error 1288 -update v2 set x=x+1; -set sql_updatable_view_key=LIMIT1; +set updatable_views_with_limit=NO; update v1 set x=x+1; update v2 set x=x+1; update v1 set x=x+1 limit 1; -- error 1288 update v2 set x=x+1 limit 1; -set sql_updatable_view_key=NO; +set updatable_views_with_limit=YES; update v1 set x=x+1 limit 1; update v2 set x=x+1 limit 1; -set sql_updatable_view_key=DEFAULT; +set updatable_views_with_limit=DEFAULT; +show variables like "updatable_views_with_limit"; select * from t1; drop table t1; drop view v1,v2; diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 8ab5daffc16..db81b14c9c9 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -4068,7 +4068,7 @@ enum options_mysqld OPT_DEFAULT_TIME_ZONE, OPT_OPTIMIZER_SEARCH_DEPTH, OPT_OPTIMIZER_PRUNE_LEVEL, - OPT_SQL_UPDATABLE_VIEW_KEY, + OPT_UPDATABLE_VIEWS_WITH_LIMIT, OPT_AUTO_INCREMENT, OPT_AUTO_INCREMENT_OFFSET }; @@ -5122,11 +5122,6 @@ The minimum value for this variable is 4096.", (gptr*) &max_system_variables.sortbuff_size, 0, GET_ULONG, REQUIRED_ARG, MAX_SORT_MEMORY, MIN_SORT_MEMORY+MALLOC_OVERHEAD*2, ~0L, MALLOC_OVERHEAD, 1, 0}, - {"sql_updatable_view_key", OPT_SQL_UPDATABLE_VIEW_KEY, - "0 = NO = Don't check presence of key in updatable VIEW. 1 = YES = Prohibit update of VIEW which does not contain key of underlying table. 2 = LIMIT1 = Same as YES but prohibited only operation with LIMIT 1 (usually get from GUI tools).", - (gptr*) &global_system_variables.sql_updatable_view_key, - (gptr*) &max_system_variables.sql_updatable_view_key, - 0, GET_ULONG, REQUIRED_ARG, 1, 0, 2, 0, 1, 0}, #ifdef HAVE_BERKELEY_DB {"sync-bdb-logs", OPT_BDB_SYNC, "Synchronously flush logs. Enabled by default", @@ -5178,6 +5173,11 @@ The minimum value for this variable is 4096.", (gptr*) &global_system_variables.trans_prealloc_size, (gptr*) &max_system_variables.trans_prealloc_size, 0, GET_ULONG, REQUIRED_ARG, TRANS_ALLOC_PREALLOC_SIZE, 1024, ~0L, 0, 1024, 0}, + {"updatable_views_with_limit", OPT_UPDATABLE_VIEWS_WITH_LIMIT, + "1 = YES = Don't issue an error message (warning only) if a VIEW without presence of a key of the underlaying table is used in queries with a LIMIT clause for updating. 0 = NO = Prohibit update of a VIEW, which does not contain a key of the underlying table and the query uses a LIMIT clause (usually get from GUI tools).", + (gptr*) &global_system_variables.updatable_views_with_limit, + (gptr*) &max_system_variables.updatable_views_with_limit, + 0, GET_ULONG, REQUIRED_ARG, 1, 0, 1, 0, 1, 0}, {"wait_timeout", OPT_WAIT_TIMEOUT, "The number of seconds the server waits for activity on a connection before closing it.", (gptr*) &global_system_variables.net_wait_timeout, diff --git a/sql/set_var.cc b/sql/set_var.cc index 1d0ddd431c4..33c6a380223 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -337,9 +337,10 @@ sys_var_thd_ulong sys_sort_buffer("sort_buffer_size", &SV::sortbuff_size); sys_var_thd_sql_mode sys_sql_mode("sql_mode", &SV::sql_mode); -sys_var_thd_enum sys_sql_updatable_view_key("sql_updatable_view_key", - &SV::sql_updatable_view_key, - &sql_updatable_view_key_typelib); +sys_var_thd_enum +sys_updatable_views_with_limit("updatable_views_with_limit", + &SV::updatable_views_with_limit, + &updatable_views_with_limit_typelib); sys_var_thd_table_type sys_table_type("table_type", &SV::table_type); @@ -600,7 +601,6 @@ sys_var *sys_variables[]= &sys_sql_low_priority_updates, &sys_sql_max_join_size, &sys_sql_mode, - &sys_sql_updatable_view_key, &sys_sql_warnings, &sys_storage_engine, #ifdef HAVE_REPLICATION @@ -620,8 +620,9 @@ sys_var *sys_variables[]= &sys_os, #ifdef HAVE_INNOBASE_DB &sys_innodb_max_dirty_pages_pct, -#endif +#endif &sys_unique_checks, + &sys_updatable_views_with_limit, &sys_warning_count }; @@ -833,8 +834,6 @@ struct show_var_st init_vars[]= { #endif {sys_sort_buffer.name, (char*) &sys_sort_buffer, SHOW_SYS}, {sys_sql_mode.name, (char*) &sys_sql_mode, SHOW_SYS}, - {sys_sql_updatable_view_key.name, - (char*) &sys_sql_updatable_view_key, SHOW_SYS}, {sys_storage_engine.name, (char*) &sys_storage_engine, SHOW_SYS}, #ifdef HAVE_REPLICATION {sys_sync_binlog_period.name,(char*) &sys_sync_binlog_period, SHOW_SYS}, @@ -858,6 +857,8 @@ struct show_var_st init_vars[]= { SHOW_SYS}, {sys_trans_prealloc_size.name, (char*) &sys_trans_prealloc_size, SHOW_SYS}, {sys_tx_isolation.name, (char*) &sys_tx_isolation, SHOW_SYS}, + {sys_updatable_views_with_limit.name, + (char*) &sys_updatable_views_with_limit,SHOW_SYS}, {"version", server_version, SHOW_CHAR}, #ifdef HAVE_BERKELEY_DB {"version_bdb", (char*) DB_VERSION_STRING, SHOW_CHAR}, diff --git a/sql/sql_class.h b/sql/sql_class.h index 6d44b82b689..32f156fbee8 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -408,7 +408,7 @@ struct system_variables /* Determines which non-standard SQL behaviour should be enabled */ ulong sql_mode; /* check of key presence in updatable view */ - ulong sql_updatable_view_key; + ulong updatable_views_with_limit; ulong default_week_format; ulong max_seeks_for_key; ulong range_alloc_block_size; diff --git a/sql/sql_view.cc b/sql/sql_view.cc index e9728d12734..a48f5196eec 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -24,11 +24,11 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view, enum_view_create_mode mode); -const char *sql_updatable_view_key_names[]= { "NO", "YES", "LIMIT1", NullS }; -TYPELIB sql_updatable_view_key_typelib= +const char *updatable_views_with_limit_names[]= { "NO", "YES", NullS }; +TYPELIB updatable_views_with_limit_typelib= { - array_elements(sql_updatable_view_key_names)-1, "", - sql_updatable_view_key_names + array_elements(updatable_views_with_limit_names)-1, "", + updatable_views_with_limit_names }; @@ -882,7 +882,8 @@ frm_type_enum mysql_frm_type(char *path) view view for check with opened table DESCRIPTION - check that undertlaying table of viey contain one of following: + If it is VIEW and query have LIMIT clause then check that undertlying + table of viey contain one of following: 1) primary key of underlying table 2) unique key underlying table with fields for which NULL value is impossible @@ -901,8 +902,9 @@ bool check_key_in_view(THD *thd, TABLE_LIST *view) uint i, elements_in_view; DBUG_ENTER("check_key_in_view"); - if (!view->view) - DBUG_RETURN(FALSE); /* it is normal table */ + if (!view->view || + thd->lex->unit.global_parameters->select_limit == HA_POS_ERROR) + DBUG_RETURN(FALSE); /* it is normal table or query without LIMIT */ table= view->table; trans= view->field_translation; key_info_end= (key_info= table->key_info)+ table->keys; @@ -952,26 +954,20 @@ bool check_key_in_view(THD *thd, TABLE_LIST *view) } if (i == elements_in_view) // If field didn't exists { - ulong mode= thd->variables.sql_updatable_view_key; /* - 0 == NO ; Don't give any errors - 1 == YES ; Give always an error - 2 == LIMIT1 ; Give an error if this is used with LIMIT 1 - This is used to protect against GUI programs that - uses LIMIT 1 to update just the current row. This - doesn't work reliable if the view doesn't have a - unique key or if the view doesn't use all fields in - table. + Keys or all fields of underlaying tables are not foud => we have + to check variable updatable_views_with_limit to decide should we + issue an error or just a warning */ - if (mode == 1 || - (mode == 2 && - thd->lex->unit.global_parameters->select_limit == 1)) + if (thd->variables.updatable_views_with_limit) { - DBUG_RETURN(TRUE); + /* update allowed, but issue warning */ + push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_WARN_VIEW_WITHOUT_KEY, ER(ER_WARN_VIEW_WITHOUT_KEY)); + DBUG_RETURN(FALSE); } - push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, - ER_WARN_VIEW_WITHOUT_KEY, ER(ER_WARN_VIEW_WITHOUT_KEY)); - DBUG_RETURN(FALSE); + /* prohibit update */ + DBUG_RETURN(TRUE); } } } diff --git a/sql/sql_view.h b/sql/sql_view.h index 431f82a5bb8..538f548d97b 100644 --- a/sql/sql_view.h +++ b/sql/sql_view.h @@ -29,7 +29,7 @@ void insert_view_fields(List<Item> *list, TABLE_LIST *view); frm_type_enum mysql_frm_type(char *path); -extern TYPELIB sql_updatable_view_key_typelib; +extern TYPELIB updatable_views_with_limit_typelib; #define VIEW_ANY_ACL (SELECT_ACL | UPDATE_ACL | INSERT_ACL | DELETE_ACL) |