📋

Description

This error indicates that a `CREATE TABLE ... SELECT ...` statement involving a table with an auto-increment column is considered unsafe for statement-based replication. The non-deterministic order of rows returned by the `SELECT` query can result in different data being inserted on the master and slave servers, leading to data inconsistency.

💬

Error Message

CREATE TABLE... SELECT... on a table with an auto-increment column is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are inserted. This order cannot be predicted and may differ on master and the slave.

🔍

Known Causes

3 known causes

⚠️

Non-Deterministic Row Insertion

The `SELECT` part of the statement does not guarantee a specific order for rows, making the insertion order unpredictable, especially when auto-increment values are assigned.

⚠️

Statement-Based Replication (SBR)

This error commonly occurs in environments using Statement-Based Replication (SBR), where the exact SQL statements are re-executed on the slave, potentially leading to diverging data.

⚠️

Missing ORDER BY Clause

Without an explicit `ORDER BY` clause in the `SELECT` statement, the database system is free to return rows in any order, which can vary between executions or servers.

🛠️

Solutions

3 solutions available

1. Explicitly Define Auto-Increment Column in SELECT easy

Prevent data loss by explicitly selecting the auto-increment column and ensuring a predictable order.

1

When creating a new table from a `SELECT` statement that includes a table with an auto-increment column, explicitly include the auto-increment column in your `SELECT` list. This helps MariaDB recognize the intent and potentially handle it more predictably. It's also crucial to add an `ORDER BY` clause to ensure a deterministic order of rows.

CREATE TABLE new_table AS SELECT id, column1, column2 FROM original_table ORDER BY id;

2

If you want the new table to also have an auto-increment column, you'll need to define it separately after the `CREATE TABLE AS SELECT` statement.

ALTER TABLE new_table ADD COLUMN new_id INT AUTO_INCREMENT PRIMARY KEY FIRST;

2. Manually Populate Auto-Increment Column medium

Avoid the `CREATE TABLE AS SELECT` pitfall by inserting data in two steps, allowing MariaDB to manage auto-increment.

1

First, create the new table with the desired structure, including the auto-increment column definition.

CREATE TABLE new_table (
    new_id INT AUTO_INCREMENT PRIMARY KEY,
    column1 VARCHAR(255),
    column2 INT
);

2

Then, insert the data from the original table into the new table. Exclude the auto-increment column from the `INSERT` statement; MariaDB will automatically generate values for it.

INSERT INTO new_table (column1, column2)
SELECT column1, column2 FROM original_table;

3. Disable `sql_mode` Temporarily (Use with Caution) easy

Temporarily bypass the strict check by disabling the relevant SQL mode. This is a quick fix but can mask underlying issues.

1

Before executing your `CREATE TABLE ... SELECT` statement, set the `sql_mode` session variable to exclude `NO_AUTO_VALUE_ON_ZERO` and `STRICT_TRANS_TABLES` (or `NO_ENGINE_SUBSTITUTION` if applicable). This is a session-level change and will not affect other connections.

SET SESSION sql_mode = 'ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES'; -- Adjust based on your existing sql_mode

2

Execute your `CREATE TABLE ... SELECT` statement.

CREATE TABLE new_table AS SELECT * FROM original_table;

3

It's highly recommended to revert the `sql_mode` to its original settings immediately after the operation to maintain data integrity and consistency, especially if replication is involved.

SET SESSION sql_mode = '<your_original_sql_mode>'; -- Restore your original sql_mode

🔗

Related Errors

5 related errors

MariaDB Error 1723: Unsafe CREATE TABLE SELECT