MySQL error 1559 occurs when you try to change the binary log format while temporary tables are open. The fix is to close all temporary tables before attempting to switch the replication format.
MySQL error 1559 (ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR) happens because of fundamental incompatibilities between how different binary logging formats handle temporary tables. When using ROW-based binary logging format, temporary tables are not logged to the binary log. However, when using STATEMENT-based logging, temporary tables are logged. This creates an impossible situation: if you have temporary tables open in ROW format and try to switch to STATEMENT format, the system wouldn't know how to handle the already-open temporary tables retroactively. MySQL prevents this ambiguity by blocking format changes whenever any temporary tables exist in the session, protecting both replication consistency and data integrity.
First, determine which temporary tables are open. In MySQL, temporary tables are session-specific and visible in the INFORMATION_SCHEMA.TABLES table.
-- List all temporary tables in the current session
SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_TYPE = 'TEMPORARY';Drop each temporary table before changing the binary log format:
DROP TEMPORARY TABLE IF EXISTS temp_table_name;If you have multiple temporary tables, drop them all:
DROP TEMPORARY TABLE IF EXISTS temp1, temp2, temp3;This is the most common and recommended solution. Once all temporary tables are closed, the format change will succeed.
Once all temporary tables have been dropped, you can safely change the binlog format:
-- For session-level change
SET @@SESSION.binlog_format = 'MIXED';
SET @@SESSION.binlog_format = 'STATEMENT';
SET @@SESSION.binlog_format = 'ROW';
-- For global change (requires SUPER privilege)
SET @@GLOBAL.binlog_format = 'MIXED';Verify the change took effect:
-- Check current binlog format
SELECT @@GLOBAL.binlog_format, @@SESSION.binlog_format;The session variable may differ from the global variable, and that's normal.
If you need to ensure the binary log format persists after a server restart, use PERSIST_ONLY instead of GLOBAL. This approach doesn't require closing temporary tables because it only updates the persisted configuration, not the runtime value:
-- Changes configuration without affecting current runtime (no restart needed)
SET @@PERSIST_ONLY.binlog_format = 'MIXED';PERSIST_ONLY writes the setting to mysqld-auto.cnf and takes effect after the next server restart. This allows you to change the persistent configuration even when temporary tables are open, though the current session's format won't change.
This is useful in setup scripts where you may not be able to guarantee all temporary tables are cleaned up.
If you're changing the binary log format on a replication master or slave with active replication channels that have open temporary tables, stop the channels first:
-- Stop the replication applier thread
STOP REPLICA;
-- Or for older MySQL versions (5.7 and earlier)
STOP SLAVE;
-- Wait for any temporary tables in the channel to be cleaned up
-- (Usually immediate if the channel was idle)
-- Now change the format
SET @@GLOBAL.binlog_format = 'ROW';
-- Restart replication
START REPLICA;
-- Or for older MySQL versions
START SLAVE;
-- Verify replication status
SHOW REPLICA STATUSGNote: This approach is only necessary if temporary tables exist in the replication channel itself, which is rare.
Binary Log Format Behavior Differences:
Different binary logging formats handle temporary tables differently:
- STATEMENT format: Logs the SQL statement that was executed. Temporary tables ARE logged, so they exist on replicas and can be referenced by subsequent statements.
- ROW format: Logs the actual row changes. Temporary tables are NOT logged at all, since they're session-specific and don't replicate.
- MIXED format: Automatically switches between statement and row logging depending on the statement. Temporary tables are treated like statement format (not logged, but tracked).
This is why you can switch FROM STATEMENT to ROW (adding a restriction), but not FROM ROW to STATEMENT (removing a restriction) when temporary tables exist.
Performance Considerations:
If you're changing binlog formats in production:
1. ROW format provides the best replication safety but uses more disk space
2. STATEMENT format is more compact but can cause issues with non-deterministic operations
3. MIXED format is the default in MySQL 5.7+ and balances safety with efficiency
Consider the impact on your replication lag and disk usage when choosing a format.
MySQL 8.0+ Changes:
In MySQL 8.4+, temporary table handling improved: operations on temporary tables are completely excluded from mixed logging format, and the presence of temporary tables has no impact on logging mode selection for other statements. However, the format switching restriction still applies for session safety.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions