mysql,

AWS Error 1290 (HY000): The MySQL server is running with the --read-only

Feb 10, 2022 · 2 mins read · Post a comment

Using AWS Aurora auto-scaling will increase the resources to handle the workload and connections. So that way you can see a bunch of replicas that are taking care of the workload. Connecting to one of the Aurora endpoints can lead you to the following error: Error 1290 (HY000): The MySQL server is running with the --read-only. Let’s see what might be.

Prerequisites

  • MySQL
  • AWS Aurora

Solution

Step 1. As a first step and very important one is to check what privileges are granted to the current user.

show grants;

If the output contains:

'GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO \'admin\'@\'%\' WITH GRANT OPTION'

It means that the user has admin privileges and the issue is not the user itself.

If the output contains only:

'GRANT SELECT, SHOW *.* TO \'admin\'@\'%\' WITH GRANT OPTION'

It means that the user doesn’t have permission to insert, update, drop tables, or in other words change the structure of the database. So in that case you will face the issue: Error 1290 (HY000): The MySQL server is running with the --read-only and to resolve it you need to grant that user more privileges or find some admin user to use.

Step 2. If you have verified that the user has all the privileges from the first output then you are connecting to the wrong AWS Aurora or MySQL endpoint. Follow the next steps to fix the issue.

1. Open Amazon RDS console.
2. Find the relevant Aurora or MySQL cluster that you are using.
3. The second column contains `Role`.
4. Make sure that you are using `Writer instance`.

Most likely previously you have connected to a Reader instance and that’s the reason for Error 1290 (HY000): The MySQL server is running with the --read-only.

Conclusion

If you have any other workarounds don’t hesitate to put a comment below. On a side note, follow our official channel on Telegram.

aws mysql aws aurora errors