Connectwise Automate MySQL 8 support is scheduled to arrive in October 2021. This will also introduce formal support for Windows Server 2019 to Automate. Here at Automation Theory, we wanted to clarify what that means for the day-to-day operations for Automate, and how to take action.
MySQL 8: What’s new?
MySQL has a long and interesting history — you can check out the Wikipedia page and see all the details (including why versions jumped from 5.7 straight to 8). However, in a recent call with Oracle, an engineer explained it like this:
“Old versions of MySQL were like the MacGyver scene where they were working on the car while driving down the mountain — teams of developers were trying to fix bugs and make features work while keeping things running. MySQL 8 is a rebuild from the ground up.”
Every version of MySQL contains security and performance enhancements, and MySQL 8 is no different. However, for the average Automate stack, improvements would include:
- Fewer locking issues due to multiple enhancements in threads and locks
- Better use of the buffer pool and how the memory-to-disk interplay works
- More effective use of other buffers for faster lookups
- Optimizations to I/O with more segmentation
- Overall faster and more stable Automate user experiences
With that, once official support arrives we universally advise partners to upgrade to the latest version of MySQL 8. However, there may be a few caveats.
Important Considerations for Connectwise Automate MySQL 8 Support
Each major version of MySQL deprecates certain legacy items, and MySQL 8 is no different. The core application compatibility is not the primary question; although there may be certain bugs that are undiscovered in the QA process. The larger issue will likely be the integration ecosystem — not to mention partner-specific customizations. Certain changes (like the deprecation of the information_schema in favor of the performance_schema) might result in older integrations being unable to understand the database, and the removal of legacy encryption functions would result in a breaking change to any vendor using them.
It’s also worth noting that certain configuration variables (common to almost every Automate deployment) have been deprecated, which will result in the MySQL service crashing upon start if the configuration is not adjusted accordingly. MySQL 8 also has different tuning needs, so partners should be prepared to adjust accordingly if there are performance issues post-upgrade (this isn’t too likely, but every partner environment is different).
How do MSPs upgrade to MySQL 8?
The upgrade process for MySQL 8 improved from previous versions, and the harder task of the metadata upgrades is automatic. However, that doesn’t make the process any less risky. Most partners have never upgraded a MySQL database before, so our common suggestions are below.
First, if you haven’t upgraded MySQL before, we don’t advise using your production Automate server as a test environment. As IT people we often take the bull by the horns and perform lots of tasks that are potentially risky. It’s certainly possible to go that route with upgrading Automate’s database, but we’ve had several partners contact us after an upgrade has failed and the database won’t start (or the application isn’t working). These situations are never pleasant to clean up. Databases are also a bit more involved to backup (since disk-only snapshots don’t capture the data in RAM), so until you’ve verified that you can restore from your backups and get a working MySQL server we’d advise against a DIY crash course in MySQL upgrades.
Secondly, for any partner wanting to take on the upgrade themselves, we suggest doing plenty of research — to the point where you understand the big picture of what’s happening. You’ll find many different guides for MySQL upgrades, but you’ll want to be prepared in the event you encounter issues not documented in the guide. It is “normal” to see databases that won’t start due to invalid configurations, permission issues, and software dependency issues when upgrading an average Automate stack. We advise anyone wanting to go this route to be prepared for such events.
Finally, we advise against following the “dump and reload” upgrade method. That is normally only done in the case of data corruption. The process can take several hours depending on database size, and it is the most convoluted method possible for upgrading MySQL. It requires moving the data twice — and this is beneficial for anyone charging hourly to perform the upgrade (as many other consulting firms do).
Is there an easier way to upgrade MySQL for Automate?
Yes! Here at Automation Theory, we offer MySQL patching as a flat-rate service. We perform an in-place upgrade, and the process takes about an hour total (including updating the MySQL Connector software as well). We’re able to work with our clients to schedule maintenance during non-business hours and to make sure that any special requirements are satisfied. We’re also certified MySQL DBA’s; we’ll make sure that the upgrade goes smoothly and that the application comes back online correctly.
Oracle releases security patches for MySQL quarterly, and ongoing patching is important for keeping Automate stable and secure. As previously discussed, MySQL 8 will also require configuration adjustments to run smoothly — and to address the ongoing needs we’ve taken our various a la carte services and packed them as a maintenance plan. This ensures that MSPs receive everything they need to keep Automate running smoothly, and that maintenance of the tool stack doesn’t detract from IT service delivery.
We hope this has been helpful to you. Please don’t hesitate to contact us if you have any upgrade needs (or want to use the opportunity to migrate your Automate stack).