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.x as of version 5.7.5 includes NO_ZERO_DATE and NO_ZERO_IN_DATE in its default sql_mode, but earlier 5.7 installations or servers with customized permissive settings allowed invalid date values (such as 0000-00-00 00:00:00) and silent string truncation. In MariaDB 10.11, strict mode (STRICT_TRANS_TABLES) is enforced by default. If the source server ran with customized permissive sql_mode rules, any non-compliant insertion triggers a fatal database engine error, breaking PHP transactions.

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, Error 503 (Service Unavailable) appeared. 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, the query execution plan must be audited 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 problematic query with correlated subquery causing the hang, execute EXPLAIN in the MySQL console:

-- Analyze the execution plan of the problematic query on orders table with correlated subquery
EXPLAIN SELECT * FROM orders
WHERE user_id = 123
  AND id IN (SELECT order_id FROM order_items WHERE product_id = 987);

Expected Inefficient Output (No Indexes): The absence of key and the ALL value in type confirm the Full Table Scan over orders, with rows in the hundreds of millions — confirming the section 3 diagnostic. The subquery appears as DEPENDENT SUBQUERY, showing that the optimizer re-evaluates the inner search for every scanned row:

+----+--------------------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------+
| id | select_type        | table       | partitions | type | possible_keys | key  | key_len | ref  | rows      | filtered | Extra                       |
+----+--------------------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------+
|  1 | PRIMARY            | orders      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 565000000 |    10.00 | Using where                 |
|  2 | DEPENDENT SUBQUERY | order_items | NULL       | ALL  | NULL          | NULL | NULL    | NULL |      1000 |    10.00 | Using where; Using temporary|
+----+--------------------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------+

Expected Optimized Output (With Indexes): The type field changing to ref on orders and rows reduced to a fraction of records indicates proper index usage. Since the query projects SELECT *, the Extra column reports Using where (not Using index, which is impossible when unindexed columns are selected):

+----+--------------------+-------------+------------+--------+-----------------------+-----------------------+---------+------------------+------+----------+-------------+
| id | select_type        | table       | partitions | type   | possible_keys         | key                   | key_len | ref              | rows | filtered | Extra       |
+----+--------------------+-------------+------------+--------+-----------------------+-----------------------+---------+------------------+------+----------+-------------+
|  1 | PRIMARY            | orders      | NULL       | ref    | idx_orders_user_status| idx_orders_user_status| 8       | const            |   50 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | order_items | NULL       | eq_ref | PRIMARY,idx_prod      | PRIMARY               | 8       | orders.id        |    1 |   100.00 | Using where |
+----+--------------------+-------------+------------+--------+-----------------------+-----------------------+---------+------------------+------+----------+-------------+

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 using a relative path
sudo tar czf /root/cakephp-backup-$(date +%Y%m%d).tar.gz -C /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

# Clean the directory completely (including dotfiles like .htaccess and .env)
sudo find /home/user/public_html -mindepth 1 -delete

# Restore original code files into the working directory matching the backup structure
sudo tar xzf /root/cakephp-backup-$(date +%Y%m%d).tar.gz -C /home/user

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.

# Configure log path, enable slow query logging, and set threshold to 2 seconds
mysql -e "SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log'; 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 /home/<user>/public_html/error_log.

4. Post-installation monitoring#

• [ ] Enable and monitor the slow query log at /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

Email 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.