[RESOLVED] database connection disappears in daemon script after some time

sneakyimp

I have set up a mod_rewrite map for apache in order to support some SEO friendly URLs. It works fine for a little while, but eventually my database connection disappears. In particular, this bit of code causes an error:

                        $sql = "SELECT title FROM " . MAJORS_TABLE . " WHERE cip='" . mysql_real_escape_string($cip) . "'";
                        $res = mysql_query($sql, $db);
                        if ($res) {
                                if (mysql_num_rows($res) >= 1) {
                                        if ($row = mysql_fetch_assoc($res)) {
                                                # output the new url
                                                $url = '' . strtolower(preg_replace('#[^a-z]+#i', '-', $row['title'])) . '/' . $dir . '/'
. $cip . '/' . $sid;
                                                $found = TRUE;
map_write_log("returning $url");
                                                print $url . "\n";
                                        } else {
                                                trigger_error("major rewrite query failed to fetch a row:\n\t" . $sql, E_USER_WARNING);
                                        }
                                } else {
                                        trigger_error("major rewrite rows returned is less than 1:\n\t" . $sql, E_USER_WARNING);
                                }
                                mysql_free_result($res);
                        } else {
                                // HERE IS THE ERROR THAT IS OCCURRING
                                trigger_error("major rewrite query returned no resource:\n\t" . mysql_error(). "\n\t" . $sql, E_USER_ERROR);
                        }

My error handling routine notifies me of this error from that trigger_error call:

E_USER_ERROR; file=/var/www/mod_rewrite_maps/map_majors.php; line=60; major rewrite query returned no resource:
	MySQL server has gone away

The issue basically seems to be that the database connection created when my script begins "goes away" after some time. It's probably garbage collected.

My problem is that it takes a considerable amount of time for this connection to go away so I can't just easily debug it. I'm wondering how I might detect if $db has "gone away" or is still valid so that I can reconnect. Will [man]is_resource[/man] do the trick? I tried using [man]mysql_pconnect[/man] instead of plain old [man]mysql_connect[/man] but it doesn't seem to help. It occurred to me that I might want to reconnect for every iteration of my main loop, but that seems like a real performance killer.

Any tips would be much appreciated.

Bjom

Why not open a fresh connection for this process (and use mysqli too)?

sneakyimp

The script is instantiated when apache starts (which may only happen once a year or less). That's how the mod_rewrite map programs work. At that point, I do open a new fresh connection. The main loop of the script runs once for each apache request that matches a particular pattern.

It seemed to me that creating a new database connection and then cleaning it up every single time this loop iterates would be a real performance-killer. Under normal circumstances, it could be looping hundreds of times per second so the likelihood that the connection will have gone away is small. In fact, I believe wait_timeout is the relevant timeout and it's set to something like 8 hours:

mysql> SHOW VARIABLES LIKE '&#37;_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| connect_timeout          | 5     |
| delayed_insert_timeout   | 300   |
| innodb_lock_wait_timeout | 50    |
| interactive_timeout      | 28800 |
| net_read_timeout         | 30    |
| net_write_timeout        | 60    |
| slave_net_timeout        | 3600  |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 28800 |
+--------------------------+-------+
9 rows in set (0.00 sec)

My problem has specifically been caused because I am working on a development server. I get the script working just fine and the database is there as I am testing my work. I send an email to my client and go to bed. By the time he goes to check my work, 8 hours have passed and the db connection has gone away.

While this probably won't be a problem on the production machine, I want my script to be as robust as possible. I just need to properly detect the 'gone away' condition before I try to run a query so that I can reconnect if necessary. I was wondering if using [man]is_resource[/man] would work for this.

Bjom

I can't comment on the is_ressource, since I have not used it, but maybe this could be an alternative:

mysqli_ping: Pings a server connection, or tries to reconnect if the connection has gone down

sneakyimp

That looks perfect. I should have noticed the ping functions. Thanks so much.