Asp.Net Core 3.1+Hangfire启动异常：MySQL连接数超限求助

Hey, let's break down your problem and figure out how to fix it. The core issue is crystal clear from your logs: your MySQL user nome_database has a maximum connection limit of 10, and when your ASP.NET Core app starts up, Hangfire's attempt to register recurring jobs is pushing this limit over the edge—resulting in the startup crash you're seeing.

Looking at the call stack, the error fires directly in HangfireJobScheduler.ScheduleRecurringJobs during app startup. This means the connection exhaustion is happening before your app even finishes spinning up, likely a combination of:

• The extremely low max_user_connections quota (only 10) for your MySQL user
• Unoptimized connection pool settings for Hangfire or your main app database
• Possibly other processes/services using the same MySQL user, eating into the limited connections

First, let's validate the current state to confirm our assumptions:

• Check MySQL user connection limits: Log into your MySQL server and run these queries:

  -- Check global default max user connections
  SHOW VARIABLES LIKE 'max_user_connections';
  -- Check the specific quota for your user
  SELECT User, max_user_connections FROM mysql.user WHERE User = 'nome_database';
  

• Audit active connections: Use SHOW PROCESSLIST; to see all active connections tied to nome_database—this will reveal if other services/apps are using up the limited quota.
• Review connection pool configurations: Check both your main app's database connection string and Hangfire's storage settings for unoptimized pool size values.

Let's prioritize solutions from quick wins to long-term optimizations:

1. Quick Fix: Increase MySQL User Connection Quota #

A 10-connection limit is far too low for any app using Hangfire (which requires persistent connections for job scheduling). Adjust the user's quota first to get your app back online:

-- Set a reasonable higher limit (adjust 50 to match your workload needs)
ALTER USER 'nome_database'@'%' WITH MAX_USER_CONNECTIONS = 50;

For a permanent change, add this to your my.cnf (Linux) or my.ini (Windows) file and restart MySQL:

[mysqld]
max_user_connections = 50

2. Optimize Hangfire's MySQL Connection Pool #

Your current Hangfire setup doesn't specify connection pool constraints, which means it might be using default values that are too high. Tweak the MySqlStorageOptions to limit Hangfire's connection usage:

services.AddHangfire(configuration => configuration
    .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
    .UseSimpleAssemblyNameTypeSerializer()
    .UseRecommendedSerializerSettings()
    .UseStorage(new MySqlStorage(
        Configuration.GetConnectionString("DefaultConnection"), 
        new MySqlStorageOptions() 
        { 
            TablesPrefix = "Hangfire",
            ConnectionPoolSize = 5, // Cap Hangfire's connection pool to avoid overusing the quota
            QueuePollInterval = TimeSpan.FromSeconds(15) // Reduce frequent polling to cut down connection churn
        })));

Also, update your connection string to include explicit pool limits (ensure the total of main app + Hangfire pool sizes stays under your user's quota):

"ConnectionStrings": {
    "DefaultConnection": "server=YOUR_SERVER;database=YOUR_DB;user=nome_database;password=YOUR_PWD;pooling=true;max pool size=8;"
}

3. Optimize App Startup Flow #

The crash happens during synchronous startup when registering recurring jobs. Move this logic to a background task to avoid blocking startup and reduce immediate connection pressure:

// In your Configure method, replace the direct call with a background execution
using var scope = app.ApplicationServices.CreateScope();
var recurringJobManager = scope.ServiceProvider.GetRequiredService<IRecurringJobManager>();
Task.Run(() => HangfireJobScheduler.ScheduleRecurringJobs(env, recurringJobManager));

(Note: Update your ScheduleRecurringJobs method to accept IRecurringJobManager instead of creating it internally.)

4. Check for Connection Leaks #

Even with proper pool settings, connection leaks can drain your quota. Audit your business code to ensure all MySqlConnection instances are wrapped in using statements (to guarantee connections are returned to the pool):

// Correct usage: connections are auto-disposed and returned to the pool
using var connection = new MySqlConnection(connectionString);
await connection.OpenAsync();
// Execute your queries here

Start with the quick connection quota increase to get your app back up, then work through the optimizations to prevent the issue from recurring. Always monitor connection usage after changes to ensure you're staying within your MySQL server's capacity.

内容的提问来源于stack exchange，提问作者Gargano