Bug #86778 SHOW FIELDS FROM A VALID VIEW FAILS WITH AN INVALID VIEW ERROR
Description:
SHOW FIELDS from a VALID view fails with the following error
View 'v6' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
How to repeat:
PATCH:
=========================================================================
diff --git a/sql/dd_sql_view.cc b/sql/dd_sql_view.cc
index d012d83..c40760a 100644
--- a/sql/dd_sql_view.cc
+++ b/sql/dd_sql_view.cc
@@ -596,6 +596,7 @@ static bool update_view_metadata(THD *thd,
if (prepare_view_tables_list<T>(thd, db, tbl_or_sf_name, &views))
return true;
+ DEBUG_SYNC(thd, "after_preparing_view_tables_list");
/*
Open views and update views metadata.
Test:
=========================================================================
CREATE TABLE t1 (f1 int);
CREATE VIEW v1 AS SELECT * FROM t1;
connect (con1, localhost, root,,);
SET DEBUG_SYNC="after_preparing_view_tables_list SIGNAL alter_view WAIT_FOR go";
--send DROP TABLE t1;
connection default;
SET DEBUG_SYNC='now WAIT_FOR alter_view';
ALTER VIEW v1 AS select 13;
SET DEBUG_SYNC='now SIGNAL go';
connection con1;
--reap
FLUSH TABLES;
connect (con2, localhost, root,,);
# SHOW FIELDS from view v1 fails.
SHOW FIELDS FROM v1;
connection default;
DROP VIEW v1;
disconnect con1;
disconnect con2;
Suggested fix:
The issue is observed because of delay in locking referencing view after preparing list of referencing views.
In this time gap, the dependency on object being dropped is removed.
So, avoid validating views which are no more depedent on the table, view or stored function being dropped.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: SHOW FIELDS from a VALID view fails with the following error View 'v6' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356) How to repeat: PATCH: ========================================================================= diff --git a/sql/dd_sql_view.cc b/sql/dd_sql_view.cc index d012d83..c40760a 100644 --- a/sql/dd_sql_view.cc +++ b/sql/dd_sql_view.cc @@ -596,6 +596,7 @@ static bool update_view_metadata(THD *thd, if (prepare_view_tables_list<T>(thd, db, tbl_or_sf_name, &views)) return true; + DEBUG_SYNC(thd, "after_preparing_view_tables_list"); /* Open views and update views metadata. Test: ========================================================================= CREATE TABLE t1 (f1 int); CREATE VIEW v1 AS SELECT * FROM t1; connect (con1, localhost, root,,); SET DEBUG_SYNC="after_preparing_view_tables_list SIGNAL alter_view WAIT_FOR go"; --send DROP TABLE t1; connection default; SET DEBUG_SYNC='now WAIT_FOR alter_view'; ALTER VIEW v1 AS select 13; SET DEBUG_SYNC='now SIGNAL go'; connection con1; --reap FLUSH TABLES; connect (con2, localhost, root,,); # SHOW FIELDS from view v1 fails. SHOW FIELDS FROM v1; connection default; DROP VIEW v1; disconnect con1; disconnect con2; Suggested fix: The issue is observed because of delay in locking referencing view after preparing list of referencing views. In this time gap, the dependency on object being dropped is removed. So, avoid validating views which are no more depedent on the table, view or stored function being dropped.