Comparison of differences between mysqli::debug and MySQL local debug logs

Debugging is an indispensable link in the process of developing PHP applications and interacting with MySQL databases. Debugging tools and logs are particularly important especially when locating connection problems, querying performance problems, or unexpected errors. Two commonly mentioned debugging methods are using the mysqli::debug() function and local debug logs that enable MySQL (such as general_log and slow_query_log ). Although they can help developers understand the operation of the database, they vary in their purpose, mechanism and degree of detail.

1. Introduction to mysqli::debug

mysqli::debug() is a static method provided by the PHP mysqli extension. It allows you to enable client-level debugging functions to track the detailed process of communication between PHP scripts and MySQL databases. This debugging information is generated by the client (i.e. the PHP engine), not the database server.

Example of usage:

 <?php
// Turn on debug logging to temporary files
mysqli::debug("d:t:O,/tmp/client_trace.log");

$mysqli = new mysqli("localhost", "user", "password", "database");

if ($mysqli->connect_errno) {
    echo "Connection failed: " . $mysqli->connect_error;
    exit();
}

$result = $mysqli->query("SELECT * FROM users WHERE email LIKE '%@m66.net%'");

while ($row = $result->fetch_assoc()) {
    print_r($row);
}

$mysqli->close();
?>

Main functions and advantages:

• Record client call stack : You can view the behavior of PHP clients before and after each call to the database.

• No server configuration required : The server permission requirements are low, and are suitable for restricted environments such as shared hosting.

• Suitable for debugging connection problems : such as unsuccessful connection, authentication failure, etc.

limitation:

• Only the behavior of the PHP client is recorded, excluding execution details inside MySQL.

• Only operations performed through mysqli can be tracked, others such as PDO or mysqlnd are invalid.

2. MySQL local debug logs (such as general_log and slow_query_log)

The MySQL server itself also provides a logging mechanism to record the operation of the database. Among them, general_log and slow_query_log are the two most commonly used:

• general_log : records all executed SQL statements.

• slow_query_log : Records slow queries whose execution time exceeds the specified threshold.

Open log example:

 -- Open general_log
SET global general_log = 1;
SET global general_log_file = '/var/log/mysql/general.log';

-- Open慢查询日志
SET global slow_query_log = 1;
SET global slow_query_log_file = '/var/log/mysql/slow.log';
SET global long_query_time = 2; -- Record execution exceeds 2 Second statement

Main functions and advantages:

• Record detailed SQL execution history : suitable for analyzing query behavior and identifying invalid queries.

• Helps optimize database performance : bottlenecks can be identified by slow query logs.

• Can be integrated with performance analysis tools : such as pt-query-digest, MySQL Workbench.

limitation:

• Database administrator permissions are required.

• May affect performance, especially when general_log is turned on.

3. Their comparison and recommended usage scenarios

Summarize

mysqli::debug() is more suitable for the debugging phase of PHP application development and is especially helpful when troubleshooting database connection issues or understanding client behavior. MySQL's local debug logs pay more attention to the server-side SQL execution details, which are particularly important for optimizing database performance and finding the root cause of problems.

The ideal approach is to use the two in combination: use mysqli::debug() to capture the call process during local development, and conduct in-depth analysis on the log system that enables MySQL in a test or production environment. This can form a complete debugging chain from the client to the server, greatly improving the efficiency of problem positioning.