Connection closed?

at 2006-06-12 in Examples by friebe (0 comments)

If you work with MySQL a lot, you've probably already seen the message "Lost connection to MySQL server during query". This occurs when for example the server is shut down (or otherwise exits) while a client is querying the database or when a (server-configurable) timeout has been reached.

The XP framework now transforms this error to a specialized exception, the SQLConnectionClosedException. For details, see RFC #0058.

Trying it out
If you happen to have a MySQL daemon running somewhere you don't need for productive use and which you are able to shutdown, you can test the behaviour with the following script we also used to test our implementations:

  require('lang.base.php');
  xp::sapi('cli');
  uses('rdbms.DriverManager');

  $db= &DriverManager::getConnection($argv[1]);
  $db->connect();
  while (1) {
    try(); {
      $q= &$db->query('select 1');
    } if (catch('SQLException', $e)) {
      $e->printStacktrace();
      exit(-1);
    }
    
    Console::writeLine(xp::stringOf($q->next()));
    sleep(1);
  }

Instructions:

Start it from the command line with the following command:
$ php disconnect.php 'mysql://user:password@host'
Watch the 1s scrolling by
Shut down the RDBMS server
Observe the exception's stacktrace

For MySQL, you'll see the errorcode #2013 and the message "Lost connection to MySQL server during query" appear.

Making use of the new exception
Note: Old code will continue to run unmodified, migration will give you benefits but is not required...

If your old scripts tried to handle this case they might've looked something like this:

  try(); {
    $conn->query(...);
  } if (catch('SQLStatementFailedException', $e)) {
    if (2013 == $e->getErrorcode()) {
      // Received disconnect during query
      $conn->connect(TRUE);
    } else {
      $e->printStackTrace();
    }
  }

This can now be rewritten to the following:

  try(); {
    $conn->query(...);
  } if (catch('SQLConnectionClosedException', $e)) {
    $conn->connect(TRUE);
  } if (catch('SQLStatementFailedException', $e)) {
    $e->printStackTrace();
  }

and will now also be independant of the hardcoded error number 2013 and thus also work for other RDBMS connections such as Sybase.

Note: Support for this in the PostgreSQL connection API will be added ASAP, we just don't have anyone with a PostgreSQL server around at the moment

You can subscribe to the XP framework's news by using RSS syndication.

Connection closed?

Subscribe

Categories

Related