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:
- The USER that you used to connect to MySQL Server need to have the privilege (Administration Rights) to modify any GLOBAL variables.
- SET GLOBAL will take effect on New connection, not on Current connection.
- SET SESSION will take effect on Current connection.
- 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.ini1. 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.