Configure MySQL Data Storage Settings for WebReports

Large WebReports reports, exports, and queued jobs may require MySQL to accept larger data packets than the default configuration allows.

Use this article to review and adjust the MySQL max_allowed_packet setting when large WebReports operations fail because of packet-size limitations.

Before You Begin

Before changing MySQL settings:

• Confirm that you have administrative access to the MySQL server.
• Record the current MySQL configuration before making changes.
• Review your organization's database-management and change-control requirements.
• If MySQL is shared with other applications, coordinate any changes with your database administrator.

Increase MySQL limits only when required by your WebReports workload. Larger values are not necessarily appropriate for every environment.

Log In to MySQL

Use the MySQL Command Line Client to review and change the MySQL settings in this article.

1. Open the Start menu.
2. Search for and open MySQL Command Line Client.
3. Enter the MySQL administrator password when prompted.
4. Press Enter.

After a successful login, the mysql> prompt appears.

Caption: MySQL Command Line Client after a successful login

Check the Current max_allowed_packet Value

At the mysql> prompt, run:

SHOW VARIABLES LIKE 'max_allowed_packet';

MySQL displays the current max_allowed_packet value in bytes.

For example:

67108864

equals approximately:

64 MB

Caption: Current max_allowed_packet value

Increase max_allowed_packet

If large WebReports operations fail because the current packet-size limit is too small, increase the value.

At the mysql> prompt, run:

SET GLOBAL max_allowed_packet = 1073741824;

This example sets max_allowed_packet to:

1 GB

Use a value appropriate for your environment. Do not increase the setting beyond what is required for your WebReports workload.

Verify the Updated Value

After changing the setting, run:

SHOW VARIABLES LIKE 'max_allowed_packet';

Verify that the expected value is displayed.

For the 1 GB example, MySQL should display:

1073741824

Caption: Verify the updated max_allowed_packet value

Existing database connections may continue using the value that was active when the connection was established. Reconnect WebReports to MySQL after changing this setting.

Make the Setting Persistent

A change made with SET GLOBAL affects the currently running MySQL instance, but the value does not persist after the MySQL service restarts.

Where supported, use SET PERSIST to apply the setting and save it for future MySQL restarts.

For example:

SET PERSIST max_allowed_packet = 1073741824;

MySQL stores persisted system-variable settings in the mysqld-auto.cnf file and automatically applies them during subsequent server startups.

For current MySQL documentation, see:

Persisted System Variables

Persisting system variables requires the appropriate MySQL administrative privileges.

About net_buffer_length

WebReports does not normally require the MySQL net_buffer_length value to be increased.

MySQL initializes each connection buffer using net_buffer_length and automatically increases the buffer up to max_allowed_packet when necessary.

MySQL recommends that net_buffer_length normally remain unchanged.

Only modify this setting if BridgeWorks Support or your database administrator determines that your environment specifically requires it.

Optional: Review MySQL Memory Configuration

If WebReports is experiencing general database-performance problems, your database administrator may also want to review MySQL memory configuration, including the InnoDB buffer pool.

The appropriate configuration depends on factors such as:

• Available system memory
• Database size
• Report workload
• Number of concurrent WebReports users
• Whether MySQL is running on a dedicated or shared server
• Other applications running on the same system

Avoid applying a fixed percentage of system memory without evaluating the overall workload of the server.

Verify WebReports

After updating the MySQL configuration:

1. Reconnect WebReports to the MySQL database if necessary.
2. Run or queue the report that previously encountered the issue.
3. Allow the report to complete.
4. Download or export the results, if applicable.
5. Verify that the operation completes successfully.

Troubleshooting

If large reports continue to fail after increasing max_allowed_packet:

• Confirm that the new value is active.
• Verify that WebReports established a new database connection after the change.
• Review the WebReports application logs.
• Review the MySQL error log.
• Confirm that the issue is related to packet size rather than memory, query execution, timeout, or another database condition.

Contact BridgeWorks Support if the issue continues.

Related Articles

• Install MySQL for WebReports
• Viewing MySQL Data Storage Specifications
• Installing WebReports on Windows