0

I am not able to insert rows into a table even though I have granted permissions to the corresponding database user.

I am able to insert rows into the same table with the same user from an individual php application. But if I try to insert a row from drupal, I get the error message as follows.

user warning: INSERT command denied to user 'username'@'localhost' for table 'table_name'

I even tried changing the password of the database user and changed the settings.php of drupal to reflect the new password, but still I get the same error message. I am able to run the SELECT queries successfully but not INSERT queries. What might be the problem here? Any help is appreciated. Thanks.

  • 1
    What code do you use to insert your row ? There is a very special way to do this under Drupal, and it is highly recommended to use it. – Countzero Mar 28 '12 at 13:27
  • I am using this to insert. $query = "INSERT INTO db.table(col1,col2,col3) VALUES ('%d','%s','%d')"; db_query($query,1,'dfgdsfgs',1); – Vivek Mar 28 '12 at 13:33
  • 1
    Maybe your alias is what screws you. Try $query = "INSERT INTO {table} (col1,col2,col3) VALUES ('%d','%s','%d')"; – Countzero Mar 28 '12 at 13:56
1

That doesn't really make sense. Drupal is just a PHP application; if you're using exactly the same connection details in Drupal as you are in your stand-alone PHP application, and that works, it's only logical that your Drupal connection will work too.

You've obviously checked that the username and password are correct, but have you checked the Drupal is connecting to the server from localhost? That would be the only other possible difference. If Drupal is connecting from a remote machine then you'll need to grant specific privileges to the user for that specific location.

The easiest way to see what you've already got is to run a SHOW GRANTS query on the database in question. That will show you something like this:

Grants for username@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'username'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxxxxx'

Plus a few more more rows more than likely.

Check through those rows to make doubly sure that there is a grant for INSERT for the database user AND the location that the Drupal website is connecting from.

EDIT

You could maybe get around the error using drupal_write_record():

$record = new stdClass;
$record->col1 = 'something';
$record->col2 = 'something';
$record->col3 = 'something';

drupal_write_record('table_name', $record);

But that really shouldn't make any difference

  • Thanks for your answer. I ran the SHOW GRANTS query and I found an entry for SELECT, INSERT, UPDATE on particular table for the user. I do not have that entry as user@localhost but as user@% which I guess is applicable for accessing from remote location also. – Vivek Mar 28 '12 at 13:40
  • 1
    Yeah the wildcard should make it match ok. It's very weird, Drupal itself writes to the database at various points so you should be seeing errors all over the place if the db user doesn't have INSERT privileges. It shouldn't make any difference but have you tried drupal_write_record instead? I'll put an example in the answer just in case you haven't – Clive Mar 28 '12 at 13:45
  • Even drupal_write_record fails. I have two databases setup and this table is from the second database and not the drupal database. So I am not getting errors at various points. – Vivek Mar 28 '12 at 14:11
  • 1
    Ahhhh that's your problem then :) Have a look at How to connect to multiple databases within Drupal, it has all the info you'll need – Clive Mar 28 '12 at 14:16
  • I know how to have multiple database connections. I am using the two databases for the whole project. But I am not able to access this table alone because of permission errors. I just found that I get different privileges for 'user'@'localhost' and 'user'@'%'. So I think they both are used in different scenarios. What do you think? – Vivek Mar 28 '12 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.