Overview

You may encounter a 'MySQL server has gone away' error while executing the ./dbmgr -D -purgedeleted command, intended to remove files that are not online and not archived. The error indicates a loss of connection to the MySQL server and prevents the successful completion of the command, leaving files that should have been removed still present in the system.

Solution

To resolve this timeout issue, there are two possible approaches:

1. Increasing the wait_timeout and max_allowed_packet MySQL configuration settings

The most common cause is the server timed out and closed the connection. In MySQL, the  wait_timeout and max_allowed_packet parameters are often the culprits. The wait_timeout parameter defines how long the server waits for activity on a non-interactive connection before closing it. The max_allowed_packet parameter sets the maximum packet size for network communication. If the data you're sending exceeds this limit, the server might close the connection.

Increasing the settings should help, unless there are too many files to be deleted.

2. Breaking down the job into smaller batches with -limited key

1. Use the ./dbmgr -D -purgedeleted -limited command instead. This command only runs 100K files at a time, reducing the load on the server and preventing the MySQL server from disconnecting during large operations.
2. Repeat this command multiple times until the number of files is reduced to a manageable size.
3. Once the number of files is reduced, run a full ./dbmgr -D -purgedeletedcommand without the -limited option.

Summary

The 'MySQL server has gone away' error during the execution of the ./dbmgr -D -purgedeleted command can be resolved by increasing the time-out values (it won't work if there are too many files to be deleted) and by breaking down the operation into smaller, more manageable batches using the -limited option. This method reduces the load on the server and prevents the MySQL server from disconnecting during large operations.

FAQ

1. What does the -limited option do in the ./dbmgr -D -purgedeleted command?
   The -limited option runs the command on only 100K files at a time, reducing the load on the server and preventing the MySQL server from disconnecting during large operations.
2. How can increasing wait_timeout and max_allowed_packet settings help?
   Increasing these parameters extends the time MySQL waits before closing an inactive connection and allows larger data packets, reducing the likelihood of the server disconnecting during the file deletion process.
3. Why does the 'MySQL server has gone away' error occur?
   This error occurs when there is a loss of connection to the MySQL server, often due to the server being overloaded with large operations.