Unable to connect to AWS RDS through PDO

2019-06-05 04:32发布

问题:

I set up a MySQL RDS instance in AWS. After instantiating the instance I tried to connect it from my EC2's command line and it works with no issue. However, when I try connecting through my PHP code I get erros. Below is the sample code that I tried to test my connectivity.

<?php
try{
    $dbh = new pdo( 'aws-test-db.hibizibi.us-west-2.rds.amazonaws.com;dbname=test',
                    'root',
                    'password',
                    array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    die(json_encode(array('outcome' => true)));
}
catch(PDOException $ex){
    die(json_encode(array('outcome' => false, 'message' => 'Unable to connect')));
}

I get {"outcome":false,"message":"Unable to connect"}. My original code is using idiorm (which uses PDO). The portion that connects with database looks like below:

# configure ORM
ORM::configure('mysql:host='.DB_SERVER.';dbname='.DB_NAME);
ORM::configure('username', DB_SERVER_USERNAME);
ORM::configure('password', DB_SERVER_PASSWORD);

From the above I get the below error trace:

SQLSTATE[HY000] [2005] Unknown MySQL server host 'aws-test-db.hibizibi.us-west-2.rds.amazonaws.com:3306' (11)
#0 /var/www/html/main/admin/applications/vendors/idiorm/idiorm.php(255): PDO->__construct('mysql:host=aws-...', 'root', 'password', NULL)
#1 /var/www/html/main/admin/applications/vendors/idiorm/idiorm.php(237): ORM::_setup_db('default')

UPDATE

I updated my test code to try connecting using mysql extension and it worked with mysql but not PDO

<?php 

$servername = "aws-test-db.hizibizi.us-west-2.rds.amazonaws.com"; 
$username = "root"; 
$password = "password"; 
$dbname='test'; 


try{ 
    $dbh = new pdo(
        'mysql:'.$servername.';dbname='.$dbname.';',
        $username,
        $password,
        array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
    );

    die(json_encode(array('outcome' => true))); 
} catch(PDOException $ex) { 
    echo json_encode(array('outcome' => false, 'message' => $ex->getMessage()))."\n"; 
} 

// Create connection 
$conn = mysqli_connect($servername, $username, $password); 

// Check connection 
if (!$conn) { 
    die("Connection failed: " . mysqli_connect_error()); 
} 
echo "Connected successfully\n";

Now, I am getting {"outcome":false,"message":"SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '\/var\/lib\/mysql\/mysql.sock' (2)"} while trying t connect through PDO. My attempt through mysql is successful. Seems like the problem is specifically with PDO. Is there anything I can check ?

回答1:

This is a very old question, but I had the exact same issue and wanted to document it here for anyone who finds this later.

The Problem

  1. You can connect to your database (Amazon RDS) manually from the command line.
  2. You can connect to your database via mysqli in PHP.
  3. You can not connect to your database via PDO in PHP.

The Solution

For me, after trying almost everything, I randomly decided to try and create a new database user. This worked and I was now able to connect via PDO.

This prompted me to investigate the issue a little further and I was able to narrow the issue down to a backslash \ character in my MySQL password.

There seems to be some kind of conflict between ENV Vars (with \), PHP and PDO.