MariaDB Patch: CREATE [[NO] FORCE] VIEW Options

MariaDB LogoBelow is my patch that implements the CREATE [[NO] FORCE] VIEW options against MySQL/MariaDB 10.1.0.

It adds two new options that look like this:

  1. CREATE NO FORCE VIEW v1 AS SELECT * FROM TABLE1; — base TABLE1 must exist, as before
  2. CREATE FORCE VIEW v1 AS SELECT * FROM TABLE1; — base TABLE1 doesn’t need to exist

Notes:

  • these options follow the Oracle Enterprise options fairly closely. NO FORCE works like the old default – a user needs database, table, column access and CREATE VIEW grant to create a view (more or less). FORCE allows a user to create a view with only database access and CREATE VIEW grant and no underlying base table. At SELECT time, full access control and grant checking is performed, and an error will occur if those constraints are not met.
  • views are more complicated than one would expect, and can be composed of base tables, derived tables, INFORMATION_SCHEMA (IS), and other views. The only table object not allowed is a temporary table
  • CREATE FORCE VIEW is an important option when managing large sets of views when you don’t want to track the creation sequence, or when creating views via program. An example is mysqldump, which can be simplified by replacing the current temporary tables ordering workarounds with FORCE VIEW.
  • It’s a fairly solid patch. I think the best thing is to commit it to alpha and let it bake for a while.
  • One permutation that will need special handling is this: CREATE FORCE VIEW view1 AS SELECT * FROM table1; Since * is not resolved to column names by FORCE, currently ” AS SELECT * AS ” is generated, causing an error. So just use explicit column names like CREATE FORCE VIEW view1 SELECT id, col1, col2 FROM table1; See this bug.
  • 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+ test permutations. It passes. 🙂

$ 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;

This entry was posted in API Programming, Linux, MySQL, Open Source, Oracle, Storage, Tech. Bookmark the permalink.

One Response to MariaDB Patch: CREATE [[NO] FORCE] VIEW Options

  1. Alex says:

    When MySQL commits an trotaacnisn that involves > 1 XA storage engine, it uses the 2 phase commit protocol in the commit. MySQL refers to this an an internal XA trotaacnisn. So, there are prepares to all of the storage engines followed by commits to all of the storage engines. If the MySQL binary log is enabled, trotaacnisns involving at least 1 XA storage engine also use a 2 phase commit protocol. The trotaacnisn is prepared in storage engines, the trotaacnisn is logged in the binlog, and finally the trotaacnisn is committed in the storage engines.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.