MySQL definers

April 3, 2020 Stanly G Linux, MySQL 0 minutes, 25 seconds

Ever try to do a mysqldump, and get an error message about invalid definer? It usually happens right after you've imported databases from another server. Or maybe you've cleaned up the old users in your databases.

Here's a way to get a list of all the tables that aren't using the default definer:

select table_schema, table_name, definer from information_schema.views where definer <> 'mysql.sys@localhost';

And now, you just need to check and see which of those users exist (or not), with the classic:

select user,host from mysql.user;