如何解决：MySQL ODBC 5.3(w)驱动每小时连接数超限问题

Hey there, let's work through this MySQL connection limit error you're seeing with Excel's MS Query. The error message you're hitting is:

连接失败 [MySQL][ODBC 5.3(w) Driver]用户'ODBC'已超出每小时最大连接数限制。

This means the MySQL user ODBC has a per-hour connection quota that's been fully used up. Since this didn't happen before, let's break down the fixes and root cause checks to get you back on track:

First, let's get your Excel queries working again right away. You'll need access to a MySQL admin account (like root) to run this command:

ALTER USER 'ODBC'@'your_host' WITH MAX_CONNECTIONS_PER_HOUR 0;

• Replace your_host with the actual host linked to the ODBC user (it might be % for any host, or a specific IP you can look up in MySQL's user table).
• Setting 0 removes the hourly connection limit entirely, so you can resume work immediately.

Since this issue is new, something changed recently. Here are key things to check:

• Excel refresh settings: Did you set up automatic refresh for your queries? If multiple worksheets are refreshing every few minutes, each refresh might create a new connection that adds to the hourly count.
• ODBC Connection Pooling: Open your ODBC Data Source Administrator, find your MySQL DSN, and check the connection pool settings. If pooling is enabled but misconfigured, connections might not release properly, leading to a buildup.
• Excel macros/automation: Are there any macros running repeated queries without closing connections? Make sure your macros explicitly close connections after they finish using them.

If you want to keep a limit but raise it to fit your usage, run this as a MySQL admin:

ALTER USER 'ODBC'@'your_host' WITH MAX_CONNECTIONS_PER_HOUR 1000; -- Adjust the number to match your actual needs

To make this setting stick even after MySQL restarts, update the user's permanent privileges:

GRANT USAGE ON *.* TO 'ODBC'@'your_host' WITH MAX_CONNECTIONS_PER_HOUR 1000;

After making these changes, restart your MySQL service to ensure everything takes effect.

To prevent this from happening again, tweak how Excel uses connections:

• Reuse connections: Instead of creating a new connection for each worksheet, share the same connection across multiple queries.
• Close idle connections: In Excel's "Data" tab, go to "Connections" and shut down any idle connections you don't actively need.
• Batch queries: Combine small, repeated queries into a single query where possible—this cuts down on the number of new connections being created.

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