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.
6
You probably using localhost as a hostname. Switching it into 127.0.0.1 instead should solve the port ignorance problem.
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.
0
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;
}
}
}
For some weird reason PDO internally translates ‘localhost’ to use a socket connection. That’s why it works when you use 127.0.0.1 instead, cause in that case the TCP/IP is explicitly stated by using an IP address.
That’s why it ignores the port argument, because it’s not even using TCP/IP.
This can become an issue when you have to manage many servers and you need to keep the same connection string accross all of them.