Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result.

Got error: 1449: The user specified as a definer ('cittool'@'%') does not exist when using LOCK TABLES

After some investigation it appears that the definer for these views is an old developer account that has been purged from the system. The databases and views with this problem are used very infrequently, and most being kept around for archival purposes.

There is about 40 views with a definer that no longer exists. Is there an easy way to change the definer to a different account on everything at once? Is there a way to get mysqldump to simply dump all the views out to a file so I could edit that file and recreate the views?

share|improve this question
up vote 8 down vote accepted

Create a text file with all the view definitions:

mysql -uusername -ppassword -A --skip-column-names -e"SELECT CONCAT('SHOW CREATE VIEW ',table_schema,'.',table_name,'\\G') FROM information_schema.tables WHERE engine IS NULL" | mysql -uusername -ppassword -A --skip-column-names > AllMyViews.sql

You edit AllMyViews.sql from there. Then, Drop the Views

mysql -uusername -ppassword -A --skip-column-names -e"SELECT CONCAT('DROP VIEW ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE engine IS NULL" | mysql -uusername -ppassword -A

After editing AllMyViews.sql reload them

mysql -uusername -ppassword -A < AllMyViews.sql

Give it a Try !!!

share|improve this answer
    
+1 as a great alternative if the view_definition of information_schema isn't showing up but SHOW CREATE VIEW works fine. – Derek Downey Jul 26 '11 at 21:01
    
While I liked DTest's solution better in theory, something about my system was preventing it from working. I basically used the above to dump the show create statements into a file, and I had to manually edit them a bit and then run them. – Zoredache Jul 26 '11 at 21:10
    
SHOW CREATE VIEW doesn't work when the definer user does not exist - it throws the same error as mysqldump: The user specified as a definer ('abc'@'1.2.3.4') does not exist – Marki555 Apr 30 '14 at 19:42
    
@Marki555 Then, you must create that user temporarily. run CREATE USER 'abc'@'1.2.3.4';. Then, try again. – RolandoMySQLDBA Apr 30 '14 at 19:44
    
I just noticed that the View uses some stored procedure and that error message is realated to the definer of that procedure, not view itself – Marki555 Apr 30 '14 at 20:24

You can use ALTER VIEW in conjunction with the information schema. You mentioned dumping it out to a text file, so perhaps something like this:

SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ",table_name," AS ", view_definition,";") 
FROM information_schema.views WHERE table_schema='databasename'

Mix this with the mysql command line (assuming *nix, not familiar with windows):

> echo "*abovequery*" | mysql -uuser -p > alterView.sql
> mysql -uuser -p databasename < alterView.sql

Sidenote: You can't alter the information_schema entries directly.

share|improve this answer
    
I like this because it looks slightly less involved and more concise than mine. +1 !!! (I forgot about ALTER VIEW) – RolandoMySQLDBA Jul 26 '11 at 19:12
    
Any idea why the view_definition field would be empty? SELECT table_name,view_definition FROM information_schema.views WHERE table_schema='cittmp'; returns | Staff | | – Zoredache Jul 26 '11 at 20:02
    
From discussion in chat, it looks like it's a permission issue with the super privilege. bugs.mysql.com/bug.php?id=39733 – Derek Downey Jul 26 '11 at 20:49
    
Perfect solution! +1 – Pablo Martinez May 22 '14 at 12:50
    
At least on RedHat, if you're outputting to a file you have to escape the back ticks, e.g. echo "SELECT CONCAT("ALTER DEFINER=\`youruser\`@\`host\` VIEW... – clav Jan 14 at 20:41

Export all the views of the database <DB>:

mysql -BNe "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '<DB>' AND TABLE_TYPE = 'VIEW'" \
    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql

or:

mysql -BNe "SELECT TABLE_NAME FROM VIEWS WHERE TABLE_SCHEMA = '<DB>'" \
    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql

Edit views.sql and recreate them:

cat views.sql | mysql <DB>

Specify -u and -p switches if necessary.

share|improve this answer

Automating on the Derek's solution, this will change DEFINER to root@localhost and set SQL SECURITY INVOKER (make sure you want that first!) in all views in all databases:

mysql -BNe 'show databases' | \
egrep -v '^(information_schema|performance_schema)$' | \
while read DB
do 
    mysql -BNe "SELECT CONCAT(\"ALTER DEFINER=\`root\`@\`localhost\` SQL SECURITY INVOKER VIEW \",table_name,\" AS \", view_definition,\";\") FROM information_schema.views WHERE table_schema=\"$DB\"" | \
    mysql $DB
done

WARNING: make sure you've made a full mysql backup (eg. by stopping mysqld, and backing up all files in /var/lib/mysql) before you run it - just in case... It worked for me, but YMMV.

you should also check all your tables/views with:

mysql -BNe 'show databases' | \
egrep -v '^(information_schema|performance_schema)$' | \
while read DB
do 
   mysql -BNe 'show tables' $DB | \
   while read t
   do
      echo "check table $t;"
   done | mysql -BN $DB
done | \
egrep -v 'status\s*OK'

it should no longer complain about invalid definers in views.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.