3

I'm trying to import a database in preparation for an upgrade from 5.0 to 5.5. The database isn't that large, only 37GB but I can't manage to import it without ERROR 2006. My most recent attempt when like this:

db01 # mysqldump -u root -p$password --databases $db --max_allowed_packet=2G --skip-extended-insert --add-drop-table  --force > /mydatabase.dmp

db02 # mysql --max_allowed_packet=2G -u root -p$password -e "source /mydatabase.dmp"
ERROR 2006 (HY000) at line 7292 in file: '/mydatabase.dmp': MySQL server has gone away

Here's my my.cnf:

[mysqld]
innodb_buffer_pool_size = 3G
server-id = 4
log_bin = /var/lib/mysql/mysql-bin.log
binlog_do_db = my_database
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

There is memory available on the server at the time - nothing else is running there. There's nothing in mysqld.log regarding this error. How can I get past this error?

  • Remove --max_allowed_packet=2G it's not needed for mysqldump.. --max_allowed_packet=2G is for direct queries not a sqldump. – Raymond Nijland Feb 24 '19 at 20:04
1

There is a misunderstanding on how large max_allowed_packet may be.

According to the following URL, the MAX is 1G.

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

| improve this answer | |
0

ERROR 2006 (HY000) at line 7292 in file: '/mydatabase.dmp': MySQL server has gone away

Means that the connection got interrupted anormaly while the connection was on. There are many reasons why that can happen, one of the most common is that the server was killed during that.

There is not enough information on your post to say something in particular, what I can suggest is:

  • Please paste the error log around that time- you said no error was show, but please let us see that by ourselves. In particular, let us see your database server's uptime to confirm it.
  • Please paste the queries around 7292 line of that file, that may provide some extra information of which query fails exactly. You could skip lines until it works, and then analyze why those doesn't work
  • You could have file corruption, or some configuration that kills a specific query, or some imcompatibility between versions, or you forgot to run mysql_upgrade, ...
  • doing mysql -e "source" is a very convoluting way of doing an import- you could just:

    cat [or pv] /mydatabase.dmp | mysql ...
    
| improve this answer | |
  • I added max_allowed_packet = 50M to my my.cnf and ran the import again. It completed without errors but took 3.5 hours. – Ken J Aug 15 '17 at 0:10
  • @KenJ Glad you had a successful process. From MySQL command prompt, SELECT @@max_allowed_packet; will reveal your configured MAP and could be helpful in the future. You may have saved some wall clock time by using a larger value, up to the size of max_allowed_packet. – Wilson Hauck Feb 25 '19 at 13:41
0

For innodb_buffer_pool_size = 3G and max_allowed_packet=2G, I assume you have at least 8GB of RAM? If only 4GB, then cut those values in half. Otherwise swapping will slow things to a crawl.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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