Error - Packets larger than {max_allowed_packet} are not allowed.

Packets larger than "max_allowed_packet" are not allowed

This exception error means the length of query is larger than the maximum length limit. The default maximum length in single SQL query is 1MB (1024 x 1024 bytes). You can change this in 3 ways:

1st way: Execute the Query to modify the value. Example:
Set the length limit to 32MB:

SET GLOBAL max_allowed_packet=32*1024*1024; 

Set the length limit to 1GB (The largest value allowed in MySQL Server):

SET GLOBAL max_allowed_packet=1024*1024*1024; 

or SET SESSION
SET SESSION max_allowed_packet=1024*1024*1024; 

Somethings you need to aware:
  1. The USER that you used to connect to MySQL Server need to have the privilege (Administration Rights) to modify any GLOBAL variables.
  2. SET GLOBAL will take effect on New connection, not on Current connection.
  3. SET SESSION will take effect on Current connection.
  4. The changes will reset once the MySQL Server is restarted.

Sample C# codes for modifying max_allowed_packet by using SET GLOBAL:

using (MySqlConnection conn = new MySqlConnection(connectionString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        cmd.CommandText = "SET GLOBAL max_allowed_packet=32*1024*1024;";
        cmd.ExecuteNonQuery();

        // Close and Reopen the Connection
        conn.Close();
        conn.Open();

        // Start to take effect here...
        // Do something....

        conn.Close();
    }
}

2nd way: Modify the option file my.ini

1. Stop MySQL Server.
2. Open MySQL option file on the server - my.ini , under the section of [mysqld], add this line:

max_allowed_packet=32M

3. Restart MySQL Server.
4. This will change the value of max_allowed_packet permanent for all connections.

Read more at MySQL Official Documentation:
http://dev.mysql.com/doc/refman/5.7/en/packet-too-large.html


3rd way, set "MaxSqlLength"

If you are not allowed or don't have the privilege to modify {max_allowed_packet} in MySQL:+*

Then, you have the limit the maximum length of single combined SQL dumped queries that MySqlBackup.NET will be generated:

Example: Limit the maximum length to 1MB:
using (MySqlConnection conn = new MySqlConnection(constring))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = conn;
            conn.Open();
            mb.ExportInfo.MaxSqlLength = 1024 * 1024; // 1MB
            mb.ExportToFile(file);
        }
    }
}

Note: If your table contains columns that have large data length datatype, for example: MEDIUMTEXT, LONGTEXT, MEDIUMBLOB, LONGBLOB.

You will fail to import the dump file, this is because the MySQL has blocked any SQL's length that is larger than it's default {max_allowed_packet}.


How to know what is the Default limit/maximum length that is allowed by specific MySQL server?

by executing this query:

show variables like 'max_allowed_packet';


Who defines the value of {max_allowed_packet}?

The owner or administrator of the MySQL server.

By default, for new/fresh installation of MySQL server, the limit is 1MB.