Error
Error Code:
1874
MySQL Error 1874: InnoDB Read-Only Mode
Description
This error indicates that the InnoDB storage engine has entered a read-only state, preventing any write operations to your database. It typically occurs when MySQL detects potential data corruption or inconsistencies, or when underlying system issues compromise data integrity.
Error Message
InnoDB is in read only mode.
Known Causes
4 known causesData File Inconsistency
InnoDB detected corruption or inconsistencies within its data files or transaction logs, prompting it to switch to read-only mode to prevent further damage.
Underlying File System Issues
Problems with the server's file system, such as insufficient disk space, I/O errors, or corruption, can prevent InnoDB from performing write operations.
Improper Server Shutdown
An abrupt or unclean shutdown of the MySQL server can leave InnoDB in an inconsistent state, causing it to start in read-only mode for protection.
Explicit Configuration
The `innodb_read_only` server variable might have been intentionally or unintentionally enabled, forcing InnoDB into a read-only state.
Solutions
3 solutions available1. Temporary Disable InnoDB Read-Only Mode easy
Quickly disable read-only mode for InnoDB for immediate write operations.
1
Connect to your MySQL server as a user with sufficient privileges (e.g., root).
2
Execute the following SQL command to set the `innodb_read_only` variable to OFF.
SET GLOBAL innodb_read_only = OFF;3
Verify the setting by querying the `GLOBAL VARIABLES` table.
SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';2. Persistent Configuration Change for InnoDB Read-Only medium
Modify the MySQL configuration file to permanently disable read-only mode for InnoDB.
1
Locate your MySQL configuration file. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or within the MySQL data directory.
2
Open the configuration file using a text editor with administrative privileges.
3
Find the `[mysqld]` section. If it doesn't exist, create it.
[mysqld]4
Add or modify the `innodb_read_only` directive to `OFF`. If the directive exists and is set to `ON`, change it to `OFF`.
innodb_read_only = OFF5
Save the configuration file.
6
Restart the MySQL server for the changes to take effect.
sudo systemctl restart mysql # Or your system's equivalent command3. Check for Read-Only File System or Mount Options advanced
Investigate if the underlying file system or mount options are causing the read-only state.
1
Identify the directory where your MySQL data files are stored. This is typically specified by the `datadir` variable in your MySQL configuration.
SHOW VARIABLES LIKE 'datadir';2
On the server, check the mount options for the file system containing the MySQL data directory. Look for `ro` (read-only).
mount | grep /path/to/your/datadir3
If the file system is mounted read-only, you'll need to remount it with read-write permissions or adjust the `/etc/fstab` file for persistent changes. This is highly dependent on your operating system and storage configuration.
sudo mount -o remount,rw /path/to/your/datadir4
Alternatively, check if the entire file system is marked as read-only due to errors. You might need to run file system checks (e.g., `fsck`).