Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to copy a database from a server to my local machine, I've installed Wamp 2.2, mysql 5.5.16, but during copying db I m getting "mysql server has gone" 2006, I've tried to fix it by changing max_allowed_packet to 32M and 64M but still no luck, I've also changed the wait time.

wait_timeout=28800
interactive_timeout = 28800

my configuration in 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
key_buffer = 16M
max_allowed_packet = 64M
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.5.16
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.5.16/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 = 64M

[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]
wait_timeout=28800
interactive_timeout = 28800

[mysqld]
wait_timeout=28800
interactive_timeout = 28800
port=3306

Can someone tell me what I'm missing?

share|improve this question
    
Check your logs for the mysql server to make sure that it hasn't crashed. –  jishi Nov 3 '11 at 11:40
    
its not crashing, "mysql has gone away" appears when query is too big, I m copying a wp database to my local machine –  M Zubair Nov 3 '11 at 11:51
    
Well, "gone away" means that the connection was closed unexpectedly, which might come from a crash on that specific thread that handled your connection (not the whole server). It's a good start to rule out any bug or corruption in the filesystem. –  jishi Nov 3 '11 at 12:32
2  
Check this link: webyog.com/blog/2009/08/10/… It may be useful. Maybe you can increase the max_allowed_packet to 100M. If you happen to get the error again increase the value further, restart your server and try copying again. Check your database tables data length by executing: SHOW TABLE STATUS FROM db_name WHERE ENGINE IS NOT NULL; and check for data_length. –  Ashwin A Nov 3 '11 at 12:45
    
Thanks both of u, I've increased limit to 100M and have restarted server several times. I 'm try to copy the db using the option "Copy Database to Different Host", I automatically copy the schema –  M Zubair Nov 3 '11 at 13:15
add comment

migrated from stackoverflow.com Nov 3 '11 at 21:34

This question came from our site for professional and enthusiast programmers.

2 Answers

The following is a repost of an answer I posted with similar circumstances back on Sep 1, 2011

One of the silent killers of MySQL Connections is the MySQL Packet. In fact, even the I/O Thread of MySQL Replication can be victimized by this.

According to the MySQL Documentation

  • You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.

  • Any INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

At the very least, you must make sure the packet sizes for both the machine you mysqldump'd from and the machine you are loading are identical.

There may be two(2) approaches you can take:

APPROACH #1 : Perform the mysqldump using --skip-extended-insert

This will make sure the MySQL Packet is not inundated with multiple BLOBs, TEXT fields. That way SQL INSERTs are performed one at a time. The major drawbacks are

  1. the mysqldump is much larger
  2. reloading such a dump takes much longer.

APPROACH #2 : Increase max_allowed_packet

This may be the preferred approach because implementing this is just a mysql restart away. Understanding what the MySQL Packet is may clarify this.

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:

MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory---a cap on the size of the packet, which this option accomplishes.

The code of interest in relation to this option is found in sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay particular attention to net_realloc().

This variable also limits the length of a result of many string functons. See sql/field.cc and sql/intem_strfunc.cc for details.

Given this explanation, making bulk INSERTs will load/unload a MySQL Packet rather quickly. This is especially true when max_allowed_packet is too small for the given load of data coming at it.

CONCLUSION

In most installs of MySQL, I usually set this to 256M or 512M. You should experiement with larger values when data loads produces "MySQL has gone away" errors.

share|improve this answer
    
Thanks, I just tried with 512M, 1024M and 2048M, (my machine has 4GB only) but still wp queries r too big for it, [mysqld] port = 3306 socket = /tmp/mysql.sock key_buffer = 384M max_allowed_packet = 2048M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M –  M Zubair Nov 4 '11 at 21:10
    
max_allowed_packet only goes to 1G. Is all your data MyISAM, InnoDB or a mixture of both? Do you use a lot of large TEXT/BLOB fields in WordPress? –  RolandoMySQLDBA Nov 4 '11 at 21:13
    
insert into wp_options(option_id,blog_id,option_name,option_value,autoload) values ('1','0','siteurl','abc.net','yes'),('2','0','blogn…:\"MagpieRSS‌​\":19:{s:6:\"parser\";i:0;s:12:\"current_item\";a:0:{}s:5:\"items\";a:30:{i: ..... Tones of data, around 6803738 character when query just break. See how its merging all insert queries, wp puts everything inside its db –  M Zubair Nov 4 '11 at 21:17
    
Here is a suggestion: If you mysqldump a file that has loads of TEXT/BLOB data, please use --hex-blob as an additional option. That might change the dump file, but it should help. –  RolandoMySQLDBA Nov 4 '11 at 21:23
    
MYISAM is used as DBEngine for all tables. it burst b/c of field "option_value" and its type is longtext –  M Zubair Nov 4 '11 at 21:28
show 2 more comments

There are several possible reasons.

Two that I have experienced are when executing a script:

  • missing ; at the end of each statement
  • single quote occuring in the database
share|improve this answer
    
Hi Sahil and welcome to dba.se - thanks for your contribution and I hope you happy with my re-formatting! –  Jack Douglas Nov 4 '11 at 13:29
    
thanks, I'm copying it from another DB directing not dumping it through a dumpfile.sql –  M Zubair Nov 4 '11 at 21:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.