Bulk INSERT in één transactie: een ADF Copy Activity of custom ETL-script laadt tienduizenden tot miljoenen rijen in één enkele INSERT-batch zonder tussendoor COMMIT te sturen, waardoor de binlog-cache overloopt.. dbt full-refresh materialization: dbt vervangt een tabel via DROP + CREATE + INSERT in één DDL/DML-sequentie. Op grote tabellen (>10M rijen) genereert dit snel meer dan de standaard max_binlog_cache_size van 32 MB.. UPDATE of DELETE over een brede tabel zonder WHERE-filter: een transformatiestap die alle rijen van een staging-tabel bijwerkt, produceert per rij een binlog-event — bij brede tabellen met veel kolommen loopt dit snel in de gigabytes.. Samengestelde transacties in Stored Procedures: een stored procedure roept meerdere INSERT/UPDATE-statements achter elkaar aan binnen een impliciete transactie, zonder COMMIT-punten tussenin, zodat de volledige binlog-output in één cache-slot past.. Laag ingesteld limiet op managed instanties: Azure Database for MySQL Flexible Server en andere managed services hebben soms een lager standaard max_binlog_cache_size dan on-premises MySQL, waardoor workloads die lokaal werken in de cloud mislukken.. Historische backfill-loads: eenmalige initiële loads die jaren aan data inlezen in één run genereren extreem veel binlog-data, zelfs als de batch-logica voor dagelijkse incrementele loads correct is.. LOAD DATA INFILE zonder expliciete batch-afbakening: de LOAD DATA INFILE-instructie laadt een heel bestand als één transactie tenzij de applicatie dat handmatig opsplitst.

Stap 1 — Controleer de huidige waarde en hoeveel cache er al gebruikt werd: `SHOW VARIABLES LIKE 'max_binlog_cache_size'; SHOW STATUS LIKE 'Binlog_cache_disk_use%;`. Stap 2 — Verhoog de limiet tijdelijk voor de sessie (test eerst zonder server-herstart): `SET GLOBAL max_binlog_cache_size = 2147483648;` (2 GB). Voeg daarna `max_binlog_cache_size=2147483648` toe aan de `[mysqld]`-sectie in my.cnf/my.ini om het persistent te maken.. Stap 3 — Splits grote transacties in batches van 10.000–100.000 rijen. Voorbeeld-loop in Python: `for chunk in pd.read_sql('SELECT id FROM big_table', conn, chunksize=50000): conn.execute('INSERT INTO dest SELECT * FROM src WHERE id IN (%s)' % ','.join(map(str, chunk['id'].tolist()))); conn.execute('COMMIT')`. Stap 4 — Converteer dbt full-refresh naar incremental: voeg aan het model `{{ config(materialized='incremental', unique_key='id') }}` toe en implementeer een `{% if is_incremental() %} WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) {% endif %}`-filter.. Stap 5 — In ADF: open de Copy Activity → Settings → enable 'Write batch size' en stel dit in op 50000. Schakel 'Pre-copy script' in om de doeltabel leeg te maken in aparte kleinere DELETE-batches in plaats van één TRUNCATE-equivalent in de transactie.. Stap 6 — Schakel binary logging uit voor de sessie als replicatie en PITR niet nodig zijn voor deze load: `SET SESSION SQL_LOG_BIN = 0;` — voer daarna de bulk-load uit, schakel daarna weer in met `SET SESSION SQL_LOG_BIN = 1;`. Let op: dit vereist SUPER of BINLOG ADMIN privilege.. Stap 7 — Verifieer na wijziging: voer de mislukte pipeline opnieuw uit en controleer `SHOW STATUS LIKE 'Binlog_cache_disk_use';` — een nul-waarde of lage waarde bevestigt dat de cache niet meer overloopt.

High severityresource

MySQL Error:
1197

What does this error mean?

MySQL schrijft elke transactie eerst naar een in-memory binlog-cache voordat deze naar schijf wordt geflusht. Wanneer een transactie meer bytes genereert dan de variabele max_binlog_cache_size toelaat, kan MySQL de transactie niet voltooien en gooit hij de volledige transactie terug. De engineer ziet dit als een abrupte fout in de ADF pipeline-run, dbt-job of ETL-script: geen data is gecommit, de tabel staat nog op de oude stand. De error treedt uitsluitend op bij servers met binary logging actief — typisch replica-setups en servers met point-in-time recovery.

Common causes

1Bulk INSERT in één transactie: een ADF Copy Activity of custom ETL-script laadt tienduizenden tot miljoenen rijen in één enkele INSERT-batch zonder tussendoor COMMIT te sturen, waardoor de binlog-cache overloopt.
2dbt full-refresh materialization: dbt vervangt een tabel via DROP + CREATE + INSERT in één DDL/DML-sequentie. Op grote tabellen (>10M rijen) genereert dit snel meer dan de standaard max_binlog_cache_size van 32 MB.
3UPDATE of DELETE over een brede tabel zonder WHERE-filter: een transformatiestap die alle rijen van een staging-tabel bijwerkt, produceert per rij een binlog-event — bij brede tabellen met veel kolommen loopt dit snel in de gigabytes.
4Samengestelde transacties in Stored Procedures: een stored procedure roept meerdere INSERT/UPDATE-statements achter elkaar aan binnen een impliciete transactie, zonder COMMIT-punten tussenin, zodat de volledige binlog-output in één cache-slot past.
5Laag ingesteld limiet op managed instanties: Azure Database for MySQL Flexible Server en andere managed services hebben soms een lager standaard max_binlog_cache_size dan on-premises MySQL, waardoor workloads die lokaal werken in de cloud mislukken.
6Historische backfill-loads: eenmalige initiële loads die jaren aan data inlezen in één run genereren extreem veel binlog-data, zelfs als de batch-logica voor dagelijkse incrementele loads correct is.
7LOAD DATA INFILE zonder expliciete batch-afbakening: de LOAD DATA INFILE-instructie laadt een heel bestand als één transactie tenzij de applicatie dat handmatig opsplitst.

How to fix it

1Stap 1 — Controleer de huidige waarde en hoeveel cache er al gebruikt werd: `SHOW VARIABLES LIKE 'max_binlog_cache_size'; SHOW STATUS LIKE 'Binlog_cache_disk_use%;`
2Stap 2 — Verhoog de limiet tijdelijk voor de sessie (test eerst zonder server-herstart): `SET GLOBAL max_binlog_cache_size = 2147483648;` (2 GB). Voeg daarna `max_binlog_cache_size=2147483648` toe aan de `[mysqld]`-sectie in my.cnf/my.ini om het persistent te maken.
3Stap 3 — Splits grote transacties in batches van 10.000–100.000 rijen. Voorbeeld-loop in Python: `for chunk in pd.read_sql('SELECT id FROM big_table', conn, chunksize=50000): conn.execute('INSERT INTO dest SELECT * FROM src WHERE id IN (%s)' % ','.join(map(str, chunk['id'].tolist()))); conn.execute('COMMIT')`
4Stap 4 — Converteer dbt full-refresh naar incremental: voeg aan het model `{{ config(materialized='incremental', unique_key='id') }}` toe en implementeer een `{% if is_incremental() %} WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) {% endif %}`-filter.
5Stap 5 — In ADF: open de Copy Activity → Settings → enable 'Write batch size' en stel dit in op 50000. Schakel 'Pre-copy script' in om de doeltabel leeg te maken in aparte kleinere DELETE-batches in plaats van één TRUNCATE-equivalent in de transactie.
6Stap 6 — Schakel binary logging uit voor de sessie als replicatie en PITR niet nodig zijn voor deze load: `SET SESSION SQL_LOG_BIN = 0;` — voer daarna de bulk-load uit, schakel daarna weer in met `SET SESSION SQL_LOG_BIN = 1;`. Let op: dit vereist SUPER of BINLOG ADMIN privilege.
7Stap 7 — Verifieer na wijziging: voer de mislukte pipeline opnieuw uit en controleer `SHOW STATUS LIKE 'Binlog_cache_disk_use';` — een nul-waarde of lage waarde bevestigt dat de cache niet meer overloopt.

Example log output

ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
[ADF] Activity 'CopyActivity_LoadFact' failed. Error: {'errorCode': 'BadGateway', 'message': '...MySQL Error 1197...', 'failureType': 'UserError'}
[dbt] Database Error in model stg_orders (models/staging/stg_orders.sql): (1197, "Multi-statement transaction required more than 'max_binlog_cache_size' bytes")

Frequently asked questions

MySQL error 1197 fix — moet ik de server herstarten na het verhogen van max_binlog_cache_size?

Nee. `SET GLOBAL max_binlog_cache_size=2147483648;` is onmiddellijk van kracht voor nieuwe sessies zonder herstart. Bestaande sessies gebruiken nog de oude waarde. Voeg de instelling ook toe aan my.cnf zodat de waarde na een herstart behouden blijft.

MySQL 1197 retry — herprobeert ADF automatisch na deze fout?

Standaard niet. ADF beschouwt error 1197 als een UserError en voert geen automatische retry uit. Je moet de batch-grootte of de binlog-limiet aanpassen voordat je de pipeline handmatig herstart, anders mislukt de retry opnieuw.

Wat is een veilige waarde voor max_binlog_cache_size op Azure Database for MySQL?

Voor zware ETL-workloads is 512 MB tot 2 GB gangbaar. De absolute bovengrens is 4 GB (4294967295 bytes). Op Azure Flexible Server pas je dit aan via Server Parameters in de portal: zoek op 'binlog_cache_size' en sla op — geen herstart vereist.

Kan ik mysql error 1197 vermijden zonder de server-configuratie aan te passen?

Ja, via twee routes: (1) splits de transactie in kleinere batches met tussentijdse COMMITs — dit werkt altijd ongeacht de server-instelling; (2) schakel binary logging uit voor de sessie met `SET SESSION SQL_LOG_BIN=0;` als replicatie en point-in-time recovery niet nodig zijn voor deze specifieke load.

Source · dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html