Resolve Database Restore Failure: ERROR 1105 (HY000) - Maximum Writeset Size Exceeded on Adobe Commerce Cloud

Last update: Mon Jan 27 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

When restoring a MariaDB backup on a Galera cluster, you may encounter the Maximum Writeset Size Exceeded error due to a 2GB transaction size limit. This article provides a step-by-step solution to adjust the backup and restore commands to handle larger transactions, ensuring a successful restore process.

Description description

Environment

Adobe Commerce Cloud

This issue occurs in a MariaDB Galera cluster environment.

Issue/Symptoms

When you attempt to restore a MariaDB database backup on a Galera cluster, the process may fail with the following error:

ERROR 1105 (HY000) at line [ specific line number] : Maximum writeset size exceeded

Cause

The Galera cluster’s wsrep_max_ws_size system variable sets a maximum writeset size of 2GB. If a backup includes large rows or binary data that exceed this size, the restore process fails.

Resolution resolution

To fix this issue, adjust the backup process to prevent large transactions by increasing the --max_allowed_packet size. This allows the MySQL server to handle bigger rows or binary data during both backup and restore operations. Remember to always test in a non-production environment.

Identify the exact cause of the error from the MySQL error logs. Look for messages similar to:

ERROR 1105 (HY000) at line [ specific line number] : Maximum writeset size exceeded

This confirms the error is due to the transaction size exceeding Galera’s limit.

Create a new backup specifying the --max_allowed_packet parameter to allow larger data sizes and prevent oversized transactions. The --max_allowed_packet parameter defines the maximum size of data packets the server can process during communication with a client. Increasing this size helps handle larger rows or objects, which prevents transaction splitting and oversized writesets. For most scenarios, --max_allowed_packet=64M is sufficient. Adjust this value to --max_allowed_packet=128M if your database contains very large rows or binary data. Also, use compression (e.g., gzip) to reduce file size.
Backup command example:

code language-none
`mysqldump -h <host> -u <username> -p <password> --single-transaction --max_allowed_packet=64M <db_name> \| gzip > /tmp/<dump_name>.sql.gz`

When restoring the backup, specify an increased --max_allowed_packet value to ensure compatibility with the modified backup.
Restore command example:

zcat /tmp/<dump_name>.sql.gz | sed -e 's/DEFINER[ ] *=[ ] *[ *] *\*/\*/' | mysql --host=127.0.0.1 -u <username> -p <password>
After restoring the backup, confirm the process completes without errors. Check logs to ensure no warnings or anomalies are present.

wsrep_max_ws_size in Galera Cluster System Variables in MariaDB documentation.

recommendation-more-help