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?
Best Answer
When a user gets read only permission (
select
), it will not be able to write/update/etc even when the globalread-only
value is 0. However, a user with\[SUPER\]
privilege, will be able to write on a server where globalread-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 setread-only
variable to 1 when you want all but super users to be able to write to. For example, a read slave.