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?

  • 1
    for me, I just added the missing account to the database and the error went away. – user1794918 Dec 29 '18 at 16:25

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 !!!

| 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'@'') does not exist – Marki555 Apr 30 '14 at 19:42
  • @Marki555 Then, you must create that user temporarily. run CREATE USER 'abc'@'';. 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. Note2: This works for only one database at a time, if you leave off WHERE table_schema you need to insert USE commands between each.

| improve this answer | |
  • 1
    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 '16 at 20:41

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
    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

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
   mysql -BNe 'show tables' $DB | \
   while read t
      echo "check table $t;"
   done | mysql -BN $DB
done | \
egrep -v 'status\s*OK'

it should no longer complain about invalid definers in views.

| improve this answer | |
  • Shamelessly added this to my snippets. Thanks! – stefgosselin Jul 27 '19 at 1:02

Export all the views of the database <DB>:

    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql


    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql

Edit views.sql (change definer) and recreate them:

cat views.sql | mysql <DB>

Specify -u and -p switches if necessary.

| improve this answer | |
  • In what way should the VIEWS, views.sql, be edited? Also, how to recreate them? – SherylHohman Aug 24 '19 at 18:57
  • @SherylHohman Change DEFINER = olduser@oldhost to DEFINER = newuser@newhost, cat views.sql | mysql <DB> recreates the views. – x-yuri Sep 3 '19 at 5:50

I created a simple python script that replaces production db definer with local db definer. This script will modify dump files and automatically import specified databases.

GIT repo: https://github.com/mjakal/db-auto-import

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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