6

I have moved a database that contains views from one machine to another, and now the views have become broken. I know I need to recreate the old user on the new machine in order to fix it, but my question is:

How do I find out the username of the old user that created the view? I no longer have access to the old machine.

Thanks.

user3640967
  • 528
  • 2
  • 9
  • 16
  • https://dba.stackexchange.com/questions/9249/how-do-i-change-the-definer-of-a-view-in-mysql – Channa Apr 18 '21 at 06:24

2 Answers2

9

Use the command SHOW CREATE VIEW viewname
You may see further information in https://dev.mysql.com/doc/refman/5.6/en/show-create-view.html

Kenneth L.
  • 116
  • 3
  • "#1356 - View 'database.table' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" - this is why I need to change the definer, which is why I need to find out what the definer's name is! :) – user3640967 Oct 16 '15 at 14:41
  • even account with SUPER privilege like root ? – Kenneth L. Oct 16 '15 at 14:44
  • Yes - on the new machine root is actually the only user I have set up so far. – user3640967 Oct 16 '15 at 14:46
  • What's your mysql version? I have tested `create view` with normal user in mysql-community-server 5.6.27, and after dropping the normal user and re-login by root user, although warnings are reported. the detail of create view syntax is still shown. `mysql> show warnings \G ... Level: Note Code: 1449 Message: The user specified as a definer ('testuser'@'localhost') does not exist ... Level: Warning Code: 1356 Message: View 'stackoverflow.testview' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them ` – Kenneth L. Oct 16 '15 at 15:38
  • It's version 5.6.16 MySQL Community Server (GPL). I have worked around the problem for now by just dropping the view and recreating it, but it's not really a solution... :/ – user3640967 Oct 16 '15 at 15:42
  • 1
    oops.. actually long time ago i have faced similar situation (mysql_upgrade from MySQL 5.1 to 5.5, and finally 5.6, after that truncate the table mysql.user ). afterwards view could not be accessed, and my senior just re-created the view by dropping it and creating it just like what you did (or `ALTER VIEW`? forgot) ... and he said if it is possible, declare `SQL SECURITY INVOKER` instead of `SQL SECURITY DEFINER` to prevent re-occurrence of similar case. – Kenneth L. Oct 16 '15 at 15:50
3

To see definer of each view, execute:

select v.TABLE_SCHEMA, v.TABLE_NAME, v.`DEFINER` 
from information_schema.VIEWS v
where v.TABLE_SCHEMA not in ('sys');
Mauricio
  • 473
  • 5
  • 11