问题描述

MySQL 默认 Max_Allowed_Packet 约为 4MB, 当我导入一个 19MB 左右的 .sql 文件时报错，错误如下：

System.ArgumentException: Option not supported. (Parameter 'max_allowed_packet')
   at MySql.Data.MySqlClient.MySqlConnectionStringBuilder.GetOption(String key)
   at MySql.Data.MySqlClient.MySqlConnectionStringBuilder.set_Item(String keyword, Object value)
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)

通过执行 SET GLOBAL max_allowed_packet=20000000; 后依旧是初始值，但是下次启动程序却发现修改又成功了。

问题解答

经过不懈努力，寻求并整理得到以下 3 个结论：

MySQL 在同一个 mysql 实例（同一个 Connection）中使用SET GLOBAL max_allowed_packet=20000000; 修改了 paket size 在当前的 Connection 中无效，只有在下个 mysql 连接中生效
MySQLConnect 通过依次调用 Close 、 Open 实现的 Connect 重连无法实现修改生效
通过SET GLOBAL max_allowed_packet=20000000; 修改了 paket size 在下次重启 MySql Service 后失效。（这是我的预期，我只希望临时生效）

解决方案：

问题都找到且失效原因都明确后，我找到了 2 个解决方案，但不管怎么说就是一个核心思路：保证在连接数据库前开一个新的进程来修改 max_allowed_packet ，随着进程的消亡，mysql 实例和连接释放，下个 Connection（也就是我们的 Connection）必然会生效。

方案一

在使用 MySQLConnection 连接前先调用 mysql.exe 设置一下 max_allowed_packet 的值，具体逻辑如下：

// 调用逻辑
public void Foo()
{
  var sqlFile = your .sql  file path here;
  ModifyMaxAllowedPacket(sqlFile);
// 这里开始你的连接
   var connStr = $"server={your IP};port={your Port};user={your User};password={your psw};charset=utf8";
   using var conn = new MySqlConnection(connStr);
    conn.Open();
// 这里开始你的 .sql  文件的导入
    var sql = File.ReadAllText(sqlFile);
    using (var importCmd = new MySqlCommand(sql, conn))
    {
        importCmd.CommandTimeout = 0;
        var result = importCmd.ExecuteNonQuery();
        Console.WriteLine($"执行 {sqlFile} 完毕，数据库新增数据 {result} 笔！");
    }    
  }

// 使用 mysql.exe 修改 max allowed packet
    private void ModifyMaxAllowedPacket(string sqlFile,string password)
    {
        var fileinfo = new FileInfo(sqlFile);
        try
        {
            // 通过 mysql.ServiceName 服务属性获取 mysql 的安装路径
            // 默认服务名称是： MySQL56
            if (serviceHandler.IsExistsService("MySQL56")) 
            {
                var mysqlPath = GetServiceExecPath(mysql.ServiceName);
                var pattern = @"^""(.*\\bin)\\.*$";
                var match = Regex.Match(mysqlPath, pattern);
                if (match.Success)
                {
                    var mysql_file = Path.Combine(match.Groups[1].Value, "mysql.exe");
                    if (!File.Exists(mysql_file))
                    {
                        throw new FileNotFoundException("未找到 mysql.exe 文件！");
                    }
// 请根据实际情况编写，另密码与-p之间没有空格
                    var args = @$"-u root -p{password} -h 127.0.0.1 -P 3306";
                    var process = new Process
                    {
                        StartInfo = new ProcessStartInfo
                        {
                            FileName = mysql_file,
                            Arguments = args,
                            UseShellExecute = false,
                            RedirectStandardInput = true,
                            CreateNoWindow = true
                        }
                    };
                    process.Start();
// 设置 max_allowed_packet 
                    process.StandardInput.WriteLine($"SET GLOBAL max_allowed_packet={fileinfo.Length * 2};");
// 退出 mysql 
                    process.StandardInput.WriteLine("exit");
                    process.WaitForExit();
                }
                 Console.WriteLine($"执行 SizeModifier 完成！");
            }
            else
            {
                throw new Exception($"未发现 {mysql.ServiceName} 服务，无法修改 max_allowed_packet！");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
    public string GetServiceExecPath(string serviceName)
    {
        string key = $@"SYSTEM\CurrentControlSet\Services\{serviceName}";
        using (RegistryKey serviceKey = Registry.LocalMachine.OpenSubKey(key))
        {
            if (serviceKey == null)
            {
                throw new Exception($"Service {serviceName} does not exist.");
            }
            return serviceKey.GetValue("ImagePath").ToString();
        }
    }

方案二

写一个控制台程序 MySQLMaxPackageSizeModifier 实现对 max_allowed_packet 的修改，在使用 MySQLConnection 连接前先调用这个控制台程序设置一下 max_allowed_packet 的值，具体逻辑如下：

MySQLMaxPackageSizeModifier.exe 调用逻辑：

// Usage: MySQLMaxPackageSizeModifier <server> <port> <user> <password> <max_allowed_packet>
// 本程序会使用传入的 user + psw 连接并修改 max allowed packet 的值为传入值(如果大于当前值)
// 程序执行完毕后，会自动关闭。

 var maxPacket = 1024 * 1024 * 1024; // 1G
 var maxPacketModifier = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "MySQLMaxPackageSizeModifier.exe");
 var args = $"{mysql.IP} {mysql.Port} {mysql.User} {password} {maxPacket}";
 var process = new Process
 {
     StartInfo = new ProcessStartInfo
     {
         FileName = maxPacketModifier,
         Arguments = args,
         UseShellExecute = false,
         RedirectStandardOutput = true,
         CreateNoWindow = true
     }
 };
 process.Start();
 process.WaitForExit();
 var message = process.StandardOutput.ReadToEnd().Trim();
 Console.WriteLine($"执行 SizeModifier 完成！返回了：{message}");

// 接下来正式开始你的 MySQLConnection 的连接和数据的写入逻辑

MySQLMaxPackageSizeModifier 的实现：

using MySql.Data.MySqlClient;

class Program
{
    static void Main(string[] args)
    {
        if (args.Length < 5)
        {
            Console.WriteLine("Usage: MySQLMaxPackageSizeModifier <server> <port> <user> <password> <max_allowed_packet>");
            return;
        }

        string server = args[0];
        string port = args[1];
        string user = args[2];
        string password = args[3];
        int maxAllowedPacket = int.Parse(args[4]);

        string connStr = $"server={server};port={port};user={user};password={password};charset=utf8";

        try
        {
            using (var conn = new MySqlConnection(connStr))
            {
                conn.Open();
                // 先查询当前 max_allowed_packet 的值
                var currentMaxAllowedPacket = 0;
                var sql = "SHOW VARIABLES LIKE 'max_allowed_packet';";
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    using var reader = cmd.ExecuteReader();
                    if (reader.Read()) // 读取结果集中的第一行
                    {
                        // Value 列是第二列，索引为 1
                        var maxValue = reader.GetString(1);
                        currentMaxAllowedPacket = int.Parse(maxValue);
                    }
                }
                if (currentMaxAllowedPacket < maxAllowedPacket)
                {
                    string setMaxAllowedPacketQuery = $"SET GLOBAL max_allowed_packet={maxAllowedPacket};";
                    using (var cmd = new MySqlCommand(setMaxAllowedPacketQuery, conn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    Console.WriteLine($"Successfully set max_allowed_packet to {maxAllowedPacket}.");
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

写到最后

我最先实现的是本文第二种解决方案，但是由于需要自己再多维护一个程序，属实不优雅；因此使用了本文第一种方法，通过 MySQL 服务定位、运行并设置 Packet 后全身而退，Prefect！

扩展阅读

Tips:使用 SHOW VARIABLES LIKE 'max_allowed_packet'; 可以查询当前 packet size 的设置值。

笔者原创，版权所有，转载请注明出处！

[教程] C# 修改 max_allowed_packet 不生效,给你两个方案!