Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
5.1.67, 5.2.14, 5.3.12, 5.5.35, 10.0.8
-
None
-
Ubuntu Precise Pangolin
Description
From MySQL bug #61718 (see below for explanation why):
Description:
If an underlying table changes SHOW CREATE VIEW is broken in 5.1 and 5.5 - but not in 5.0
This ia a problem because the VIEW definition needs to be adjusted. That can be difficult it you cannot display it as it was created.
Affects various GUI clients.
How to repeat:
SELECT VERSION(); -- 5.0.90-community-nt |
USE test; |
DROP TABLE IF EXISTS k; |
CREATE TABLE k (a VARCHAR(20)); |
DROP TABLE IF EXISTS l; |
CREATE TABLE l (b VARCHAR(20)); |
CREATE VIEW m AS SELECT k.a, l.b FROM k,l; |
ALTER TABLE `test`.`k` CHANGE `a` `aa` VARCHAR(20); |
SHOW CREATE VIEW m; --success |
SHOW FULL FIELDS FROM m; |
/*
|
Error Code : 1356
|
View 'test.m' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them*/
|
SELECT VERSION(); -- 5.1.57-community |
USE test; |
DROP TABLE IF EXISTS k; |
CREATE TABLE k (a VARCHAR(20)); |
DROP TABLE IF EXISTS l; |
CREATE TABLE l (b VARCHAR(20)); |
DROP VIEW IF EXISTS m; |
CREATE VIEW m AS SELECT k.a, l.b FROM k,l; |
ALTER TABLE `test`.`k` CHANGE `a` `aa` VARCHAR(20); |
SHOW CREATE VIEW m; |
/*
|
Error Code : 1356
|
View 'test.m' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them */
|
SHOW FULL FIELDS FROM m; -- same error |
SELECT * FROM `information_schema`.`VIEWS` WHERE table_name = 'm' -- success!! |
So workaround is to SELECT from I_S.
Suggested fix:
Restore 5.0 behaviour. If SELECT from I_S can show the VIEW then also SHOW should.
-----------
We use MariaDb, and Oracle make registering and entering a comment on an existing bug way too difficult!
This issue was originally raised as MySQL bug #54139 (1 June 2010) and then again as bug #61718 (1 July 2011) with more information. However, #61718 is closed, but the only progress was to note it as a known bug in the 5.7.0 changelog! It is noted that it affects late versions of 5.0, 5.1, 5.5, etc onwards.
Meanwhile bug #54139 was closed as a duplicate of #61718 on 10 January 2013, so there is no open bug report anywhere, and just an acknowledgement of the bug in the 5.7.0 changelog.
There is a workaround:
SELECT * FROM `INFORMATION_SCHEMA`.`VIEWS` WHERE TABLE_SCHEMA='database' AND TABLE_NAME='view_name' |
but all of the sensible SQL commands for displaying the VIEW no longer work:
MariaDB [private_17_production]> show create view data_view_854; |
ERROR 1356 (HY000): View 'private_17_production.data_view_854' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
MariaDB [private_17_production]> show create table data_view_854; |
ERROR 1356 (HY000): View 'private_17_production.data_view_854' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
MariaDB [private_17_production]> desc data_view_854; |
ERROR 1356 (HY000): View 'private_17_production.data_view_854' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
This just makes diagnosing a problem with a VIEW difficult - if you cannot see the definition of the VIEW then you cannot work out what needs to be fixed: the VIEW or the underlying tables (and which ones).
There is a comment on bug #61718:
What is a potential bug here is that query to I_S does NOT produce error in 5.1, while SELECT and SHOW commands do...
In other words, make it impossible to diagnose VIEW errors rather than difficult!
Hopefully MariaDb can make better progress...
Despite obscure information in the public reports or the lack thereof, the bug is actually fixed in 5.7. Now SHOW CREATE VIEW produces the desired output with a warning about referenced tables.
Here is the commit comment which provides more details:
revno: 4635
revision-id: raghav.kapoor@oracle.com-20121005072219-jgkpyjuq5c3ogvjs
parent: mysql-builder@oracle.com-20121005070911-e8d741cf8hmucsts
committer: Raghav Kapoor <raghav.kapoor@oracle.com>
branch nick: mysql-trunk-bug-12762393
timestamp: Fri 2012-10-05 12:52:19 +0530
message:
BUG#12762393 - CAN NOT SHOW CREATE VIEW IF UNDERLYING TABLES
ARE ALTERED
BACKGROUND:
There was refactoring done in 5.1, which changed the way the
warnings are suppressed in the server. Before the refactoring,
the warnings were suppressed (or propagated to errors) based
on the global flag.
Afterwards the warnings were suppressed/propagated by local
instances of Internal_error_handler on a per case base.
This bug is an indication of an issue with that refactoring.
The issue was that ER_BAD_FIELD_ERROR was forgotten to be
handled when opening the underlying tables in SHOW CREATE VIEW.
FIX:
As a fix for this bug, added a case to handle ER_BAD_FIELD_ERROR
in Show_create_error_handler to generate a warning instead of error
when columns of view's underlying table is changed.
Also some redundant error codes have been removed in
Show_create_error_handler and in TABLE_LIST::hide_view_error()
by adding the appropriate comments.
It would be nice to backport the bugfix into 10.0 line if possible.