📋

Description

This error indicates that an `INSERT DELAYED` statement cannot be safely replicated or written to the binary log with the current binary log format. It occurs because the exact insertion time of rows is non-deterministic, making it challenging to maintain data consistency across replicas or for point-in-time recovery.

💬

Error Message

The statement is unsafe because it uses INSERT DELAYED. This is unsafe because the times when rows are inserted cannot be predicted.

🔍

Known Causes

3 known causes

⚠️

Use of INSERT DELAYED

The `INSERT DELAYED` syntax defers the actual row insertion, making the operation non-deterministic regarding its execution time.

⚠️

Statement-based Binary Logging

The server's binary log format is set to `STATEMENT` or `MIXED`, which is unsafe for operations whose timing cannot be exactly reproduced on a replica.

⚠️

Replication Environment

The error typically arises in a replication setup where maintaining data consistency between primary and replica servers is critical.

🛠️

Solutions

3 solutions available

1. Remove INSERT DELAYED Clause easy

The most direct solution is to remove the 'DELAYED' keyword from your INSERT statements.

1

Locate all `INSERT DELAYED` statements in your application code or scripts.

2

Modify these statements by removing the `DELAYED` keyword. This will make the INSERT operations synchronous and predictable.

UPDATE your_table SET column1 = value1, column2 = value2 WHERE condition;
-- becomes:
INSERT INTO your_table (column1, column2) VALUES (value1, value2);

3

Test your application thoroughly after making these changes to ensure data integrity and expected behavior.

2. Configure Table to Not Use INSERT DELAYED medium

Alter the table definition to explicitly disallow INSERT DELAYED.

1

Identify the tables that are currently configured to use `INSERT DELAYED`.

SHOW VARIABLES LIKE 'insert_delayed_blocks%';
SHOW VARIABLES LIKE 'insert_delayed_queue_size%';

2

Modify the table definition to remove any `INSERT_DELAYED` option. This is typically done by altering the table.

ALTER TABLE your_table DISABLE_INSERT_DELAYED;

3

If you are using MyISAM tables, be aware that `INSERT DELAYED` is a feature of MyISAM. Consider migrating to InnoDB if possible for better transactional integrity and performance characteristics.

3. Migrate to InnoDB and Use Standard INSERT advanced

For robust data handling, switch to the InnoDB storage engine and use standard INSERT statements.

1

Backup your database before proceeding with any storage engine changes.

2

Identify tables using MyISAM that have `INSERT DELAYED` operations.

SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE ENGINE = 'MyISAM';

3

Alter the storage engine of these tables to InnoDB. This will automatically disable `INSERT DELAYED` for these tables.

ALTER TABLE your_table ENGINE=InnoDB;

4

Update your application code to use standard `INSERT` statements instead of `INSERT DELAYED`.

INSERT INTO your_table (column1, column2) VALUES (value1, value2);

5

Test your application thoroughly to ensure data consistency and performance.

🔗

Related Errors

5 related errors

MariaDB Error 1669: Unsafe INSERT DELAYED Statement