Shulou(Shulou.com)06/01 Report--

Editor to share with you the solution that the program is not connected to mysql. I hope you will gain a lot after reading this article. Let's discuss it together.

The solution that the program can not connect with 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, and so on.

Summary of the problem that the program can not connect to mysql

There are generally four kinds of errors when you can't connect to mysql:

1:Can't connect to MySQL server2:Lost connection to MySQL server3: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:

1the server of MySQL did not start up.

2. The network is not connected (iptables,selinux, the network is unstable)

Corresponding solution:

1DBA determines whether the mysql service is normal first.

2Perfect MySQL service is normal and ask the operation and maintenance to confirm the network problem.

Two: Lost connection to MySQL server

There are four situations that can lead to such errors:

1. Network problem

2. The result set of a query is too large. For example, a query returns millions of results.

3. When the client initiates a database connection, it reports an error 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 blob type, which exceeds the limit of max_allowed_packet parameter.

The corresponding solutions for these four situations:

1. Whether there is any problem with the operation and maintenance monitoring network

2, increase the net_read_timeout value

3, increase the connect_timeout value

4, increase the max_allowed_packet value

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

This error is not native to mysql, and similar information has not been found on the Internet. At present, it has been found that there are three situations in which this kind of error can be reported:

1. There is a problem with the network

2. Too many database connections

3. The account password of the database is incorrect.

Four: MySQL server has gone away

There are 12 situations that can lead to such errors:

1. The default wait_ timeout value is 8 hours. If the idle connection exceeds this value, it will be kill.

2dba manual kill

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

4. Client TCP/IP connection timed 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 (automatic reconnection)

6. Incorrect query statement

7. The query statement is too long and exceeds the limit of max_allowed_packet

8. Too many rows are updated by an insert or replace statement

9. Domain name resolution failed

10. The firewall blocks port 3306.

11. A thread fork multiple child processes, which will cause an error when multiple child processes share a connection.

12meme MySQL server is dead.

There are many reasons for errors in reporting to MySQL server has gone away, and we can deal with this problem from three aspects:

1, operation and maintenance

A, confirm the firewall rules

There is no exception on bminute DNS server.

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

Connect_timeout, default is 60s

Mysqli.reconnect, default is off

2,DBA

A, make sure that the number of mysql services and connections is normal.

B, set a reasonable wait_ timeout value

C, set a reasonable max_allowed_ packet value on the server side

D, confirm that the permissions of the account are correct

3, development

A, it is recommended to use mysqli instead of mysql

B, use as few long connections as possible

Mysql.ping () can reconnect automatically after the connection is disconnected, but it 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 | innodb_rollback_on_timeout | OFF | | interactive_timeout | 86400 | lock_wait_timeout | 31536000 | net_read_timeout | 31536000 | net_write_timeout | | rpl_stop_slave_timeout | 31536000 | slave_net_timeout | 3600 | thread_pool_idle_timeout | 60 | wait_timeout | 86400 | + | + |

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

Connect_timeout: when getting a link, the timeout for waiting for a handshake is generally available by default (10s). In order to avoid connection congestion due to poor network, you can appropriately increase this value. Online configuration is 60s

Wait_timeout/interactive_timeout: the connection status persists to the timeout of sleep. The default is 28800 (8 hours), which is now adjusted to 24 hours.

Net_read_timeout: the timeout for the server to wait for data to be sent by the client. Default is 60s, and online is 120s.

Net_write_timeout: timeout for writing data from server to client. Default is 60s, and online is 120s.

Take another look at the max_allowed_packet parameter: the default value of max_allowed_packet is 1m (1048576) and the maximum 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. Normally, the failure of the program to connect to the database should have nothing to do with the parameter configuration of the database itself. We need to solve this problem from other directions.

After reading this article, I believe you have a certain understanding of the solution that the program is not connected to mysql. If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.