Database Compatibility Across PHP Versions

In the vast savannas of East Africa, elephant herds traverse hundreds of square miles, relying on an extraordinary collective memory passed down through generations of matriarchs. When drought strikes and water sources shrink or disappear, this accumulated knowledge determines survival—the herd knows which distant waterholes remain when others dry up, which routes are safe, and where to find sustenance in unfamiliar terrain.

Similarly, when you upgrade your PHP application across major versions, you need that same accumulated knowledge about database compatibility. Which extensions still work? Which patterns lead to “dry holes” of fatal errors? Which paths provide safe passage through breaking changes? If you’ve ever inherited a legacy PHP codebase, you’ve likely encountered the “Call to undefined function mysql_connect()” error that greeted thousands of applications when ext/mysql was removed in PHP 7.0.

This guide serves as that memory, illuminating the terrain of PHP database compatibility. We’ll explore the landscape together—from the evolution of PHP’s database APIs through the specific breaking changes in PHP 7.0 and 8.0, to practical strategies for ensuring your upgrade succeeds. By the end, you’ll have a clear mental map of which paths are safe and which to avoid. Before we get into that, though, let’s take a step back and understand the broader evolution of PHP’s database extensions. This context will help you make informed decisions that serve your application for years to come.

What This Guide Covers

Strictly speaking, database compatibility spans several interconnected concerns:

1. Extension availability: Whether PDO, MySQLi, or pgsql is installed and enabled
2. API changes: Deprecated functions, removed extensions, and new behaviors
3. Error handling: How failures are reported and must be handled
4. Authentication: How your PHP version connects to your database (e.g., caching_sha2_password vs mysql_native_password)
5. Testing strategy: Verifying compatibility before production deployment

We’ll address each of these in turn. Of course, we don’t need to discuss every single database system—we’ll focus on the major players: MySQL/MariaDB, PostgreSQL, and SQLite. Let’s begin with the core choice you’ll face: PDO or MySQLi?

Prerequisites

Before we dive into the technical details, let’s establish what you should already know and have in place:

Required Knowledge:

• Basic PHP programming (functions, classes, error handling with exceptions)
• Familiarity with SQL and relational databases
• Understanding of your current PHP version and target version

Required Environment:

• Access to a development or staging environment for testing
• Command-line access to run PHP and Composer commands
• A working database installation (MySQL, PostgreSQL, or SQLite)

Recommended Tools:

• Composer (for dependency management if using frameworks/ORMs)
• PHPUnit or similar testing framework (for verification)
• mysqldump or equivalent for database backups

If you’re working with a legacy application that still uses the old mysql_* functions, we’ll cover migration strategies in the section on legacy code. For now, assume you’re working with PDO or MySQLi.

Understanding PHP’s Database Extensions: PDO vs MySQLi

Imagine you’re preparing for a long journey across unfamiliar territory. You have two possible maps: one that covers multiple regions with consistent symbols (PDO), and another that provides extremely detailed coverage of a single region but can’t help you elsewhere (MySQLi). Which map serves you better depends on your destination—but for most travelers, the versatile map wins.

If you are working with a legacy PHP application, you might encounter the old mysql_* functions. Strictly speaking, these weren’t just deprecated—they were removed entirely in PHP 7.0. They are insecure and lack the features of modern database extensions. Today, there are two primary ways to connect to a MySQL database:

PDO: The Versatile Standard

PDO is my recommended choice for most applications. Let’s look at a practical example. Suppose you’re building a new application and want to establish a secure database connection. Here’s what that looks like using PDO:

<?php
// Tested with PHP 8.2, MySQL 8.0
$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected successfully\n";
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

If you run this code, you’ll see either “Connected successfully” or an exception with details. Notice the key options:

• PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ensures errors throw exceptions rather than failing silently
• PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC returns associative arrays by default
• PDO::ATTR_EMULATE_PREPARES => false uses native prepared statements for better security

Now, let’s see what happens when we query the database:

<?php
$stmt = $pdo->query('SELECT name FROM products');
while ($row = $stmt->fetch()) {
    echo $row['name'] . "\n";
}

Expected output:

Widget A
Widget B
Widget C

MySQLi: MySQL-Specific Optimization

MySQLi has its place, though. Of course, if you’re working exclusively with MySQL or MariaDB and need access to MySQL-specific features like mysqlnd’s memory usage statistics or multi-query support, MySQLi provides those capabilities. Here’s the same connection using MySQLi’s object-oriented interface:

<?php
// Tested with PHP 8.2, MySQL 8.0
$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$mysqli = new mysqli($host, $user, $pass, $db);
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// Set charset
if (!$mysqli->set_charset($charset)) {
    die('Error loading character set utf8mb4: ' . $mysqli->error);
}

echo "Connected successfully\n";

// Query example
$result = $mysqli->query('SELECT name FROM products');
while ($row = $result->fetch_assoc()) {
    echo $row['name'] . "\n";
}

Notice the differences:

• MySQLi requires separate steps for connection and charset setting
• Error handling uses procedural checks (connect_error, set_charset())
• The query method returns a result set object rather than a statement

MySQLi does have advantages in some scenarios. For instance, mysqli_get_client_stats() (available with mysqlnd) provides detailed memory usage information—something PDO doesn’t expose. However, for most applications, PDO’s portability and cleaner exception-based error handling outweigh these specialized features.

Should You Choose PDO or MySQLi? A Comparison

One may wonder: which approach should I choose? Let me compare them directly to help you decide.

Feature	PDO	MySQLi
Database support	Multiple (MySQL, PostgreSQL, SQLite, and more)	MySQL/MariaDB only
Interface	Object-oriented only	Both procedural and OO
Prepared statements	Native support	Native support
Named parameters	Yes (e.g., :name)	No (only ?)
Portability	High—switch databases by changing DSN	Low—code tied to MySQL functions
MySQL-specific features	Limited	Full access (multi-query, async, stats)
Error handling	Exceptions (configurable)	Mixed (properties + exceptions)
Performance	Excellent	Slightly faster for MySQL-specific ops

Here’s how I’d characterize the trade-offs:

Choose PDO when:

• You might switch database systems in the future
• You prefer cleaner, more consistent OO interfaces
• You want named parameters for readability
• You value portability and maintainability

Choose MySQLi when:

• You need MySQL-specific features (async queries, multi-query, statistics)
• You’re maintaining existing MySQLi code
• You prefer the procedural style (for better compatibility with legacy code)
• You’re absolutely certain you’ll never leave MySQL/MariaDB

Of course, this isn’t a binary choice that must be made once and forever—you can use both in different parts of your application if needed. However, for new projects, I strongly recommend standardizing on PDO. Let’s move on to the breaking changes that affect these extensions.

Major Breaking Changes: Migrating from PHP 5.x

The most significant breaking change in the last decade of PHP was the removal of the original MySQL extension (ext/mysql) in PHP 7.0. To understand the scale of this change, consider that the mysql_* functions were part of PHP since version 2.0 (released in 1996). Any application using mysql_connect(), mysql_query(), and other mysql_* functions would fail immediately after upgrading to PHP 7.0 or newer.

Let’s examine what legacy code looks like and how to migrate it.

Before (Legacy Code - will NOT work in PHP 7.0+):

<?php
// PHP 5.x ONLY - these functions don't exist in PHP 7.0+
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

$sql = 'SELECT name FROM products';
$result = mysql_query($sql);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

while ($row = mysql_fetch_assoc($result)) {
    echo $row['name'] . "\n";
}

mysql_free_result($result);
mysql_close($link);

If you tried this in PHP 7.0 or later, you’d see:

Fatal error: Uncaught Error: Call to undefined function mysql_connect()

After (Modern PDO Code):

The simplest migration path is to convert to PDO. Here’s a direct translation:

<?php
// Works in PHP 7.0+ and PHP 8.x
$host = 'localhost';
$db   = 'test';
$user = 'mysql_user';
$pass = 'mysql_password';

$dsn = "mysql:host=$host;dbname=$db;charset=utf8mb4";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    
    $stmt = $pdo->query('SELECT name FROM products');
    while ($row = $stmt->fetch()) {
        echo $row['name'] . "\n";
    }
} catch (\PDOException $e) {
    die('Database error: ' . $e->getMessage());
}

After (MySQLi Alternative):

If you prefer MySQLi, here’s the procedural approach that feels closest to the original mysql_* style:

<?php
// Works in PHP 7.0+ and PHP 8.x
$host = 'localhost';
$db   = 'test';
$user = 'mysql_user';
$pass = 'mysql_password';

$link = mysqli_connect($host, $user, $pass, $db);
if (!$link) {
    die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}

$result = mysqli_query($link, 'SELECT name FROM products');
if (!$result) {
    die('Invalid query: ' . mysqli_error($link));
}

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "\n";
}

mysqli_free_result($result);
mysqli_close($link);

You might notice that MySQLi’s procedural API uses mysqli_* functions that are quite similar to the old mysql_* functions. This similarity can make migration easier. However, the object-oriented MySQLi interface (shown earlier) is generally preferred for new code.

Modern PHP: Compatibility in PHP 7.x and 8.x

The PDO and MySQLi extensions are stable and well-supported in all modern PHP versions. However, there are still important changes to be aware of, especially when moving to PHP 8.x. Let’s examine the specific changes you’ll encounter.

PHP 7.0 (December 2015): The Breaking Point

As mentioned, PHP 7.0 removed ext/mysql. This is the primary reason legacy applications couldn’t simply upgrade. The version jump from PHP 5.6 to PHP 7.0 also introduced:

• Scalar type declarations for function parameters (with strict types optional)
• Return type declarations for functions
• These changes don’t directly affect database code but can impact how data flows through your application layers

PHP 7.1 (December 2016): Iteration Improvements

PHP 7.1 added nullable return types and void return type. For database code, the most relevant change was continue inside switch statements—not directly database-related but useful for complex query logic.

PHP 7.2 (November 2017): Parameter Type Warnings

PHP 7.2 introduced warnings when counting non-countable types. If you had code like count($result) where $result might be false, you’d now see warnings. This encourages better error checking.

PHP 7.3 (December 2018): Improved Flexibility

PHP 7.3 added is_countable() function to safely check if something is countable. It also improved array_column() to work with objects. These are minor but helpful for database result processing.

PHP 7.4 (November 2019): Typed Properties

PHP 7.4 introduced typed properties and arrow functions. Again, these are language features that improve code quality but don’t break database extensions directly.

PHP 8.0 (November 2020): Significant Behavioral Changes

PHP 8.0 introduced several changes that affect database code:

Default PDO Error Mode Changed

In PHP 8.0, PDO’s default error mode was changed from PDO::ERRMODE_SILENT to PDO::ERRMODE_EXCEPTION. Let me explain what this means in practice.

In PHP 7.x with PDO (silent mode), a failed query would simply return false:

<?php
// PHP 7.x with default PDO::ERRMODE_SILENT
$stmt = $pdo->query('INVALID SQL');
if ($stmt === false) {
    // You had to explicitly check for errors
    $error = $pdo->errorInfo();
    echo "Query failed: " . $error[2] . "\n";
}

In PHP 8.0+ (exception mode), the same invalid query throws an exception immediately:

<?php
// PHP 8.0+ with default PDO::ERRMODE_EXCEPTION
try {
    $stmt = $pdo->query('INVALID SQL');
} catch (\PDOException $e) {
    echo "Query failed: " . $e->getMessage() . "\n";
}

This is a fantastic change for security and code quality, as it forces errors to be handled via exceptions rather than being silently ignored. However, if your existing code does not wrap database queries in try...catch blocks, you may see unhandled exceptions after upgrading to PHP 8.0+.

One may wonder: is this change really necessary? Can’t PDO still work without exceptions? The answer: yes, you can set PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT explicitly in your options. But the PHP core team’s judgment was that exceptions encourage better error handling. My recommendation: embrace the change and wrap your database operations in appropriate try-catch blocks.

Stricter Type Enforcement

PHP’s evolution towards stricter typing can affect how data is handled. For example, passing null to a function that expects an int now triggers a TypeError. This is generally beneficial—database code that assumed 0 when receiving null will now fail fast, revealing edge cases you should handle explicitly.

MySQL Native Driver (mysqlnd)

For years, mysqlnd has been the default driver for all MySQL-related extensions. It offers better performance and more features than the older libmysql client library. If you are on a very old system, ensure mysqlnd is being used by checking phpinfo() output for “Client API library version” showing mysqlnd.

PHP 8.1 (November 2021): Minor Database-Relevant Changes

PHP 8.1 added never return type and readonly properties. It also deprecated passing null to str_contains(), str_starts_with(), and str_ends_with(). These functions might be used in database query building or validation code.

PHP 8.2 (December 2022): Disabling Dynamic Properties

PHP 8.2 deprecates dynamic properties by default. If your code creates properties on database result objects dynamically (e.g., $row->custom_field = 'value'), you’ll see deprecation warnings. This is a feature, not a bug—it catches typos and unintended properties.

PHP 8.3 (November 2023): Latest Stability

PHP 8.3 focuses on consistent type system improvements. Database extensions remain stable with no breaking changes from PDO or MySQLi.

PHP Version Summary Table

Let’s summarize the key database-relevant changes across PHP versions:

PHP Version	Key Database Impact
7.0	Removed ext/mysql entirely; PDO and MySQLi only
7.1-7.4	Minor warning additions; improved error checking
8.0	PDO default error mode changed to exceptions
8.1	String function deprecations affecting query building
8.2	Dynamic property deprecation may affect result handling
8.3	No significant database changes

A Practical Compatibility Checklist

Before you upgrade your PHP environment, follow this checklist to avoid database-related issues.

You might be thinking: “I’ve read the documentation and it feels overwhelming.” That’s a fair concern. The checklist below provides concrete, actionable steps.

1. Audit Your Codebase

Search your entire project for mysql_* functions. If you find any, they must be refactored before you can upgrade to PHP 7+ or 8+. Here’s a quick shell command to find them:

find . -name "*.php" -type f -exec grep -l "mysql_\|mysqli_\|pdo_" {} \;

Of course, this finds all database-connected files. You’re looking specifically for mysql_ (without the “i”). If you only see mysqli_ or pdo, you’re already on the right track.

2. Check Your PHP Extensions

Use the command php -m in your terminal or check the output of phpinfo() to see which database extensions (e.g., pdo_mysql, mysqli, pdo_pgsql) are enabled. Ensure the correct ones are active in your new environment.

Let me show you a practical way to verify:

php -m | grep -E 'pdo|mysqli|pgsql|sqlite'

Expected output (example):

pdo
pdo_mysql
mysqli
pdo_pgsql
pdo_sqlite

If you don’t see the extension you need, you’ll need to install it. On Ubuntu/Debian:

sudo apt-get install php-mysql php-pgsql php-sqlite3

Then restart your web server: sudo systemctl restart apache2 or sudo systemctl restart php-fpm.

3. Review Your Dependencies

If you use a database abstraction layer like Doctrine DBAL or Laravel’s Eloquent ORM, check their documentation and your composer.json file to ensure they are compatible with your target PHP version.

For example, to check your current dependencies:

composer show | grep -E 'doctrine|eloquent|illuminate'

Then visit Packagist (https://packagist.org) and verify the require section includes your target PHP version. Most modern ORMs support PHP 7.4+; many now require PHP 8.0+.

4. Test in a Staging Environment

This is the most critical step. Never upgrade your production environment without first testing your entire application—including all database interactions—in a staging environment that mirrors production.

Let’s walk through a practical verification process:

Step 1: Create a staging database dump

mysqldump -u root -p --single-transaction production_db > staging_dump.sql

Step 2: Import into staging

mysql -u root -p staging_db < staging_dump.sql

Step 3: Configure staging PHP Ensure your staging php.ini uses the target PHP version and has the appropriate extensions enabled.

Step 4: Run your test suite

# If using PHPUnit
./vendor/bin/phpunit --group database

# If using custom integration tests
./scripts/test-db-connection.php

Step 5: Manually verify critical paths Navigate through your application’s database-intensive features (user authentication, data submission, reports, etc.). Look for any warnings, errors, or unexpected behavior.

Step 6: Check logs

tail -f /var/log/php-error.log
tail -f /var/log/nginx/error.log

You’ll want to watch for any database-related warnings during this testing phase.

5. Verify Your Error Handling

Since PHP 8.0 changed PDO’s default error mode to exceptions, you should verify that your database code properly handles exceptions. Search for PDO usage and ensure queries are wrapped:

grep -r "new PDO" app/ --include="*.php"

For each result, confirm that connection and queries are within try...catch (\PDOException $e) blocks.

If you find unprotected queries, you have two options:

1. Add explicit exception handling (recommended)
2. Set PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT in your connection options (workaround, not recommended)

6. Check for Authentication Method Mismatches

Modern MySQL (8.0+) defaults to caching_sha2_password authentication, while older clients expect mysql_native_password. If you see authentication failures like:

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

You’ll need to either:

• Update your MySQL client library (ensure mysqlnd is current)
• Or alter the user: ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

This is a common pitfall when upgrading PHP without also ensuring the MySQL client library is sufficiently modern.

Common Databases: Key Considerations

Let’s examine each major database system and its specific compatibility considerations.

MySQL / MariaDB

For MySQL and MariaDB, the primary concern is the ext/mysql removal. As long as you are using PDO or MySQLi, you should have a smooth transition. Be aware of potential authentication method mismatches (e.g., caching_sha2_password in modern MySQL) which may require configuration changes in your my.cnf file or connection options.

Of course, MariaDB users—who tend to value backward compatibility—will find the transition even smoother. MariaDB maintains the mysql_native_password default, avoiding authentication issues.

One important consideration: if you’re using persistent connections with PDO (PDO::ATTR_PERSISTENT => true), test thoroughly. Persistent connections behave differently across PHP versions, and some edge cases around connection cleanup were fixed in PHP 7.1+.

PostgreSQL

PostgreSQL has a reputation for stability. Its PHP extensions, pdo_pgsql and the procedural pg_* functions, are well-maintained and have not seen major breaking changes in recent PHP versions.

That said, PostgreSQL 12+ introduced some changes to authentication and SSL handling that might affect your connection strings. If you’re upgrading PostgreSQL alongside PHP, review the PostgreSQL release notes for your version jump.

For example, if you’re moving from PostgreSQL 9.x to 12+, you might need to update connection options:

// Before (PostgreSQL 9.x)
$pdo = new PDO('pgsql:host=localhost;port=5432;dbname=mydb', $user, $pass);

// After (PostgreSQL 12+ with SSL required)
$pdo = new PDO(
    'pgsql:host=localhost;port=5432;dbname=mydb;sslmode=require',
    $user,
    $pass
);

SQLite

SQLite is an embedded database that is often included with PHP itself. The pdo_sqlite extension is extremely stable and generally works across all PHP versions without issue, making it an excellent choice for development, testing, and small-scale applications.

Though SQLite is simple, it’s not appropriate for high-concurrency production applications. For those use cases, you’ll want MySQL/PostgreSQL. But for testing, SQLite can accelerate your development cycle dramatically.

One note: if you’re using SQLite with PDO and need specific features like JSON1 extension or FTS (Full-Text Search), verify that your PHP build includes those SQLite extensions. Usually they’re enabled by default, but some stripped-down distributions may not include them.

Should You Consider an ORM?

Earlier we mentioned ORMs briefly. Let me address this more thoroughly, as it’s a common question.

You have several options for database access:

1. Raw PDO/MySQLi (what we’ve shown so far)
2. Database Abstraction Libraries like Aura.Sql or Medoo
3. Full ORMs like Doctrine ORM or Eloquent
4. Query Builders like Laravel’s query builder standalone

The trade-offs are real. Here’s how I’d characterize them:

Approach	Learning Curve	Performance	Flexibility	Portability
Raw PDO	Low	Highest	Full SQL control	Very high (PDO supports many databases)
DB Abstraction	Medium	High	SQL-like syntax	High
ORM	High	Variable (usually lower)	High-level objects	Medium (Doctrine supports many; Eloquent is MySQL-focused)
Query Builder	Medium	High	SQL-like but programmatic	Medium

If you’re building a small application or a microservice, raw PDO is often perfectly adequate. One must wonder: why add the complexity of an ORM when PDO handles the job well?

However, for large applications with complex domain models, an ORM can help manage relationships, lazy loading, and data integrity. Doctrine, for instance, can generate entity classes from your database schema (or vice versa)—a powerful feature for large teams.

My personal preference? Start with PDO. Introduce an abstraction layer only when you find yourself writing the same query patterns repeatedly or when your business logic becomes too coupled to SQL. Don’t prematurely optimize with an ORM.

A Walkthrough: Verifying PDO Exception Behavior

Let’s walk through a practical exercise to understand PDO’s exception behavior across PHP versions. This will help you diagnose issues if your application behaves differently after upgrading.

Step 1: Create a test script

Create a file called test-pdo-error-mode.php:

<?php
// test-pdo-error-mode.php

$host = '127.0.0.1';
$db   = 'nonexistent_db';  // Intentionally wrong to trigger an error
$user = 'root';
$pass = '';

$dsn = "mysql:host=$host;dbname=$db;charset=utf8mb4";

echo "Testing PDO connection with DSN: $dsn\n";
echo "PHP Version: " . PHP_VERSION . "\n\n";

try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);
    echo "Connected (unexpected)\n";
} catch (\PDOException $e) {
    echo "Caught PDOException:\n";
    echo "  Code: " . $e->getCode() . "\n";
    echo "  Message: " . $e->getMessage() . "\n";
}

Step 2: Run the script

php test-pdo-error-mode.php

Step 3: Observe the output

Expected output (PHP 8.0+):

Testing PDO connection with DSN: mysql:host=127.0.0.1;dbname=nonexistent_db;charset=utf8mb4
PHP Version: 8.2.10

Caught PDOException:
  Code: 1049
  Message: SQLSTATE[HY000] [1049] Unknown database 'nonexistent_db'

Step 4: Try without the try-catch

Modify the script to remove the try-catch block and run again. In PHP 8.0+, you’ll get an uncaught exception with a full stack trace. In PHP 7.x with PDO’s default silent mode, $pdo would be a PDO object set to false internally, and no error would surface until you tried to query.

You can also test the silent mode explicitly:

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT
]);

$stmt = $pdo->query('SELECT * FROM nonexistent_table');
if ($stmt === false) {
    $error = $pdo->errorInfo();
    echo "Query failed: " . $error[2] . "\n";
}

This walkthrough demonstrates how error handling changed. You also may notice that the exception message includes both the SQLSTATE code (1049) and the database-specific error code (the second number). This dual error reporting helps you diagnose issues precisely.

Verification and Testing

Now that we’ve covered the technical details, let’s discuss how to verify your application’s database compatibility before upgrading production. Of course, testing is where theory meets reality.

Creating a Comprehensive Test Plan

Your test plan should include:

1. Unit tests for data access layers
2. Integration tests that exercise full database workflows
3. Manual testing of critical user paths
4. Load testing if your application handles high traffic
5. Error scenario testing to ensure failures are handled gracefully

Let me show you a practical verification script you can adapt:

<?php
// verify-db-compatibility.php
// Run this on your staging environment after PHP upgrade

$tests = [
    'pdo_connection' => function() {
        try {
            $pdo = new PDO(
                'mysql:host=127.0.0.1;dbname=test;charset=utf8mb4',
                'root',
                '',
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_EMULATE_PREPARES => false,
                ]
            );
            return ['success' => true, 'message' => 'PDO connection successful'];
        } catch (\PDOException $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    },
    
    'mysqli_connection' => function() {
        $mysqli = new mysqli('127.0.0.1', 'root', '', 'test');
        if ($mysqli->connect_error) {
            return ['success' => false, 'message' => $mysqli->connect_error];
        }
        $mysqli->close();
        return ['success' => true, 'message' => 'MySQLi connection successful'];
    },
    
    'prepared_statements' => function() {
        try {
            $pdo = new PDO(
                'mysql:host=127.0.0.1;dbname=test;charset=utf8mb4',
                'root',
                '',
                [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
            );
            
            $stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
            $stmt->execute(['id' => 1]);
            $result = $stmt->fetch();
            
            return ['success' => true, 'message' => 'Prepared statements work correctly'];
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    },
    
    'transaction_handling' => function() {
        try {
            $pdo = new PDO(
                'mysql:host=127.0.0.1;dbname=test;charset=utf8mb4',
                'root',
                '',
                [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
            );
            
            $pdo->beginTransaction();
            // Simple test query within transaction
            $stmt = $pdo->query('SELECT COUNT(*) FROM information_schema.tables');
            $count = $stmt->fetchColumn();
            $pdo->rollBack();
            
            return ['success' => true, 'message' => 'Transactions function properly'];
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    },
];

echo "Database Compatibility Verification\n";
echo "PHP Version: " . PHP_VERSION . "\n\n";

$allPassed = true;
foreach ($tests as $name => $test) {
    $result = $test();
    $status = $result['success'] ? '✓ PASS' : '✗ FAIL';
    echo sprintf("%-30s %s\n", $name, $status);
    if (!$result['success']) {
        echo "  Error: " . $result['message'] . "\n";
        $allPassed = false;
    }
}

echo "\n";
echo $allPassed ? "All tests passed!\n" : "Some tests failed. Investigate before upgrading.\n";

Run this script on your staging environment:

php verify-db-compatibility.php

Expected output:

Database Compatibility Verification
PHP Version: 8.2.10

pdo_connection               ✓ PASS
mysqli_connection            ✓ PASS
prepared_statements          ✓ PASS
transaction_handling         ✓ PASS

All tests passed!

What to Look For

During testing, watch for:

• Connection failures: Authentication errors, missing extensions, network issues
• Query errors: SQL syntax issues, table/column mismatches, type errors
• Silent failures: Queries returning unexpected results without errors
• Performance regressions: Queries that are suddenly slower
• Memory issues: Leaks or excessive memory usage (especially with persistent connections)

Troubleshooting

Even with thorough testing, you may encounter specific issues during upgrade. Let’s address the most common problems.

”Call to undefined function mysql_connect()”

Symptom: Your application immediately fails with this error when you upgrade to PHP 7.0+.

Cause: Your code still uses the old mysql_* functions that were removed.

Solution: You must migrate to PDO or MySQLi. We covered the migration patterns earlier. If you have a large codebase, consider:

1. Using a tool like php7-mysql (a compatibility layer)
2. Gradually refactoring module by module
3. Prioritizing critical paths first

PDO Exceptions Not Being Caught

Symptom: After upgrading to PHP 8.0, database errors now throw uncaught exceptions.

Cause: By default, PDO now throws exceptions, but your code doesn’t have try-catch blocks.

Solution: Add exception handling to your database operations:

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    $stmt = $pdo->query('SELECT * FROM table');
    // Process results
} catch (\PDOException $e) {
    // Log the error appropriately
    error_log('Database error: ' . $e->getMessage());
    // Show user-friendly message or rethrow
    throw new \RuntimeException('Database temporarily unavailable');
}

Alternatively, if you need a quick workaround, explicitly set error mode to silent:

$options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_SILENT;

But this is not recommended—it just hides the real issue.

MySQL Authentication Fails with “caching_sha2_password”

Symptom: After upgrading PHP, MySQL connections fail with authentication error.

Cause: MySQL 8.0+ defaults to caching_sha2_password, but older PHP/mysqlnd versions expect mysql_native_password.

Solution: Either upgrade your mysqlnd driver (recommended) or change the MySQL user’s authentication method:

ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

If you control the MySQL server configuration, you can also set default_authentication_plugin=mysql_native_password in my.cnf and restart MySQL.

Dynamic Property Deprecation in PHP 8.2

Symptom: You see warnings like “Creation of dynamic property” when fetching database results.

Cause: Your code (or a library you use) is setting properties on stdClass objects that don’t exist in the class definition. PHP 8.2 deprecates this behavior.

Solution: Use associative arrays instead of objects, or define proper classes. For PDO:

• Use PDO::FETCH_ASSOC instead of PDO::FETCH_OBJ
• Or define a class with the expected properties

If the issue is in a third-party library, check if an updated version exists that’s compatible with PHP 8.2.

Persistent Connection Issues

Symptom: After upgrading PHP, persistent connections behave differently—connections aren’t reused, or you see intermittent failures.

Cause: Persistent connection handling changed between PHP versions; edge cases around connection cleanup were fixed in PHP 7.1+.

Solution: Test thoroughly with and without persistent connections. Consider:

• Disabling persistent connections during migration: PDO::ATTR_PERSISTENT => false
• Ensuring your code handles connection failures gracefully
• Monitoring connection counts on your database server

Conclusion: Long-Term Compatibility

Navigating database compatibility is a manageable but essential part of the PHP upgrade process. By understanding the shift from legacy extensions, embracing modern APIs like PDO, and following a rigorous testing plan, you can ensure your application remains stable and reliable.

The key takeaways are:

• If you see any mysql_ functions in your codebase (without the “i”), your code will break on PHP 7.0 and newer. There’s no workaround; these functions don’t exist.
• Standardize on PDO for its portability, modern features, and clean error handling. MySQLi is acceptable for MySQL-specific projects that need MySQL-only features.
• Be prepared for stricter error handling, especially PDO’s exception mode in PHP 8.0+. Wrap your database operations in appropriate try-catch blocks.
• Always test your application in a staging environment before upgrading production. This includes running your full test suite, manual verification of critical paths, and checking error logs for warnings.
• When moving to PHP 8.0+, review your code for any areas that assumed silent PDO errors—these will now throw exceptions.
• For PostgreSQL and SQLite, the transition is generally smoother, but verify authentication and SSL configurations for PostgreSQL.
• Consider whether an ORM or query builder makes sense for your project’s scale and team size. Start simple; add abstraction only when needed.

Upgrading PHP doesn’t have to be a terrifying experience. With careful planning, understanding of the breaking changes, and thorough testing, you can confidently modernize your application’s stack while maintaining database compatibility.

Remember: the goal isn’t just to make your code work on the newest PHP version—it’s to build a foundation that will serve you well for years to come. Write code that’s clear, maintainable, and defensive about errors. Your future self will thank you.