What to do if the program cannot connect to mysql

Solutions for programs that cannot connect to mysql: 1. Determine whether the mysql service is normal, and contact the operation and maintenance to confirm the network problem; 2. Increase the value of “max_allowed_packet”; 3. Increase the value of “net_read_timeout” ; 4. Increase the value of “connect_timeout”, etc.

Recommendation: “Mysql Video Tutorial”

Summary of the problem that the program cannot connect to mysql

When you can’t connect to mysql, there are generally the following four types of errors:

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

One: Can’t connect to MySQL server

Possible reasons:

1, the mysql server is not started Get up

2, the network is not available (iptables, selinux, network instability)

The corresponding solution:

1, the dba first determines whether the mysql service is normal

2, when the mysql service is normal, find the operation and maintenance to confirm the network problem

2: Lost connection to MySQL server

There are four situations that may cause this Error:

1, network problem

2, the result set of the query is too large, for example, a query needs to return several million results

3, the client When initiating 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 this value has increased through SHOW GLOBAL STATUS LIKE ‘Aborted_connects’

4 , the query has a blob type, which exceeds the limit of the max_allowed_packet parameter

The corresponding solutions for these four situations:

1, Is there a problem with the operation and maintenance monitoring network

2, increase the value of net_read_timeout

3, increase the value of connect_timeout

4, increase the value of max_allowed_packet

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

This error is not a native error of mysql, and no similar information has been found on the Internet. At present, there are three situations where this error is reported:

p>

1, there is a problem with the network

2, there are too many database connections

3, the account password connected to the database is wrong

Four: MySQL server has gone away

The following 12 situations may cause such an error:

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

2, dba manually kill

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

4, the client TCP/IP connection timeout, such as using mysql_options( …, MYSQL_OPT_READ_TIMEOUT,…) or mysql_options(…, MYSQL_OPT_WRITE_TIMEOUT,…) function

5, when the client disables automatic reconnection (automatic reconnection)

6, the query statement is incorrect

7, the query statement is too long, exceeding the limit of max_allowed_packet

8, an insert or replace statement updates too many rows

9, domain name resolution failed

10, firewall blocked port 3306

11, one thread forked multiple sub-processes, when multiple sub-processes share a connection, it will cause Error

12, mysql server hangs

There are many reasons for the error reporting MySQL server has gone away, we can deal with this problem from three aspects:

p>1, operation and maintenance

a, confirm the firewall rules

b, the dns server is normal

d, reasonably configure the parameters of connecting to mysql in php.ini

connect_timeout, the default is 60s

mysqli.reconnect, the default is off

2, DBA

a, determine the mysql service and connection There are no exceptions

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 less long connections

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 |
 +-----------------------------+----------+

come Take a look at these timeout parameters related to not being able to connect to the database:

connect_timeout: When obtaining a link, wait for the timeout time for the handshake. Generally, the default value is 10s. In order to avoid problems caused by poor network Connection congestion can increase this value appropriately. The online configuration is 60s

wait_timeout/interactive_timeout: the timeout period for which the connection status continues to be sleep, the default is 28800 (8 hours), and it is now adjusted to 24 hours

p>

net_read_timeout: server side, etc.��The timeout time for the client to send data, the default is 60s, and the online value 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.

You can see from the above configuration The settings of these parameters are relatively large. Under normal circumstances, the program reports that the database cannot be connected to the database. It 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 program connection For more details about what to do without mysql, please pay attention to other related articles on 1024programmer.com!