What should I do if the program cannot connect to mysql?

Solutions to the problem that the program cannot connect to mysql: 1. Determine whether the mysql service is normal, and find the operation and maintenance to confirm the network problem; 2. Increase the "max_allowed_packet" value; 3. Increase the "net_read_timeout" value ;4. Increase the "connect_timeout" value, etc.

Recommended: "mysql video tutorial"

Summary of the problem that the program cannot connect to mysql

When unable to connect to MySQL, there are generally four types of error reports:

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 with.
4:MySQL server has gone away

1: Can't connect to MySQL server

Possible reasons:

1, the mysql server is not started

2, the network is blocked (iptables, selinux, network instability)

Corresponding solution:

1, DBA confirms first Is the mysql service normal?

2. If the mysql service is normal, contact operation and maintenance to confirm the network problem.

2: Lost connection to MySQL server

There are four situations that may cause this Such error reports:

1, network problem

2, the result set of the query is too large, for example, a query returns millions of results

3, initiated by the client When connecting to the database, an error is reported because the connect_timeout setting time is too short. If the error is caused by this reason, you can use SHOW GLOBAL STATUS LIKE 'Aborted_connects' to check whether the value has increased.

4, the query has blob type, exceeds The limit of the max_allowed_packet parameter has been exceeded

Solutions corresponding to these four situations:

1. Operation and maintenance monitor whether there are problems with the network

2. Increase the net_read_timeout value

3, Increase the connect_timeout value

4, Increase the max_allowed_packet value

3: Sorry, due to line fault, temporarily unable to browse, we are dealing with.

This error is not a native mysql error, and I have not found any similar information on the Internet. Currently, there are three situations where this error will occur:

1, there is a problem with the network

2. Too many database connections

3. The account and password for the database are incorrect

4: MySQL server has gone away

The following 12 situations may cause Such error report:

1, the default wait_timeout value is 8 hours, if the idle connection exceeds this value, it will be killed

2, dba manual kill

3, customer The client account does not have permission to connect to mysql server

4, the client TCP/IP connection times out, such as using 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, exceeding max_allowed_packet Limitations

8, One insert or replace statement updates too many rows

9, Domain name resolution fails

10, The firewall blocks port 3306

11. A thread forks multiple child processes. When multiple child processes share a connection, an error will be reported

12. The mysql server hangs

Comparison of the error reasons for reporting MySQL server has gone away Many, we can deal with this problem from three aspects:

1, operation and maintenance

a, confirm the firewall rules

b, there is no abnormality in the dns server

d, Properly configure the parameters for connecting to mysql in php.ini

connect_timeout, the default is 60s

mysqli.reconnect, the default is off

2,DBA

a, Make sure there are no abnormalities in the mysql service and the number of connections

b, Set a reasonable wait_timeout value

c, Set a reasonable max_allowed_packet value on the server side

d, Confirm that the account permissions are correct

3, development

a, it is recommended to use mysqli instead of mysql

b, try to use as few long connections as possible

c,mysqli.ping () can automatically reconnect after the connection is disconnected. mysql.ping() does not support automatic reconnection since mysql5.0.3

d. Do not share a database connection between child processes

Taking gamiss as an example, let’s take a look at the parameters related to max_allowed_packet and timeout:

>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: in When acquiring a link, the timeout period for waiting for the handshake is usually 10s by default. In order to avoid connection congestion due to poor network, this value can be increased appropriately. The online configuration is 60s

wait_timeout/interactive_timeout: connection The timeout for the state to remain sleep, the default is 28800 (8 hours), now adjusted to 24 hours

net_read_timeout: The timeout for the server to wait for the client to send data, the default is 60s, online is 120s

net_write_timeout: The timeout time for the server to write data to the client, the default is 60s, and the online value is 120s

Look at the max_allowed_packet parameter again: the default value of max_allowed_packet is 1M (1048576), and the maximum value is 1G ( 1073741824), our current online value is already the upper limit.

From the above configuration, we can see that the settings of these parameters are relatively large. Under normal circumstances, the database cannot be connected to the program. The database should have nothing to do with the parameter configuration of the database itself. We need to solve this problem from other directions.

The above is the detailed content of What should I do if the program cannot connect to mysql?. For more information, please follow other related articles on the PHP Chinese website!