WL#7167: Change DDL to update rows for view columns in DD.COLUMNS and other dependent values.
Affects: Server-8.0
—
Status: Complete
Schema of new data dictionary assumes that a) Rows with information about view columns are stored in the mysql.COLUMNS table while creating the view. b) Rows with information about view columns in the mysql.COLUMNS are updated each time type of view column changes, i.e. each time when type of columns in one of the base table changes. c) Each time when view becomes updatable or non-updatable column mysql.TABLE.IS_UPDATABLE is updated. d) Each time when keys in parent table changes then mysql.FOREIGN_KEYS.UNIQUE_CONSTRAINT_ID is updated. INFORMATION_SCHEMA.COLUMNS is modified to list information from the New DD table mysql.COLUMNS. In the current code only information about columns of Tables are stored in the mysql.COLUMNS table. Hence INFORMATION_SCHMEA.COLUMNS lists only columns of Tables. Columns of views are not listed. Even "SHOW COLUMNS" from views does not list column information. As part of this WL, view's column information is stored into the DD table mysql.COLUMNS. Even implementation of DDL statements affecting underlying tables of view is extended to update view columns information in mysql.COLUMNS and mysql.TABLE.IS_UPDATABLE. Note: Extending Add user, Drop user, Grant and revoke statements to update view validity is outside the scope of this WL. DDL operations extended to update view columns are not automic, there is a possibility of things going out of sync in fatal error or crash scenarios. Making these operations automic is outside scope of this WL.
F-1: Create/Alter VIEW statement must store rows with information
about view columns in the mysql.COLUMNS table.
As of now, rows with information about view columns are not
stored in the mysql.COLUMNS table.
As part of this WL, modify View Creation and alter code to store
the rows with view columns information in mysql.COLUMNS table.
F-2: Create/Alter VIEW statement must store information about the base
tables and views used by a view in the mysql.VIEW_TABLE_USAGE table.
This work is mostly about storing base tables and views
used in the FROM clause of a view query in mysql.VIEW_TABLE_USAGE
table.
F-3: Create/Alter VIEW statement must store information about stored
function used by the view query in mysql.VIEW_ROUTINE_USAGE table.
This work is about introducing new DD table VIEW_ROUTINE_USAGE
and storing Stored functions used by the views.
Definition of mysql.VIEW_ROUTINE_USAGE:
view_id BIGINT UNSIGNED NOT NULL,
routine_catalog VARCHAR(64) NOT NULL COLLATE utf8_bin/utf8_tolower_ci,
routine_schema VARCHAR(64) NOT NULL COLLATE utf8_bin/utf8_tolower_ci,
routine_name VARCHAR(64) NOT NULL COLLATE utf8_general_ci,
PRIMARY KEY (view_id, routine_catalog, routine_schema, routine_name),
FOREIGN KEY (view_id) REFERENCES tables(id),
KEY (routine_catalog, routine_schema, routine_name)
F-4: Drop VIEW must drop information stored for view in mysql.COLUMNS,
mysql.VIEW_TABLE_USAGE and mysql.VIEW_ROUTINE_USAGE tables.
F-5: Alter base table to change column type must update the view column
type.
This work is mostly about finding all the views using base
table from mysql.VIEW_TABLE_USAGE and update view columns information
using the new column type.
View columns information update operation is transitive as views
depending updated views are also updated and so on until there are
no more dependent views.
F-6: Following DDL operations must update view status as "INVALID",
- Alter base table to drop or rename column used by view.
- Drop or rename base table.
- Drop or rename view.
- Alter view used in FROM clause of view query.
This work is mostly about finding all the views using base table or
view being used in DDL operations mentioned above from the
mysql.VIEW_TABLE_USAGE table and update status of those view as
"INVALID".
View status update operation is transitive as views depending
updated views are also updated and so on until there are
no more dependent views.
F-7: Following DDL operations must update view status as "VALID",
- Alter base table to add or rename column.
- Create or rename base table.
- Create or rename View.
- Alter view used in FROM clause of view query.
This work is mostly about finding all the views using base table or
view being used in DDL operations mentioned above from the
mysql.VIEW_TABLE_USAGE table. Check if view query can be prepared
correctly with new definition of table or view. If yes then
update all view columns, insert view column information if
they are missing and mark status of the view as "VALID".
View status update operation is transitive as views depending
updated views are also updated and so on until there are
no more dependent views.
F-8: Stored function drop must set state of views using it as invalid.
This work is mostly about finding all the views using the
stored function in mysql.VIEW_ROUTINE_USAGE table and mark view
as invalid.
View status update operation is transitive as views depending
updated views are also updated and so on until there are
no more dependent views.
F-9: Create stored function with same name as stored function used by
views in invalid state must update view status.
This work is mostly about finding all the view using stored
function with same name in mysql.VIEW_ROUTINE_USAGE table.
Check if view query can be prdepared correctly. If yes then
update all view columns, insert view column information if
they are missing and mark status of the view as "VALID".
View status update operation is transitive as views depending
updated views are also updated and so on until there are
no more dependent views.
F-10: Drop database must set state as "invalid" for all the views using
table/views/stored functions defined in the database being dropped.
F-11: SELECT on INFORMATION_SCHEMA.COLUMNS should display columns of
views.
F-12: Proper Warning and Note messages should be reported for SELECT
on INFORMATION_SCHEMA.COLUMNS of views, if view is invalid.
This work is mostly about checking status of each view whose
columns are listed by SELECT on INFORMATION_SCHEMA.COLUMNS.
If view state is invalid then report proper warning or note
messages.
F-13: SHOW COLUMNS from views should display columns of views.
F-14: Proper warning or error messages should be reported for
SHOW COLUMNS on INVALID views.
F-15: Create view must update is_updatable state of it and also update
is_updatable state of views using it.
This work is mostly about finding all the views using View
being created. Check if each view is still updatable or not
with new view definition and update the same in column
mysql.TABLES.IS_UPDATABLE.
F-16: Rename view update is_updatable state of views referencing it.
This work is mostly about finding all the views using new view name
used in RENAME operation. Check if each view is still updatable
or not with the view definition and update the same in column
mysql.TABLES.IS_UDPATABLE.
F-17: Alter index of parent table must update
mysql.FOREIGN_KEYS.UNIQUE_CONSTRAINT_ID
of child table.
New Data Dictionary assumes that rows with view column information is
stored in the New DD table mysql.COLUMNS and updated each time type of view
column changes i.e. type of column in one of the base table changes.
Similar assumptions are made about mysql.VIEWS.IS_UPDATABLE and about
mysql.FOREIGN_KEYS.UNIQUE_CONSTRAINT_ID. As part of WL these assumptions
are implemented.
A) Handling View column Information:
==============================================
INFORMATION_SCHEMA.COLUMNS is modified to list information from the New DD
table mysql.COLUMNS. In the current code only information about columns of
Tables are stored in the mysql.COLUMNS table. Hence INFORMATION_SCHEMA.COLUMNS
lists only columns of Tables. Columns of views are not listed. Even "SHOW
COLUMNS" from views does not list column information. As part of this WL, view
code is modified to insert rows with view's column information in DD table
mysql.COLUMNS.
A.a) Changes to View Creation code(F-1 to F-4, F-11 and F-13):
--------------------------------------------------------------
To insert rows with view column information in mysql.COLUMNS
following changes are introduced in the View Create code,
*) Similar to CREATE TABLE .. SELECT method, from the SELECT_LEX
prepared for a view query Create_field objects are created.
Fields Items of SELECT_LEX is used to prepare Create_field
objects.
*) DD::View object has collection object to hold the column
information. Create_field objects prepared from the SELECT_LEX
of the query are used to populate column information in DD::Views
object. Existing method DD::fill_dd_columns_from_create_fields()
to store column information for tables is called for views as
well to store column information.
*) store() method of View object is used to store columns of View
similar to Table object.
Note: In the current code, alter view operation drops exiting view
information from the DD table and inserts new View object
created with the new definition. Hence with the above changes
to create code, rows with column's information prepared from
new view definition are inserted to mysql.COLUMNS table while
altering the view.
For the view column, metadata columns name and ordinal positions
are related to the view. Followings metadata columns are related to
the base table, view's column, stored function return type or
evaluated type for expressions,
type
is_nullable
is_zerofill
is_unsigned
char_length
numeric_precision
numeric_scale
datetime_precision
colletion_id
has_no_default
default_value
default_value_utf8
default_option
update_option
column_type_utf8
Other columns are set to NULL.
With these changes, INFORMATION_SCHEMA.COLUMNS and "SHOW COLUMNS"
list columns from the view as well.
To find views using table, view and stored function, information
about these objects used by views is stored in the tables
mysql.VIEW_TABLE_USAGE and mysql.VIEW_ROUTINE_USAGE while creating/
Altering the view.
*) While preparing DD::View object for a view, View_table collection
type object is populated with Table names and View names used
by the view.
store() implementation for views stores view to base table and
views used in view query relation in DD table
mysql.VIEW_TABLE_USAGE.
*) New table mysql.VIEW_ROUTINE_USAGE is introduced to store the
relation between view and stored functions used by it.
While preparing DD::View object for view, stored functions
used by views are stored with new collection type member
View_routines of DD::View.
store() implementation for views stores view to stored function
relation in new DD table mysql.VIEW_ROUTINE_USAGE.
Note: drop() implementation for view, drops View_table and
View_routine collection object's content too. Which removes
entries for view from VIEW_TABLE_USAGE and
VIEW_ROUTINE_USAGE tables.
A.b) View column type update (F-5):
-----------------------------------
For ALTER TABLE operation after successfully altering the table and
removing the back up of the old table, column information of all
the views referencing altered table are modified.
For ALTER VIEW after storing the new definition by calling method
mysql_register_view() column information of all the views
referencing alter table are modified.
For ALTER TABLE/VIEW operation following changes is introduced to
modify column information of all the views using it,
a) Read views using base table or view from the information stored
in mysql.VIEW_TABLE_USAGE. Modification to view column metadata
might affect others views referencing them. So read all the views
referencing listed views and so on, until no more dependent
views are found.
b) Columns of all the views are updated as below once the changes
to table or view is completed.
b.1) Open all views listed with MDL lock request type
MDL_EXCLUSIVE.
Note: In Lock table mode, while opening views and its
base table lock table mode is temporarily disabled
by setting LTM_NONE.
These tables are closed explicitly in
close_thread_tables().
If view open fails with DEADLOCK or LOCK TIMEOUT issue
then error is reported and transaction is rolled back.
b.2) Prepare SELECT_LEX for view query with new definition
of table view.
b.3) Update view columns metadata using current ALTER VIEW
code i.e. by calling mysql_register_view(VIEW_ALTER).
A.c) Handling View status(VALID/INVALID) (F-6 to F-10):
--------------------------------------------------------
There is a possibility of view(s) becoming INVALID because DDL
operations on the base tables, views and stored functions used
by it.
Following table contains DDL operations on the base table, view and
stored functions because of which view can become INVALID. Following
tables even has list of DDL operations because of which INVALID
view can become VALID again.
+-------------------------------+--------------------------------+
|DDLs sets state to 'INVALID' | DDLs reset state to 'VALID' |
| | |
|-------------------------------+--------------------------------|
| | |
|ALTER TABLE org_tbl DROP | ALTER TABLE org_tbl CHANGE |
| COLUMN org_col; | any_col TO |
| | org_col ; |
|(Drop columns referenced by | (Rename any column to Column |
| views from the base table) | name referenced from the |
| | base tables by the views) |
| | |
| | |
| | ALTER TABLE org_tbl |
| | ADD org_col INT |
| | (Add new column with name |
| | referenced from the base |
| | base table by the views) |
| | |
|-------------------------------+--------------------------------|
| | |
|DROP TABLE org_tbl | CREATE TABLE org_tbl ... |
|(Drop base table used by views)| (Create new table "org_tbl" |
| | with same name for the |
| | columns referenced by views) |
| | |
| | RENAME TABLE any_tbl to |
| | org_tbl; |
| | (Rename any table having same |
| | column names referenced |
| | by the views to the table |
| | name "org_tbl") |
| | |
|-------------------------------+--------------------------------|
|RENAME TABLE org_tbl to any_tbl| CREATE TABLE org_tbl ... |
|(Rename tables referenced by | (Create new table "org_tbl" |
| view to any_tbl) | with same names for the |
| | columns referenced by views) |
| | |
| | RENAME TABLE any_tbl to |
| | org_tbl; |
| | (Rename any table having same |
| | column names referenced by |
| | views to the table name |
| | "org_tbl") |
| | |
|-------------------------------+--------------------------------|
|ALTER VIEW new_query | ALTER VIEW old_query |
|(Change names of the View | (Change names of the view |
| columns or Drop View columns | columns or add new columns |
| referenced by other views) | with same name referenced |
| | by other views) |
| | |
|-------------------------------+--------------------------------|
|DROP VIEW org_vw | CREATE VIEW org_vw |
|(DROP view org_vw referenced by| (Org_vw having view query |
| other views) | with the same name for |
| | columns) |
| | |
| | RENAME TABLE any_vw to org_vw |
| | (Rename any view having same |
| | column name referenced by |
| | other views to the view name |
| | "org_vw") |
| | |
|-------------------------------+--------------------------------|
|DROP STORED FUNCTION sf | CREATE STORED FUNCTION sf |
|(All views using stored | (sf with same name and |
| function 'sf' in view query | parameters) |
| will be INVALID) | |
| | |
+-------------------------------+--------------------------------+
|DROP DATABASE org_db | Recreate org_db with same |
|(All views referencing table, | name for table, view having |
| view or store function objects| same name for column and |
| from db be INVALID) | stored function with same |
| | name and parameter. |
| | |
+-------------------------------+--------------------------------+
When view is invalid, proper warning message is reported by the
SELECT on INFORMATION_SCHEMA.COLUMNS and error message by
"SHOW COLUMNS".
State of the view is stored in mysql.TABLES.options. Option
introduced to store state of the view is "view_valid".
For all these operations, method mentioned above in A.b is used
with some additional changes to update view status.
A.c.1) For DDL operations on the Base Table or View:
----------------------------------------------------
Following changes are done to update view columns and view status
(Additional changes are highlighted with **)
a) Read views using base table or view from the information
stored in mysql.VIEW_TABLE_USAGE. Modification to view column
metadata might affect others views referencing them. So read
all the views referencing listed views and so on, until no
more dependent views are found.
b) Columns of all the views are updated as below once the changes
to table or view is completed.
**b.1) If Base table or View is dropped then mark all views
using it as "INVALID" after acquiring MDL_EXCLUSIVE
lock on views.
b.2) Otherwise Open all views listed with MDL lock request
type MDL_EXCLUSIVE.
Note: In Lock table mode, while opening views and its
base table lock table mode is temporarily disabled
by setting LTM_NONE.
These tables are closed explicitly in
close_thread_tables().
If view open fails with DEADLOCK or LOCK TIMEOUT issue
then error is reported and transaction is rolled back.
b.3) Prepare SELECT_LEX for view query with new definition
of table/view.
**b.4) If b.3 step fails because of changes to base table or
view definition then mark View as INVALID.
b.5) Update view columns metadata using current ALTER VIEW
code i.e by calling mysql_register_view(VIEW_ALTER).
**b.6) If view state was INVALID before mark it as VALID.
A.c.2) For DDL operations on the stored functions:
--------------------------------------------------
For DDL operations related to Stored functions following code
changes are introduced to CREATE and DROP Stored functions,
a) Read views using stored function from the information stored
in mysql.VIEW_ROUTINE_USAGE. Modification to view column metadata
might affect others views referencing them. So read all the views
referencing listed views form mysql.VIEW_TABLE_USAGE and so on,
until no more dependent views are found.
b) Views are marked as VALID or INVALID as below
b.1) If stored function is dropped then mark all views using
it as "INVALID" after acquiring MDL_EXCLUSIVE lock on
views.
b.2) Otherwise Open all views listed with MDL lock request
type MDL_EXCLUSIVE.
Note: In Lock table mode, while opening views and its base
table lock table mode is temporarily disabled by
setting LTM_NONE.
These tables are closed explicitly in
close_thread_tables().
If view open fails with DEADLOCK or LOCK TIMEOUT issue
then error is reported and transaction is rolled back.
b.3) Prepare SELECT_LEX for view query with new definition of
Stored function.
b.4) If b.3 fails because of changes to stored function
definition then mark view as INVALID.
b.5) Update view columns metadata using current ALTER VIEW
code i.e. by calling mysql_register_view(VIEW_ALTER).
b.6) If view state was INVALID before mark it as VALID.
A.d) UDF to check view status (F-12, F-14):
--------------------------------------------
To verify VIEW state new UDF function
"INTERNAL_CHECK_VIEW_WARNINGS_OR_ERROR" is introduced. This function
check view status and then report WARNING or NOTE message. UDF
function is used in the view query of INFORMATION_SCHEMA.COLUMN.
SHOW COLUMN uses same function to report a NOTE or error message.
View column information stored in mysql.columns table and view_valid
value from options, is used by query INFORMATION_SCHEMA views and SHOW
statements.
B) Handling mysql.TABLE.IS_UPDATABLE(F-15, F-16):
=================================================
Information about view is updatable or not updatable is stored in the
DD table while Creating or Altering the view. But if view is using
another view in the query then there is chance that view becomes
updatable or not-updatable depending the other views updatable status.
In the current code, view updatable information is not handled in such
cases.
Following change is introduced to update IS_UPDATABLE value of view
using view being altered,
B.a) Read views using base table or view from the information stored
in mysql.VIEW_TABLE_USAGE. Modification to view column metadata
might affect others views referencing them. So read all the views
referencing listed views and so on, until no more dependent
views are found.
B.b) Views are marked as updatable or non-updatable as below,
b.1) Open all views listed with MDL lock request type
MDL_EXCLUSIVE.
Note: In Lock table mode, while opening views and its base
table lock table mode is temporarily disabled by
setting LTM_NONE.
These tables are closed explicitly in
close_thread_tables().
If view open fails with DEADLOCK or LOCK TIMEOUT issue
then error is reported and transaction is rolled back.
b.2) Prepare SELECT_LEX for view query with new definition of
view being altered.
b.3) Mark view is updatable or non-updatable depending on
the new definition of view being altered.
b.4) Update view metadata using current ALTER VIEW code
i.e. by calling mysql_register_view(VIEW_ALTER).
C) Handling mysql.FOREIGN_KEYS.UNIQUE_CONSTRAINT_ID(F-17):
=======================================================
Currently mysql.FOREIGN_KEYS table is not used.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.