Add failover handling

[nanoflite]

On AWS RDS with a failover setup, no connection gets made to the new writer after a failover happens. This PR fixes that and handles failover gracefully. In a test I conducted, after a failover, it took only 8 seconds to recover.

[jeremydaly]

HI @nanoflite!

Thanks for this. Couple of quick questions. How is the ER_OPTION_PREVENTS_STATEMENT error tied to AWS RDS failover exactly, as it seems to be a generic error? Also, would there be any harm in just including a retry on this error anyway? That is, do we even need a flag for "handleFailover"?

I want to try and keep this library non-AWS specific, so if there is a way to make it more generic, that'd be preferred.

Thanks, Jeremy

[nanoflite]

Hey,

The 'ER_OPTION_PREVENTS_STATEMENT' error is not tied to AWS RDS specifically, but would occur in any failover scenario that works with a switchover via DNS. This error just says that the --readonly option is set for the mysql process while you are trying to do a write operation.

A retry would not help, because you really need to destroy the connection so a new connection is made to the right database host, i.e. the one with the writer role.

I understand you want to keep the library non-AWS specific, but I assume all failover scenarios that depend on a DNS switchover would be handled gracefully.

No worries if you do not want to merge this PR.

have a nice one, Johan

On Fri, Nov 27, 2020 at 2:51 PM Jeremy Daly [email protected] wrote:

HI @nanoflite https://github.com/nanoflite!

Thanks for this. Couple of quick questions. How is the ER_OPTION_PREVENTS_STATEMENT error tied to AWS RDS failover exactly, as it seems to be a generic error? Also, would there be any harm in just including a retry on this error anyway? That is, do we even need a flag for "handleFailover"?

I want to try and keep this library non-AWS specific, so if there is a way to make it more generic, that'd be preferred.

Thanks, Jeremy

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/jeremydaly/serverless-mysql/pull/91#issuecomment-734844903, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAOHZJU7UCBVLSEMSZMVF3SR6VGFANCNFSM4UD6M34A .

-- "Your time is limited, so don’t waste it living someone else’s life.", Steve Jobs

http://about.me/johan.vandenbrande

[jeremydaly]

Thanks, @nanoflite. I'll look this over and get it merged in.

[janpio]

Fundamentally ER_OPTION_PREVENTS_STATEMENT seems to also be able to be caused by other situations, not just a failover situation. Is this correct @nanoflite? If so, it might be a bit far reaching to use it as a signal for a failover.