English
English
Developers often experience connection errors or performance issues when interacting with MySQL using PHP. Mysqli::$errno and mysqli_options can help us better troubleshoot and adjust MySQL connection properties, thereby solving common connection errors or instability problems. This article will explain in detail how to use these tools to debug a MySQL connection.
MySQL provides a wealth of connection options and error codes to help developers diagnose problems when interacting with MySQL. The mysqli extension in PHP provides tools such as mysqli::$errno and mysqli_options , allowing us to more granularly control database connections and catch and adjust errors.
mysqli::$errno is an integer representing the error code when the previous MySQL operation failed. This property can return the corresponding error code when a database connection fails or an error occurs while executing a SQL query. By analyzing this error code, we can help us understand the cause of the error.
Common MySQL error codes include:
2002 : Unable to connect to MySQL server
1045 : Access denied, username or password error
1049 : The specified database does not exist
The mysqli_options function allows us to set different connection options when creating a MySQL connection. Through these options, we can adjust the behavior of MySQL connections, such as setting connection timeouts, adjusting character sets, etc. This function can be applied through the mysqli::real_connect() method.
Common options include:
MYSQLI_OPT_CONNECT_TIMEOUT : Sets the connection timeout time.
MYSQLI_OPT_LOCAL_INFILE : Enable or disable the LOAD DATA LOCAL command.
MYSQLI_OPT_COMPRESS : Enable or disable data compression.
With mysqli::$errno , we can determine whether it is successful after establishing the connection. If the connection fails, you can get detailed error information through mysqli::$error .
<?php
$mysqli = new mysqli("m66.net", "username", "password", "database");
if ($mysqli->connect_errno) {
echo "Connection failed: " . $mysqli->connect_error;
exit();
}
echo "Connection successfully！";
?>
In this example, if $mysqli->connect_errno returns a non-zero value, it means that the connection has failed. We can further troubleshoot according to the error code.
Sometimes, a MySQL connection may fail due to a default timeout setting or a character set mismatch. These settings can be adjusted using mysqli_options .
For example, if we suspect that the connection timeout, we can adjust MYSQLI_OPT_CONNECT_TIMEOUT :
<?php
$mysqli = new mysqli();
$timeout = 10; // Set the connection timeout to10Second
// Set connection timeout
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, $timeout);
$mysqli->real_connect("m66.net", "username", "password", "database");
if ($mysqli->connect_errno) {
echo "Connection failed: " . $mysqli->connect_error;
exit();
}
echo "Connection successfully！";
?>
If a timeout error occurs while connecting, we can adjust MYSQLI_OPT_CONNECT_TIMEOUT to increase tolerance and avoid connection failure due to network latency or slow response of MySQL server.
After obtaining the specific error code through mysqli::$errno , we can take different measures according to the error type. For example:
<?php
$mysqli = new mysqli("m66.net", "username", "wrong_password", "database");
if ($mysqli->connect_errno) {
switch ($mysqli->connect_errno) {
case 1045:
echo "Incorrect username or password，Please check the entered voucher";
break;
case 2002:
echo "Unable to connect to MySQL server，请检查server地址和端口";
break;
default:
echo "Connection failed，Error code: " . $mysqli->connect_errno;
}
exit();
}
?>
This method can help us locate errors accurately and make corresponding adjustments.
This error is usually caused by unreachable database servers or port configuration errors. Solutions include:
Check that the MySQL server is running.
Make sure there is no problem with the network connection.
Make sure the m66.net domain name resolution is correct and there is no firewall to block access.
This error indicates that the database connection's credentials are incorrect. Solutions include:
Check that the username and password are correct.
Ensure that the MySQL user has sufficient permissions to connect to the specified database.
This error indicates that the requested database does not exist on the server. Solutions include:
Check that the database name is correct.
Make sure that the database is created on the MySQL server.
With mysqli::$errno and mysqli_options , we can more effectively diagnose and resolve MySQL connection problems. Using these tools correctly can help us identify problems in a timely manner and take appropriate measures to fix them, ensuring the stability and reliability of database operations.
