Error Code: 1044. Access denied for user 'root'@'%' to database 'xxxxxxxABC'

1
votes

I want to create following schedule in Mysql. it showing error: "Error Code: 1044. Access denied for user 'root'@'%' to database 'xxxxxABC'"

CREATE EVENT resetAccount
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO 
 UPDATE user_master SET AccountNonLocked = 1 WHERE AccountNonLocked = 0  
       and username in 
                    (select username from  user_attempts  where last_modified between          DATE_SUB(now(),INTERVAL 24 HOUR) and DATE_SUB(now(),INTERVAL 23 HOUR)
                    );

I checked all possible solutions mention in previous stackoverflow comments. I checked PRIVILEGES also for user and run following command also. "SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user;"

It shows following output enter image description here

Also i run "SELECT USER(),CURRENT_USER();" output: enter image description here

Now, Why i am getting this error as user has all the privileges.

1
mysqleventstriggersscheduled-tasks
What OS are you using? – itsols
What's your webserver? WAMP or IIS or something else? – itsols
Use the username 'root' with empty password and see if it works. It should work. Then I'll give you the correct answer. This is basically a permissions problem. – itsols
We used username is root only. but it seems in MySQL workbench in "Users and Previlieges" that passoword is compulsary field. because i deleted passoword from the filed and click on "Apply". but default password is coming automatically in that field. – Neel

1 Answers

2
votes

Try to execute this:

echo "SET PASSWORD FOR root@localhost=PASSWORD('password');" > /var/lib/mysql/rootpwd.sql
service mysql restart
rm -f /var/lib/mysql/rootpwd.sql

or try to GRANT the privilege

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';