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...