📋

Description

This error indicates that an SQL statement attempts to write data to a combination of transactional (e.g., InnoDB), nontransactional (e.g., MyISAM), or temporary tables. MariaDB flags this operation as unsafe, particularly when using statement-based replication, to prevent potential data inconsistencies.

💬

Error Message

Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them.

🔍

Known Causes

3 known causes

⚠️

Mixed Table Type Operations

The core cause is a single SQL statement trying to modify data in both transactional tables (e.g., InnoDB) and nontransactional tables (e.g., MyISAM).

⚠️

Statement-Based Replication Incompatibility

MariaDB detects these mixed operations as potentially unsafe for statement-based binary logging, which can cause data divergence between master and replica servers.

⚠️

Involvement of Temporary Tables

This error can also be triggered if the statement includes a temporary table along with other transactional or nontransactional tables during a write operation.

🛠️

Solutions

3 solutions available

1. Convert Nontransactional Tables to InnoDB medium

Migrate all tables involved in the problematic statement to the InnoDB storage engine.

1

Identify the nontransactional table(s) involved in the statement. You can do this by examining the `CREATE TABLE` statements or by querying the `information_schema.tables` table.

SELECT TABLE_NAME, ENGINE FROM information_schema.tables WHERE TABLE_SCHEMA = 'your_database_name' AND ENGINE != 'InnoDB';

2

For each nontransactional table, perform a `ALTER TABLE` statement to convert it to InnoDB. It's highly recommended to do this during a maintenance window or with backups in place.

ALTER TABLE your_table_name ENGINE=InnoDB;

3

Re-run the statement that previously caused the error. If all tables involved are now transactional (e.g., InnoDB), the error should be resolved.

YOUR_ORIGINAL_STATEMENT_HERE;

2. Separate Writes into Transactional and Nontransactional Operations medium

Restructure the statement to avoid simultaneous writes to both transactional and nontransactional tables.

1

Analyze the statement that is causing the error. Identify which parts are writing to transactional tables (e.g., InnoDB) and which are writing to nontransactional tables (e.g., MyISAM).

YOUR_ORIGINAL_STATEMENT_HERE;

2

Break down the statement into separate, independent operations. Perform writes to transactional tables within a transaction, and perform writes to nontransactional tables separately.

START TRANSACTION;
-- Writes to transactional tables
INSERT INTO transactional_table (column1) VALUES ('value1');
COMMIT;

-- Writes to nontransactional tables (executed separately)
INSERT INTO nontransactional_table (column2) VALUES ('value2');

3

If the original statement involved reading from both types of tables and then writing, consider if the write operation can be isolated to only one type of table or if the data can be staged in a temporary table first.

START TRANSACTION;
INSERT INTO temporary_table (data) SELECT column1 FROM transactional_table WHERE condition;
COMMIT;

-- Then, process temporary_table and write to nontransactional_table separately.

3. Use Temporary Tables Wisely (if applicable) easy

Ensure temporary tables are not mixed with nontransactional tables in write operations.

1

If your statement involves writing to a temporary table and a nontransactional table simultaneously, this error will occur. Temporary tables in MariaDB are typically transactional by default (depending on their creation).

CREATE TEMPORARY TABLE temp_data (id INT);
INSERT INTO temp_data VALUES (1);
-- If nontransactional_table is MyISAM, the following will fail:
INSERT INTO nontransactional_table (col) SELECT id FROM temp_data;

2

Separate the operations. Write to the temporary table, then commit or end the transaction for the temporary table's writes, and then perform the write to the nontransactional table as a separate operation.

CREATE TEMPORARY TABLE temp_data (id INT);
INSERT INTO temp_data VALUES (1);

-- Perform the write to the nontransactional table separately
INSERT INTO nontransactional_table (col) SELECT id FROM temp_data;

DROP TEMPORARY TABLE temp_data;

🔗

Related Errors

5 related errors

MariaDB Error 1693: Unsafe Mixed Table Write