MySQL error 1449 The user specified as a definer does not exist

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

User · Answer

Go into the edit routine section and and at the bottom  change Security Type from Definer to Invoker

User · Answer

For future googlers  I got a similar message trying to update a table in a database that contained no views  After some digging  it turned out I had imported triggers on that table  and those were the things defined by the non-existant user  Dropping the triggers solved the problem

User · Answer

This happened to me after moving the DB from one server to another server  Initially  the definer was using localhost and the user  On the new server we don t have that user  and host had also been changed  I took a back up of that particular table and removed all the triggers manually from phpmyadmin  After that it has been working fine for me

User · Answer

grant all on     to  username      identified by  password  with grant option    example   grant all on     to  web2vi      identified by  password  with grant option

User · Answer

update all or particular procedures to your wanted  use existing user  my case -root   1   UPDATE mysql proc p SET definer    root    WHERE 1 1 LIMIT 1000    limit clause is for the reason that various mysql versions complains when is updated not restricted with limit or no where condition is used   2   FLUSH PRIVILEGES     or restart the server

User · Answer

in my case I had a trigger on that table that I could not update data getting the same error      MySQL error 1449  The user specified as a definer does not exist   the solution was to delete the triggers on that table and recreate them again  this fixed the issue  since the the trigger was made with another user from another server  and the user name changed on the new server after changing hosting company   that s my 2 cents

User · Answer

I have tried the above methods  but feels like repetitive action when creating the view  I got the same issue while updating the views of the imported database  You can simply overcome the issue in LOCAL by simply creating the User with the create privilege

User · Answer

Create the deleted user like this     mysql gt  create user  web2vi     or  mysql gt  create user  web2vi

User · Answer

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     gt  echo above query   mysql -uuser -p  gt  alterView sql  gt  mysql -uuser -ppass databasename  lt  alterView sql   Note  the command generates and extra SELECT CONCAT on the file  making mysql -uuser -ppass databasename  lt  alterView sql fail if you don t remove it   Source  https   dba stackexchange com questions 4129 modify-definer-on-many-views

User · Answer

The user who originally created the SQL view or procedure has been deleted   If you recreate that user  it should address your error

User · Answer

If the user exists  then   mysql gt  flush privileges

User · Answer

I had the same problem with root user ans it worked for me when I replaced   root     by  root localhost   So  if the user  web2vi  is allowed to connect from  localhost   you can try   web2vi localhost   I m connected remotely to the database

User · Answer

i came here for the same problem  i couldn t find anywhere in my code where a certain user was making the action  apparently it was from a trigger that was using a user which was long deleted  db was restored from an older version  so in case you are puzzled as i were  take a look at your db events triggers routines  hope this will help someone

User · Answer

Fixed by running this following comments       grant all on     to  web2vi      identified by  root  with grant option  FLUSH PRIVILEGES    if you are getting some other instead of web2vi then you have to change the name accordingly

User · Answer

My 5 cents   I had same error while I tried to select from a view   However problem appears to be that this view  selected from another view that was restored from backup from different server   and in fact  YES  user was invalid  but was not obvious where to from the first look

User · Answer

As addition  to change definer for TRIGGERS  ALTER does not work   you can do it like this   Generate a DROP and a CREATE command for every Trigger   SELECT CONCAT  DROP TRIGGER    trigger name         CREATE TRIGGER    TRIGGER NAME    AFTER    EVENT MANIPULATION    ON    EVENT OBJECT SCHEMA       EVENT OBJECT TABLE    FOR EACH ROW    ACTION STATEMENT       AS sqlCommand FROM information schema triggers WHERE EVENT OBJECT SCHEMA    yourdatabase     Execute it in a foreach  I use this in my app when I take the production database to my development machine and go it with a foreach over all commands and recreate the triggers automatically  This gives me the option to automate it   Example in PHP Laravel        this- gt info  DROP and CREATE TRIGGERS         pdo   DB  connection  - gt getPdo         sql    SELECT CONCAT  DROP TRIGGER    trigger name         CREATE TRIGGER    TRIGGER NAME    AFTER    EVENT MANIPULATION    ON    EVENT OBJECT SCHEMA       EVENT OBJECT TABLE    FOR EACH ROW    ACTION STATEMENT       AS sqlCommand FROM information schema triggers WHERE EVENT OBJECT SCHEMA    mydatabase          stmt    pdo- gt prepare  sql   PDO  MYSQL ATTR USE BUFFERED QUERY   gt  true         stmt- gt execute         result    stmt- gt fetchAll PDO  FETCH ASSOC        stmt- gt closeCursor         foreach  result as  rs            pdo   DB  unprepared  rs  sqlCommand             break          Hint  I have to do it with pdo because of the mysql buffer query problem  described here

User · Answer

If this is a stored procedure  you can do   UPDATE  mysql   proc  SET definer    YournewDefiner  WHERE definer  OldDefinerShownBefore    But this is not advised   For me  better solution is to create the definer   create user  myuser  identified by  mypass   grant all on  mytable    to  myuser  identified by  mypass

User · Answer

Why am I getting that error  How do I fix it    I spent a hour before found a decision for a problem like this  But  in my case  I ran this   mysql gt  UPDATE  users  SET  somefield    1 WHERE  user id    2  ERROR 1449  HY000   The user specified as a definer   root       does not exist   If you really want to find the problem  just run this commands one by one   SHOW PROCEDURE STATUS  SHOW FUNCTION STATUS  SHOW TRIGGERS  SHOW FULL TABLES IN database name WHERE TABLE TYPE LIKE  VIEW        and  after each of them  look for the field  definer    In my case it was bearded old trigger  that somebody of developers forgot to delete

User · Answer

This commonly occurs when exporting views triggers procedures from one database or server to another as the user that created that object no longer exists   You have two options   1  Change the DEFINER  This is possibly easiest to do when initially importing your database objects  by removing any DEFINER statements from the dump   Changing the definer later is a more little tricky   How to change the definer for views   Run this SQL to generate the necessary ALTER statements  SELECT CONCAT  ALTER DEFINER  youruser   host  VIEW     table name    AS    view definition        FROM information schema views  WHERE table schema  your-database-name    Copy and run the ALTER statements   How to change the definer for stored procedures  Example   UPDATE  mysql   proc  p SET definer    user    WHERE definer  root      Be careful  because this will change all the definers for all databases   2  Create the missing user     If you ve found following error while using MySQL database     The user specified as a definer   someuser       does not exist        Then you can solve   it by using following    GRANT ALL ON     TO  someuser      IDENTIFIED BY  complex-password   FLUSH PRIVILEGES     From http   www lynnnayko com 2010 07 mysql-user-specified-as-definer-root html  This worked like a charm - you only have to change someuser to the name of the missing user  On a local dev server  you might typically just use root   Also consider whether you actually need to grant the user ALL permissions or whether they could do with less

User · Answer

In my case  deleting all the views solved the issue  DROP VIEW view name

User · Answer

Try the following   mysqldump --routines --single-transaction -u root -proot portalv3  gt  c  portal sql

User · Answer

you can create user with name web2vi and grant all privilage

User · Answer

quick fix to work around and dump the file   mysqldump --single-transaction -u root -p xyz live db  gt  xyz live db bkup110116 sql

User · Answer

Follow these steps    Go to PHPMyAdmin Select Your Database Select your table On the top menu Click on  Triggers  Click on  Edit  to edit trigger Change definer from  user localhost  to root localhost   Hope it helps

User · Answer

Solution is just a single line query as below    grant all on     to  ROOT      identified by  PASSWORD  with grant option    Replace ROOT with your mysql user name  Replace PASSWORD with your mysql password

User · Answer

Try this This is simple solution  mysql -u root -p SET GLOBAL sql mode  SELECT REPLACE   sql mode  ONLY FULL GROUP BY          more at https   stackoverflow com a 42183702 5407056

User · Answer

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

User · Answer

The database user also seems to be case-sensitive  so while I had a root     user I didn t have a ROOT     user  I changed the user to be uppercase via workbench and the problem was resolved

User · Answer

when mysql proc is empty  but system always notice  user 192 168    for table name no exist you just root in mysql command line and type   CHECK TABLE  database   table name  QUICK FAST MEDIUM CHANGED  flush privileges    over

User · Answer

You can try this     mysql -u root -p   gt  grant all privileges on     to  root      identified by  password     gt  flush privileges

User · Answer

Your view   view quotes  may have been copied from a different database where  web2vi  is a valid user into a database where  web2vi  is not a valid user   Either add the  web2vi  user to the database or alter the view  normally removing the DEFINER  web2vi      part and executing the script will do the trick

User · Answer

In my case  the table had a trigger with a DEFINER user that didn t exist

User · Answer

One or several of your views where created registered by another user  You ll have to check the owner of the view and    Recreate the user  as the other answers say  or  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

User · Answer

Try to set your procedure as  SECURITY INVOKER   Mysql default sets procedures security as  DEFINER   CREATOR OF    you must set the security to the  invoker

User · Answer

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    Login to database as root  or whatever has enough power to make changes   Delete view  table or whatever you are having trouble with  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

User · Answer

The user  web2vi  does not exist on your mysql server    See http   dev mysql com doc refman 5 1 en error-messages-server html error er no such user  If that user does exist  check what servers it can access from  although I would have thought that would be a different error  EG you might have web2vi localhost  but you are accessing the db as web2vi    At anything

User · Answer

This happened to me after I imported a dump on Windows 10 with MYSQL Workbench 6 3 Community  with  root   does not exist   Even though the user existed   First I tried to comment out the DEFINER however  this did not work   I then did a string replace on  root    with  root localhost  and reimported the dump  This did the trick for me

User · Answer

From MySQL reference of CREATE VIEW      The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time     This user must exist and is always better to use  localhost  as hostname  So I think that if you check that the user exists and change it to  localhost  on create view you won t have this error

[mysql] MySQL error 1449: The user specified as a definer does not exist

Examples related to mysql

Examples related to permissions