Local vs remote database 2

[svar]

Just about to move from a local to a remote database. Is there anything else I should worry about except
for :
a) specifying the database e.g.

$link = new mysqli('localhost', $dbusername, $dbpasswd, $db);

changed to

$link = new mysqli('[URL unfurl="true"]http://www.mybdsite.org/database_path/',[/URL] $dbusername, $dbpasswd, $db);

and

b) programming for responses to connectivity errors?

e.g.

if ($mysqli->connect_error) {
echo "error: $mysqli->connect_errno  message: $mysqli->connect_error";
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);

for example if the connection is down.


I assume there may be an issue with creating a link once (e.g. require dbconnect.php) and closing and reopening connections as needed), but unless the connection is very bad, I'd keep it open.


Am I missing something here?

 

[feherke]

Hi

$link = new mysqli('[URL unfurl="true"]http://www.mybdsite.org/database_path/',[/URL] $dbusername, $dbpasswd, $db);

Database communication is not performed over HTTP protocol. They not even use port 80; MySQL by default uses port 3306.

From the above you can deduce that you may need to instruct your firewall to allow the communication through the port used by MySQL.

Regarding the connection errors :
[ul]
[li]Make sure the connection failures are logged.[/li]
[li]Even if the connection succeeds, the communication may fail later when performing database operations.[/li]
[li]For the above mentioned failure case make sure you use transactions where appropriate.[/li]
[li]Do not just call [tt]die()[/tt], send the visitor a meaningful message.[/li]
[/ul]


Feherke.
feherke.ga

 

[svar]

Thanks so
a) use

http://www.mybdsite.org

or its IP instead of localost and then

mysql_select_db($dbname);

(actually its mysqli equivalent) and similarly for other dbs

b) good point on traNSACTIONS. I assume if I do a SELECT and nothing comes back due to a connection error and check for errors I should find a message explaining that connection was lost.

 

[feherke]

Hi

b) good point on traNSACTIONS. I assume if I do a SELECT and nothing comes back due to a connection error and check for errors I should find a message explaining that connection was lost.

Yes, generally transactions are used only for data modification statements.

However they may be useful for [tt]select[/tt] too, to avoid inconsistency. For example a status message like "1 new user registered today, the last one was NULL" - in case the count and the last name were queried separately and a new user was registered between the two queries. But this cases are usually ignored - the extra load added by excessive locks or snapshots are worst than little and rare inconsistencies.


Feherke.
feherke.ga

 

[ChrisHirst]

From the above you can deduce that you may need to instruct your firewall to allow the communication through the port used by MySQL.

Not quite, it is the LISTENING end that has to allow incoming connections to port 3306 in the firewall. The outbound request will be using an ephemeral or dynamic port for communication.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[vacunita]

Thanks so
a) use

http://www.mybdsite.org

or its IP instead of localost and then

That depends entirely on the server setup.

If its set up to respond to the URL you can use the URL. Otherwise using an IP may be required. But again that depends on where you are connecting, and how the db server is set to respond. It may not even accept requests from outside its own network. Its not something we can know. You would need to ask the server service provider for the setup.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[feherke]

Hi

Not quite, it is the LISTENING end that has to allow incoming connections to port 3306 in the firewall. The outbound request will be using an ephemeral or dynamic port for communication.

Right. But outgoing connections aiming to connect to port 3306 on a remote host, are not also filtered by the local firewall ?
( Sorry for the potentially stupid question, I do not master firewall configuration. )


Feherke.
feherke.ga

 

[ChrisHirst]

Yes and no,

The target port is carried in the outbound packets, but the port that the request is going out through, will not be 3306, it will be in the range of 49152 to 65535, so unless your firewall has been specifically set for 'stateful packet inspection' (SPI) that examines outbound traffic with a destination port of 3306 the request will pass through unhindered.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[feherke]

Hi

Thank you for the explanation, Chris.


Feherke.
feherke.ga