📋

Description

This error indicates that a SQL statement attempts to write to a table containing an auto-increment column while simultaneously selecting data from another table. MariaDB deems such operations unsafe because the order of rows retrieved from the SELECT cannot be guaranteed, potentially leading to different outcomes on a master and its replication slave, thus breaking data consistency.

💬

Error Message

Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

🔍

Known Causes

4 known causes

⚠️

Combined DML and SELECT Operations

Executing DML statements (like INSERT or UPDATE) that modify an auto-increment column while simultaneously using a SELECT subquery from another table.

⚠️

Non-Deterministic Row Retrieval

The order in which rows are returned by the SELECT part of the statement cannot be guaranteed, leading to unpredictable outcomes for the auto-increment column value.

⚠️

Replication Safety Concerns

The statement is deemed unsafe for statement-based replication because its execution on a replica might differ from the master, causing data inconsistency.

⚠️

Missing or Insufficient ORDER BY

The absence of an ORDER BY clause, or an ORDER BY that does not guarantee a unique and consistent row order, contributes to the non-deterministic behavior.

🛠️

Solutions

3 solutions available

1. Explicitly Order Rows Before Inserting easy

Ensure a predictable order of rows by using an ORDER BY clause in the SELECT statement.

1

Modify your `INSERT ... SELECT` statement to include an `ORDER BY` clause on the selected rows. This guarantees a consistent retrieval order, which in turn ensures a predictable set of rows will be inserted.

INSERT INTO target_table (auto_increment_column, other_column)
SELECT NULL, source_column
FROM source_table
ORDER BY some_deterministic_column;

2

Choose a column or combination of columns in `source_table` that will always provide a unique and consistent ordering. This could be a primary key, a timestamp, or any other column that guarantees order.

ORDER BY source_table.id ASC;

2. Use a Temporary Table for Intermediate Storage medium

Insert into a temporary table first, then insert from the temporary table into the target table.

1

Create a temporary table that mirrors the structure of `target_table`, excluding the auto-increment column if you intend to let it populate automatically.

CREATE TEMPORARY TABLE temp_target_data (
    other_column VARCHAR(255)
);

2

Select data from `source_table` and insert it into the temporary table. This step can use an `ORDER BY` clause if still desired for clarity, though the issue is largely mitigated here.

INSERT INTO temp_target_data (other_column)
SELECT source_column
FROM source_table
ORDER BY some_deterministic_column;

3

Insert the data from the temporary table into your `target_table`. The `INSERT ... SELECT` here is safe because the temporary table has a defined order (or lack thereof, which is now predictable).

INSERT INTO target_table (auto_increment_column, other_column)
SELECT NULL, other_column
FROM temp_target_data;

4

The temporary table will be automatically dropped when the session ends.

3. Disable or Reconfigure Binlog Row Image advanced

Change the binlog_row_image setting to a safer value to avoid this replication-related issue.

1

Check the current `binlog_row_image` setting. If it is set to `ROW`, this error is more likely to occur in a replication setup.

SHOW VARIABLES LIKE 'binlog_row_image';

2

Consider changing `binlog_row_image` to `STATEMENT` or `MIXED`. `STATEMENT` logs the SQL statements, while `MIXED` logs statements for some operations and row changes for others. This can resolve the ordering ambiguity.

SET GLOBAL binlog_row_image = 'STATEMENT';

3

Alternatively, you can configure this in your MariaDB configuration file (`my.cnf` or `my.ini`) for a persistent change. Restart the MariaDB server after making this change.

[mysqld]
binlog_row_image = STATEMENT

4

**Caution:** Changing `binlog_row_image` can have other implications for replication and point-in-time recovery. Thoroughly test this change in a staging environment before applying it to production. `STATEMENT` mode can sometimes lead to other replication issues if statements are not inherently deterministic.

🔗

Related Errors

5 related errors

MariaDB Error 1722: Unsafe Auto-Increment Write Select