Posted: 2016-02-06 03:17:25
A user trying to install a webapp reported this MySQL error –
Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
This error is a result of using a mode for MySQL’s Binary Log Format that is not compatible with TRANSACTION/COMMIT type statements when using InnoDB tables…
The above error is specifically mentioned here…
If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used.
MySQL has 3
STATEMENT, statement-based logging: every SQL statement is recorded.
ROW, row-based logging: every SQL result is recorded.
MIXED, mixed logging: if data modification is non-deterministic uses ROW, otherwise STATEMENT.
While the Binary Log is only used for Replication and Point-in-Time Recovery (i.e., nothing to do with installing single-DB webapps), when enabled + using the above transactional statements, SQL results (ROW) must be recorded instead of SQL statements (STATEMENT).
Edit my.ini (via notepad) –
Modify the Binary Log Format from –
# binary logging format binlog-format=STATEMENT
Save file. Restart MySQL.
If this does not fix the issue, then instead of “MIXED” use value “ROW”.