'#1449 - phpMyAdmin (The user specified as a definer ('***'@'localhost') does not exist)

I am currently transferring all of my 'Views' from a VPS hosted with 123-reg to another VPS provided by Heart Internet.

Here is the View:

CREATE ALGORITHM=UNDEFINED DEFINER=`etd`@`localhost` SQL SECURITY DEFINER VIEW `vShoppingCart` AS SELECT `VCoursesUOC`.`Distributor` AS `Distributor`,`VCoursesUOC`.`Company` AS `Company`,`VCoursesUOC`.`Title` AS `Title`,`VCoursesUOC`.`Price` AS `Price`,(`VCoursesUOC`.`Price` / 5) AS `VAT`,(`VCoursesUOC`.`Price` * 1.2) AS `WithVAT`,(sum(`VCoursesUOC`.`Price`) * 1.2) AS `Total`,count(`VCoursesUOC`.`CourseId`) AS `NoOfCourses` FROM `VCoursesUOC` WHERE (`VCoursesUOC`.`Payment` = 'Unpaid') GROUP BY `VCoursesUOC`.`Title`,`VCoursesUOC`.`Distributor`,`VCoursesUOC`.`Company`,`VCoursesUOC`.`Price`

Everytime I try to run this I get the following error:

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

I have tried to change etd to root and a Database username on the MySQL Database but it does not fix the error.

What can I try?

I am logged in to phpMyAdmin as the root user.

phpmysqlphpmyadmin


Solution 1:[1]

The user does not exist, so you either need to create it or to use another user, which exists and has the necessary privileges.

etd is a user used at the original source as a definer.

Solution 2:[2]

The DEFINER is optional in the MySQL CREATE VIEW statement.

You could try modifying the statement to remove the DEFINER = 'user'@'host' from the statement.

That would be equivalent to specifying DEFINER = CURRENT_USER in the statement.

Given the SQL SECURITY DEFINER, this will impact the security context of the view.

If you want to specify a DEFINER in the view definition (in the CREATE VIEW statement), the specified user will need to exist in MySQL.

Note that a "user" in MySQL is identified by both a username and and host.

That is, 'etd'@'%' is a different user than 'etd'@'localhost'.

To create a user, you can run a statement like this:

GRANT USAGE ON *.* TO 'etd'@'localhost' IDENTIFIED BY 'mysecretpassword'

This same user will also need to be granted appropriate SELECT privileges on the tables underlying the view,

To create a user on a MySQL instance, where you want to "copy" the definition from another MySQL instance to another...

On the "source" instance (where the 'etd'@'localhost' user that we want to "copy" exists, we can run a statement like this statement:

 SHOW GRANTS FOR 'etd'@'localhost'

The rows returned by that are a pretty good representation of the statements we would need to run on another MySQL instance, to create the user there.

Solution 3:[3]

  select user, host from mysql.user;

You can use this command for getting your user and host information. If you are using root as user then try with root@localhost

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Lajos Arpad
Solution 2
Solution 3 reshma

Related Questions