📋

Description

This error occurs when a client attempts to execute a data modification statement (e.g., INSERT, UPDATE, DELETE) within a transaction that has been explicitly or implicitly set to read-only mode. MySQL prevents such operations to maintain data integrity and consistency, especially in replication or specific application architectures.

💬

Error Message

Cannot execute statement in a READ ONLY transaction.

🔍

Known Causes

4 known causes

⚠️

Explicit Read-Only Transaction

The current transaction was explicitly started or configured with the `READ ONLY` attribute, disallowing any data modification statements.

⚠️

Server in Read-Only Mode

The MySQL server instance itself is configured with the `read_only` system variable enabled, preventing writes from non-privileged users.

⚠️

Replication Slave Instance

The statement is being executed on a MySQL replication slave server that is intentionally set to read-only to prevent divergence from the master.

⚠️

Application Configuration Error

The application or ORM framework is inadvertently initiating transactions in read-only mode when write operations are intended.

🛠️

Solutions

4 solutions available

1. End the Read-Only Transaction Explicitly easy

If you are intentionally in a read-only transaction, explicitly end it to allow writes.

1

If you are using `START TRANSACTION READ ONLY;` or have set `tx_read_only = 1` for the session, commit or rollback the transaction to exit the read-only state.

COMMIT;
-- OR --
ROLLBACK;

2

After ending the transaction, retry your write operation.

INSERT INTO your_table (column1) VALUES ('some_value');

2. Disable Session Read-Only Mode easy

Turn off the session-level read-only setting that is preventing writes.

1

Check if your current session is in read-only mode. You can do this by querying the `tx_read_only` system variable.

SHOW VARIABLES LIKE 'tx_read_only';

2

If `tx_read_only` is ON (1), set it to OFF (0) for the current session.

SET SESSION tx_read_only = 0;

3

Now, execute your write statement.

INSERT INTO your_table (column1) VALUES ('some_value');

3. Modify Global Read-Only Setting (Use with Caution) medium

Disable the global read-only mode if it's preventing writes across all sessions.

1

Check the global read-only status. This setting affects all connections unless overridden at the session level.

SHOW GLOBAL VARIABLES LIKE 'read_only';

2

If `read_only` is ON (1), you can disable it. **Note:** This requires super privileges and will affect all users and connections. It's generally better to manage this at the session level if possible.

SET GLOBAL read_only = 0;

3

You may need to restart your MySQL server for this global change to fully take effect in some configurations or if the server was started with `read_only=1` in its configuration file.

# Example for systemd-based systems
sudo systemctl restart mysql

# Example for older init systems
sudo service mysql restart

4

Verify the global setting again and then attempt your write operation.

SHOW GLOBAL VARIABLES LIKE 'read_only';
INSERT INTO your_table (column1) VALUES ('some_value');

4. Review Application Logic for Unintended Read-Only Transactions advanced

Investigate your application code to find where read-only transactions are being initiated.

1

Examine your application's database connection and transaction management code. Look for explicit calls to `START TRANSACTION READ ONLY;` or settings that enable `tx_read_only` for the session.

Example in Python with SQLAlchemy:

from sqlalchemy import create_engine, text

engine = create_engine('mysql+mysqlconnector://user:password@host/dbname')

with engine.connect() as connection:
    # Check if read-only is set
    result = connection.execute(text("SHOW VARIABLES LIKE 'tx_read_only';")).fetchone()
    if result and result[1] == 'ON':
        print("Session is in read-only mode. Disabling.")
        connection.execute(text("SET SESSION tx_read_only = 0;"))

    # Proceed with write operation
    connection.execute(text("INSERT INTO your_table (column1) VALUES (:value);", {'value': 'some_value'}))
    connection.commit()

2

If you find code that incorrectly sets the session to read-only, modify it to remove that setting before performing write operations.

Remove or conditionally execute lines like `SET SESSION tx_read_only = 1;` or `START TRANSACTION READ ONLY;` when writes are intended.

3

Test your application thoroughly after making changes to ensure write operations now succeed and that read-only transactions are only used when intended.

N/A

🔗

Related Errors

5 related errors

MySQL Error 1792: Write in Read-Only Transaction