Error
Error Code:
1705
MariaDB Error 1705: Multi-row Statement Cache Exceeded
Description
This error indicates that MariaDB attempted to store a multi-row statement in its binary log statement cache but encountered insufficient space. It occurs when the cumulative size of statements within a transaction exceeds the configured 'max_binlog_stmt_cache_size' limit.
Error Message
Multi-row statements required more than 'max_binlog_stmt_cache_size' bytes of storage; increase this mysqld variable and try again
Known Causes
3 known causesLarge Multi-Row Statements
Executing SQL statements within a transaction that affect a very large number of rows, exceeding the cache's capacity.
Low max_binlog_stmt_cache_size
The configured 'max_binlog_stmt_cache_size' variable is set too low for the typical size or number of multi-row statements in your workload.
Unoptimized Application Logic
Application code that frequently generates very large INSERT or UPDATE statements within transactions without proper batching.
Solutions
3 solutions available1. Temporarily Increase `max_binlog_stmt_cache_size` easy
Quickly resolve the error by increasing the statement cache size for the current session.
1
Connect to your MariaDB server using a client like `mysql`.
mysql -u your_user -p2
Execute the following SQL command to set the `max_binlog_stmt_cache_size` variable for the current session. A value of `4294967295` (the maximum allowed) is a common choice for immediate resolution, but you can also try a significantly larger value than the default.
SET GLOBAL max_binlog_stmt_cache_size = 4294967295;3
Re-run the multi-row statement that caused the error. This change is temporary and will be lost when the server restarts.
2. Permanently Increase `max_binlog_stmt_cache_size` medium
Configure the statement cache size to persist across server restarts.
1
Locate your MariaDB configuration file. This is typically `my.cnf` or `mariadb.conf.d/50-server.cnf` on Linux systems.
2
Open the configuration file with a text editor.
3
Find the `[mysqld]` section. If it doesn't exist, create it.
4
Add or modify the `max_binlog_stmt_cache_size` directive within the `[mysqld]` section. A value of `4294967295` is the maximum and will likely resolve the issue. Alternatively, set a value that is significantly larger than the default, considering your system's memory.
[mysqld]
max_binlog_stmt_cache_size = 42949672955
Save the configuration file.
6
Restart the MariaDB server for the changes to take effect.
sudo systemctl restart mariadb7
Re-run the multi-row statement that caused the error.
3. Analyze and Optimize Multi-Row Statements advanced
Reduce the memory footprint of multi-row statements by breaking them down or optimizing their structure.
1
Identify the specific multi-row statements causing the error. This might involve reviewing your application logs or the MariaDB error log.
2
If the multi-row statement is an `INSERT` statement with many rows, consider breaking it down into smaller `INSERT` statements. For example, instead of one `INSERT` with 1000 rows, use 10 `INSERT` statements with 100 rows each.
Original:
INSERT INTO my_table (col1, col2) VALUES (val1a, val2a), (val1b, val2b), ..., (val1z, val2z);
Optimized:
INSERT INTO my_table (col1, col2) VALUES (val1a, val2a), ..., (val1j, val2j);
INSERT INTO my_table (col1, col2) VALUES (val1k, val2k), ..., (val1t, val2t);
-- and so on3
For `UPDATE` or `DELETE` statements affecting a large number of rows based on a condition, ensure the `WHERE` clause is efficient and utilizes appropriate indexes. If the statement is extremely complex, consider if it can be broken into multiple simpler operations.
Example of an inefficient WHERE clause:
SELECT * FROM my_table WHERE some_function(column_name) = 'some_value';
Consider if an index can be created or if the logic can be rewritten to use indexed columns.4
If possible, review your application code to see if it can generate smaller, more manageable statements instead of very large multi-row ones.