MySQL error 1449: The user specified as a definer

Question

When I run the following query I get an error:

SELECT
  `a`.`sl_id`                     AS `sl_id`,
  `a`.`quote_id`                  AS `quote_id`,
  `a`.`sl_date`                   AS `sl_date`,
  `a`.`sl_type`                   AS `sl_type`,
  `a`.`sl_status`                 AS `sl_status`,
  `b`.`client_id`                 AS `client_id`,
  `b`.`business`                  AS `business`,
  `b`.`affaire_type`              AS `affaire_type`,
  `b`.`quotation_date`            AS `quotation_date`,
  `b`.`total_sale_price_with_tax` AS `total_sale_price_with_tax`,
  `b`.`STATUS`                    AS `status`,
  `b`.`customer_name`             AS `customer_name`
FROM `tbl_supplier_list` `a`
  LEFT JOIN `view_quotes` `b`
    ON (`b`.`quote_id` = `a`.`quote_id`)
LIMIT 0, 30

The error message is:

#1449 - The user specified as a definer ('web2vi'@'%') does not exist

Why am I getting that error? How do I fix it?

Answer 1

One or several of your views where created/registered by another user. You'll have to check the owner of the view and:

1. Recreate the user; as the other answers say. or
2. Recreate the views that where created by the user 'web2vi' using ALTER VIEW

I had this problem once.

I was trying to migrate views, from BD1 to BD2, using SQLYog. SQLYog recreated the views in the other DataBase (DB2), but it kept the user of BD1 (they where different). Later I realized that the views I was using in my query were having the same error as you, even when I wasn't creating any view.

Hope this help.

Answer 2

I got the same error after updating mysql.

The error has been fixed after this command:

mysql_upgrade -u root

mysql_upgrade should be executed each time you upgrade MySQL. It checks all tables in all databases for incompatibilities with the current version of MySQL Server. If a table is found to have a possible incompatibility, it is checked. If any problems are found, the table is repaired. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

Answer 3

The problem is clear - MySQL cannot find user specified as the definer.

I encountered this problem after synchronizing database model from development server, applying it to localhost, making changes to the model and then reapplying it to localhost. Apparently there was a view (I modified) defined and so I couldn't update my local version.

How to fix (easily):

Note: it involves deleting so it works just fine for views but make sure you have data backed-up if you try this on tables.

1. Login to database as root (or whatever has enough power to make changes).
2. Delete view, table or whatever you are having trouble with.
3. Synchronize your new model - it will not complain about something that does not exist now. You may want to remove SQL SECURITY DEFINER part from the item definition you had problems with.

P.S. This is neither a proper nor best-all-around fix. I just posted it as a possible (and very simple) solution.

Answer 4

Follow these steps:

1. Go to PHPMyAdmin
2. Select Your Database
3. Select your table
4. On the top menu Click on 'Triggers'
5. Click on 'Edit' to edit trigger
6. Change definer from [user@localhost] to root@localhost

Hope it helps

Answer 5

quick fix to work around and dump the file:

mysqldump --single-transaction -u root -p xyz_live_db > xyz_live_db_bkup110116.sql

Answer 6

I had your very same problem minutes ago, I ran into this issue after deleting an unused user from mysql.user table, but doing an alter view fixed it, here is a handy command that makes it very simple:

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 above_query | mysql -uuser -p > alterView.sql
> mysql -uuser -ppass databasename < alterView.sql

Note: the command generates and extra SELECT CONCAT on the file, making mysql -uuser -ppass databasename < alterView.sql fail if you don't remove it.

Source: https://dba.stackexchange.com/questions/4129/modify-definer-on-many-views