<p>MySQL flags CREATE ... IGNORE SELECT as unsafe for statement-based replication, raising error 1717 when binlog_format is not ROW.</p>
<p>MySQL Error 1717 ER_BINLOG_UNSAFE_CREATE_IGNORE_SELECT occurs because CREATE ... IGNORE SELECT is non-deterministic for statement-based replication. Switch the server to ROW binlog_format or rewrite the statement without IGNORE to resolve the issue.</p>
CREATE... IGNORE SELECT is unsafe because the order in
Error 1717 tells MySQL administrators that the statement CREATE ... IGNORE SELECT cannot be safely recorded in the binary log when the server is using STATEMENT or MIXED binlog_format. Because the row order returned by the SELECT is unpredictable, two servers can ignore different rows and diverge.
The error appears at execution time when MySQL detects the unsafe pattern and aborts the statement to protect replication consistency. It does not appear when binlog_format is ROW, because row images eliminate ordering ambiguity.
The combination of the IGNORE keyword and a SELECT lacking deterministic ordering makes the resulting row set non-repeatable. When replication replays the statement on a slave, indexes, storage engines, or parallel execution may return rows in a different order, changing which duplicates are skipped.
The server marks such statements as unsafe for statement-based or mixed logging modes. If log_bin is enabled and binlog_format is not ROW, MySQL refuses to execute and surfaces error 1717 to the client.
The most reliable fix is to switch the global or session binlog_format to ROW before running the statement. Row events store each affected row directly and remove ordering problems.
Alternatively, rewrite the logic: use ON DUPLICATE KEY UPDATE instead of IGNORE, add a deterministic ORDER BY clause, or run the operation in a session with sql_log_bin = 0 if replication is not required.
Bulk-loading data from a staging table often triggers the error. Convert the pattern to INSERT ... ON DUPLICATE KEY UPDATE so each row’s primary key dictates behavior.
Schema migrations that copy rows into a new table can fail on production masters. Wrap the migration in SET SESSION binlog_format = ROW; to finish safely without modifying global settings.
Standardize on ROW binlog_format in modern MySQL deployments. It eliminates most unsafe-statement errors and simplifies operational playbooks.
When IGNORE is necessary, always include an ORDER BY primary key in the SELECT and test on a replica. Document coding guidelines so engineers know the replication implications of IGNORE.
Error 1592 ER_BINLOG_UNSAFE_STATEMENT signals other non-deterministic statements such as LOAD DATA. The corrective actions mirror those for error 1717: prefer ROW logging or rewrite the query.
Error 1660 ER_BINLOG_UNSAFE_AUTOINC_COLUMNS shows auto-increment columns used in multi-table updates. Switching to ROW or breaking the update into deterministic steps resolves it efficiently.
The server is running with binlog_format = STATEMENT or MIXED, making determinism mandatory.
The SELECT clause lacks ORDER BY and can return rows in varying sequences across servers.
IGNORE discards duplicate-key rows based on the retrieval order, introducing ambiguity.
High-volume data loads magnify ordering differences, increasing the chance of inconsistent duplicates.
Triggers for general non-deterministic statements such as UDFs or system-dependent functions.
Raised when auto-increment columns participate in multi-table updates.
Similar to 1717 but applies to INSERT ... IGNORE SELECT constructs rather than CREATE.
Yes. Disable binary logging or set sql_log_bin = 0 for the session, because replication safety is irrelevant on a standalone server.
ROW produces larger binary logs, but compression and faster apply times often offset the storage cost. Most modern MySQL setups run ROW by default.
If the ORDER BY column is not uniquely indexed, ties can appear in random order, keeping the statement unsafe. Order by a unique or primary key column.
Galaxy’s SQL editor highlights unsafe replication patterns and suggests switching to ROW or rewriting queries, reducing deployment-time surprises.
.avif)
.avif){kind=logo}
