Error
Error Code:
1675
MySQL Error 1675: Unsafe Mixed Table Transaction
Description
This error occurs when a transaction attempts to access a non-transactional table (e.g., MyISAM) after it has already accessed a transactional table (e.g., InnoDB). MySQL flags this as 'unsafe' for statement-based replication because changes to non-transactional tables cannot be rolled back, potentially leading to data inconsistency on replicas.
Error Message
Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction.
Known Causes
3 known causesMixing Table Engines
Performing DML operations on both transactional (e.g., InnoDB) and non-transactional (e.g., MyISAM) tables within a single `START TRANSACTION ... COMMIT/ROLLBACK` block.
Incorrect Operation Order
The transaction specifically accesses a non-transactional table *after* having already accessed a transactional table, which is deemed unsafe for replication integrity.
Statement-Based Replication (SBR)
This error is primarily raised when using statement-based binary logging (`binlog_format=STATEMENT`), as SBR cannot reliably replicate mixed transactions involving non-transactional tables.
Solutions
3 solutions available1. Separate Transactions easy
Execute statements involving transactional and non-transactional tables in separate transactions.
1
Identify which tables are transactional (e.g., InnoDB) and which are non-transactional (e.g., MyISAM).
SHOW TABLE STATUS WHERE Name = 'your_table_name';
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';2
If a transaction involves both types of tables, commit or rollback the transaction involving the transactional table *before* accessing the non-transactional table.
-- Example: Transactional table operation
START TRANSACTION;
-- Perform operations on transactional table
UPDATE transactional_table SET column = 'value' WHERE id = 1;
COMMIT; -- Or ROLLBACK;
-- Now, perform operations on non-transactional table
-- (This will not trigger the error if done separately)
INSERT INTO non_transactional_table (column) VALUES ('new_value');2. Convert Non-Transactional Tables to Transactional medium
Migrate non-transactional tables to a transactional storage engine like InnoDB.
1
Identify all non-transactional tables in your database. Common examples include MyISAM tables.
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name' AND ENGINE != 'InnoDB';2
For each non-transactional table, alter its engine to InnoDB. It's highly recommended to perform this during a maintenance window or with appropriate backups.
ALTER TABLE your_non_transactional_table ENGINE=InnoDB;3
After converting all relevant tables, your transactions will no longer mix transactional and non-transactional access, resolving the error.
3. Disable `FOREIGN_KEY_CHECKS` or `UNIQUE_CHECKS` (Temporary & Risky) easy
Temporarily disable checks to allow mixed operations within a single statement (use with extreme caution).
1
Before executing the statement that causes the error, disable foreign key checks.
SET FOREIGN_KEY_CHECKS = 0;2
Execute your original statement that mixes transactional and non-transactional table access.
-- Your original problematic statement here
-- e.g., START TRANSACTION; UPDATE transactional_table ...; INSERT INTO non_transactional_table ...; COMMIT;3
Crucially, re-enable foreign key checks immediately after the statement to maintain data integrity.
SET FOREIGN_KEY_CHECKS = 1;4
Note: This is a quick fix and bypasses important data integrity mechanisms. It should only be used as a temporary workaround or if you fully understand the implications and have other means of ensuring data consistency.