I am migrating my Database from MSSQL to Aurora(MYSQL). The application is using .net and NHibernate. We tried the Nhibernate driver for MYSQL "MySql.Data" and it works fine for all use cases but failover. When a failover happens, the connection is not reset and it keeps trying to connect to the old IP from the writer cluster and so all the write call fails with the exception "The MySQL server is running with the --read-- only option so it cannot execute this statement". This only gets resolved after doing an IISRESET on the web servers.
I know that for java, the MariaDB connector is able to take care of this use case. Is there any similar driver available for .net applications?
What is the best practice to handle such use case?
MariaDB java connector has a specific implementation for Aurora. There is none that i know for .net.
Still the normal solution is using cluster DNS : Aurora provide different DNS link:
- one for each instance
- cluster endpoint (like xxx.cluster-yyy.zzz.rds.amazonaws.com)
- reader endpoint (load balanced) (like xxx.cluster-ro-yyy.zzz.rds.amazonaws.com)
Using the cluster endpoint, you are using the current writer instance. When there is a failover, this DNS will point to the new master, but this change take some time (<60s), so during that interval you may end up pointing to the previous master, that if available will be now a slave pointing to a new paster.
The best solution is to query "show global variables like 'innodb_read_only'" checking that result is OFF to validate connection.
