MySQLserverhasgoneaway solution _ MySQL

Source: Internet
Author: User
Tags drupal
MySQLserverhasgoneaway solution bitsCN.com

First, check whether there is a problem with your space provider. if you are a virtual space, perform the following operations first.

1. if you are a VM user, contact the space provider to check whether the MySQL server is normal or if your program consumes too much server resources during running. contact the space provider for confirmation;

2. for standalone host users, please optimize your MySQL configuration, check the running status of MySQL, and add the server configuration as appropriate.

3. the MySQL connection times out because of excessive execution actions. if it is an independent host, modify the value of wait_timeout in the MySQL configuration file to a larger value.

If you are using your own server, you can perform the following operations:


1. applications (such as PHP) execute MYSQL statements in batches for a long time. The most common is collection or conversion of new and old data.
Solution:
Add or modify the following two variables in the my. cnf file:

Wait_timeout = 2880000
Interactive_timeout = 2880000


For details about the two variables, refer to google or the official manual. If you cannot modify my. cnf, you can set it when connecting to the database.

CLIENT_INTERACTIVE, for example:
SQL = "set interactive_timeout = 24*3600 ";
Mysql_real_query (...)


2. execute an SQL statement, but the SQL statement is too large or the statement contains BLOB or longblob fields. For example, processing image data
Solution:
Add or modify the following variables in the my. cnf file:

Max_allowed_packet = 10 M (you can also set the size you need)
The max_allowed_packet parameter is used to control the maximum length of the communication buffer.


It may be due to the following reasons:
One possibility is that the SQL statement sent is too long and exceeds the size of max_allowed_packet. For this reason, you only need to modify my. cnf and increase the value of max_allowed_packet.
There is another possibility that timeout may be caused due to some reasons. for example, Singleton is used in the program to obtain the database connection. Although the program connects to the database multiple times, it actually uses the same connection, in addition, if the interval between two database operations in the program exceeds wait_timeout (this setting can be seen in show status), a problem may occur. The simplest way to deal with it is to increase wait_timeout. of course, you can also use mysql_ping () in the program from time to time, so that MySQL knows that it is not a battle.
Solve MySQL server has gone away

1. applications (such as PHP) execute MYSQL statements in batches for a long time. The most common is collection or conversion of new and old data.

Solution:

Add or modify the following two variables in the my. cnf file:

Wait_timeout = 2880000
Interactive_timeout = 2880000

  
For details about the two variables, refer to google or the official manual. If you cannot modify my. cnf, you can set CLIENT_INTERACTIVE when connecting to the database. for example:

The code is as follows:
SQL = "set interactive_timeout = 24*3600 ";
Mysql_real_query (...)

2. execute an SQL statement, but the SQL statement is too large or the statement contains BLOB or longblob fields. For example, processing image data

Solution:

Add or modify the following variables in the my. cnf file:

Max_allowed_packet = 10 M

(You can also set the desired size)

Max_allowed_packet
The parameter is used to control the maximum length of the communication buffer.

MySQL: the strange MySQL server has gone away and its solution


Introduction
Here is a step by step guide, equally valid for your Linux server as well as any local Windows MySQL installation you may be using as a trial installation along with your local Drupal installation.

MySQL comes with a default configuration of the resources it is going to use, specified in "my. ini "(Windows) or" my. cnf "(Linux) during the installation of MySQL. in Windows this file is located by default at C: Program FilesMySQLMySQL Server X. ymy. ini. in Linux this file is located at/etc/my. cnf to set global options, or the http://www.hzhuti.com/nokia/c7//usr/local/var/mysql-data-dir/my. cnf to set server-specific options.

Resources allowed by the default configuration are normally insufficient to run a resource-intensive application. you must modify the following resource specifications if they are available in your original configuration file, or add them to the configuration file if they are not already specified (because some are not present by default ):

Important: Remember to keep backup files before you do anything! You will also have to reload the MySQL service after making changes to these configuration files.

MyISAM specifications

[Mysqld]


Port = 3306
Socket =/tmp/mysql. sock
Skip-locking
Key_buffer = 384 M
Max_allowed_packet = 64 M
Table_cache = 4096
Sort_buffer_size = 2 M
Read_buffer_size = 2 M
Read_rnd_buffer_size = 64 M
Myisam_sort_buffer_size = 64 M
Thread_cache_size = 8
Query_cache_size = 32 M
InnoDB specifications

Innodb_buffer_pool_size = 384 M
Innodb_additional_mem_pool_size = 20 M
Innodb_log_file_size = 10 M
Innodb_log_buffer_size = 64 M
Innodb_flush_log_at_trx_commit = 1
Innodb_lock_wait_timeout = 180


Note: It is assumed here that you are using the InnoDB database tables, as Drupal is a resource intensive application. if you are not using the InnoDB database tables try to change this, in view of the fact that you are getting the Warning: mySQL server has gone away-apparently meaning that your setup is resource intensive. convert MyISAM Tables to InnoDB.

BitsCN.com

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.