Gatsby, Ghost, Ubuntu 18.04, & MySQL - 5.7, or 8.0?
So Adam and I are adding in the JAM stack to our repertoire (with this blog). And, all things considered... it's not that bad. But let's talk about picking the right installation for the job. Ghost prefers a MySQL db, and we decided to start with an easy-up config repo we found at:
GitHubSo why write this article?
Everything in the installation instructions for Ghost and MySQL on an Ubuntu 18.04 server seems in the clear, and the last known issue was 2 years ago, give or take an acorn.
Seemed like a quick install, a user setup, and Robert's your father's Brother, call it a day!
Because all is not as it seems.
(By the way, did you know the "Bob's your Uncle" expression came from a minister of Ireland receiving his job through nepotism? Literal nepotism, from Nepos, Latin for nephew, as the Minister who appointed him was his actual uncle.)
Here's the rub; what happens when the docs tell you there aren't issues?
Specifically we were setting up a MySQL database for an installation of Ghost, our headless CMS weapon of choice, on a multi-use Digital Ocean droplet for 🎉 SuperCodeBros. 🎉
The Process
1. We first checked to see if MySQL 8.0 was compatible. The ghost docs are pretty straightforward and easy to follow, and yep! Ubuntu 18.04 is certainly on the list of available setup options. Seems groovy.
Ghost
2. We then installed and tried to set up MySQL 8.0 with ghost - This also went pretty straightforward, until...
3. As we are running Ubuntu 18.04 on our droplet, setting our new user and password was the next step. We chose a secondary user name for access to our post db (not ghost, as this will cause a conflict with ghost itself), and set about this next step...
ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL clientA bit cryptic as errors go, but a dive's a dive, and we found a few minor issues along the way.
As of MySQL 5.7.5 the password() function we normally used was taken out, but this wasn't the issue.
As of MySQL 8.0 the default password management plugin is caching_sha2_password rather than mysql_native_password (as of 5.7 and earlier), so the following statement didn't seem like it would work for us.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';...It didn't.
4. Since we were on a call at this point and debugging the install together, I should have been writing down all the errors and the solutions we found the way I normally do. It's also important to note that several back-and-forth options were tried, and some small mistakes were likely made along the way, but at this point, we tried:
- Remaking a user. Same error.
- Changing the alter user statement to use the newer plugin. (same error!)
- Configuring our MySQL 8.0 installation to USE the old plugin, changing to mysql_native_password, then changing the password. No dice, but you can find more information about MySQL password validation plugins in their docs here.
- Reinstalling MySQL 8.0 with the legacy encryption option. (same error!) (GUI version pictured below)
None of them worked, same error.
So we uninstalled, and rolled back to MySQL 5.7, bing bang boom, donezo.
This means we'll eventually have to upgrade from MySQL 5.7... by then let's hope the issue we ran into with the newer authentication plugin has been fixed and works with MySQL 8.0 and Ghost by then.
The Take-home...
I'm not saying skip the encryption and validation plugin weirdness and just use MySQL 5.7, even though you still need a connector just to USE the newer plugin if you want to use an interface like Workbench, far from it.
We went with 5.7 at the end mostly because my collaborator-bröder is exceptionally comfortable with it (he works with it professionally), and to make sure that we hadn't accidentally changed anything else during our tweaking. After a few try-and-fail solutions, it became a game of diminished loss since we were just setting up our db and there was no issue with data loss or downtime.
What I am saying, is you can read those docs, and still find pitfalls, but climb out anyway. After all, this blog uses our accepted configuration right now, just fine.
and now, the good news:
You are not always as far from a solution as it seems.
🐱🏍Onward and Upward, Evan Out.🐱🏍
Other references:
Stack Overflow