MySQL Nontransactional Statements Explained: Effects on binlog Cache Parameters

What are nontransactional statements?

In MySQL documentation, a nontransactional statement is any SQL command that modifies a table that does not support transactions. When such statements are executed inside a transaction and require more memory than the limit set by max_binlog_stmt_cache_size, the server raises an error.

Nontransactional tables (MyISAM)

Tables that use the MyISAM storage engine are non‑transactional; they cannot roll back changes. The following session shows how a rollback on a MyISAM table produces a warning instead of undoing the change.

create table no_trans(id int) ENGINE=MyISAM;
start transaction;
insert into no_trans values(1);
select * from no_trans;
+------+
| id   |
+------+
| 1    |
+------+
rollback;
-- Query OK, 0 rows affected, 1 warning (0.00 sec)
show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+
select * from no_trans;
+------+
| id   |
+------+
| 1    |
+------+

The warning confirms that MyISAM tables do not support rollback.

Transactional tables (InnoDB)

In contrast, InnoDB tables are transactional and fully support rollbacks. The example below demonstrates a successful rollback that removes the inserted row.

create table trans(id int);
start transaction;
insert into trans values(1);
select * from trans;
+------+
| id   |
+------+
| 1    |
+------+
rollback;
-- Query OK, 0 rows affected (0.00 sec)
select * from trans;
Empty set (0.00 sec)

Impact on binlog cache parameters

Because nontransactional statements operate on non‑transactional tables, the parameter max_binlog_stmt_cache_size governs the memory allocated for caching these statements. If the cache is too small, MySQL reports that more space is needed.

Conversely, max_binlog_cache_size applies to transactional statements on tables such as InnoDB. Insufficient space for this cache also triggers a warning requesting a larger allocation.