How to keep a mysql connection alive in php

php mysql keep connection open
php persistent connection
php mysql connection reuse
php keep database connection open
php maintain mysql connection
php limit database connections
mysql persistent connections vs non-persistent
php pdo keep connection alive

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!

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.

Persistent Database Connections - Manual, This function can be used by scripts that remain idle for a long while, to check Returns TRUE if the connection to the server MySQL server is working, otherwise FALSE . Make sure the connection is still alive, if not, try to reconnect */ Warning. There are a couple of additional caveats to keep in mind when using persistent connections. One is that when using table locking on a persistent connection, if the script for whatever reason cannot release the lock, then subsequent scripts using the same connection will block indefinitely and may require that you either restart the httpd server or the database server.

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

mysqli persistent connection

mysql_ping - Manual, Ok, so I wasted about 3 hours on this, and have only sort of found a solution. I have a PHP script which is going to be running 24/7 checking a� PHP may not be able to connect to the MySQL server if the server name is not recognized. Make sure that the server name is set to localhost. In case of other errors, make sure to consult the error_log file to help when trying to solve any issues. The file is located in the same folder where the script is running.

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

How to keep a MySQL connection alive • Aaron Parecki, Better way to keep mysql connection alive other than pinging. I'm sure a lot of developers have run into the dreaded "Mysql has gone away" issue, especially� Establishes a persistent connection to a MySQL server. mysql_pconnect() acts very much like mysql_connect() with two major differences. First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password.

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.

PHP + MySQL: Lost connection to MySQL server during query, echo "Failed to connect to MySQL: " . $mysqli -> connect_error; exit(); } // Check if server is alive if ($mysqli -> ping()) { echo "Connection is ok!"; } else { PHP 5 and later can work with a MySQL database using: MySQLi extension (the "i" stands for improved) PDO (PHP Data Objects) Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.

PHP mysqli ping() Function, How do I set the time limit in MySQL 5.0.51a-3ubuntu5.4? Note: I am using PHP 5.2.x and symfony framework 1.2. share. The MySQL site has docs for a few older versions that I the first place I would look. You might also look at the parameter for the server side connection and increase that by 50 or 100 per cent. But usually the connect string parameters override the the server side connection time.

How long is "too long" for MySQL Connections to sleep?, Most of them will be just holding keep-alive while still keeping MySQL connection open, others will serve static content such as images which� Right after connection client application creates temporary table `Login`. I want to keep this table untill application is closed, that's why i want to keep connection alive. In fact the connection is closed after 2 minutes. I used to ping server with queries like "SELECT 1" or "SELECT SessionId FROM Login" every 10 sec.

Are PHP persistent connections evil ?, A MySQL connection will automatically close as soon as the current script is finished executing. The connection to MySQL will only stay alive while the PDO object� to use load data local infile function from mysql (at mysql 4.0.16, php 4.3.3), set fifth parameter of mysql_connect() to CLIENT_LOCAL_FILES(128), which based on MYSQL C API ( also mysql server support load file, check by "show variables like 'local_infile' ")

Comments
  • Is it your server or are you able to edit its configuration? You may run into mysql.connect_timeout
  • set_time_limit(0); It's not your php script timing out is it?
  • The script runs on many servers. Some of which I have control. It would be nice to handle any configuration from within the script. @Eddie - the script is not timing out.
  • What errors do you get? I assume you are running with error_reporting(E_ALL) and logging errors, and enabling any error handling in the ADODB library?
  • It is not necessary or desirable to use persistent connections to maintain a connection within one page. Persistent connections are generally undesirable
  • @MarkR Do you have any other suggestions on how to maintain connection?
  • mysql_pconnect is now deprecated - please use an alternative.