Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
When you encounter the error message Column count of mysql.proc is wrong. Expected 20, found 16, your instinct might be to immediately suspect a database corruption or a deep-seated bug in the MySQL server itself. However, in the context of shared hosting environments like 000webhost.com and when using tools like phpMyAdmin, this error almost always points to a specific architectural mismatch rather than a catastrophic failure of the storage engine.
The root cause lies in how the deprecated mysql_* extension handles result sets compared to modern MySQL clients. The mysql_query() function returns a resource handle that represents a result set. When you iterate over this result using mysql_fetch_array(), the client driver expects the underlying server protocol to report a specific number of columns for that particular query execution.
The error message explicitly states a discrepancy: the driver expected 20 columns based on the schema definition in the metadata buffer (mysql.proc), but the actual packet received from the server contained only 16 fields. This mismatch triggers a fatal error because the PHP extension attempts to read beyond the bounds of the provided row data.
This specific scenario is highly indicative of a version incompatibility between the MySQL client library bundled with your PHP installation and the MySQL server running on the host. In shared hosting environments, developers often cannot upgrade their PHP versions or change the underlying MySQL server version. If you recently updated your local development stack to a newer PHP version (e.g., PHP 8.x) but are deploying code that relies on the legacy mysql_* functions, you might encounter this issue if the host's environment has not fully aligned its libraries.
However, the most common culprit in shared hosting scenarios is simply that the mysql_* extension is fundamentally deprecated and often removed or stripped of functionality to force migration to PDO or MySQLi. Even if the extension is present, the internal metadata structures might be misaligned due to version drift between the client library and the server.
Diagnosing the Environment
Before attempting complex workarounds, verify your PHP environment. Run phpinfo() and search for "mysql." If you see a warning about deprecated functions or if the extension is missing entirely, this explains why the column count validation fails. The legacy mysql_* extension was removed in PHP 7.0. If you are running PHP 7 or higher on a host that hasn't updated its internal libraries to match, or if the host uses a stripped-down PHP build, the metadata parsing logic for mysql.proc may not align with what the server sends.
The error message regarding mysql.proc is particularly revealing. This system table holds information about stored procedures and user-defined functions. When MySQL reports a mismatch in column counts here, it suggests that the client's understanding of the result set structure does not match the server's actual output.
The Solution: Migration to PDO
The only robust solution for this error is to stop using the mysql_* extension entirely and migrate to either MySQLi or, preferably, PDO. The legacy extension is unmaintained and prone to exactly these kinds of subtle metadata mismatches.
Here is how you can refactor your logic using PDO, which handles column binding more gracefully and offers consistent behavior across different PHP versions:
$pdo = new PDO("mysql:host=$host;dbname=$database;charset=utf8", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if (isset($_GET['userLatitude']) && isset($_GET['userLongitude'])) {
$latitude = $_GET['userLatitude'];
$longitude = $_GET['userLongitude'];
// Use prepared statements for safety and consistency
$stmt = $pdo->prepare("SELECT locationName, locationInfo, locationLatitude, locationLongitude, locationPic, city
FROM Location
HAVING distance < 2 ORDER BY distance LIMIT 0, 20");
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (!empty($results)) {
$response['Location'] = array();
foreach ($results as $row) {
// Map the row data explicitly to avoid column count assumptions
$product = [
'locationName' => $row['locationName'],
'locationInfo' => $row['locationInfo'],
'locationLatitude' => $row['locationLatitude'],
'locationLongitude' => $row['locationLongitude'],
'locationPic' => $row['locationPic'],
'city' => $row['city']
];
array_push($response['Location'], $product);
}
$response['success'] = 1;
} else {
$response['success'] = 0;
$response['message'] = "No products found";
}
} else {
$response['success'] = 0;
$response['message'] = "Required field(s) is missing";
}
echo json_encode($response);
Why This Works
By switching to PDO, you bypass the buggy mysql_proc column count validation entirely. PDO uses a different protocol layer that abstracts away the low-level packet parsing issues associated with the legacy extension. It also forces you to use prepared statements and explicit column mapping, which makes your code more resilient to schema changes or metadata inconsistencies.
If you are unable to change your PHP version immediately due to hosting restrictions, contact your provider (000webhost.com) to ensure their server-side PHP build includes the correct MySQL client libraries compatible with their server version. However, relying on deprecated functions is never a sustainable strategy for production applications. The transition to PDO or MySQLi is the standard path forward for maintaining stable, secure database interactions.
