MetricSign
Start free
Critical severityconnection

MySQL Error:
1040

What does this error mean?

MySQL Error 1040 fires when every available connection slot is occupied and the server refuses new connection attempts. The default limit is 151 (150 usable + 1 reserved for SUPER). In data-pipeline contexts this typically hits when multiple ADF copy activities, Power BI refreshes, or dbt runs execute concurrently — each opening its own connection without sharing a pool. The engineer sees immediate connection refusals: queries hang or fail, pipeline activities error out with 'Too many connections', and downstream tables stop updating. Because MySQL does not queue connection requests, every new attempt during saturation fails instantly rather than waiting.

Common causes

  • 1The max_connections variable is set at the default of 151, which is too low for concurrent pipeline workloads — a single ADF pipeline with 10 parallel copy activities already consumes 10+ connections.
  • 2Connection pooling is not configured in the application layer or connector. Each thread, activity, or refresh opens a dedicated connection and never reuses it.
  • 3Long-running queries (full table scans, unindexed JOINs, large ELT transformations) hold connections open for minutes, leaving fewer slots for other processes.
  • 4ADF parallel copy activities combined with concurrent Power BI dataset refreshes and dbt runs all hit the same MySQL instance simultaneously during overlapping schedules.
  • 5Connections are opened but not properly closed by the application — leaked connections accumulate until the limit is reached. Common in custom Python scripts that lack try/finally blocks around cursor usage.
  • 6Azure Database for MySQL Flexible Server has a max_connections ceiling tied to the compute tier (e.g., Burstable B1ms caps at 341). Increasing the variable beyond the tier limit has no effect.

How to fix it

  1. 1Step 1: Check current usage vs. limit — run `SHOW STATUS LIKE 'Threads_connected';` and `SHOW VARIABLES LIKE 'max_connections';`. If Threads_connected is within 10 of max_connections, you are at risk.
  2. 2Step 2: Identify who is consuming connections — run `SELECT user, host, COUNT(*) AS conn_count FROM information_schema.processlist GROUP BY user, host ORDER BY conn_count DESC;` to see which application or host is holding the most slots.
  3. 3Step 3: Kill idle connections that are no longer needed — run `SHOW PROCESSLIST;` and terminate stale ones with `KILL <process_id>;`. For bulk cleanup: `SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE command = 'Sleep' AND time > 300;` generates kill statements for connections idle longer than 5 minutes.
  4. 4Step 4: Increase max_connections for immediate relief — `SET GLOBAL max_connections = 500;`. To persist across restarts, add `max_connections = 500` to your my.cnf under [mysqld]. On Azure Flexible Server, set it via Server parameters in the Azure portal.
  5. 5Step 5: Configure connection pooling. In ADF, reduce DIU and parallel copy degree per activity. In Python use SQLAlchemy with `pool_size=10, max_overflow=5`. In dbt, set `threads: 4` in profiles.yml to cap concurrent connections.
  6. 6Step 6: Stagger pipeline schedules to avoid concurrent peaks — shift Power BI refresh windows, ADF triggers, and dbt runs to non-overlapping time slots. A 15-minute offset between major jobs prevents simultaneous connection surges.
  7. 7Step 7: Set up a connection monitoring query as a scheduled check — `SELECT IF(threads_connected / max_conn > 0.8, 'WARNING', 'OK') AS status FROM (SELECT VARIABLE_VALUE AS threads_connected FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected') t1, (SELECT VARIABLE_VALUE AS max_conn FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'max_connections') t2;`

Example log output

ERROR 1040 (HY000): Too many connections
mysqlnd unable to connect: [1040] Too many connections (trying to connect via tcp://mysql-prod.example.com:3306)
com.microsoft.datafactory.v2.runtime: ErrorCode=UserErrorFailedToConnectToMySql, Message='Failed to connect to MySQL server. Error: Too many connections'

Frequently asked questions

What is the default MySQL max_connections limit?

MySQL defaults to 151 connections. The actual usable limit is 150 — one is reserved for the SUPER privilege user for emergency access. On Azure Database for MySQL, the effective maximum depends on your compute tier: a Burstable B1ms allows up to 341, while a General Purpose D2s_v3 allows up to 1365.

Can I set max_connections indefinitely high?

No — each connection consumes memory (roughly 1-10 MB per connection depending on buffers, temp tables, and sort operations). Setting it too high risks OOM kills. A practical formula: (available_RAM_MB - innodb_buffer_pool_size_MB - OS_overhead_MB) / per_connection_MB. Monitor with SHOW STATUS LIKE 'Max_used_connections' to find your actual peak.

Will retrying automatically fix Error 1040?

No. If the connection pool is saturated, retries add pressure — each retry attempt is another connection request that gets refused immediately. Configure retries with exponential backoff (e.g., 5s, 15s, 45s) and a maximum of 3 attempts. Fix the root cause (pooling, idle connections, or limit) before relying on retries.

How do I check who is using all the connections?

Run SELECT user, host, db, command, time, state FROM information_schema.processlist ORDER BY time DESC; — this shows every active connection, which user opened it, from which host, and how long it has been active. Connections in 'Sleep' state with high time values are idle and safe to kill.

Source · dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

Other connection errors