Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    29

    Unanswered: Mysql table could open

    hi,

    I have a problem to open and/or describe table. The errors I had are


    mysql_stgssl>desc ATL1_USER_DETAILS_VW;
    ERROR 1356 (HY000): View 'atl1.ATL1_USER_DETAILS_VW' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
    mysql_stgssl>select * from ATL1_USER_DETAILS_VW;
    ERROR 1356 (HY000): View 'atl1.ATL1_USER_DETAILS_VW' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
    mysql_stgssl>

    thanks,

    bvo

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    You have your error right there.
    Seems fairly obvious.
    1) You are trying to describe a view, which is perfectly valid but it's not the real table that you might be after, and will probably only offer information on the select query that generated it.
    2) One/all of the underlying tables, table columns or functions that the view uses does not exist
    3) The user you are currently trying to use to DESCRIBE the table does not have this privilege.

  3. #3
    Join Date
    May 2004
    Posts
    29
    I'm new with MySQl and do not know how to correct it... Would you please show me how? I am not create this table/view. Someone did and that person is somewhere I do not know.

    Is there a way I can select the table name, user name to see who created it? I tried to select from dba_tables like Oracle and couldn't find the dba_tables or user_tables existed... Search on web for data dictionary chart for MySQL and again couldn't find it.

    Thanks.

    BV

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    At the cmd line you can execute "SHOW GRANTS" which will show you what permissions you have. (this may not work if you have insufficient permissions).
    You can also do "SHOW TABLES" to show all the tables available to yourself.
    To get information about the view you can use the information_schema table which is standard throughout SQL i believe. Again, if you don't have sufficient permissions you will be unable to use this database.
    Code:
    USE information_schema;
    SELECT * FROM VIEWS WHERE TABLE_NAME = 'ATL1_USER_DETAILS_VW'\G
    The above should give you a VIEW_DEFINITION part which will specify the full query that is being executed. If you have missing tables there's nothing you can do about it unless you have a backup of previous data that may contain those tables, in which case you can restore them.
    You will also see the DEFINER part which should specify which user created the view and thus which user can also use it. If you don't have VIEW permissions you won't be able to use the view as the current user you are.
    Last edited by aschk; 10-17-07 at 06:40.

  5. #5
    Join Date
    May 2004
    Posts
    29
    Thanks very much for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •