English
English
When developing MySQL-based applications with PHP, some developers use mysql_info() or mysqli_info() to retrieve supplementary details after executing certain SQL operations, such as the number of affected rows in bulk inserts, updates, or deletes. However, you may occasionally find that the returned data is inaccurate—or even false or an empty string after some operations. What causes this unreliable behavior? This article explores the underlying mechanisms, analyzes the reasons, and offers practical solutions.
mysql_info() is primarily used to get a brief status message after executing non-SELECT statements, such as:
$link = mysqli_connect("localhost", "user", "pass", "testdb");
mysqli_query($link, "UPDATE users SET status = 1 WHERE id < 100");
echo mysqli_info($link);
The output might be:
Rows matched: 100 Changed: 100 Warnings: 0
This is especially useful during batch operations to determine how many rows were matched or changed. However, it depends on the status information returned by MySQL itself.
Not all non-SELECT SQL statements generate info messages. According to MySQL’s official documentation, only certain statement types may return this information:
INSERT INTO ... SELECT ...
UPDATE
DELETE
LOAD DATA
Statements like plain INSERT or REPLACE, especially those that don't use subqueries to insert data, often won’t populate the info buffer.
The MySQL client library used by PHP (libmysql or mysqlnd) also affects info retrieval. When using mysqlnd (MySQL Native Driver), mysqli_info() tends to return more accurate results. In contrast, libmysql may not log or may delay info details.
If multiple SQL statements are executed in a single connection (e.g., in batch mode), mysqli_info() only reflects the info of the last executed statement. For example:
mysqli_query($link, "UPDATE table1 SET name = 'A'; UPDATE table2 SET name = 'B';");
echo mysqli_info($link); // Returns info only for table2
This may mislead developers into thinking that earlier operations didn't execute or had no effect.
Different MySQL versions return different levels of info. For instance, older versions might omit Rows matched or Warnings, or contain bugs that report incorrect row counts. Additionally, certain performance-optimizing configurations might bypass some internal statistics gathering.
Ensure your PHP environment is running mysqlnd, which can be checked like this:
if (function_exists('mysqli_get_client_stats')) {
echo "mysqlnd enabled";
}
mysqlnd offers more standardized and comprehensive support for info reporting and is preferred over libmysql in production environments.
Don’t depend on info in multi-statement queries. Instead, execute each SQL statement individually and read its corresponding info result right after:
mysqli_query($link, "DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY");
$logInfo = mysqli_info($link);
echo "Log cleanup result: " . $logInfo;
For critical SQL operations, don’t fully rely on the textual output of mysql_info(). Validate results through additional queries or logging mechanisms. For instance, compare row counts before and after cleanup, or use transactions for precise control.
Note that Changed: 0 does not imply that the SQL statement failed—it may simply indicate no actual data change occurred (e.g., updating to the same value). This is common in UPDATE operations and explains why you might see Rows matched alongside Changed: 0.
To enhance debugging and monitoring, consider logging the info output like this:
$sql = "UPDATE users SET status = 0 WHERE active = 0";
mysqli_query($link, $sql);
file_put_contents("/var/log/mysql_ops.log", date('Y-m-d H:i:s') . " " . $sql . " => " . mysqli_info($link) . "\n", FILE_APPEND);
You can also send these logs to a remote monitoring system via API:
