Azure database for MySQL doesn't get detected properly

[spacentropy]

Hi.

Short story: we've got three production incidents on Azure Database for MySQL & MySqlConnector because of this :-(

MySqlConnector/src/MySqlConnector/Core/ServerSession.cs

Line 1315 in f3508e6

return ServerVersion.OriginalString == "5.6.26.0" || ServerVersion.OriginalString == "5.6.39.0";

Long story:

• Azure Database for MySQL 5.7 identifies itself as 5.6.42.
• MySqlConnector uses COM_CHANGE_USER as a connection reset method in 5.6. In 5.7 things have improved and we have mysql_reset_connection() method, which is considerably more efficient.
  MySqlConnector has a way to understand if it's talking to Azure Database for MySQL and issue select version() in order to get real version of MySQL. The way it's implemented is prone to error, as it uses hardcoded version number which MySQL presents as only means of getting the version. We use 0.44.1 version of connector which has only "5.6.26.0" hardcoded. I see that "5.6.39.0" has been added after a while, but right now our servers show "5.6.42.0", so even if we used 0.60, the detected version would still be wrong.

Because of these things combined, we've got three production incidents after Microsoft updated minor version of MySQL and introduced a slight latency in COM_CHANGE_USER. Servers with 5k+ queries per second would have 300+ threads active waiting for Change user with apps' latencies skyrocketing. We shouldn't see COM_CHANGE_USER at all, as we have MySQL 5.7.

I can make a PR adding "5.6.42.0" to this list, but let's make a more robust solution. Every time Microsoft updates their proxy version, we would have downtime. Maybe we could use connection string with something like ServerVersionOverride=5.7 or ForceGetServerVersion=true.
Or we could use DNS names of servers (*.mysql.database.azure.com) instead.
I personally think ServerVersionOverride=5.7 would better as it's takes roundtrips than getting the server version each time with select version().

Related issues:
#288

[bgrainger]

I asked Microsoft for an official way but it didn't get far: https://social.msdn.microsoft.com/Forums/en-US/155e76b4-074c-41fa-8527-972855664966/detect-the-azure-mysql-proxy?forum=AzureDatabaseforMySQL

Do you have a support channel with Microsoft where you could raise this issue (while we work on alternatives)?

[badbod99]

It seems that their advice is to use SELECT VERION(); to get the version number after being connected. Since they are using a gateway MySQL server to redirect to your actual instance, and that gateway will be the same regardless of the instance it's redirecting to, it seems that's the only possible method.

[spacentropy]

Sure, Yes, I've raised this issue through our channels. Waiting for the response 👀
Actually I would assume that this has something to do with statelessness of their proxy, maybe they don't want to fetch/deal with the version number on the proxy

[bgrainger]

FWIW, ServerVersionOverride might be too simplistic: MariaDB is generally compatible with MySQL Server 5.6, but adds some 5.7 features.

I like checking the DNS name as a way of detecting the proxy, but as you mentioned, it does take an extra round-trip to detect the actual server version.

(And my question to them wasn't about detecting the server version, which is "easy", but about detecting the proxy, in order to know whether the server version should be checked separately.)

[bgrainger]

I started working on integration tests for Azure Database for MySQL some time ago (e1d2238) but never merged them into master. If I got those working again, it might help detect changes to the proxy version (by breaking the build, which could be annoying).

[spacentropy]

I think that's still not optimal, as it will break our prod faster than integration tests as we have more servers :-) Also they update different regions at different pace.

Ideally this would require help from Microsoft. We could delete code in ServerSession...

[bgrainger]

I've pushed the simplest fix for this issue to address the immediate problem (it can ship soon): 148c5ea

But I would like a more robust fix long-term.

[spacentropy]

Commented on commit, thanks!

[bgrainger]

I don't think I ever made a UserVoice suggestion for identifying the proxy, so I've done that now: https://feedback.azure.com/forums/597982-azure-database-for-mysql/suggestions/38907748-identify-the-azure-database-for-mysql-proxy-in-the

[bgrainger]

Fixed in 0.60.3.