1

I'm using Amazon Aurora and I have two endpoints, my endpoint which can read/write are having some issues to execute queries, these are the issues:

SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement, query was: UPDATE `integration` SET `setup_type` = ? WHERE (integration_id='4')

SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement, query was: UPDATE `indexer_state` SET `indexer_id` = ?, `status` = ?, `updated` = '2018-03-12 14:38:45', `hash_config` = ? WHERE (state_id='1')

Amazon uses the global --read-only configuration in one of the servers. What's the difference between to use --read-only and a user which can just execute SELECT queries?

1

When a user gets read only permission (select), it will not be able to write/update/etc even when the global read-only value is 0. However, a user with \[SUPER\] privilege, will be able to write on a server where global read-only value is set to 1.

One more difference is that read only permissions are very granular, while read-only variable is a server wide variable.

Usually, you'd give SELECT permissions to users who you want them to read from a Server/DB/Table where other users can write. On the other hand, you set read-only variable to 1 when you want all but super users to be able to write to. For example, a read slave.

2

To the user, probably no difference.

To the system integrity, --read-only prevents you from accidentally writing to a Slave, thereby messing up the Replication integrity.

Aurora has no control (I assume) over whether you grant write permission and happen to let them hit a Slave with an UPDATE. This is an easy, simple, prevention.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.