Interfacing MySQL to Apache

It seems my attempt to post to the mysql general list has been silently rejected, or otherwise lost. It would probably be futile to speculate on what’s going on. If anyone reading this has the ear of the MySQL devs, perhaps you can draw their attention to it.

To: mysql@lists.mysql.com
Subject: Tying actions to (re)opening a connection
Date: Sat, 7 Jul 2007 10:44:05 +0100

The Apache DBD module maintains a dynamic pool of connections.

SQL statements may be defined at system startup. Such queries are then treated as prepared statements. In the MySQL driver, the sequence is:

    mysql_real_connect
    for each defined SQL statement {
        mysql_stmt_init
        mysql_stmt_prepare
    }

So far, so good. However, we also need to deal with connections that go stale. Our API defines a check_connection function. mysql_ping is *nearly* right for this, but unfortunately leaves us (silently) with a new connection without the prepared statements.

It would work perfectly if we could tie a callback to mysql_ping. Something like:

    int mysql_ping2(MYSQL *mysql,
                    int (*callback)(MYSQL*, void*),
                    void *dptr);

where the callback (if non-null) is invoked whenever mysql_ping detects a stale connection and reconnects.

Is this something you’d consider adding? Any other suggestions for a workaround to check the connection now?

Posted on July 10, 2007, in apache, mysql. Bookmark the permalink. 2 Comments.

  1. I was about to enter a MySQL feature request per your note, but I noticed two things in the MySQL docs at http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html

    1.) The mysql_ping behavior re: auto-reconnect is intentional. Apps are expected to react to a mysql_ping failure and take actions like re-creating prep stmts, even when auto-reconnect is enabled.

    2.) The auto-reconnect behavior has changed in recent versions of MySQL

  2. Thanks Tom – that looks useful. I was trying to find that OPT_RECONNECT in TFM for mysql_options, but it isn’t there.

    Need to decide how best to deal with this in the context of DBD semantics.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: