EC
1197
MariaDBERRORNotableReplication / LoggingHIGH confidence

Multi-statement transaction exceeded max_binlog_cache_size

Production Risk

Medium — transaction is rolled back; no data corruption, but operation must be retried.

What this means

ER_TRANS_CACHE_FULL (1197, SQLSTATE HY000) is raised when a multi-statement transaction requires more binary log cache space than max_binlog_cache_size allows. The transaction is rolled back.

Why it happens
  1. 1Very large transactions with many rows or large BLOB/TEXT values
  2. 2max_binlog_cache_size set too low for the workload
  3. 3Bulk inserts or updates inside a single transaction
How to reproduce
trigger — this will error
trigger — this will error
-- Large bulk insert in one transaction
START TRANSACTION;
INSERT INTO big_table SELECT * FROM source_table;  -- millions of rows
COMMIT;  -- may fail if binlog cache exceeded

expected output

ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

Fix 1

Increase max_binlog_cache_size

Increase max_binlog_cache_size
SET GLOBAL max_binlog_cache_size = 1073741824;  -- 1 GB

Why this works

Allows larger transactions to be cached in memory before writing to the binary log.

Fix 2

Split large transactions into smaller batches

Split large transactions into smaller batches
-- Insert in batches of 10,000 rows per transaction
START TRANSACTION;
INSERT INTO big_table SELECT * FROM source_table LIMIT 10000;
COMMIT;

Why this works

Smaller transactions reduce binlog cache pressure and are generally safer.

Sources
Official documentation ↗

MySQL 8.0 — 1197 ER_TRANS_CACHE_FULL

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MariaDB errors