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.

Answer 3

The legacy mysql_* functions were removed in 5.5.0, which was released today (6/20/2013). I realize your hands are tied in regards to this old database, but keep in mind what you are now experiencing is know as technical debt, and it's an important concept to understand and to explain to the people in charge.

I'm sure you already know the dangers of old software (exploits, etc) but obviously this applies to your PHP as well. Currently 5.4.x is still supported, but generally speaking only the 2 most recent versions of PHP are supported, so that means 5.3.x is expiring soon, and only 5.5.x and 5.4.x are supported. Because of your need for legacy MySQL, you're already using the oldest supported PHP, and that's a dangerous game to play.

If you were hoping for some silver bullet, I can't put it any simpler than: there isn't one. You have to make a hard choice, support legacy and open yourself up to eventual exploits, or draw a line in the sand and just say no. I'd like to say, in all my years of IT, I've never heard anybody say (particularly on the business side of things) how happy they were for keeping with the old legacy stuff.

Answer 4

From mysql_* to PDO isn't really that bad, PDO just seems a little frightening at first, and agreed, is somewhat more complicated, at least for retrieving data from queries (IMO).

It might be a bit at first, but it shouldn't be too bad, and it's more secure as well, in addition to being one of the two current standards.

For example,

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

and

mysql_query($sql)

become

$link = new PDO("mysql:host=localhost;dbname=$dbname", $dbuser, $dbpass);

and

$statement = $link->prepare($sql);
$statement->execute(values);

Answer 5

This could be quite a simple solution... As you are running legacy applications on a outdated MySQL Database revision.. Why don't you keep contiunity? I'm assuming that the applications are hosted on a server which is maintained by yourself and not a third party (web hosting)

What i'd suggest doing is having a different machine.. Possibly a Virtual Server running mysql4.0 and a version of PHP which suits the applications.. Then migrate all legacy over to the new server.. Then you will no have to worry about the removal/depreciation of mysql_* functions..

With your main server, keep upgrading as you wish to keep with your current development requirements?

Answer 6

This is an old question, but surprisingly relevant. The answer is actually pretty easy. If you have an old mysql that you need data pulled out from, via a "new" version of PHP, just setup a slave with a new version of MariaDB. It should be part of your migration process.

Replicate to a new version of MariaDB, and connect to that slave to read data (and you should set it up as a read-only replica).

Answer 7

This answer was written while the OP believed PDO could connect to MySQL 4.

What you could maybe do is rewriting the mysql functions with the PDO driver, so you don't change your original code. If I take the examples given by Serdnad, you could do something like:

function mysql_connect($dbhost, $dbusr, $dbpass){
  return new PDO("mysql:host=$dbhost;dbname=mydb", $dbuser, $dbpass);
}

function mysql_select_db($db, $link=false){
  // If you were not using the link argument you might use some global instead
  $link->query("USE $db");
  return 1;
}

function mysql_query($sql, $link=false){
  // If you were not using the link argument you might use some global instead
  $statement = $link->prepare($sql);
  return $statement->execute(values);
}

Well, there are quite a few mysql_* functions but in the end you can find out which ones you really use (which might be only a few) with a simple regex search in your source.

I think you'd have to be smart with some functions such as mysql_escape_string, mysql_affected_rows, or mysql_insert_id but I am pretty sure it's doable.

If your extension is still active when you redefine the functions you will get a fatal error that you can avoid by using a namespace, which would be easy to add to your code. If you disable mysql ext I think you can achieve your goal without changing a line of your original code.