As explained thoroughly in an earlier post, the P19 migration meant we needed to add a new element to our infrastructure… namely the ProxySQL.
Life was not easy for it at first, but it soon learned how to handle the diverse range of configurations your websites have, particularly when it comes to making sure the path connecting to your data is always up.
Databases connection 101
Let’s go back to how a user connects to its MySQL/MariaDB database without a ProxySQL…
The user uses the MySQL client, and provides a host/IP, a username and a password.
From this point of view, everything seems pretty simple. But in real life, things aren’t so easy…
First of all, MySQL has evolved through the years to fix various security issues, such as removing their old authentication algorithm. Our infrastructure tries to keep up with all these changes, but there is always latency when migrating from the old versions to the newest version, especially as some of these changes can impact your website.
We are in luck though, as MySQL clients and servers have a good level of backward compatibility.
So what do we have then?
Now we are closer to reality.
Now let’s add our little friend, ProxySQL, to this picture!
First hours of the life of a ProxySQL at OVHcloud
The ProxySQL is a very nice and useful piece of software. Its main job is to establish a connection to your database on your behalf. So a connection arrives, the ProxySQL looks up the database in its configuration, checks the credentials the user provided and, if they are indeed legit, act as a MySQL client to connect to the actual MySQL server.
From this point of view, everything seems pretty simple. But in real life, things aren’t so easy… Wait, did I already say that?
Let’s add our full diversity to this picture, just for fun! Remember our MySQL versions? Two specific versions were mentioned…
MySQL5.6
This is the latest version at OVHcloud. What is interesting with this specific version is that the authentication mechanism is the latest and greatest from MySQL. It generates a 41-byte hash, a change that was introduced in MySQL 4.1.
The actual version we had in P19 was MySQL5.5, but those two versions are pretty similar from a practical point of view.
MySQL5.1
Pretty old stuff, but we still have some instances using it.
Some customers using this version were migrated from MySQL4.0 and kept the passwords generated by that version. To keep this simple, we will refer to those as ‘MySQL5.1 with old passwords‘. This version uses a 16-byte hash, which is weak and prone to interceptions.
So what do we have then?
There we are! The journey of our ProxySQL does not go so smoothly, as it cannot authenticate against MySQL 4.0. This makes sense, as it was written long after support for MySQL 4.0 had been dropped.
Nonetheless, we needed to find a solution that took into account the legacy authentication and would prevent outages for the websites using those connections.
Under the hood: a MySQL connection story
As we highlighted earlier, to connect to a MySQL server, three parameters need to be provided:
- A host (say,
mysqlXXX.sqlXXX) - A user
- A password
The interesting part here is the password, as this is what changed between the two versions.
Obviously, MySQL does not store your password in plain text, but a hash instead. To enable ProxySQL to connect, the hash is inserted in its internal database, after which the connection is possible.
Three type of passwords exist:
- Plain text
mysql_native_password(the 41-byte hash)old_password(the 16-byte hash)
ProxySQL only supports the first two… and therefore cannot connect to your awesome but obsolete database.
What are our resolution paths?
The simplest solution would be, as you can imagine, to update the passwords to use the new algorithm.
However, we do not store your passwords!
In some cases, we were able to compute this, but given the significant number of customers facing this issue (more than 50,000… remember the 1% of more than one million?), this was not doable.
The first step was to teach to our ProxySQL how to handle the connection from the client. Several patches later, our ProxySQL was proud of its new feature, and able to perform that task.
Let’s put it into a picture:
But we definitely didn’t want to keep those old passwords forever… so we had to be nasty.
Remember, the only information we had was the legacy hashed password, which was the only source of information we can give to the ProxySQL. To simplify things, here is the situation we are in:
We needed to give our ProxySQL client a password, but could not add the old password (and didn’t want to!).
In the end, the solution was to take the old password hash and make a new password out of it. This way, we would have a new password, in the correct format!
As the ProxySQL client and MySQL server are hidden from the user, this was all fine, but we couldn’t do that in the ProxySQL server (you want to keep the same password, right?).
Lucky for us, we had the plain-text password field, which remained unused, so we could use it to store your OLD_HASH and complete the authentication chain.
We did it! You can finally access your database!
Let’s be clear here though… This is all very temporary, and as stated in the previous article, we do not want to maintain a forked version of ProxySQL where we can’t update the upstream code, as all this is very legacy-based and specific.
Conclusion
Maintaining a diverse range of software with incompatible versions is tough!
To make sure the migration had as little impact as possible we had to fix many small details. In the specific case detailed in this article, we had issues with old MySQL passwords and, given the timeline, could not ask MySQL5.1 users to change their passwords. But with a bit of hacking, we were able to start the migration… even for legacy authentication!
Again, this was only one of the issues we faced while setting up the ProxySQL and adapting it to our very specific requirements. Remember, tackling legacy software is a difficult and demanding task, but there is no avoiding it! This is what we did with the P19 migration, and what we will keep doing to always improve the service we offer you.
