Error
Error Code:
1675
MariaDB Error 1675: Unsafe Mixed Transactional Access
Description
This error indicates that a transaction attempted to access a non-transactional table (like MyISAM) after it had already accessed a transactional table (like InnoDB). This sequence is considered unsafe for binary logging, particularly with statement-based replication, as it can lead to data inconsistency during replication or rollbacks. MariaDB prevents this operation to maintain data integrity.
Error Message
Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction.
Known Causes
3 known causesMixed Storage Engines in Transactions
The transaction explicitly or implicitly involves tables from different storage engines, where some are transactional (e.g., InnoDB) and others are not (e.g., MyISAM).
Statement-Based Replication (SBR)
The MariaDB server is configured for statement-based replication, which cannot reliably replicate transactions that mix transactional and non-transactional tables.
Application Design Flaw
The application's SQL queries or transaction management logic inadvertently combines operations on different table types within a single `START TRANSACTION ... COMMIT/ROLLBACK` block.
Solutions
3 solutions available1. Separate Transactional and Non-Transactional Operations easy
Execute operations on transactional and non-transactional tables in separate transactions.
1
Identify the statement causing the error. It will typically involve `BEGIN`, `COMMIT`, or `ROLLBACK` implicitly or explicitly, and then accessing a table that uses a non-transactional storage engine (like MyISAM) after accessing a transactional one (like InnoDB).
2
Modify your application logic or SQL script to ensure that all operations on transactional tables are completed and committed (or rolled back) *before* any operations are performed on non-transactional tables within the same logical unit of work.
Example: If you're using stored procedures, ensure that a stored procedure that modifies transactional tables doesn't also modify non-transactional tables in the same execution block. You might need to call two separate stored procedures.3
If using explicit transactions, ensure the `COMMIT` or `ROLLBACK` happens after all transactional operations and before any non-transactional operations.
BEGIN;
-- Operations on transactional tables (e.g., InnoDB)
INSERT INTO transactional_table (col1) VALUES ('value1');
COMMIT; -- Commit transactional operations
-- Now, operations on non-transactional tables (e.g., MyISAM)
INSERT INTO non_transactional_table (col2) VALUES ('value2');2. Convert Non-Transactional Tables to a Transactional Engine medium
Migrate MyISAM or other non-transactional tables to InnoDB.
1
Identify all tables in your database that are using a non-transactional storage engine. Common non-transactional engines include `MyISAM`. You can check this with the following SQL command:
SHOW TABLE STATUS WHERE Engine NOT IN ('InnoDB', 'XtraDB', 'TokuDB');2
For each identified non-transactional table, convert it to the InnoDB storage engine. This is a common and recommended practice for most modern MariaDB deployments as InnoDB provides ACID compliance.
ALTER TABLE your_non_transactional_table ENGINE=InnoDB;3
After converting all relevant tables, re-run your application or SQL statements that previously caused the error. This change should permanently resolve the issue as all tables will now support transactions.
3. Disable Mixed Transactional Access Enforcement (Use with Caution) advanced
Temporarily disable the `sql_require_primary_key` and `innodb_strict_mode` to allow mixed access, but understand the implications.
1
Understand that this is a workaround and not a true fix. Disabling these settings can lead to data integrity issues if not managed carefully, as it bypasses some safety checks. It's generally recommended to avoid this unless absolutely necessary and you fully understand the risks.
2
You can disable `innodb_strict_mode` globally or for the current session. Disabling it globally requires modifying the MariaDB configuration file (`my.cnf` or `my.ini`). For a session, use the following command:
SET SESSION innodb_strict_mode = 0;
SET SESSION sql_require_primary_key = 0;3
After setting these session variables to `0`, re-run the problematic statement. This will allow mixed transactional access.
4
Remember to reset these settings or ensure they are properly configured in your application's connection parameters if you only want this to apply to specific connections. For a global change, edit your MariaDB configuration file and restart the server.
Example (in my.cnf):
[mysqld]
innodb_strict_mode = 0
sql_require_primary_key = 0