I tried the same situation in a Postgres, and the error seems slightly
different, depending on the command:
testdb=> insert into table values ('lorem ipsum');
ERROR: cannot execute INSERT in a read-only transaction
testdb=> update table set name = 'lorem ipsum';
ERROR: cannot execute UPDATE in a read-only transaction
testdb=> delete from table;
ERROR: cannot execute DELETE in a read-only transaction
Hopefully it's simple to consider this scenario as well in the postgres
adapter.
Thanks so much for the quick response!
On Sunday, June 17, 2018 at 10:24:14 PM UTC+2, Jeremy Evans wrote:
>
> On Sunday, June 17, 2018 at 9:41:57 AM UTC-7, Ian Murray wrote:
>>
>> Background
>>
>> We're currently running an Aurora MySQL cluster in AWS. This cluster
>> consists of one writer and several read-replicas. Whenever the writer
>> fails-over, one of the readers gets promoted to writer and the writer
>> becomes a reader. The way one connects to this cluster is via two different
>> endpoints, a writer endpoint (which obviously also allows reading) and a
>> read-only endpoint. These endpoints basically translate to an instance's ip
>> address whenever one connects. For the read-only endpoint, amazon
>> apparently round-robins between the read replicas.
>>
>> The problem
>>
>> If the write instance fails, aws promotes one of the read replicas to a
>> writer, meaning the old instance will become a reader. Now, when sequel
>> connects to the write endpoint, it connects to a write instance. If this
>> instance fails, eventually sequel will reattempt to execute write
>> statements to this instance, which has now become a read-only instance. The
>> instance will error with the message The MySQL server is running with
>> the --read-only option so it cannot execute this statement. The problem
>> is that sequel does not recongnize this as a disconnect error, so it keeps
>> trying to execute commands which fail.
>>
>> What should happen
>>
>> I would expect sequel to attempt to reconnect to the cluster's write
>> endpoint after receiving an error like this, which should solve the issue,
>> since aws would route the connection to the new write instance.
>>
>> How I think this can be solved
>>
>> Adding the error to
>> https://github.com/jeremyevans/sequel/blob/master/lib/sequel/adapters/utils/mysql_mysql2.rb#L12-L20
>>
>> should get rid of the issue. Unfortunately, I'm not quite sure how an
>> Aurora Postgres cluster behaves in the same situation. I hope someone can
>> shed some light if they have encountered this in a Postgres cluster.
>>
>> I prefer to ask about this here before attempting to submit a PR about it.
>>
>
> I think treating the error as a disconnect error makes sense and have made
> the change locally. It should be pushed to GitHub tomorrow after I have a
> chance to do more thorough testing.
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
