[sql connector] Please recreate sql connection on connection failure

[hankduan]

Summary:
We had an incident where we failed over the mysql instance so the cname resolves to another ip at that time, but cadence did not recover. Cadence recovered after manually restarting it.
Later we found this is because Cadence uses go-sql-driver, which does not re-establish the connection to sql on failure.
Given this is a common failure mode, can Cadence recreate the sql connection on connection failure?

Here's the slack chat thread

Regarding the MySQL connector for cadence. We had an incident where we failed over the mysql instance so the cname resolves to another ip at that time, but cadence did not recover. Does Cadence's mysql connector re-resolve the cname

Maxim 23 hours ago
Filed go-sql-driver/mysql#1064

Hank Duan 23 hours ago
Thanks!

Maxim 21 hours ago
Did Cadence recover after restart?

Hank Duan 17 hours ago
yea

Hank Duan 17 hours ago
but it required a manual restart

Maxim 7 hours ago
Then I believe it is db driver issue

Hank Duan 2 hours ago
looks like they will not address it at the driver side. Are there any plans for cadence to reestablish connection on failures?

Hank Duan 2 hours ago
this is a very common failure mode

Hank Duan 2 hours ago
i.e. db failure over causes cname to switch. The original ip might no longer be connectable and will fail indefinitely until cadence re-resolve cname

Maxim 1 hour ago
Please file an issue

[jontro]

Just to chime in here, the issue could potentially be caused by a too long idle timeout.

See http://go-database-sql.org/connection-pool.html and this linked issue go-sql-driver/mysql#257

Changing maxConnLifetime should be a first step to try.
Afaik the connection manager should take care of retries. Dns records must of course be properly configured with a low ttl

[sheerun]

Could this be handled at cadence level by specifying multiple DNS addresses to connect? e.g. addresses to primary master and secondary master