📋

Description

This error signifies an attempt to create a new table using `CREATE TABLE ... SELECT ...` where the source table contains an `AUTO_INCREMENT` column. MySQL flags this operation as unsafe because the order of rows retrieved by the `SELECT` is non-deterministic, potentially leading to inconsistencies in replicated environments.

💬

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 source and the replica.

🔍

Known Causes

3 known causes

⚠️

Using CREATE TABLE ... SELECT

The error occurs when using the `CREATE TABLE new_table AS SELECT ... FROM existing_table` syntax.

⚠️

Source Table Has AUTO_INCREMENT

The `SELECT` statement is querying a source table that contains one or more `AUTO_INCREMENT` columns.

⚠️

Non-Deterministic Row Order

Without an explicit `ORDER BY` clause, the order in which rows are retrieved by the `SELECT` query is not guaranteed, making insertion order unpredictable.

🛠️

Solutions

3 solutions available

1. Explicitly Define Auto-Increment Value During Insertion medium

Manually assign auto-increment values instead of relying on automatic generation for `CREATE TABLE ... SELECT`.

1

Create the target table without an auto-increment column. You will manage the IDs manually.

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

2

Fetch the maximum existing ID from the source table or generate a sequence of IDs. A common approach is to use a session variable to simulate auto-increment.

SET @auto_id = (SELECT MAX(your_auto_increment_column) FROM source_table);

INSERT INTO new_table (id, column1, column2)
SELECT
    (@auto_id := @auto_id + 1) AS id,
    column1,
    column2
FROM source_table;

3

If you are creating a new table from scratch and want to maintain a similar auto-increment behavior, you can define the ID column as `BIGINT` or `INT` and manually manage it as shown above. For subsequent insertions into an existing table, you can use `INSERT ... SELECT` with a session variable to ensure deterministic ID generation.

SET @new_id = (SELECT MAX(your_auto_increment_column) FROM existing_table);

INSERT INTO existing_table (your_auto_increment_column, column1, column2)
SELECT
    (@new_id := @new_id + 1) AS your_auto_increment_column,
    column1,
    column2
FROM another_table;

2. Use `ALTER TABLE` to Add Auto-Increment After Data Load easy

Load data first, then add the auto-increment property to an existing column.

1

Create the target table without the auto-increment property on the ID column.

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

2

Insert the data from the source table into the new table. The `SELECT` statement will use the existing primary key from the source table.

INSERT INTO new_table (id, column1, column2)
SELECT id, column1, column2 FROM source_table;

3

After the data is loaded, alter the `id` column to be an auto-increment column. This requires the `id` column to be unique and not NULL.

ALTER TABLE new_table MODIFY COLUMN id INT AUTO_INCREMENT;

4

If the `id` column is not unique or has NULL values after the insert, you'll need to clean it up before altering. For example, you might need to renumber the IDs sequentially before making it auto-increment.

SET @row_number = 0;
UPDATE new_table SET id = (@row_number:=@row_number + 1) ORDER BY id;
ALTER TABLE new_table MODIFY COLUMN id INT AUTO_INCREMENT;

3. Replicate Data Using a Deterministic Ordering medium

Ensure consistent row ordering for the `SELECT` statement by adding an explicit ordering clause.

1

When creating the new table using `CREATE TABLE ... SELECT`, always include an `ORDER BY` clause on a column that guarantees a unique and stable order. A primary key or a unique timestamp column is ideal.

CREATE TABLE new_table LIKE source_table;

INSERT INTO new_table (column1, column2) -- Exclude the auto-increment column from the INSERT list
SELECT column1, column2 FROM source_table ORDER BY source_table.primary_key_column;

2

Alternatively, if you are creating the table structure and selecting data in one go, ensure the `ORDER BY` clause is present. MySQL's auto-increment behavior during `CREATE TABLE ... SELECT` is indeed problematic due to potential ordering inconsistencies. This approach bypasses that by explicitly controlling the order.

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

INSERT INTO new_table (column1, column2)
SELECT column1, column2 FROM source_table ORDER BY source_table.some_unique_column;

3

If the `source_table` itself has an auto-increment column that you want to preserve, you might need to fetch the data into a temporary table first, then use a session variable to manage the IDs for the final insertion into the new table. This is similar to the first solution, but it emphasizes ordering before ID generation.

CREATE TEMPORARY TABLE temp_source LIKE source_table;
INSERT INTO temp_source SELECT * FROM source_table ORDER BY source_table.auto_increment_column;

SET @auto_id = 0;
CREATE TABLE new_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1 VARCHAR(255),
    column2 INT
);
INSERT INTO new_table (column1, column2)
SELECT column1, column2 FROM temp_source;

DROP TEMPORARY TABLE temp_source;

🔗

Related Errors

5 related errors

MySQL Error 1723: Unsafe Auto-Increment Table Creation