CODEWITHSUNDEEP
phpmysql
froogz3301
froogz3301

Reputation: 546

How to keep a mysql connection alive in php

I have a script which loops over tables and fields doing a find and replace. The script takes around 4 minutes to complete. The database is about 1.5GB.

The problem is that I am somehow loosing the database connection. I am using ADODB to connect to mySQL. The db connection is opened at the beginning and remains open the whole duration of the script.

The problem disappears when I use the following code:

if ($rowCount % 100000 == 0) {
    $db->Execute('USE ' . $db->database);
}

I am not a fan of this solution!

Any suggestions/explanations/discussions that help me to stop scratching my head would be greatly appreciated!

Upvotes: 5

Views: 8448

Answers (4)

Amir Forsati
Amir Forsati

Reputation: 5960

You can use one of these methods to make a persistent connection to mysql:

Mysqli

Try using mysqli_connect() with p: prefix for host name.

PDO

Try using PDO::__construct() with PDO::ATTR_PERSISTENT as a driver option.

Upvotes: 0

StampyCode
StampyCode

Reputation: 8098

The word you're looking for is 'persistent', and the answer you're looking for is here:

mysqli persistent connection

Upvotes: 2

Livia Martinez
Livia Martinez

Reputation: 155

Use mysqli::ping() to determine if connection is alive. Connect again if not.

Checks whether the connection to the server is working. If it has gone down, and global option mysqli.reconnect is enabled an automatic reconnection is attempted.

This function can be used by clients that remain idle for a long while, to check whether the server has closed the connection and reconnect if necessary.

Upvotes: 1

Mads Ohm Larsen
Mads Ohm Larsen

Reputation: 3715

You could use mysql_pconnect(); or equally for ADODB $conn->PConnect(); to create a persistent connection

Upvotes: 1

Related Questions