It all started when I was trying to use the MySQL package to connect to Azure's Managed MariaDB offering but kept getting some cryptic errors – while I found the root cause and solution, the additional research uncovered more fascinating behaviors.

For context, I was using v2.7.0 (which is the default when working in the Auth0 Node runtime). The short version is that upgrading to v2.15.0 or higher resolves the issue.

As a sanity check, I tested this against AWS's RDS MariaDB and experienced the same behavior, but against a database manually installed on a single VM had no issue.

Problem

The initial error I received was:

Error: MySQL server is requesting the old and insecure pre-4.1 auth mechanism.Upgrade the user password or use the {insecureAuth: true} option.

After adding the option it instructs me to, I got the following:

ER_HANDSHAKE_ERROR: Bad handshake

Solution

In the version of the library I was using, it didn't support the authentication switch request that the server was attempting to reply with.

The issue was resolved in v2.150 of the package. The insecureAuth option can be removed if using a newer version of the package as the initial error thrown was the package not understanding the server's response.

Background

While the package introduces a fix for the behavior, the root cause still exists and causes issues in other clients due to some ambiguity in the documented protocols and more providers offering databases-as-a-service with various modifications to make RDMS more cloud-friendly.

The packet-level issue is that the managed databases send an RST, ACK after COM_QUIT.

The MySQL protocol says that the response to a QUIT is an OK packet or the connection closing, but doesn't specify if the closing should be FIN or RST (or whether it even matters).

Now, an RST response alone should be handled as an error, but the addition of an ACK can confuse some clients (as was the case here).

While there is a thread on the MSDN forums requesting a server-side solution to the problem, however, the response from Microsoft was less than helpful, but it did shed some light on what might be going on.

I’d suspect this is a behavior change when you connect to a Azure Database for MySQL instance where you are not directly connect to the instance, but through a gateway.
[...]
Out of security reasons, we decided to respect MySQL protocols and let server natively handles all the challenges and hexes.

Since the managed database cluster runs as part of much larger managed cluster/infrastructure, cloud providers (Azure and AWS alike) introduce gateways that sit in front of the actual database instances, which are evidenced by checking the DNS forwarding.

$ nslookup XXXX.mariadb.database.azure.com
Server:		10.0.0.2
Address:	10.0.0.2#53

Non-authoritative answer:
XXXX.mariadb.database.azure.com	canonical name = westus2-a.control.database.windows.net.
Name:	westus2-a.control.database.windows.net
Address: 13.66.226.202

That control endpoint is one of many multi-tenant gateways that route requests to the appropriate instance under the hood.

What I believe is happening is that the database instance itself is returning an RST packet as expected. The gateway is appending an ACK since, as far as it's concerned, the request completed successfully.

I've experienced some clients crash when specific actions are performed against cloud-managed MariaDB/MySQL, and I am now suspecting that that extra ACK is confusing them and causing the crash, though I don't have a way to be sure as most are closed-source.