#2006 Wamp 中的 MySQL 服务器已消失错误

#2006 MySQL Server has gone away error in Wamp

提问by john

Im using Wampserver version 2.0 . When i try to import a sql file, either through MySQL Query Browser or PHPMyAdmin,i get the following error.

我使用的是 Wampserver 2.0 版。当我尝试通过 MySQL 查询浏览器或 PHPMyAdmin 导入 sql 文件时，出现以下错误。

Error 2006: MySQL Server has gone away.

The size of the file is 54,528 KB.

该文件的大小为 54,528 KB。

In C:\wamp\bin\mysql\mysql5.1.30\my.ini , i set the max_allowed_packet to 100M in [mysqldump] and [wampmysqld]. I couldnt find wait_timeout variable in my.ini. Any way to fix this problem ?. For your reference, i have given below the contents of my.ini file.

在 C:\wamp\bin\mysql\mysql5.1.30\my.ini 中，我将 [mysqldump] 和 [wampmysqld] 中的 max_allowed_pa​​cket 设置为 100M。我在 my.ini 中找不到 wait_timeout 变量。有什么办法可以解决这个问题？为了您的参考，我在下面给出了 my.ini 文件的内容。

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is C:\mysql\data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port        = 3306
socket      = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[wampmysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 100M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir=c:/wamp/bin/mysql/mysql5.1.30
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.1.30/data

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Disable Federated by default
skip-federated

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir     = /tmp/     
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 100M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld]
port=3306

回答by Adi

I'm using XAMPP on Windows and had the same problem. I thought it was the timeout variable but it was max_allowed_packet.

我在 Windows 上使用 XAMPP 并且遇到了同样的问题。我认为这是超时变量，但它是max_allowed_packet.

This has fixed it:

这已修复它：

# note the change was made at this section
[mysqld] 
port= 3306
socket= "/xampp/mysql/mysql.sock"
basedir="/xampp/mysql" 
tmpdir="/xampp/tmp" 
datadir="/xampp/mysql/data"
skip-locking
key_buffer = 16M
# it was 1M by default
max_allowed_packet = 2M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

This configuration file is located at ?XAMPP install directory, this isC:\XAMPPby default?\mysql\bin\my.ini.

此配置文件位于?XAMPP 安装目录，C:\XAMPP默认情况下是？\mysql\bin\my.ini.

回答by Sathish D

This may be because of max_allowed_packet

这可能是因为 max_allowed_packet

Change in the my.ini/my.cnffile. Include the single line under [mysqld]in your file

在my.ini/my.cnf文件中更改。[mysqld]在文件中包含单行

max_allowed_packet=500M

now restart the MySQL serviceonce you are done. You can see it's curent value in mysql like this:

完成后立即重新启动MySQL service。您可以在 mysql 中看到它的当前值，如下所示：

SHOW VARIABLES LIKE 'max_allowed_packet'

You can read about it here http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

你可以在这里阅读它http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

回答by ducnguyenvn

I'm using Xampp 1.7.7 on Windows 7 and had the same problem too. The below is what fixed it:

我在 Windows 7 上使用 Xampp 1.7.7 并且也遇到了同样的问题。以下是修复它的内容：

• Increase mysql.connect_timeout value:
  ; Maximum time (in seconds) for connect timeout. -1 means no limit
  ; http://php.net/mysql.connect-timeout
  mysql.connect_timeout = 10 ; it was 3 by default

• Tried what I did earlier and got an other problem about 'max_allowed_packet'.

• Go to mysql\bin\my.ini and find setting max_allowed_packet = 1M, then change it to 10M: max_allowed_packet = 10M ; It was 1M by default
  

• 增加 mysql.connect_timeout 值：
  ; 连接超时的最长时间（以秒为单位）。-1 表示没有限制
  ；http://php.net/mysql.connect-timeout
  mysql.connect_timeout = 10 ; 默认为 3

• 尝试了我之前所做的并遇到了关于“max_allowed_pa​​cket”的另一个问题。

• 进入mysql\bin\my.ini，找到设置max_allowed_pa​​cket = 1M，然后改成10M： max_allowed_pa​​cket = 10M ; 默认是1M
  

Hope this can help.

希望这能有所帮助。

Duc

德

回答by Menacho

PhpMyAdmin Documentation:

PhpMyAdmin 文档：

1.16 I cannot upload big dump files (memory, HTTP or timeout problems).

1.16 我无法上传大转储文件（内存、HTTP 或超时问题）。

Starting with version 2.7.0, the import engine has been re–written and these problems should not occur. If possible, upgrade your phpMyAdmin to the latest version to take advantage of the new import features.

从2.7.0版本开始，重新编写了导入引擎，应该不会出现这些问题。如果可能，请将您的 phpMyAdmin 升级到最新版本以利用新的导入功能。

The first things to check (or ask your host provider to check) are the values of upload_max_filesize, memory_limit and post_max_size in the php.iniconfiguration file. All of these three settings limit the maximum size of data that can be submitted and handled by PHP. One user also said that post_max_size and memory_limit need to be larger than upload_max_filesize.

首先要检查（或要求您的主机提供商检查）是php.ini配置文件中的upload_max_filesize、memory_limit 和 post_max_size的值。所有这三个设置都限制了 PHP 可以提交和处理的数据的最大大小。一位用户还表示 post_max_size 和 memory_limit 需要大于 upload_max_filesize。

There exist several workarounds if your upload is too big or your hosting provider is unwilling to change the settings:

如果您的上传太大或您的托管服务提供商不愿意更改设置，则有几种解决方法：

Look at the $cfg['UploadDir'] feature. This allows one to upload a file to the server via scp, ftp, or your favorite file transfer method. PhpMyAdmin is then able to import the files from the temporary directory. More information is available in the Configuration section of this document.

查看 $cfg['UploadDir'] 功能。这允许您通过 scp、ftp 或您喜欢的文件传输方法将文件上传到服务器。PhpMyAdmin 然后能够从临时目录导入文件。本文档的配置部分提供了更多信息。

Using a utility (such as BigDump) to split the files before uploading. We cannot support this or any third party applications, but are aware of users having success with it.

在上传之前使用实用程序（例如 BigDump）拆分文件。我们无法支持此应用程序或任何第三方应用程序，但知道用户已成功使用它。

If you have shell (command line) access, use MySQL to import the files directly. You can do this by issuing the "source" command from within MySQL: source filename.sql.

如果您有 shell（命令行）访问权限，请使用 MySQL 直接导入文件。您可以通过从 MySQL 中发出“source”命令来完成此操作：source filename.sql。

回答by Jeremy Moritz

FOR THOSE DEVELOPERS USING Mac OSX, you will need to change the max_allowed_packet from 1M to 10M within the "my.cnf" file.

对于使用 Mac OSX 的开发人员，您需要将“my.cnf”文件中的 max_allowed_pa​​cket 从 1M 更改为 10M。

max_allowed_packet = 10M

You will probably find the file "my.cnf" in one of these locations:

您可能会在以下位置之一找到文件“my.cnf”：

1. /etc/my.cnf (MOST LIKELY LOCATION FOR MAVERICKS USERS)
2. /etc/mysql/my.cnf
3. SYSCONFDIR/my.cnf
4. $MYSQL_HOME/my.cnf
5. defaults-extra-file (the file specified with --defaults-extra-file=path, if any)
6. ~/.my.cnf

1. /etc/my.cnf（最适合小牛用户的位置）
2. /etc/mysql/my.cnf
3. SYSCONFDIR/my.cnf
4. $MYSQL_HOME/my.cnf
5. defaults-extra-file（用 --defaults-extra-file=path 指定的文件，如果有的话）
6. ~/.my.cnf

回答by Sandeep Sharma

Steps for MySQL error 2006 solution:

MySQL错误2006解决步骤：

1. Edit the “my.ini” file found at “:\xampp\mysql\bin\”.
2. In the my.ini file, edit the “max_allowed_packet” by increasing the value.
   • XAMPP default value is 1M. I updated it to 10M.
3. Save the my.ini file.
4. Restart your MySQL and Apache server in XAMPP.

1. 编辑位于“:\xampp\mysql\bin\”的“my.ini”文件。
2. 在 my.ini 文件中，通过增加值来编辑“max_allowed_pa​​cket”。
   • XAMPP 默认值为 1M。我将其更新为10M。
3. 保存 my.ini 文件。
4. 在 XAMPP 中重新启动 MySQL 和 Apache 服务器。

回答by user1895954

I noticed this error while updating Magento from version x to y. Looking at the logs I saw there was an issue in a table.

我在将 Magento 从版本 x 更新到 y 时注意到了这个错误。查看日志，我发现表中存在问题。

[ERROR] Index UNQ_DH_CORE_URL_REWRITE_REQUEST_PATH_STORE_ID of databaseabc /core_url_rewrite has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

[ERROR] databaseabc /core_url_rewrite 的索引 UNQ_DH_CORE_URL_REWRITE_REQUEST_PATH_STORE_ID 在 InnoDB 中有 2 列唯一，但 MySQL 正在询问 3 列的统计信息。您是否混淆了来自不同安装的 .frm 文件？见http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

Fixing this table fixed the issue. In my case I could truncate the table as it was an index that could be generated again.

修复这个表解决了这个问题。在我的情况下，我可以截断表，因为它是一个可以再次生成的索引。

回答by Bing

If the increase memory sizeanswers don't work for you, check your code for an infinite loop.

如果增加内存大小的答案对您不起作用，请检查您的代码是否存在无限循环。

That is what I had in my code which exhausted my memory no matter what I increased it to. The stack trace gives good clues, ensure debugging is turned on, at least locally!

这就是我的代码中的内容，无论我将其增加到什么程度，它都会耗尽我的记忆。堆栈跟踪提供了很好的线索，确保调试已打开，至少在本地！