MySQL Bugs: #76484: show create view won't work if view references invalid column

Bug #76484	show create view won't work if view references invalid column
Submitted:	25 Mar 2015 16:36	Modified:	14 Jul 2015 6:16
Reporter:	David Norman	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.1/5.5/5.6	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	invalid column, show create view

Description:
If a view references an invalid column, then show create view won't work.

This is particularly annoying if you want to determine why the view isn't working to fix it!

Bizarrely, if it references an invalid table, it will work.

How to repeat:
create table test
(
	x integer not null,
	y integer not null
);

create or replace view test_view as select * from test;

alter table test drop column y;

# As expected, this won't work.
select * from test_view;

# But this won't work either.
show create view test_view;

drop table test;

# As expected, this still won't work.
select * from test_view;

# But bizarrely, this now does work!
show create view test_view;

Suggested fix:
Make show create view work, regardless of the state of the tables and views it references.

Thank you for the bug report. 5.1/5.5/5.6 versions converted the warning on error disregarding the sql_mode:

mysql 5.6 > select * from test_view;
ERROR 1356 (HY000): View 'l.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql 5.6 >
mysql 5.6 > # But this won't work either.
mysql 5.6 > show create view test_view;
ERROR 1356 (HY000): View 'l.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql 5.6 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| innodb_version          | 5.6.24                               |
| protocol_version        | 10                                   |
<CUT>
mysql 5.5 > # As expected, this won't work.
mysql 5.5 > select * from test_view;
ERROR 1356 (HY000): View 'l.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql 5.5 >
mysql 5.5 > # But this won't work either.
mysql 5.5 > show create view test_view;
ERROR 1356 (HY000): View 'l.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql 5.5 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| innodb_version          | 5.5.43                               |
| protocol_version        | 10                                   |
<CUT>
mysql 5.1 > # As expected, this won't work.
mysql 5.1 > select * from test_view;
ERROR 1356 (HY000): View 'l.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql 5.1 >
mysql 5.1 > # But this won't work either.
mysql 5.1 > show create view test_view;
ERROR 1356 (HY000): View 'l.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql 5.1 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.74-Win X64      |
<cut>
mysql 5.0 > # As expected, this won't work.
mysql 5.0 > select * from test_view;
ERROR 1356 (HY000): View 'l.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql 5.0 >
mysql 5.0 > # But this won't work either.
mysql 5.0 > show create view test_view;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| View      | Create View                                                                                                                                               |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_view` AS select `test`.`x` AS `x`,`l`.`test`.`y` AS `y` from `test` |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql 5.0 > SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                       |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1356 | View 'l.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.0 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.0.97-Win X64      |
<CUT>
mysql 5.7 > # As expected, this won't work.
mysql 5.7 > select * from test_view;
ERROR 1356 (HY000): View 'l.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql 5.7 >
mysql 5.7 > # But this won't work either.
mysql 5.7 > show create view test_view;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View      | Create View                                                                                                                                           | character_set_client | collation_connection |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| test_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_view` AS select `test`.`x` AS `x`,`test`.`y` AS `y` from `test` | cp850                | cp850_general_ci     |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set, 1 warning (0.00 sec)

mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+-------------------------------------+
| Variable_name           | Value                               |
+-------------------------+-------------------------------------+
| innodb_version          | 5.7.8                               |
| protocol_version        | 10                                  |

I filed this 4.5 years ago
https://bugs.mysql.com/bug.php?id=54139

Shane,

Thanks for pointing this out. I tried to find any previous instances in the database, but searching for '"show create view" invalid column' returned over 500 entries, which quickly moved away from anything to do with show create view, so I guessed there wasn't anything - or if there was, I was unlikely to find it.

David.

Fixed in 5.7.0 as a duplicate of

Bug#61718 Cannot SHOW CREATE VIEW if underlying tabels are ALTERed