PDO not working with Port

Question

I am trying to connect to an external database using PDO.

$dbh = new PDO('mysql:host=hotsname;port=3309;dbname=dbname', 'root', 'root');

However this is not working on one particular server I have.

I thought maybe only a certain host was allowed, but I checked the mysql rules and also tried this code on a server I have personally and the connection worked.

So I know the code works and that their is no block on my side and that the firewall is accepting requests to port 3309 and passing it to the correct server on port 3306.

So the issue is solely on one server. As a test I thought I'd open port 3306 to test the code without specifying a port.

$dbh = new PDO('mysql:host=hotsname;dbname=dbname', 'root', 'root');

This worked straight away.

So my question is, why when adding the port does this cause it not to be able to connect to the database. It is only this particular server that has this issue.

Many Thanks for your time.

Update

The error given is simply "Cannot connect to mysql server on xxx.xxx.xxx.xxx".

I have gotten slightly further now. The code is now working providing I have a firewall rule to allow incoming on 3306 to the server using 3306. So even though I have specified a port, it seems to ignore this and force it to go out of 3306.

Is it possible there is an *outbound* firewall rule on the *client* machine blocking port 3309? — Sajan Parikh, Jan 10 '14 at 14:23
What does it do instead of working (what error does it produce)? Does the connecting user have permission to connect remotely from the connecting host? Is MySQL listening for TCP connections (`skip-networking` not enabled in my.cnf) — Michael Berkowski, Jan 10 '14 at 14:25
@MichaelBerkowski I have updated my question. Also `skip-networking` is disabled. The connecting user has permissions as proven by me using the same code on other servers successfully as well as checking the mysql table and ensuring the allowed host is wildcard `%`. It certainly seems as though the server I am having trouble with just ignores the port completely and only uses 3306. It is a hosted server with cpanel. — The Humble Rat, Jan 10 '14 at 14:31
@TheHumbleRat Ah, try to debug it with other clients then too like `mysql -hhostname -P3309 -uroot -proot` Beyond that you may need to talk with the host's support. Your PDO is correct the way you have it. — Michael Berkowski, Jan 10 '14 at 14:33
@MichaelBerkowski great suggestion. Have tried this via ssh and not working either. So definitely seems I need to talk to the host. Hoping this would not be the case, but that's life. Many thanks for all your help, definitely appreciated. — The Humble Rat, Jan 10 '14 at 14:37

score 20 · Answer 1 · answered Feb 09 '16 at 22:27

20

You probably using localhost as a hostname. Switching it into 127.0.0.1 instead should solve the port ignorance problem.

answered Feb 09 '16 at 22:27

Slavik Meltser

9,712
3
47
48

3

This solved my problem (proxy connections through ssh). Any insight on why localhost ignores port while 127.0.0.1 behaves 'properly'? – seanoc5 Feb 23 '16 at 01:23
@sea MySQL ends up using the unix socket instead of TCP. On the command line I've seen the same issue. There you can solve it by forcing TCP protocol with parameter: --protocol=TCP – Juha Palomäki Oct 19 '16 at 05:56
And this answer describes what is happening with PDO (using "localhost" means implicitly, use sockets) http://stackoverflow.com/questions/1819592/error-when-connecting-to-mysql-using-php-pdo – Juha Palomäki Oct 19 '16 at 06:00

score 3 · Accepted Answer · answered Mar 01 '14 at 10:30

3

The issue ended up to be due to the host. All traffic was forced out on 3306 regardless of the port specified within the code.

answered Mar 01 '14 at 10:30

The Humble Rat

4,586
6
39
73

score 1 · Answer 3 · answered Jul 29 '21 at 11:35

The Problem is not from the host server but the local server, especially if you are using WAMP server.

configure your DB connection file on local environment as this,

class Database{
    
    // CHANGE THE DB INFO ACCORDING TO YOUR DATABASE
    private $db_host = 'localhost';
    private $db_name = 'database_api';
    private $db_username = 'root';
    private $port = "3308";
    private $db_password = '';
    
    public function dbConnection(){
        
        try{
            $conn = new PDO('mysql:host='.$this->db_host.'; port=3308; dbname='.$this->db_name,$this->db_username,$this->db_password);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return $conn;
        }
        catch(PDOException $e){
            echo "Connection error ".$e->getMessage(); 
            exit;
        }
          
    }
}

On live or deployment server, like this. @remove the port

class Database{

// CHANGE THE DB INFO ACCORDING TO YOUR DATABASE
private $db_host = 'localhost';
private $db_name = 'database_api';
private $db_username = 'root';
private $db_password = '';

public function dbConnection(){
    
    try{
        $conn = new PDO('mysql:host='.$this->db_host.'; dbname='.$this->db_name,$this->db_username,$this->db_password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $conn;
    }
    catch(PDOException $e){
        echo "Connection error ".$e->getMessage(); 
        exit;
    }
      
}

}

PDO not working with Port

3 Answers3