Update connection host. impact on connection pool?

[chaoyangnz]

Sorry, I am not sure where I should put my question.

I have a project needing to switch MySQL host at a moment to make the application readonly.

So at the beginning, I configured two hosts:

• master is read-write
• and a read replica

internally, this pool will create two pools in Sequelize.

Assume 2 hours late, I need to update database host both using read replica.

I add the code in Sequelize hook like the below:

sequelize.beforeConnect((options) => {
    options.host = 'readonly.host'
    options.username = 'readonly.user'
    options.password = 'readonly.password'
})
sequelize.beforePoolAcquire((options) => {
    options.host = 'readonly.host'
    options.username = 'readonly.user'
    options.password = 'readonly.password'
})

So at the moment, in the writer pool, there are some connections actually retaining the read-write host, right?

then when Sequelize acquires a connection, is it still possible to acquire the old connection pointing to read-write host? as I hope after I switch host to readonly, all subsequent connections are supposed to be readonly connections pointing to readonly host.

Please help clarify.

Thanks

[sushantdhiman]

When your application updates the host information, it could also call destroyAllNow. That should clear all connections to the read-write host and force re-connection to the new updated host.

So update the host information, as your are doing in the hooks. Then call the destroyAllNow to refresh the pools.

There is still the issue of already acquired connections, may be you can kill them when application wants to update the host information.

[chaoyangnz]

Thanks @sushantdhiman that is great.

For already acquired connections and on-going queries, that makes sense as they happened before I switch.