php mysqli_query Access Denied for user

Question

Ok, I'm completely stumped here. I have setup an AWS RDS (free-tier) and tested the connection via mac os x terminal and I can connect perfectly fine.

I can also issue a connection statement with php (which seems to work) like this:

$this -> _mysqli = new mysqli( );
$this -> _mysqli -> init( );
$this -> _mysqli -> options(MYSQLI_OPT_LOCAL_INFILE, true);
$this -> _mysqli -> real_connect($host, $username, $password, $db, $port);
print_r( $this -> _mysqli );

/*print_r returns*/
mysqli Object ( 
    [affected_rows] => 0 
    [client_info] => 5.5.33 
    [client_version] => 50533 
    [connect_errno] => 0 
    [connect_error] => 
    [errno] => 0 
    [error] => 
    [error_list] => Array ( ) 
    [field_count] => 0 
    [host_info] => sadmicrowave-dev.*****.*****-****.rds.amazonaws.com via TCP/IP 
    [info] => 
    [insert_id] => 0 
    [server_info] => 5.6.13 
    [server_version] => 50613 
    [stat] => Uptime: 26448 Threads: 3 Questions: 27070 Slow queries: 0 Opens: 1962 Flush tables: 1 Open tables: 98 Queries per second avg: 1.023 
    [sqlstate] => 00000 
    [protocol_version] => 10 
    [thread_id] => 122 
    [warning_count] => 0 )

Which seems to indicate to me that the connection succeeded; right? After that long-winded introduction of my setup, here comes my problem. Further down in my class I have a method to issue a query; it looks like this:

$q = mysqli_query( 
       $this -> _mysqli, 
      "LOAD DATA INFILE '$FILE'
       IGNORE INTO TABLE `php-challenge-apr-2014`
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
       LINES TERMINATED BY '\n'
       IGNORE 1 LINES"
);

But this query returns the following error (despite the fact that I am working in the same class instance which means $this -> _mysqli was already set in the connect statement above)

Access denied for user 'sadmicrowave'@'%' (using password: YES)]"}

What the F is going on?

Do sadmicrowave user have FILE permission? do other queries work? — Your Common Sense, Apr 18 '14 at 15:00
Looks like a duplicate for http://stackoverflow.com/questions/1014724/mysql-permission-errors-with-load-data — Your Common Sense, Apr 18 '14 at 15:00
@YourCommonSense no it doesn't appear that I have `FILE` permissions. I'm the server admin yet when I run: `GRANT FILE ON `challenge`.* TO sadmicrowave@'%' identified by '*******';` on the RDS I get `ERROR 1045 (28000): Access denied for user 'sadmicrowave'@'%' (using password: YES)` — sadmicrowave, Apr 18 '14 at 15:06
yet just in case - does SELECT query work? or it throws the same error? if select works - then it's apparently permissions and you have to recheck them — Your Common Sense, Apr 18 '14 at 15:10
@YourCommonSense yes I can query successfully using a simple select query statement — sadmicrowave, Apr 18 '14 at 15:30

score 1 · Accepted Answer · answered Apr 18 '14 at 16:05

This is SO dumb of me but I figured out my problem and it was that I forgot the local keyword in the LOAD DATA query. Without the local keyword the MySQL server thinks I am trying to access a file on the server rather than on my localhost. Of course, in this case Amazon has probably locked this functionality down on their RDS instances for a reason; hence the Access Denied issue.

php mysqli_query Access Denied for user

1 Answers1