Databases

Heads up: Using local sockets to connect to MySQL

MySQL allows running MySQL servers with disabled networking. This is suggested as security measure if the application and the database server run on the same machine, but in reality we usually don't have this kind of a setup except on the developers' machines. This is where we hit an inconsistency in our userland MySQL driver "mysqlx": It wasn't connecting to the MySQL server on localhost, where the standard MySQL driver was. This has been fixed in the meantime.

Some research into this topic revealed the following:

• MySQL uses an AF_UNIX socket on Un*x systems. By default, this is in /tmp/mysql.sock
  
• On Windows, a named pipe serves the same purpose. It's usually called \\.\pipe\MySQL
  
• The MySQL libraries will connect via these local sockets if "localhost" is supplied as host name

  // Connect to the MySQL server on this machine via local sockets.
  // Determine socket by using OS-dependant lookup mechanisms.
  $conn= DriverManager::getConnection('mysql://./NEWS');
  
  // Supply local socket's name
  $conn= DriverManager::getConnection('mysql://./NEWS?socket=/tmp/mysql.sock');
  $conn= DriverManager::getConnection('mysql://./NEWS?socket=\\\\.\\pipe\\mysql');

  $conn= DriverManager::getConnection('mysql://localhost/NEWS');

Support for SQLite3 / SQLite2 deprecation

With pull request #104 implemented the XP Framework now supports SQLite v3 databases via PHP's sqlite3 extension. To acquire a "connection" to an SQLite database, use "." as the hostname and supply the filename where you usually put the database in the DSN string:

  $conn= DriverManager::getConnection('sqlite://./test.db?autoconnect=1');

Explicitely selecting drivers in DriverManager

In a recent discussion in the XP Framework's issue tracker, the topic was brought up that the database connectivity layer was preferring the userland MySQL implementation over PHP's builtin-MySQL support. This change occurs in PHP versions using the mysqlnd implementation (default as of PHP 5.3.0) and was causing productive applications to malfunction.

While the change in the XP Framework was made due to the fact that mysqlnd doesn't support older MySQL versions, matching our BC strategy, and there should've been more testing before deploying upgraded versions of both PHP and the XP Framework, it's still undesirable that there's no easy way "back".

TDS Protocol implementation

One of the XP Framework's strategies is to keep compatibility over a large number of PHP versions on multiple platforms. For example, the message digest API contains a workaround for certain PHP versions with a broken CRC32b implementation, hiding it transparently from the user. In other places like the lang.Process class, we take care of platform differences, employing OS and feature detection, and even compensating for some implementation vagaries. Constructs like this can be found in various other places in our code base, and while this is definitely not desirable, it at least saves the user from going through this hell, and this way, we support the full range of PHP 5.2.10 through 5.3.8 (inofficially 5.2.0 - 5.5.0-dev also works) on a variety of Windows and Un*x systems.

Following this strategy, we go as far as rewriting functionality previously available through a PHP extension to userland implementations: The FTP API (because of limitations in streaming support), the parse_url() function rewrite to work around behaviour changes, userland ini file parsing to support Unicode, a reimplemented MD5-crypt to compensate for a critical bug in crypt() in PHP 5.3.7 and our own MySQL protocol implementation to be able to support old MySQL 4.x instances, to name only a few cases.

The TDS protocol implementation supporting connectivity with Microsoft SQL Server and Sybase database servers is the most recent addition to this stack. Though not yet completely finished, we expect to be able to add it to one of the upcoming 5.8 releases.

5.8.1-RELEASE: Command line, chains, testing and MySQLx

The XP group would like to announce the immediate availability of XP, version 5.8.1. This release includes a bigger change to the exception mechanism, lang.ChainedException's functionality has been moved to the base class lang.Throwable and all exceptions may now optionally have a cause. This is consistent with Java (since 1.4, java.lang.Throwable has getCause() and corresponding constructors), C#'s InnerException and PHP's "previous" exception (available since 5.3.0). Other bigger changes are now that MySQL connectivity is provided by a userland driver implementation in certain cases - see Heads up: XP Framework and MySQL for further details. Also noteable are the possibility to run a single test method from the command line as well as the command line reflection improvements.

For a full list of changes and to download the release, visit:

http://releases.xp-framework.net/xml/release?5.8.1

Heads up: XP Framework and MySQL

The XP Framework's native MySQL driver, rdbms.mysqlx.MySqlxConnection has been committed to SVN head and will now snap into place in two cases:

• When no MySQL driver is loaded in PHP, that is, neither ext/mysql nor ext/mysqli is available
  
• ...and, maybe more surprisingly, when the mysqlnd extension is in use. This means basically any Windows installation with PHP 5.3+ will use our userland driver to speak to MySQL servers.
  

Available database drivers

Here's how to find out which database drivers are available (keys) - and what classes they're implemented by (values):

  Console::writeLine(DriverManager::getInstance()->drivers);

XP: Log SQL queries

Reading PHP: 62 characters to see all MySQL queries, I thought I'd post a similar use case solution here. The task is to log all SQL queries and a backtrace of where they're executed from. In Ulf's post, this is accomplished by an extension function written in C, which needs to be compiled and loaded into PHP.

Altough a nice feature, this way has several downsides, first of all, the compilation, a hurdle big enough to keep me from trying it out, and second, it's only going to work with MySQL databases (and to be precise, only those newer than 4.0, because mysqlnd doesn't support MySQL's old password protocol handshake).

Userland database driver implementations

Following a discussion we had about software packaging for our test, qa and live clusters (most of which run Debian Lenny, meaning it brings PHP 5.2.6 with the mssql extension, unfortunately unsuitable for ASE 15 / univarchar fields), we decided two things:

1. We need to compile a more recent PHP version with sybase_ct ourselves
2. We should investigate in more independence from installed system libraries and PHP extensions

RFC #0203: Unbuffered queries

Scope of Change
Unbuffered queries will be supported by a dedicated API. Instead of using rdbms.DBConnection::query() (or any of the insert, update, delete or select methods), the result-only rdbms.DBConnection::open() method will support unbuffered queries.

Rationale
Incremental row processing to save memory.

Read the full RFC here

Subscribe

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

Categories