Mysql – Old user removed, now having DEFINER errors

MySQLstored-procedures

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.

  1. to change definers in views: SELECT CONCAT("ALTER DEFINER=youruser@hostVIEW ", 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 information_schema.views table.

  2. To change definers in store procedures: UPDATEmysql.procp SET definer = 'user@%' WHERE definer='root@%' this will manually change the definer in the mysql.proc table. 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.

Best Answer

as You can see from text of both commands - they not work with data other than single table, so from this point of view - both safe.

In worst case - You already have not worked views and continue have them :), but it not expected result.

But, in any case - the best practice - make backup before any kind of similar operations, so You always can rollback.

Notice in server documentation:

The server manipulates the mysql.proc table in response to statements that create, alter, or drop stored routines. It is not supported that the server will notice manual manipulation of this table.

more related to other types of manipulation, like - manual edit of BLOB object with text or parameters, but change definer - it just change single column.

Related Question