Hi List. Below is my patch that implements the CREATE [[NO] FORCE] VIEW options against MariaDB 10.1.0. Notes: - It's a fairly solid patch, but I think the best thing is to commit it to alpha and let it bake for a while. - it passes t/view.test: # ./mysql-test-run.pl view Logging: ./mysql-test-run.pl view vardir: /usr/local/mariadb-10.1.0/mysql-test/var MariaDB Version 10.1.0-MariaDB-debug TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- main.view [ pass ] 1896 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 1.896 of 7 seconds executing testcases Completed: All 1 tests were successful. - I wrote tests/view.pl which does 8,000 tests. It passes. (I will include it in a separate email.) - Please take a look at the style, and the 411 comments. Thanks, James Briggs. -- Available for MySQL/NoSQL DBA/Programming in San Jose area or remote. $ cat create_force_view.patch --- ../mariadb-10.1.0/sql/sql_view.h 2014-06-27 04:50:36.000000000 -0700 +++ sql/sql_view.h 2014-09-02 02:35:42.000000000 -0700 @@ -29,10 +29,10 @@ /* Function declarations */ bool create_view_precheck(THD *thd, TABLE_LIST *tables, TABLE_LIST *view, - enum_view_create_mode mode); + enum_view_create_mode mode, enum_view_create_force force); bool mysql_create_view(THD *thd, TABLE_LIST *view, - enum_view_create_mode mode); + enum_view_create_mode mode, enum_view_create_force force); bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table, uint flags); --- ../mariadb-10.1.0/sql/sql_lex.h 2014-06-27 04:50:33.000000000 -0700 +++ sql/sql_lex.h 2014-09-02 01:21:10.000000000 -0700 @@ -170,6 +170,12 @@ VIEW_CREATE_OR_REPLACE // check only that there are not such table }; +enum enum_view_create_force +{ + VIEW_CREATE_NO_FORCE, // default - check that there are not such VIEW/table + VIEW_CREATE_FORCE, // check that there are not such VIEW/table, then ignore table object dependencies +}; + enum enum_drop_mode { DROP_DEFAULT, // mode is not specified @@ -2442,6 +2448,7 @@ }; enum enum_var_type option_type; enum enum_view_create_mode create_view_mode; + enum enum_view_create_force create_view_force; enum enum_drop_mode drop_mode; uint profile_query_id; --- ../mariadb-10.1.0/sql/sql_parse.cc 2014-06-27 04:50:34.000000000 -0700 +++ sql/sql_parse.cc 2014-09-02 02:34:31.000000000 -0700 @@ -4943,7 +4943,7 @@ Note: SQLCOM_CREATE_VIEW also handles 'ALTER VIEW' commands as specified through the thd->lex->create_view_mode flag. */ - res= mysql_create_view(thd, first_table, thd->lex->create_view_mode); + res= mysql_create_view(thd, first_table, thd->lex->create_view_mode, thd->lex->create_view_force); break; } case SQLCOM_DROP_VIEW: --- ../mariadb-10.1.0/sql/sql_yacc.yy 2014-06-27 04:50:37.000000000 -0700 +++ sql/sql_yacc.yy 2014-09-05 17:19:29.000000000 -0700 @@ -1851,7 +1851,7 @@ statement sp_suid sp_c_chistics sp_a_chistics sp_chistic sp_c_chistic xa opt_field_or_var_spec fields_or_vars opt_load_data_set_spec - view_algorithm view_or_trigger_or_sp_or_event + view_algorithm view_or_trigger_or_sp_or_event view_force_option definer_tail no_definer_tail view_suid view_tail view_list_opt view_list view_select view_check_option trigger_tail sp_tail sf_tail udf_tail event_tail @@ -2446,6 +2446,7 @@ VIEW_CREATE_OR_REPLACE); Lex->create_view_algorithm= DTYPE_ALGORITHM_UNDEFINED; Lex->create_view_suid= TRUE; + Lex->create_view_force= VIEW_CREATE_NO_FORCE; /* initialize just in case */ } view_or_trigger_or_sp_or_event { @@ -15887,6 +15888,15 @@ | event_tail ; +view_force_option: + /* empty */ /* 411 - is there a cleaner way of initializing here? */ + { Lex->create_view_force = VIEW_CREATE_NO_FORCE; } + | NO_SYM FORCE_SYM + { Lex->create_view_force = VIEW_CREATE_NO_FORCE; } + | FORCE_SYM + { Lex->create_view_force = VIEW_CREATE_FORCE; } + ; + /************************************************************************** DEFINER clause support. @@ -15944,7 +15954,7 @@ ; view_tail: - view_suid VIEW_SYM table_ident + view_suid view_force_option VIEW_SYM table_ident { LEX *lex= thd->lex; lex->sql_command= SQLCOM_CREATE_VIEW; --- ../mariadb-10.1.0/sql/sql_view.cc 2014-06-27 04:50:36.000000000 -0700 +++ sql/sql_view.cc 2014-09-05 19:33:58.000000000 -0700 @@ -248,7 +248,7 @@ */ bool create_view_precheck(THD *thd, TABLE_LIST *tables, TABLE_LIST *view, - enum_view_create_mode mode) + enum_view_create_mode mode, enum_view_create_force force) { LEX *lex= thd->lex; /* first table in list is target VIEW name => cut off it */ @@ -259,7 +259,7 @@ DBUG_ENTER("create_view_precheck"); /* - Privilege check for view creation: + Privilege check for view creation with default (NO FORCE): - user has CREATE VIEW privilege on view table - user has DROP privilege in case of ALTER VIEW or CREATE OR REPLACE VIEW @@ -272,6 +272,7 @@ checked that we have not more privileges on correspondent column of view table (i.e. user will not get some privileges by view creation) */ + if ((check_access(thd, CREATE_VIEW_ACL, view->db, &view->grant.privilege, &view->grant.m_internal, @@ -285,6 +286,11 @@ check_grant(thd, DROP_ACL, view, FALSE, 1, FALSE)))) goto err; + if (force) { + res = false; + DBUG_RETURN(res || thd->is_error()); + } + for (sl= select_lex; sl; sl= sl->next_select()) { for (tbl= sl->get_table_list(); tbl; tbl= tbl->next_local) @@ -369,7 +375,7 @@ #else bool create_view_precheck(THD *thd, TABLE_LIST *tables, TABLE_LIST *view, - enum_view_create_mode mode) + enum_view_create_mode mode, enum_view_create_force force) { return FALSE; } @@ -391,7 +397,7 @@ */ bool mysql_create_view(THD *thd, TABLE_LIST *views, - enum_view_create_mode mode) + enum_view_create_mode mode, enum_view_create_force force) { LEX *lex= thd->lex; bool link_to_local; @@ -425,14 +431,13 @@ goto err; } - if ((res= create_view_precheck(thd, tables, view, mode))) + if (res= create_view_precheck(thd, tables, view, mode, force)) goto err; lex->link_first_table_back(view, link_to_local); view->open_type= OT_BASE_ONLY; - if (open_temporary_tables(thd, lex->query_tables) || - open_and_lock_tables(thd, lex->query_tables, TRUE, 0)) + if (open_temporary_tables(thd, lex->query_tables) || (!force && open_and_lock_tables(thd, lex->query_tables, TRUE, 0))) { view= lex->unlink_first_table(&link_to_local); res= TRUE; @@ -513,6 +518,7 @@ } } +if (!force) { /* prepare select to resolve all fields */ lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW; if (unit->prepare(thd, 0, 0)) @@ -612,6 +618,7 @@ } } #endif +} res= mysql_register_view(thd, view, mode); @@ -621,7 +628,7 @@ meta-data changes after ALTER VIEW. */ - if (!res) + // if (!res) + if (!res && !force) /* 411 - solves segfault problems with CREATE FORCE VIEW option sometimes */ tdc_remove_table(thd, TDC_RT_REMOVE_ALL, view->db, view->table_name, false); if (mysql_bin_log.is_open()) @@ -908,6 +915,8 @@ fn_format(path_buff, file.str, dir.str, "", MY_UNPACK_FILENAME); path.length= strlen(path_buff); if (ha_table_exists(thd, view->db, view->table_name, NULL)) { if (mode == VIEW_CREATE_NEW) --- ../mariadb-10.1.0/mysql-test/t/view.test 2014-06-27 04:50:30.000000000 -0700 +++ mysql-test/t/view.test 2014-09-06 00:23:32.000000000 -0700 @@ -5263,4 +5263,17 @@ --echo # ----------------------------------------------------------------- --echo # -- End of 10.0 tests. --echo # ----------------------------------------------------------------- + +create no force view v1 as select 1; +drop view if exists v1; + +create force view v1 as select 1; +drop view if exists v1; + +create force view v1 as select * from missing_base_table; +drop view if exists v1; + +--echo # ----------------------------------------------------------------- +--echo # -- End of 10.1 tests. +--echo # ----------------------------------------------------------------- SET optimizer_switch=@save_optimizer_switch;