MySQL server has gone away while restoring backup

-

One day I was restoring the mysqldump, shared by one of my team mate, into my local machine.

I was using HeidiSQL as the client, but believes that same has happened if I were using command line utility.

All was going well, the dump is being restored.

But after a while, as the database is being restored, I got the following error:

Error

SQL Error (2006): MySQL server has gone away


Explanation

Now, let us see why we got this error. Since it happens due to the MySQL configuration system variable max_allowed_packet setting, hence the timing of getting the error will depend upon when you hit that limit in your database restore.

max_allowed_packet is basically the maximum size (in bytes) of one packet or any generated/intermediate string exchanged between client and MySQL server.

Straight from the documentation

A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a source replication server to a replica.

The largest possible packet that can be transmitted to or from a MySQL 5.7/8.0 server or client is 1 GB.

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.

In other words, Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. To fix, you can increase the maximal packet size limit max_allowed_packet in your MySQL configuration file.

This is more common in case your database involved LOB columns.

I checked it in my system and found that it is set to 2 MB.

In order to check the same

  • Find the setting in MySQL configuration file (my.ini/my.cnf) under [mysqld] section OR
  • Execute SHOW VARIABLES LIKE 'max_allowed_packet'; on mysql prompt.

max_allowed_packet=2097152

For more information, Read: Packet Too Large

Solution

In order to resolve the issue, Please perform the following steps:

1. Edit your configuration file and set the value of max_allowed_packet to a higher value.

  • In my.ini/cnf file, if running MySQL on-premise/locally. Open my.ini/cnf, located in your MySQL installation folder or where you have mentioned it during installation, and under [mysqld] section change the max_allowed_packet to 32 MB.
  • AWS Parameter Group or similar if using another cloud DBaaS.

max_allowed_packet=33554432

2. Make sure that no command/query is running on the MySQL server as we need to restart it.

3. Restart the MySQL server.

4. Restore your database backup using your preferred method.

To find your my.ini file in Windows. Type services.msc in search box of windows. Find the MySQL service with the name that you have used while installing. Right Click and see Properties.

You may find similar like this:

Path to executable:

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57

Hopefully, the issue should be fixed and no more error triggered. If not, try to increase the max_allowed_packet to a higher value say 64 MB.

NOTE: This method only works if the error is coming due to max_allowed_packet value is less than communication packet exchanged between client and MySQL server.

If you feel that your max_allowed_packet setting is good enough, then please investigate further.

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cer...
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0 ...
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can...
Read more