How to Resolve Data Retrieval Error in MySQL Due to "Too Many Connections"

Problem

In some cases, you may get the error “Too many Connections” while connecting MySQL data base in Bold BI^®

Reason for the error “Too Many Connections”

The error “Too many connections,” usually occurs when there are more simultaneous connections to the MySQL database than allowed by the configuration settings.
When using Bold BI^®, the number of dashboards and widgets being viewed can impact the number of connections opened by the application. For fetching data to the widget, Bold BI^® opens connections that are then automatically closed after the query has been executed.

Solution

You need to check and modify the maximum number of allowed connections in the MySQL configuration file.

Steps to Increase Maximum Connections in MySQL Database

1. Connect to your MySQL server using your preferred method (e.g. SSH, MySQL client).
2. Open the MySQL configuration file. The location of the file may vary depending on your system, but it is often called my.cnf or my.ini. On Linux systems, you can usually find it in the /etc/mysql/ directory.
3. Locate the max_connections setting in the file. It should be under the [mysqld] section. If the setting is not present, you can add it to the file under the [mysqld] section. Here’s an example: max_connections = 200.
4. Change the value of max_connections to the desired number of allowed connections. We recommend setting it to a value that is appropriate for your application’s usage patterns and server resources. Note that increasing the number of allowed connections may require additional server resources.
5. Save the changes to the file and exit.
6. Restart the MySQL server to apply the changes.

If you are unable to locate the specified file, you can also modify the maximum number of allowed connections in MySQL using MySQL Workbench:

1. Open MySQL Workbench and connect to your MySQL server.
2. From the main menu, click on the “Server” menu and select “Options File”. This will open the MySQL configuration file in a new tab within MySQL Workbench.
   
   
3. Locate the max_connections setting in the file. It should be under the [mysqld] section. If the setting is not present, you can add it to the file under the [mysqld] section. Here’s an example: max_connections = 200.
   
   
4. Change the value of max_connections to the desired number of allowed connections. We recommend setting it to a value that is appropriate for your applications usage patterns and server resources. Note that increasing the number of allowed connections may require additional server resources.
5. Save the changes to the file by clicking on the “Apply” button in the top right corner of the MySQL Workbench window.
6. Restart the MySQL server to apply the changes. You can do this by clicking on the “Server” menu and selecting “Stop Server” followed by “Start Server”.

You can check the current number of open connections to the MySQL server by running the below command in the MySQL client or MySQL Workbench,

show status where variable_name = 'Threads_connected'

Additional References

• MySQL Workbench
• MySQL Connector in Bold BI