Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I've been using
this
free hosting site for development and testing.
I couldn't use an
UPDATE
MySQL command to change the database values, even though the user is supposed to be allowed to use all commands from cPanel.
Then, I've tested the same with a default user, it still won't work. However, it works fine on my system.
The MySQL error is
UPDATE command denied to user 'test'@'localhost' for table 'content'
Other commands are working fine.
Why is this happening? And how can it be prevented? Or any solution for this?
And I am very sure that users have permission to use the
UPDATE
command because I can use phpMyAdmin with the same user and modify the MySQL fields.
I don't understand why some MySQL commands from PHP are denied for a user who was given all priviliges and can do everything via phpMyAdmin. Given that script, phpMyAdmin and the SQL host are on the same server.
–
–
For everyone who have tried answering this question here is my sincere thanks. I have found the problem and solution.
my sql query is like this
UPDATE `dblayer`.`test` SET `title` = 'hello a' WHERE `test`.`id` =1;
which I got from phpmyadmin and it works perfectly on my system.
But when I work on the servers it doesn't work and it says command denied may be because
`dblayer`.`test`
I am trying to select the table globally (or something like that, I'm not sure)
but if my sql query is
UPDATE `test` SET `title` = 'hello a' WHERE `test`.`id` =1;
it works on my server too.
As everyone else said, it's permission issue. I am sure you probably checked, but just in case. After you log into phpmyadmin, run the following:
SELECT USER();
That should spit out 'test'@'localhost' as indicated in all the comments above
Then run
SHOW GRANTS FOR 'test'@'localhost'
That should give you all privs for that user. When you get results, select 'Options', Full Text and click go to get full text.
Make sure permissions in the output have something like that:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `your_database`.* TO 'test'@'localhost'
You might not be able to get GRANT ALL on some hosting setups but as long as you got INSERT, UPDATE, DELETE you should definitely be able to update the data
–
Your user doesn't have the right permissions. You need to give it access to the UPDATE command, like so:
GRANT UPDATE ON database.* TO test@'localhost' IDENTIFIED BY 'password';
If you are using a graphical tool to manage the database - e.g. PHPMyAdmin or SQLYog, etc - you should be able to use those tools to enable these permissions too.
–
–
I had the same problem, the permission was right but it was working in some environments and not in others. I was using a table name like
scheme.TABLE_NAME.
Without changing the permission but using just
table_name
worked in all environments.
–
–
It's happening for what it says it's happening: the user test does not have update permissions on table content. Something like this should grant the user the required permission:
GRANT UPDATE ON database.content TO test@'localhost' IDENTIFIED BY 'password';
*password above is just a place holder. You should use the real one.
–
–
Provide the full access to test user with ip as localhost.Check the user table from mysql db.
Login as root and enter into mysql database and then to user table.You will be finding the current privileges of your test user.
This error came up for me when I was creating triggers, which included an update clause, for remote MySql instance (via JawsDB).
Instead of setting up trigger this:
CREATE TRIGGER updateRecentDateFromProcGen
After Insert On ``mq6swfzd39ygjejl``.``proceduregeneration``
For Each Row
BEGIN
UPDATE ``mq6swfzd39ygjejl``.``users``
mostRecentDateAllActivities = NEW.date
WHERE
NEW.userID = users.ID;
I set it up like this
CREATE TRIGGER updateRecentDateFromProcGen
After Insert On ``mq6swfzd39ygjejl``.``proceduregeneration``
For Each Row
BEGIN
UPDATE ``users``
mostRecentDateAllActivities = NEW.date
WHERE
NEW.userID = users.ID;
Basically, I just removed the database name in the UPDATE line and left the table name by itself.
Note, wherever you see the `` there should only be one tilde not two. If anyone knows how to fix that in stack overflow's markup please let me know!
SELECT * mysql.user
Look at the privileges and then update the ones you want. I like that more personally then using the above commands because I can see all of my options available in that table.
UPDATE mysql.user
SET Insert_priv = 'Y',
Update_priv = 'Y'
WHERE user.Host = 'localhost' AND user.User = 'test';
After updating the privileges I noticed I would have to restart the mysql server for the client to see them:
/etc/init.d/mysql restart
Then it would work fine
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
