1940
MySQLERRORCriticalReplicationHIGH confidence
Cannot mix transactional and non-transactional engines in the same transaction with this binlog format
Production Risk
High — mixed-engine transactions can cause replication divergence.
What this means
When binlog_format is ROW, MySQL disallows mixing writes to transactional (InnoDB) and non-transactional (MyISAM) tables in the same statement or transaction, because this creates unsafe replication scenarios.
Why it happens
- 1A single INSERT/UPDATE/DELETE touches both InnoDB and MyISAM tables.
- 2A stored procedure modifies both engine types within one transaction.
How to reproduce
trigger — this will error
trigger — this will error
-- binlog_format=ROW, modifying InnoDB and MyISAM in same transaction
expected output
ERROR 1940 (HY000): Cannot execute statement: binlog unsafe for storage engine because statement is in a transaction.
Fix 1
Migrate MyISAM tables to InnoDB
Migrate MyISAM tables to InnoDB
ALTER TABLE myisam_table ENGINE=InnoDB;
Why this works
Using the same storage engine for all tables eliminates mixed-engine transaction problems.
Fix 2
Separate operations into distinct transactions
Separate operations into distinct transactions
-- Commit the InnoDB transaction before touching MyISAM
Why this works
Keep transactional and non-transactional writes in separate transactions.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 1940 ER_BINLOG_ROW_ENGINE_AND_STMT_ENGINE2
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev