How do I figure out why MySQL isn't letting me query a view?

Question

I'm using MySql 5.6. I'm querying a view and getting this error

mysql> select * FROM report_toc_item;
ERROR 1356 (HY000): View 'my_db.report_toc_item' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql>

I can see the view definition and there doesn't look like there's anything wrong

mysql>  SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE table_schema = 'my_db' and TABLE_NAME = 'report_toc_item';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| VIEW_DEFINITION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| select `ti`.`ID` AS `ID`,`ti`.`PARENT_ID` AS `PARENT_ID`,`ti`.`TOC_ID` AS `TOC_ID`,`ti`.`TITLE` AS `TITLE`,`ti`.`DESCRIPTION` AS `DESCRIPTION`,`ti`.`TYPE_ID` AS `TYPE_ID`,`ti`.`ORDER_NUM` AS `ORDER_NUM`,`ti`.`MY_OBJECT_SEGMENT_ID` AS `MY_OBJECT_SEGMENT_ID`,`ti`.`MY_OBJECT_SEGMENT_ORDER_NUM` AS `MY_OBJECT_SEGMENT_ORDER_NUM`,`ti`.`ELEMENT_ID` AS `ELEMENT_ID`,`ti`.`UNIT_TOC_ITEM_ID` AS `UNIT_TOC_ITEM_ID`,`ti`.`SHORT_NAME` AS `SHORT_NAME`,`ti`.`THIRD_PARTY_PROMPT_ID` AS `THIRD_PARTY_PROMPT_ID`,`pti`.`TYPE_ID` AS `PARENT_TYPE_ID` from (`my_db`.`toc_item` `ti` join `my_db`.`toc_item` `pti` on((`pti`.`ID` = `ti`.`PARENT_ID`))) where ((`ti`.`TYPE_ID` = 'sub_segment') and ((`pti`.`TYPE_ID` = 'lesson') or (`pti`.`TYPE_ID` = 'activity') or (`pti`.`TYPE_ID` = 'activity_practice') or (`pti`.`TYPE_ID` = 'unit_opener'))) |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

How do I figure out what columns and tables are invalid? Note that I'm logged in as root when running both commands.

Edit: Here is the result of "SHOW CREATE VIEW report_toc_item," as requested.

mysql> show create view report_toc_item;
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View               | Create View                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | character_set_client | collation_connection |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| report_toc_item | CREATE ALGORITHM=UNDEFINED DEFINER=`myuser`@`localhost` SQL SECURITY DEFINER VIEW `report_toc_item` AS select `ti`.`ID` AS `ID`,`ti`.`PARENT_ID` AS `PARENT_ID`,`ti`.`TOC_ID` AS `TOC_ID`,`ti`.`TITLE` AS `TITLE`,`ti`.`DESCRIPTION` AS `DESCRIPTION`,`ti`.`TYPE_ID` AS `TYPE_ID`,`ti`.`ORDER_NUM` AS `ORDER_NUM`,`ti`.`MY_OBJECT_SEGMENT_ID` AS `MY_OBJECT_SEGMENT_ID`,`ti`.`MY_OBJECT_SEGMENT_ORDER_NUM` AS `MY_OBJECT_SEGMENT_ORDER_NUM`,`ti`.`ELEMENT_ID` AS `ELEMENT_ID`,`ti`.`UNIT_TOC_ITEM_ID` AS `UNIT_TOC_ITEM_ID`,`ti`.`SHORT_NAME` AS `SHORT_NAME`,`ti`.`THIRD_PARTY_PROMPT_ID` AS `THIRD_PARTY_PROMPT_ID`,`pti`.`TYPE_ID` AS `PARENT_TYPE_ID` from (`toc_item` `ti` join `toc_item` `pti` on((`pti`.`ID` = `ti`.`PARENT_ID`))) where ((`ti`.`TYPE_ID` = 'sub_segment') and ((`pti`.`TYPE_ID` = 'lesson') or (`pti`.`TYPE_ID` = 'activity') or (`pti`.`TYPE_ID` = 'activity_practice') or (`pti`.`TYPE_ID` = 'unit_opener'))) | utf8                 | utf8_general_ci      |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

Edit 2:

Here are the grants assigned for the user in question

mysql> show grants for 'myuser'@'localhost';
+-------------------------------------------------+
| Grants for myuser@localhost                |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'localhost' |
+-------------------------------------------------+
1 row in set (0.00 sec)

Answer 1

The full error message is:

... or definer/invoker of view lack rights to use them

Check the view definer, and if there is one, check that the definer account can execute the select.

To see the definer, use:

SHOW CREATE VIEW

SELECT DEFINER FROM INFORMATION_SCHEMA.VIEWS WHERE ...

Answer 2

DEFINER=myuser@localhost SQL SECURITY DEFINER means that the view will be executed with the rights of myuser@localhost (not as your current root login), see the documentation:

• A stored program or view that executes in definer security context executes with the privileges of the account named by its DEFINER attribute. These privileges may be entirely different from those of the invoking user. The invoker must have appropriate privileges to reference the object (for example, EXECUTE to call a stored procedure or SELECT to select from a view), but when the object executes, the invoker's privileges are ignored and only the DEFINER account privileges matter. If this account has few privileges, the object is correspondingly limited in the operations it can perform. If the DEFINER account is highly privileged (such as a root account), the object can perform powerful operations no matter who invokes it.
• A stored routine or view that executes in invoker security context can perform only operations for which the invoker has privileges. The DEFINER attribute can be specified but has no effect for objects that execute in invoker context.

So depending on what you want to achieve, either grant the required rights to myuser@localhost, change the definer to a user that has these rights or change the sql security to invoker. Usually, the second option will be used, unless you have reasons not to.

To change the definer or the sql security, you will have to alter (or drop and recreate) the view, including the whole as select ... part. You can use the output of show create view report_toc_item for that and just change it at the beginning. The default values for definer and sql security will be the current user and definer.

Answer 3

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

So check the following query that define the view work properly. Because if you delete any column from toc_item table then the view will not work.

select `ti`.`ID` AS `ID`,`ti`.`PARENT_ID` AS `PARENT_ID`,`ti`.`TOC_ID` AS TOC_ID\,`ti`.`TITLE` AS `TITLE`,`ti`.`DESCRIPTION` AS `DESCRIPTION`,`ti`.`TYPE_ID` AS `TYPE_ID`,`ti`.`ORDER_NUM` AS `ORDER_NUM`,`ti`.`MY_OBJECT_SEGMENT_ID` AS `MY_OBJECT_SEGMENT_ID`,`ti`.`MY_OBJECT_SEGMENT_ORDER_NUM` AS `MY_OBJECT_SEGMENT_ORDER_NUM`, `ti`.`ELEMENT_ID` AS `ELEMENT_ID`, `ti`.`UNIT_TOC_ITEM_ID` AS `UNIT_TOC_ITEM_ID`,`ti`.`SHORT_NAME` AS `SHORT_NAME`,`ti`.`THIRD_PARTY_PROMPT_ID` AS `THIRD_PARTY_PROMPT_ID`, `pti`.`TYPE_ID` AS `PARENT_TYPE_ID` from (`my_db`.`toc_item` `ti` join `my_db`.`toc_item` `pti` on((`pti`.`ID` = `ti`.`PARENT_ID`))) where ((`ti`.`TYPE_ID` = 'sub_segment') and ((`pti`.`TYPE_ID` = 'lesson') or (`pti`.`TYPE_ID` = 'activity') or (`pti`.`TYPE_ID` = 'activity_practice') or (`pti`.`TYPE_ID` = 'unit_opener')));

See the following example:

 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;

Source here