PHP MySQLi预准备语句无法绑定变量，但PDO预语句可正常工作

Let’s dig into this puzzling issue where your MySQLi prepared statements are misbehaving—setting fields to NULL, throwing 503s on SELECTs—while regular MySQLi queries and PDO prepared statements work perfectly. Since your app was stable for months and UAT is error-free, this is almost certainly tied to a recent change in your shared hosting environment. Here’s how to diagnose and fix it:

1. Check for Silent PHP Extension/Config Updates #

Shared hosts often push backend changes without notifying users, especially for older PHP versions (yours is 7.0.30, which is end-of-life—more on that later). Verify these key points:

• MySQLi Driver Version: Run phpinfo() and compare the mysqli driver version between production and UAT. A buggy or mismatched driver update could easily break prepared statement handling.
• Mysqli INI Settings: Look for changes to directives like mysqli.allow_local_infile or mysqli.max_links. Even minor tweaks can cause unexpected silent failures.
• Opcode Cache Corruption: If your host uses OPcache or APC, a stale cache might be corrupting your prepared statement logic. Ask your host to clear the opcode cache, or add a temporary opcache_reset() call (only for testing—don’t leave this in production!).

2. Audit MariaDB Server-Side Prepared Statement Settings #

MariaDB 10.2.15 has specific configurations that could be misconfigured on your shared host:

• max_prepared_stmt_count: This setting limits the number of prepared statements the server can handle. If it’s set to a low value (like 0 or a small integer), the server might silently drop statements, leading to NULL inserts or 503s. Check it with:

  SHOW VARIABLES LIKE 'max_prepared_stmt_count';
  

• Binary Logging Conflicts: If your host enabled binary logging recently, the binlog_format setting (if set to STATEMENT) can clash with prepared statements. Run SHOW VARIABLES LIKE 'log_bin'; to confirm if logging is enabled, and check the format.
• Missing EXECUTE Privilege: Even if your user has read/write access, missing the EXECUTE privilege will break prepared statements. Verify this with:

  SHOW GRANTS FOR 'your_db_user'@'localhost';
  

3. Force Explicit Error Reporting for MySQLi #

Since no errors are being thrown, we need to force MySQLi to reveal what’s going wrong. Add these lines at the top of your prepared statement scripts:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
error_reporting(E_ALL);
ini_set('display_errors', 1);

This will turn silent failures into exceptions or visible errors, which might expose why fields are being set to NULL or why SELECT prepared statements are returning 503s. Also double-check your parameter binding—mismatched types (e.g., using i for a string) or missing bindings could cause these issues, even if they worked before.

4. Rule Out Shared Host Resource Throttling #

Shared hosts often impose hidden limits that can break prepared statements:

• Connection Limits: If your app is hitting the host’s connection cap, prepared statements might be terminated mid-execution. Ask your host for access to the MariaDB error log to look for connection-related errors.
• Packet Size Limits: A reduced max_allowed_packet setting can truncate data in prepared statements, leading to NULL values. Compare this value between production and UAT with:

  SHOW VARIABLES LIKE 'max_allowed_packet';
  

5. Address the End-of-Life PHP Version Risk #

PHP 7.0 reached end-of-life in December 2018, meaning it no longer gets security updates or bug fixes. Your shared host might be applying partial patches or deprecated features that break older code. While UAT runs the same PHP version, the host’s underlying environment (like system libraries or custom patches) could be drastically different. Upgrading to a supported PHP version (7.4+ as a minimum) is a critical long-term fix to avoid these kinds of unpredictable issues.

内容的提问来源于stack exchange，提问作者Mkov