mysql_connect / mysql_close

This one looks straightforward, but is actually one of the most complicated changes. Depending on the application, it can be a non-issue or the biggest hurdle.

Connection Visibility

The mysqli_connect function no longer returns a resource but rather a mysqli object, which needs to be passed around in the application. The original application might not have been passing the resource object and instead relying on mysql_ functions to access the last connection internally.

With mysqli, this means that we would need to store these connections in a centralized location, as that would most closely mimic the mysql_connect behavior. For example, we could create a class named MysqlToMysqli and store the mysqli objects in a static property.

New Link

If we make a second call with the same arguments, mysql_connect won't connect again, but will instead return the existing connection (link). We can override this behavior by passing true as the fourth argument, which will then always create a new link. In this example that reuses the link, both references are strictly equal and can be used interchangeably.

$mysql1 = mysql_connect('host', 'user', 'pass');
$mysql2 = mysql_connect('host', 'user', 'pass');
// $mysql1 === $mysql2;

The mysqli_connect function, on the other hand, always creates a new link and there is no way to override this.

$mysqli1 = mysqli_connect('host', 'user', 'pass');
$mysqli2 = mysqli_connect('host', 'user', 'pass');
// $mysqli1 !== $mysqli2;

If we want to maintain the behavior of the mysql extension while using mysqli, we could maintain a list of connections indexed by the provided arguments. This only applies to cases where mysql_connect did not receive the new_link argument, or received a value of false.

$args = json_encode(func_get_args());
self::$mysqliLinks[$args] = mysqli_connect($host, $user, $pass);

Connection Reference Count

In addition to reusing connection objects, the mysql extension cares about how many references we have to that connection object. This has an impact when we attempt to close a connection using mysql_close.

Let's say we open a connection using the same arguments twice, and store both connection references.

$mysql1 = mysql_connect('host', 'user', 'pass');
$mysql2 = mysql_connect('host', 'user', 'pass');

If we were to close $mysql1 and use it later anyway, it would still work. This is because we had two references to the same connection, which means that mysql_close merely decremented an internal counter, and did not really close the connection.

mysql_close($mysql1);
mysql_query('show tables', $mysql1);

If we were to close it a second time using the exact same variable name, it would fail with a warning:

mysql_close($mysql1);
mysql_close($mysql1);
mysql_query('show tables', $mysql1); // mysql_query(): 5 is not a valid MySQL-Link resource

It doesn't even matter which of the two references we close and in what order. We need to call mysql_close as many times as we stored the output of mysql_connect (receiving the same arguments).

Problem

The existing application may be calling mysql_close, but then still use that connection object later on. With mysqli_close, our connection will always close, so subsequent calls using that connection object would start failing. This can cause the application to crash unexpectedly.

If your application does not have explicit mysql_close calls, then this problem shouldn't affect you.

Solution

The code can be refactored to adapt to the mysqli_close behavior, but that is very risky and is usually very costly. We don't recommend attempting this unless the application has extensive acceptance tests.

Here is the approach that allowed us to permanently solve the problem on very large codebases:

1. Track the connections, and the number of times they were reused (with references being stored).
2. Decrement that counter whenever close is called, but don't close the connection yet.
3. Once the counter reaches zero, close the connection.