Recreate MySQL views without definer, one-liner solution (Linux)

MySQLRather cryptic headline describes a pain many of us have to go through when restoring MySQL database backup from different machine. If you have ever done this on database with DB views - you'd probably run at this problem too.

Source of the problem is that MySQL dump exports CREATE VIEW DLL with DEFINER attribute and there is no way how to instruct it to exclude this attribute (as far as I know). When you take such export and import it for example on developer machine you'd probably not have the same user or user with same privileges on this machine. Each and every select to the views with invalid definer fails with error - for example:

Error Code: 1449. The user specified as a definer ('xxxx'@'localhost') does not exist

One-liner

If you're so lucky that you run Linux as your operating system - you can use this one-liner solution, that will drop all views in your database and will recreate them without definer, so that they become defined by current user and will start to work again:

mysql -uuser -ppwd -A --skip-column-names -e"SELECT CONCAT('SHOW CREATE VIEW ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE engine IS NULL and table_schema like 'mydb%'" | mysql -uuser -ppwd -A --skip-column-names | sed -rn 's/.*?VIEW ([^\s]+?) (AS .*?)\s([^\s]+?)\s([^\s]+?)/DROP VIEW ;\nCREATE VIEW  ;/p' | mysql -uuser -ppwd -A --skip-column-names

Note: You have only to replace strings in bold with your DB user credentials and database name / like pattern.

What the script does?

  1. connects to the database and selects all tables without engine - ie. views from information schema of MySQL
  2. constructs SHOW CREATE VIEW for each returned view name
  3. runs that newly created script against MySQL database again
  4. returned create statements runs through sed with regular expression that converts them into DROP VIEW + CREATE VIEW without definer
  5. generated scripts runs again in MySQL database

Don't repeat yourself

If you happen to use this one-liner often you might want to create ALIAS to your bash profile that will let you to use it as a short command:

recreate-views mydb% user pwd

Just add to your ~/.bash_aliases following lines:

function recreate-views-fct() {
   mysql -u$2 -p$3 -A --skip-column-names -e"SELECT CONCAT('SHOW CREATE VIEW ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE engine IS NULL and table_schema like '$1'" | mysql -u$2 -p$3 -A --skip-column-names | sed -rn 's/.*?VIEW ([^\s]+?) (AS .*?)\s([^\s]+?)\s([^\s]+?)/DROP VIEW ;\nCREATE VIEW  ;/p' | mysql -u$2 -p$3 -A --skip-column-names
}
alias recreate-views=recreate-views-fct

Sources