The master guide to legacy migration: from domino effects to architecture mismatch

The Master Guide to Legacy Migration: From Domino Effects to Architecture Mismatch#

Corporate infrastructure specialists know that migrating legacy systems is rarely a predictable workflow. Recently, I managed a high-complexity transition: moving robust accounts from obsolete servers (MySQL 5.7) to modern environments (MariaDB 10.11 / CloudLinux 9). What was projected as a standard block transfer turned into an analytical hunt for compatibility bugs.

Below, I deconstruct the major failure pillars and the debugging processes involved in this journey.

1. ModSecurity and SQL strict mode collision#

During the restoration of the database on the new MariaDB 10.11 server, the first symptoms of rejection emerged at the WAF (Web Application Firewall). ModSecurity blocked legitimate requests due to structural errors generated by the database engine's behavior.

A. Strict mode behavior#

MySQL 5.7 allowed, by default or through permissive configurations, the recording of invalid date fields (e.g., 0000-00-00 00:00:00) and accepted silent truncation of strings larger than the column's physical limit. In MariaDB 10.11, strict mode (STRICT_TRANS_TABLES) is active by default. Any incompatible insertion generates a blocking database engine error, breaking the PHP transaction.

B. Diagnosis and resolution in SQL mode#

To screen the status of database behavior flags, execute:

# Check the active sql_mode in the MariaDB database
mysql -e "SHOW VARIABLES LIKE 'sql_mode';"

If STRICT_TRANS_TABLES or ONLY_FULL_GROUP_BY flags are active and causing crashes in old CakePHP applications, you must mitigate this by editing the database global configurations temporarily (or adjusting the application connection settings).

2. PHP sessions and dynamic link incompatibilities (openssl mismatch)#

Another classic error when migrating legacy systems involves the PHP execution stack (LSAPI/FPM). When moving compiled applications to modern servers running CloudLinux 9, legacy PHP binary initialization (e.g., legacy PHP 5.6 or 7.2) fails due to outdated shared OpenSSL libraries on the operating system.

A. Shared library (.so) diagnosis#

The modern operating system delivers the libssl.so.3 library (OpenSSL 3.x), while legacy PHP compilations look for the physical signature libssl.so.1.1 (OpenSSL 1.1.1). This mismatch prevents the cryptography extension and the curl module from loading in PHP, blocking external connections and HTTPS session routines.

# Audit the dynamic dependencies of the lsphp binary searching for ssl references
ldd /usr/local/lsws/lsphp72/bin/lsphp | grep -i ssl

If the command returns "not found" for the old OpenSSL keys, the interpreter will fail to initialize authentication and encryption routines in PHP.

3. The legacy limit: MariaDB 10.11 vs. cakephp 2.0#

With regularized traffic, we faced Error 503 (Service Unavailable). Via lvetop, I identified the user hitting 200% SPEED and IOPS saturation.

Investigating the kernel#

I used strace to diagnose lsphp processes active for hundreds of seconds:

# Monitor CloudLinux CPU, IO, and process resource usage
lvetop

# Monitor active lsphp processes and consumed CPU times
ps aux | grep lsphp

# Inspect ongoing syscalls in the hung LSAPI process
strace -p [PID]

The output returned restart_syscall. The process was in I/O Wait, hung at the MariaDB socket waiting for a transaction response.

Traceability: the MariaDB optimizer#

Using SHOW FULL PROCESSLIST, I captured the query. The issue was in the CakePHP 2.0 billing module, which used correlated subqueries. The MariaDB 10.11 optimizer ignored the index on the orders table, resorting to a Full Table Scan of 565 million iterations.

4. SQL query audit: optimizers, EXPLAIN, and indexes#

To understand query inefficiency and apply database corrections, we must audit the query execution plan with EXPLAIN and validate the presence of indexes.

A. Application version verification#

Before proposing code modifications in the CakePHP framework, identify the declared version:

# Check the declared CakePHP framework version in Config/core.php
cat /home/user/public_html/Config/core.php | grep -i "version"

B. Execution plan analysis (EXPLAIN)#

Upon capturing the correlated subquery causing the hang, execute EXPLAIN in the MySQL console:

-- Analyze the execution plan of the problematic query on orders table
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

Expected Inefficient Output (No Indexes): The type field showing ALL indicates that MariaDB will perform a full disk scan (Full Table Scan).

+----+-------------+--------+------------+------+---------------+------+---------+------+-----------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows      | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-----------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 565000000 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-----------+----------+-------------+

Expected Optimized Output (With Indexes): The type field changed to ref and the number of rows (rows) reduced to a small fraction of records indicates the correct usage of indexes.

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_orders_user_status| idx_orders_user_status| 8       | const |   50 |   100.00 | Using index |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+

C. Database index audit and creation#

To check existing indexes on the orders table:

# Check structured indexes on the orders table
mysql -u user -p -d database -e "SHOW INDEX FROM orders;"

If there is no index for user_id and status, create a composite index to avoid full table scans:

-- Create a composite covering index to optimize the query on orders
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

5. Preventive backup and rollback protocols#

Upgrading legacy databases requires atomic and consistent backups of both code and data structures to ensure a fast rollback path in case of failure.

A. Preventive backup scripts#

Generate dated backups of the database and application directory before applying changes or migrating engines:

# Perform a complete database backup saving to /root/
mysqldump -u root -p --all-databases --single-transaction --quick > /root/all-databases-$(date +%Y%m%d).sql

# Compress the folder containing the legacy CakePHP application code
sudo tar czf /root/cakephp-backup-$(date +%Y%m%d).tar.gz /home/user/public_html/

B. Rollback procedure#

If the migration or database adjustments cause severe regressions in production tables, execute the rollback immediately:

# Restore the databases from the preventive SQL dump
mysql -u root -p < /root/all-databases-$(date +%Y%m%d).sql

# Restore the original code files in the cPanel/CloudLinux working directory
sudo rm -rf /home/user/public_html/*
sudo tar xzf /root/cakephp-backup-$(date +%Y%m%d).tar.gz -C /home/user/public_html/

6. Post-fix validation and continuous monitoring#

After applying database indexes or migrating to a dedicated VPS running native MySQL via Governor, execute operational tests to ensure service health.

A. Accessibility test and PHP logs#

Validate that web requests respond normally and there are no thread locking issues:

# Test if the web application returns an HTTP 200 code
curl -I https://site.com/

# Audit the legacy application PHP error logs looking for warnings or fatal errors
tail -50 /home/user/public_html/error_log

# Test immediate database connectivity via the terminal
mysql -u user -p -e "SELECT 1;"

B. Slow query log monitoring#

The slow query log is the most effective tool to detect performance degradation over time.

# Enable slow query logging and set the threshold limit to 2 seconds
mysql -e "SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2.0;"

# Monitor slow queries executed on the database server in real-time
tail -f /var/log/mysql/slow-query.log

7. MySQL → MariaDB migration checklist#

Use the checklist below to verify that all phases of the legacy database migration are completed:

1. Pre-migration#

• [ ] Perform structured database backup using mysqldump with safe flags.
• [ ] Compress the legacy application directory containing the CakePHP framework.
• [ ] Map and document active CakePHP versions and system SSL library layouts.
• [ ] Identify and log the queries consuming the most execution time.

2. Migration execution#

• [ ] Perform the database restore on the new MariaDB instance.
• [ ] Check table syntax integrity: mysqlcheck --all-databases.
• [ ] Adjust global sql_mode parameters to prevent strict mode application crashes.
• [ ] Audit dynamic OpenSSL dependencies on legacy lsphp instances.

3. Technical validation#

• [ ] Run EXPLAIN on complex queries targeting large core tables.
• [ ] Validate the presence of composite indexes on frequently queried columns.
• [ ] Verify HTTP status codes on the home page and critical internal routes.
• [ ] Review Apache/LiteSpeed and PHP logs at the path /home/user/public_html/error_log.

4. Post-installation monitoring#

• [ ] Enable and monitor /var/log/mysql/slow-query.log in real-time.
• [ ] Track CPU and IOPS consumption using CloudLinux lvetop command.
• [ ] Validate the server Load Average baseline for a continuous 24-hour window.

8. Legacy migration risk and mitigation matrix#

The following matrix displays the risks mapped during this architectural transition and their mitigations:

Technical conclusion#

This case demonstrates that infrastructure evolution can break systems depending on obsolete behaviors. The engineer's role is to identify when software has reached the end of the line for current hardware. MariaDB 10.11 is strict, and its query optimizer requires robust indexes to process high-volume queries without locking the server's PHP process pools.

See also

Infrastructure MySQL handshake desynchronization in multi-tenant environments: cPanel, CloudLinux, and CageFS

Infrastructure Complete Guide to Exim Spool: Frozen Queues, Kernel Locks, and CloudLinux Troubleshooting

Databases Running legacy MySQL versions on cPanel: from package errors to MySQL governor and Docker

This post is licensed under CC BY-NC.