Summary of the problem that the program cannot connect to mysql

Summary of the problem that the program cannot connect to mysql

When mysql cannot be connected, the following four errors are reported:
1: Can't connect to MySQL server
2: Lost connection to MySQL server
3: Sorry, due to line fault, temporarily unable to browse, we are dealing.
4: MySQL server has gone away

I. Can't connect to MySQL server
Possible causes:
1. the mysql server is not started.
2. Network disconnection (iptables, selinux, network instability)

Corresponding solution:
1. the dba first determines whether the mysql service is normal.
2. Check the O & M personnel for network problems when the mysql service is normal.

Ii. Lost connection to MySQL server
In four cases, the following error may occur:
1. Network Problems
2. the query result set is too large. For example, a query must return millions of results.
3. When the client initiates a database connection, an error is reported because the connect_timeout setting time is too short, if the error is caused by this reason, you can check whether the value is increased by using show global status like 'aborted _ CTS '.
4. the query has the blob type, which exceeds the limit of the max_allowed_packet parameter.

Solutions for these four situations:
1. Is the O & M monitoring network faulty?
2. Increase the value of net_read_timeout.
3. Increase the value of connect_timeout.
4. Increase the value of max_allowed_packet.

Iii. Sorry, due to line fault, temporarily unable to browse, we are dealing.
This error is not a native mysql error. Similar information has not been found on the Internet. Currently, this error is reported in three cases:
1. Network Problems
2. Too many database connections
3. The account and password for database connection are incorrect.

Iv. MySQL server has gone away

In the following 12 cases, the following errors may occur:
1. The default value of wait_timeout is 8 hours. If the idle connection exceeds this value, it will be killed.
2. dba manual kill
3. The client account does not have the permission to connect to mysql server.
4. Client TCP/IP connection timeout. For example, use the mysql_options (..., MYSQL_OPT_READ_TIMEOUT,...) or mysql_options (..., MYSQL_OPT_WRITE_TIMEOUT,...) function.
5. When the client disables automatic reconnection)
6. the query statement is incorrect.
7. the query statement is too long and exceeds the limit of max_allowed_packet.
8. There are too many rows updated by an insert or replace statement.
9. domain name resolution failed
10. The firewall shields port 3306.
11. One thread fork has multiple sub-processes. When multiple sub-processes share one connection, an error is reported.
12. The mysql server is down.

MySQL server has gone away is reported for many reasons. We can solve this problem in three ways:
1. O & M
A. Confirm the firewall rules.
B. No exceptions occur on the dns server.
D. properly configure the mysql connection parameters in php. ini.
Connect_timeout, 60 s by default
Mysqli. reconnect. The default value is off.
2. DBA
A. Check whether the mysql service and connection count are abnormal.
B. Set a reasonable wait_timeout value.
C. Set a reasonable max_allowed_packet value on the server.
D. Check that the account has the correct permissions.
3. Development
A. We recommend that you use mysqli instead of mysql.
B. Try to use less persistent connections
C, mysqli. ping () can be automatically reconnected after the connection is disconnected. mysql. ping () cannot be automatically reconnected after mysql5.0.3.
D. Do not share one database connection between sub-Processes

Taking gamiss as an example, let's take a look at max_allowed_packet's timeout-related parameters:
> Show variables like 'max _ allowed_packet ';
+ -------------------- + ------------ +
| Variable_name | Value |
+ -------------------- + ------------ +
| Max_allowed_packet| 1073741824 |
+ -------------------- + ------------ +

> Show variables like '% timeout % ';
+ ----------------------------- + ---------- +
| Variable_name | Value |
+ ----------------------------- + ---------- +
| Connect_timeout | 60 |
| Delayed_insert_timeout | 300 |
| Have_statement_timeout | YES |
| Innodb_flush_log_at_timeout | 1 |
| Innodb_lock_wait_timeout | 120 |
| Innodb_rollback_on_timeout | OFF |
| Interactive_timeout | 86400 |
| Lock_wait_timeout | 31536000 |
| Net_read_timeout | 120 |
| Net_write_timeout | 120 |
| Rpl_stop_slave_timeout | 31536000 |
| Slave_net_timeout | 3600 |
| Thread_pool_idle_timeout | 60 |
| Wait_timeout | 86400 |
+ ----------------------------- + ---------- +

Let's take a look at these timeout parameters related to the inability to connect to the database:
Connect_timeout: Wait for the handshake timeout when obtaining the link. Generally, the timeout value is 10 s by default. To avoid connection congestion caused by poor network performance, you can increase the value properly. The online configuration is 60 s.
Wait_timeout/interactive_timeout: the timeout time when the connection status continues to sleep. The default value is 28800 (8 hours) and is now 24 hours.
Net_read_timeout: the timeout time for the server to wait for the client to send data. The default value is 60 s, and the online value is 120 s.
Net_write_timeout: the timeout time for writing data from the server to the client. The default value is 60 s, and the online value is 120 s.
Let's take a look at the max_allowed_packet parameter: the default value of max_allowed_packet is 1 M (1048576), and the maximum value is 1G (1073741824). Our current online value is already an upper limit.
From the above configuration, we can see that the settings of these parameters are relatively large. Under normal circumstances, the program reports that the database cannot connect to the database, which should be unrelated to the parameter configuration of the database. we need to solve this problem in other ways.