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 by MySql.Data.DLL. This exception message tells 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 bytes.

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 in 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 bytes).

You can solve this problem in 3 ways:

1st way: Execute the Query to modify the value

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. 1SET SESSION1 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 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.

By default, for new/fresh installation of MySQL server, the limit is 1MB (1024 x 1024 bytes).