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