Old users were removed from the
mysql.user table and now while running some views, I got the error that the user@host does not exist. Also while taking a dump, a view is unable to be dumped due to user not existing. I have been researching on the best way to update definers when I remove an old user from the
mysql.user table. Of course, the old users were DEFINERS in one view, or sp. A quick fix I did was to take a dump of the db with a regex that removes the definers from the dump file and restore the db - this way the new definer will be the user performing the restore operation.
Also I found 2 other ways online (https://www.adminbirds.com/mysql/how-to-change-the-definer-for-views/) - I need to verify this method.
to change definers in views:
SELECT CONCAT("ALTER DEFINER=youruser
VIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='your-database-name';this will generate the alter statement for manually changing the definer in the
To change definers in store procedures:
p SET definer = 'user@%' WHERE definer='root@%'this will manually change the definer in the
mysql.proctable. Although I read from MySQL doc page that
It is not supported that the server will notice manual manipulation of this table.
So here is my question: If I run the above commands, will it affect the data? Is it safe to run? I know the
ALTER statement only affects structure, but just want to be sure, since I will be running this in a prod environment as well. The
UPDATE statement definitely changes the data in the
mysql.proc table and I need to be sure this is safe to proceed. Please advise.