📋

Description

Error 1874 indicates that the InnoDB storage engine, the default for MariaDB, is currently operating in a read-only state. This prevents any data modification operations (INSERT, UPDATE, DELETE) from being executed on InnoDB tables. It commonly occurs during database recovery processes, after a crash, or when explicitly configured for maintenance.

💬

Error Message

InnoDB is in read only mode.

🔍

Known Causes

4 known causes

⚠️

Crash Recovery Mode

After an unclean shutdown or server crash, MariaDB might start InnoDB in read-only mode to prevent further data corruption during the recovery process.

⚠️

Manual Configuration

The `innodb_read_only` system variable has been explicitly set to `ON` in the MariaDB server configuration (`my.cnf`) for maintenance or data protection purposes.

⚠️

Low Disk Space

Insufficient disk space for the InnoDB data files or transaction logs can trigger read-only mode or recovery, preventing new writes.

⚠️

Filesystem Errors

Underlying filesystem corruption or errors can force InnoDB into read-only mode to protect data integrity and prevent further damage.

🛠️

Solutions

3 solutions available

1. Temporarily Disable InnoDB Read-Only Mode easy

Quickly re-enable write operations by setting the global variable.

1

Connect to your MariaDB server as a user with sufficient privileges (e.g., root).

2

Execute the following SQL command to disable read-only mode for InnoDB:

SET GLOBAL innodb_read_only = OFF;

3

Verify that InnoDB is no longer in read-only mode by checking the variable:

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';

2. Permanently Configure InnoDB Read-Only Mode in `my.cnf` medium

Prevent the error from recurring by adjusting the configuration file.

1

Locate your MariaDB configuration file, typically named `my.cnf` or `mariadb.conf`. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or within `/etc/mysql/conf.d/`.

2

Edit the configuration file using a text editor (e.g., `nano`, `vim`). You'll need root privileges.

sudo nano /etc/my.cnf

3

Find the `[mysqld]` section. If it doesn't exist, create it.

4

Add or modify the `innodb_read_only` parameter to `OFF` or comment it out. If the line is present and set to `ON`, change it to `OFF`. If it's not present, add it.

[mysqld]
innodb_read_only = OFF

5

Save the changes to the configuration file and exit the editor.

6

Restart the MariaDB service for the changes to take effect.

sudo systemctl restart mariadb

3. Check for Accidental `SET GLOBAL READ_ONLY` Command easy

Ensure no explicit `READ_ONLY` session or global variable is set.

1

Connect to your MariaDB server.

2

Check the global `read_only` variable:

SHOW GLOBAL VARIABLES LIKE 'read_only';

3

If the value is `ON`, disable it with:

SET GLOBAL read_only = OFF;

4

Check the session `read_only` variable (if you suspect a specific session):

SHOW SESSION VARIABLES LIKE 'read_only';

5

If the session value is `ON`, disable it for the current session with:

SET SESSION read_only = OFF;

🔗

Related Errors

5 related errors

MariaDB Error 1874: InnoDB Read-Only Mode Enabled