English
English
In web development, especially in projects using PHP + MySQL, the database behavior differences between the development environment and the online environment often cause a series of strange problems. For example, some queries are fast locally, but the online is as slow as a snail; or there is no error reported locally, but the online execution fails. In this case, if there are no clear clues to track it, a lot of time may be wasted.
Fortunately, PHP's mysqli extension provides a very practical debugging tool - the mysqli::debug() method. Although this approach is not well known, it can provide critical help in some scenarios, especially in positioning issues and optimizing performance .
mysqli::debug() is a static method of the mysqli class that enables debug output. It allows you to record the debugging information of libmysqlclient . The format is as follows:
mysqli::debug("d:t:o,/tmp/client.trace");
Parameter description:
d : Turn on debugging
t : Create a debug file for each thread
o,/tmp/client.trace : Specify the output file path
?? Note: This method is only valid when using libmysqlclient , and is usually only enabled during the debugging phase. The production environment should avoid being enabled by default.
The following is a typical use scenario:
<?php
mysqli::debug("d:t:o,/tmp/dev_trace.log");
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$result = $mysqli->query("SELECT * FROM users WHERE status = 'active'");
while ($row = $result->fetch_assoc()) {
echo $row['name'] . "<br>";
}
$mysqli->close();
?>
You can learn more about the SQL execution process, connection process and other details by viewing the /tmp/dev_trace.log file.
<?php
mysqli::debug("d:t:o,/tmp/prod_trace.log");
$mysqli = new mysqli("127.0.0.1", "user", "password", "database");
// Generally we recommend logging access interfaces，for example：
$url = "https://m66.net/api/user/status"; // Example URL
file_put_contents("/tmp/trace_log_access.log", "Visited: $url\n", FILE_APPEND);
$result = $mysqli->query("SELECT * FROM users WHERE status = 'active'");
while ($row = $result->fetch_assoc()) {
// Simplify processing
}
$mysqli->close();
?>
You can compare trace files in development and production environments, such as using the diff tool or the IDE comparison function to see which behaviors are inconsistent:
Query execution time difference
Is there an implicit conversion (such as string to number)
Is there a connection to try again
Does the SQL optimizer select a different index
By comparing the trace files of the two environments, you can:
Identify the reasons for slow query :
The query logic has not changed, but the online is slow? It may be that the index is not synchronized.
Confirm the connection configuration difference :
Different settings of max_allowed_packet and timeout may affect behavior.
Identify the impact of database version differences :
Some SQL writings may be very different in execution plans in 5.7 and 8.0.
The problem of auxiliary positioning "online cannot be reproduced" :
The trace log can clearly display the entire execution process, avoiding the blind spot of "the code is correct but the error occurs".
Although mysqli::debug() is not a must-use tool for developers in daily life, it can provide key clues when troubleshooting complex database problems. Using it to compare the behavior of the development environment with the online environment is a very low-cost and high-reward debugging strategy.
As long as you master the timing of use and control the permissions and file locations, mysqli::debug() can become your secret weapon to optimize performance and troubleshoot differences.
Related Tags:
mysqli
