Error
Error Code:
1693
MySQL Error 1693: Unsafe Mixed Table Writes
Description
MySQL Error 1693 occurs when a single SQL statement attempts to perform write operations (INSERT, UPDATE, DELETE) on both transactional (e.g., InnoDB) and non-transactional (e.g., MyISAM) tables, or temporary tables. This error is specific to environments using statement-based binary logging, where such mixed operations are deemed unsafe for replication consistency.
Error Message
Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them.
Known Causes
3 known causesMixed Transactional/Nontransactional Tables
Executing a single SQL statement that modifies data in both transactional storage engine tables (e.g., InnoDB) and nontransactional tables (e.g., MyISAM).
Temporary Table Interaction
A single statement attempts to write to a temporary table while also modifying persistent transactional or nontransactional tables.
Statement-Based Replication Context
This error arises when the MySQL server is configured for statement-based binary logging, which flags mixed-engine write operations as potentially unsafe for replication.
Solutions
3 solutions available1. Convert Nontransactional Tables to Transactional medium
Upgrade all tables involved in the statement to a transactional storage engine like InnoDB.
1
Identify the nontransactional tables. You can do this by querying the `information_schema.tables` table. Look for `ENGINE` values other than 'InnoDB' (e.g., 'MyISAM').
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.tables WHERE TABLE_SCHEMA = 'your_database_name' AND ENGINE != 'InnoDB';2
For each identified nontransactional table, alter its storage engine to InnoDB. **Caution:** This operation can take a significant amount of time and resources for large tables, and may lock the table during the conversion. It's highly recommended to perform this during a maintenance window.
ALTER TABLE your_database_name.your_table_name ENGINE=InnoDB;3
Once all tables involved in the problematic statement are using InnoDB, re-execute your original statement. It should now succeed without the 1693 error.
/* Your original statement that caused the error */2. Separate Writes into Transactional and Nontransactional Operations medium
Rewrite the statement to perform writes on transactional tables within a transaction, and writes on nontransactional tables separately.
1
Analyze your original statement to determine which tables are transactional (e.g., InnoDB) and which are nontransactional (e.g., MyISAM).
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.tables WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME IN ('table1', 'table2', 'table3');2
If your statement involves writing to both transactional and nontransactional tables, you need to split these operations. First, perform the writes to the transactional tables within an explicit transaction.
START TRANSACTION;
-- Your write operations on transactional tables (e.g., InnoDB)
UPDATE your_transactional_table SET column = 'value' WHERE id = 1;
INSERT INTO another_transactional_table (col1, col2) VALUES ('a', 'b');
COMMIT;
3
Next, perform the writes to the nontransactional tables as separate, non-transactional statements. These will execute independently of the previous transaction.
-- Your write operations on nontransactional tables (e.g., MyISAM)
UPDATE your_nontransactional_table SET column = 'new_value' WHERE id = 5;
INSERT INTO yet_another_nontransactional_table (colA, colB) VALUES ('x', 'y');
4
Ensure your application logic correctly sequences these separate write operations.
text3. Disable Mixed-Format Writes Enforcement (Use with Extreme Caution) easy
Temporarily disable the `sql_allow_nontransactional_updates` system variable to allow mixed writes. This is a quick fix but bypasses safety checks and should only be used for debugging or in specific, controlled environments.
1
Connect to your MySQL server as a user with sufficient privileges.
mysql -u your_user -p2
Set the `sql_allow_nontransactional_updates` system variable to `1` for the current session. This allows mixed writes.
SET SESSION sql_allow_nontransactional_updates = 1;3
Execute your original statement that was causing the error.
/* Your original statement that caused the error */4
Once your statement has executed, it's crucial to reset the variable to its default value (`0`) to re-enable safety checks. This variable is session-specific, so it will revert when the session ends, but explicit resetting is good practice.
SET SESSION sql_allow_nontransactional_updates = 0;