Packets larger than max_allowed_packet are not allowed

Packets larger than "max_allowed_packet" are not allowed

This is one of the Exception message (error) that is raised in the condition that the length of query is larger than the maximum length limit.

Take this SQL query for example:

SELECT * FROM member;

The length of above query is: 21 characters.

There are cases that the length of the query becomes very long. For example, inserting LONGBLOB or LONGTEXT, or joining several INSERTs. For example:

INSERT INTO member(code,name,age) values
('A1','John',30),
('B1','Smith',32),
('C1','Adam',31),
('D1','Cane',28),
('E1','Irene',25),
('F1','Boo',21);

Above query inserts 6 rows into the table, 144 characters, still considered short, but if you combine a large amount INSERTs into single query, you will be able to reach the length limit, which is called: max_allowed_packet

The default maximum length in single SQL query is 1MB (1024 x 1024).

You can solve this problem in 3 ways:

1st way: Execute A SQL Query/Statement to Modify The Limit

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 SESSION max_allowed_packet=32*1024*1024;";
        cmd.ExecuteNonQuery();

        // 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. In Windows, this file might located at:

%ProgramData%\MySQL\MySQL Server x.x\my.ini
C:\ProgramData\MySQL\MySQL Server x.x\my.ini

On UNIX-like system, the file might located at:

/etc/mysql/my.cnf

Read More: https://dev.mysql.com/doc/refman/8.0/en/option-files.html

under the section of [mysqld], edit or add this line:

max_allowed_packet=32M

3. Restart MySQL Server. (If you installed as service, restart the service)
4. This will change the value of max_allowed_packet permanently.

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

3rd way, set MaxSqlLength in MySqlBackup.ExportInfo

If you are not allowed or don't have the privilege to modify max_allowed_packet in MySQL, then, you have to set the limit of 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 might fail to import the dump file by using above codes. This is because the MySQL server 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.