Connecting to old MySQL servers

Question

I understand that PHP's mysql_* functions are deprecated and I should not be using them.

However, we have several legacy MySQL 4.0 databases, which neither the mysqli_* nor PDO functions support connecting to. What is the best way to continue using the latest PHP versions and still be able to connect to these databases?

(Based on the description at the top of the PDO intro page, I initially thought PDO might be an option, but the Changelog further down the page suggests that support for pre-4.1 has been dropped in PHP 5.4)

I understand that MySQL 4.0 is 10 years old and the real problem here is that we're still using it. Upgrading MySQL is a separate issue that's outside the scope of both my influence and this question. What I do have control over is the PHP version that we use - and I'd hate to have to stop upgrading PHP just because we need to connect to some old databases.

Even PDO will no longer connect to these old MySQL servers.

I should also clarify that we have several legacy applications accessing these databases, and we want to change these applications as little as possible. They are not in active development, and testing a change that involved rewriting large sections of code would quickly balloon into a rather large QA project.

For this reason, invasive solutions such as rewriting the code or upgrading the MySQL version, are highly unlikely to be worthwhile. If they are the only solutions available, we'll probably end up doing nothing - using mysql_* as long as possible, and then freezing the PHP version (at least for these apps) as soon as the latest PHP can no longer connect.

On the other hand, technically complex solutions (such as compiling something from scratch) are definitely possible, and are actually preferred to making extensive code changes.

Answer 1

You can use either mysql_* functions or PDO to connect from PHP 5.4 to MySQL 4.0. I just built MySQL 4.0.30 and launched it with MySQL Sandbox. Then I tested the code example below successfully.

However, you can't use the TCP/IP protocol.

You can only access MySQL locally, using the unix socket. Use a hostname of 'localhost' and make sure your php.ini has configured the mysql.default_socket correctly for your MySQL instance.

To use the local access method, your PHP application and your database server must be installed on the same host.

<?php

print "PHP VERSION = " . phpversion() . "\n";

print "\nTEST DEPRECATED EXT/MYSQL:\n";
mysql_connect('localhost', 'msandbox', 'msandbox');
$result = mysql_query("SELECT VERSION() AS 'MySQL VERSION'");
while ($row = mysql_fetch_assoc($result)) {
  print_r($row);
}

print "\nTEST PDO_MYSQL:\n";
try {
  $dbh = new PDO('mysql:host=localhost', 'msandbox', 'msandbox');
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch(PDOException $err) {
  die($err->getMessage());
}

$stmt = $dbh->prepare("SELECT VERSION() AS 'MySQL VERSION'");
$result = $stmt->execute();
foreach ($stmt->fetchAll() as $row) {
  print_r($row);
}

Here's the output of the above script on my system:

PHP VERSION = 5.4.15

TEST DEPRECATED EXT/MYSQL:
Array
(
    [MySQL VERSION] => 4.0.30
)

TEST PDO_MYSQL:
Array
(
    [MySQL VERSION] => 4.0.30
)

Answer 2

Why not upgrade mysql? If you want minimal change, then upgrading to 4.1 is enough to mysqli running. I think this is the first thing that you should try because you can have a test environment to see if the old apps worked with 4.1. You also have the list of changes in 4.1 that you might need to check, they're in http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-previous-series.html

If testing 4.1 expose some issues, then you'll know that a more radical change will be needed and you need that information to build a strategy. I know that you want to keep updating PHP, just don't leave mysql and the OS left behind. I'd have a stable combination of mysql, os and php to run in production. When it's time to move on, build a complete new environment.