the mysql server is running with the --read-only option so it cannot execute this statement

Question

Problem :

I am facing a strange MySQL error may be it is related to the database's read-only flag. The Web application that is using the MySQL is running on Debian 7.9. It was running well but suddenly started attempting to access the application powered website and also started producing the below error message on the blank webpage:

Error: 500 - SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement

Answer 1

There are two broad reasons behind your database is being set to read only as follows:

1) MySQL setting itself to read only

I am not very sure what is causing the MySQL to go read only may be the disk issues or corruption of database In any case something will appear in the logs, so please check the MySQL and system logs very carefully.

2) Client setting the database to read only

The clients connecting to MySQL can also set the database read only using the following command:

SET GLOBAL read_only = ON;

But to do this the user needs to have the SUPER user privileges. This permission is not needed for websites or applications those are using MySQL, They will keep it only for an admin account that it is used only for administering the database.

So one way is to lock down the permissions that each user has so they will only have permissions for doing the things that they need on the databases that are applicable to them. For using some out-of-the-box application they must come with instructions detailing what permissions are required e.g. SELECT, INSERT, DELETE, UPDATE.