Take the 2-minute tour ×
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.

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

3 Answers 3

up vote 6 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 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 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 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 at 12:50

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

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.