Error
Error Code:
1667
MariaDB Error 1667: Binary Log Mixed Engine Conflict
Description
This error indicates that MariaDB cannot write the current statement to its binary log. It occurs when a single statement or transaction involves tables managed by multiple storage engines, and at least one of these engines performs its own internal logging (self-logging), creating a conflict with MariaDB's global binary logging mechanism.
Error Message
Cannot execute statement: impossible to write to binary log since more than one engine is involved and at least one engine is self-logging.
Known Causes
3 known causesMixed Storage Engine Usage
A database operation attempts to modify data across tables that are configured to use different storage engines within the same transaction or statement.
Self-Logging Engine Involvement
A storage engine that handles its own transaction logging internally (e.g., NDBCLUSTER) is involved in an operation alongside other, non-self-logging engines.
Cross-Engine DDL Operations
Executing Data Definition Language (DDL) statements that affect tables across different storage engines, particularly when a self-logging engine is present.
Solutions
3 solutions available1. Disable Binary Logging for Non-Transactional Tables easy
Temporarily or permanently disable binary logging for tables using engines that don't support it.
1
Identify tables using non-transactional engines like MyISAM.
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE != 'InnoDB' AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');2
For each identified table, alter it to use a transactional engine like InnoDB.
ALTER TABLE `your_database_name`.`your_table_name` ENGINE=InnoDB;3
Alternatively, if modifying tables is not feasible, disable binary logging entirely for a specific session if the operation is temporary.
SET sql_log_bin = 0;4
Execute the statement that caused the error.
-- Your statement here --5
Re-enable binary logging for the session if it was disabled.
SET sql_log_bin = 1;2. Ensure All Tables in Replication/GTID are Transactional medium
Convert all tables involved in replication or GTID to use a transactional engine.
1
Verify your binary log format. GTID requires transactional engines.
SHOW VARIABLES LIKE 'binlog_format';2
Check if GTID is enabled. This is a strong indicator that all tables should be transactional.
SHOW VARIABLES LIKE 'gtid_mode';3
Identify all tables in your databases, especially those that might be part of replication. Focus on tables that are NOT InnoDB.
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE != 'InnoDB';4
For each non-InnoDB table, convert it to InnoDB. This is a critical step for consistency with binary logging, especially with GTID.
ALTER TABLE `your_database_name`.`your_table_name` ENGINE=InnoDB;5
Restart your MariaDB server after making significant engine changes to ensure configurations are reloaded correctly.
sudo systemctl restart mariadb3. Configure MariaDB to Ignore Non-Transactional Engines in Binary Log advanced
Adjust MariaDB configuration to skip logging for specific non-transactional engines.
1
Locate your MariaDB configuration file. This is typically `my.cnf` or `mariadb.conf.d/50-server.cnf`.
ls -l /etc/mysql/my.cnf /etc/mysql/mariadb.conf.d/*.cnf2
Edit the configuration file and add or modify the `binlog_ignore_db` or `binlog_ignore_table` directives. `binlog_ignore_db` is generally preferred for simplicity.
[mysqld]
binlog_ignore_db = mysql,information_schema,performance_schema,sys,your_database_to_ignore3
If you need to ignore specific tables, use `binlog_ignore_table` in the format `database_name.table_name`.
[mysqld]
binlog_ignore_table = database_name.non_transactional_table4
Save the configuration file.
-- No code, manual save --5
Restart the MariaDB server for the changes to take effect.
sudo systemctl restart mariadb