Error

Error Code: 1792

MariaDB Error 1792: Statement in Read-Only Transaction

📦 MariaDB

📋

Description

This error indicates an attempt to execute a statement that modifies data or alters the database state within a transaction explicitly designated as READ ONLY. Read-only transactions are designed solely for querying data, ensuring data integrity and consistency by preventing any modifications.

💬

Error Message

Cannot execute statement in a READ ONLY transaction.

🔍

Known Causes

4 known causes

⚠️

Explicit Read-Only Transaction

The current transaction was intentionally started using `START TRANSACTION READ ONLY` or a similar command, preventing any data modification operations.

⚠️

Session Read-Only Mode

The `transaction_read_only` session variable is set to `ON`, forcing all subsequent transactions within the current session to be read-only.

⚠️

Global Read-Only Mode

The `transaction_read_only` global variable is set to `ON`, which affects all new sessions and their transactions, making them read-only by default.

⚠️

Replica Server Restriction

The MariaDB server is configured as a replica (slave) and is operating in read-only mode to prevent accidental writes and maintain replication integrity.

🛠️

Solutions

4 solutions available

1. Disable Read-Only Mode for the Session easy

Temporarily disable read-only mode for the current database session.

Connect to your MariaDB server using a client (e.g., `mysql` command-line client, DBeaver, MySQL Workbench).

mysql -u your_user -p your_database

Execute the following SQL statement to disable read-only mode for the current session. This setting is session-specific and will revert to its default upon disconnection.

SET SESSION TRANSACTION READ WRITE;

Now, execute your intended statement that was previously failing.

-- Your INSERT, UPDATE, DELETE, or other DML statement here

2. Commit or Rollback Pending Transactions easy

Close any open transactions that might be implicitly setting the session to read-only.

Connect to your MariaDB server.

mysql -u your_user -p your_database

Check for any active transactions. If you're unsure, you can try committing or rolling back.

COMMIT;

If `COMMIT` doesn't resolve it, try rolling back any pending changes.

ROLLBACK;

After ensuring no pending transactions are blocking, execute your statement.

-- Your INSERT, UPDATE, DELETE, or other DML statement here

3. Verify and Adjust Global Read-Only Settings medium

Check if the server-wide `read_only` or `super_read_only` system variables are enabled and disable them if necessary.

Connect to your MariaDB server with administrative privileges.

mysql -u root -p

Check the current value of the `read_only` and `super_read_only` system variables.

SHOW VARIABLES LIKE 'read_only';

Check the `super_read_only` variable as well.

SHOW VARIABLES LIKE 'super_read_only';

If `read_only` or `super_read_only` is ON, you can temporarily disable it for the current session using `SET SESSION TRANSACTION READ WRITE;` (as in Solution 1). For a more permanent change, you can disable it globally (requires `SUPER` or `SYSTEM_VARIABLES_ADMIN` privilege). Execute the following command.

SET GLOBAL read_only = OFF;

Similarly, for `super_read_only`:

SET GLOBAL super_read_only = OFF;

To make this change persistent across server restarts, you need to modify your MariaDB configuration file (e.g., `my.cnf` or `my.ini`). Find the `[mysqld]` section and ensure `read_only = OFF` and `super_read_only = OFF` are set, or remove these lines if they are present and set to ON. After editing the configuration file, restart the MariaDB service.

# Example my.cnf snippet:
[mysqld]
read_only = OFF
super_read_only = OFF

After making the global change and restarting the server (or using `SET GLOBAL`), execute your statement.

-- Your INSERT, UPDATE, DELETE, or other DML statement here

4. Ensure Proper Transaction Handling in Application Code advanced

Review your application's database interaction logic to ensure transactions are managed correctly.

Identify the part of your application code that is attempting to execute the statement causing the error.

N/A

Examine how transactions are being started, committed, and rolled back. Look for patterns where a transaction might be left open or implicitly marked as read-only.

Example in Python with `mysql.connector`:
# Incorrect: Transaction might remain open
try:
    cursor.execute("SELECT ...")
    cursor.execute("INSERT ...") # This might fail if SELECT started a read-only transaction implicitly
finally:
    connection.close() # Transaction not explicitly committed/rolled back

# Correct: Explicitly manage transactions
try:
    cursor.execute("INSERT ...")
    connection.commit()
except Exception as e:
    connection.rollback()
finally:
    cursor.close()
    connection.close()

Ensure that every `START TRANSACTION` or equivalent (e.g., implicit transaction start) is paired with a `COMMIT` or `ROLLBACK` before the connection is closed or reused for a write operation.

N/A

If your application uses connection pooling, ensure that connections are properly returned to the pool after transactions are finalized to avoid stale transaction states.

N/A

🔗

Related Errors

5 related errors