PHP Warning: mysqli_query():Solution

With the continuous development and use of PHP technology, sometimes we may encounter some problems and errors. Among them, the MySQLi function library provided by PHP is a tool commonly used by developers to operate MySQL databases. When using the mysqli_query() function, a common error message may appear: "PHP Warning: mysqli_query(): (HY000/2006): MySQL server has gone away". This error message may hinder our development process. When you find an error, you should find a solution as soon as possible so that your PHP code can work properly.

Below, we will provide you with several possible solutions.

1. Check PHP code

When this error occurs, you first need to review your PHP code. Check whether there is too long a gap between MySQLi connection statements or whether the MySQLi connection is not maintained. When processing large amounts of data, the probability of this error is higher.

If the connection timeout is set too short, the MySQL database will close the connection during the connection process and display an error message in the browser. In most cases, setting the connection timeout longer will reduce the frequency of this error.

Solution:

We can use the ping() function in MySQLi to maintain the connection status and check whether the MySQLi connection is normal. This method can easily solve the problem of unexpected connection closing.

Make sure to use the following syntax in the code:

// Mysqli连接语句
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 判断数据库连接是否可用
if (mysqli_ping($conn)) {
    echo 'MySQL connection is still active!';
} else {
    echo 'MySQL connection is dead.';
}

2. Increase the timeout period

If the amount of data is large, you can increase the timeout period to improve connection stability . You can use the following code snippet:

// 设置超时时间 (单位为秒)
ini_set('mysqlI.connect_timeout', 3600);
ini_set('mysqlI.default_socket_timeout', 3600);

3. Check MySQL server configuration

The error message not only contains the actual error message, but also may include the database error message. We can get more detailed information about the error by accessing the database server logs.

MySQL database will load the configuration file when starting, such as /etc/my.cnf or /etc/mysql/my.cnf. The following parameters need to be added to this configuration file:

max_allowed_packet = 128M
wait_timeout = 28800
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 1G

Among them, max_allowed_packet can set the maximum transmission size (in bytes) allowed by a single SQL query statement. The default value of this parameter is 4MB, which can be adjusted according to actual needs. Make adjustments accordingly. The wait_timeout parameter can set the idle time of the MySQL connection in seconds. If this time is too long, it will reduce the performance of the server. If it is shortened, the connection may be lost due to timeout. The innodb_flush_log_at_trx_commit and innodb_buffer_pool_size parameters can improve the performance and stability of MySQL connections and can be adjusted accordingly according to needs.

4. Check MySQL database

If you have tried the above methods and still cannot solve the problem, you can finally check whether there is a problem with the MySQL database itself. For example, you can use MySQL's own tools for diagnosis and repair, such as:

mysqlcheck -o -A

This command will perform a consistency check and repair on the MySQL database. This command requires administrator privileges, and you need to be root or a user with equivalent privileges to execute this command.

Summary

The above are the solutions you can try when encountering the "PHP Warning: mysqli_query(): (HY000/2006): MySQL server has gone away" error. MySQLi is a very commonly used tool in PHP development. Although the occurrence of such errors brings a lot of trouble to our code development, using the above methods, you can still solve the problem through repair and improve the stability and reliability of the code.

The above is the detailed content of PHP Warning: mysqli_query():Solution. For more information, please follow other related articles on the PHP Chinese website!