Import of Wordpress database only works in replica mode

[joshourisman]

I'm attempting to import a Wordpress database from Google Cloud SQL to PlanetScale. Overall the import process seems to work great, and if I point a Wordpress container at the PlanetScale database while it's in replica mode everything behaves exactly as you'd expect, and I can view the site as normal, including the admin. However when I then switch the PlanetScale database to primary mode, the Wordpress site gives me the installation screen instead of the existing blog. If I switch it back to replica mode, it goes back to working as expected.

If I'm understanding the import process correctly, the main difference between replica and primary mode is that writes will be routed to the external database and synced to PlanetScale in replica mode, and the opposite in primary mode. I'm at a bit of a loss as to how that would cause this issue, but I am also not a Wordpress expert, and inherited support of this site…

[orware]

Hello @joshourisman,

That does sound odd! Based on what you shared, if you are already pointing to the PlanetScale database and using our hostname and credentials while in Replica Mode and everything is working normally within WordPress, then the switch to Primary Mode should be pretty painless, since you're essentially already using PlanetScale for all of your reads while in Replica Mode as you shared.

This would probably be one that we would want to dig into a little further with you so when you have an opportunity, please share the organization and database name, along with some screenshots of what you've described above, and share them with us over at https://support.planetscale.com/ that way we can work with you more closely to understand what might be happening.

[fooforge]

@joshourisman No need, I have found your database.

I'm fairly certain this is because you have promoted this branch to production effectively disabling any direct DDL operations on this branch. Wordpress, however, needs to be able to perform those, e.g. on upgrades and plugin installations. I suspect, Wordpress runs some checks to see if it has everything it needs, and otherwise renders the setup routine.

I have demoted the branch back to development. I would appreciate if you could give this another try to see if that was it.

More broadly, hosting a Wordpress database on PlanetScale means that you will not be able to use production branches, but only development branches. Those, however, come with fewer resources, they're not getting backed up automatically every 24 hours, and are also not write-protected from schema changes.

I'm no Wordpress expert, but I imagine, for Wordpress to function properly with a database hosted on PlanetScale, you would need to write a plugin or otherwise customize your installation so that schema updates get decoupled from the rest and that Wordpress would give up on its requirement to execute direct DDL operations. None of that is straight-forward nor feasible, I'm afraid.

The gist is that I can only recommend to host your Wordpress blog on PlanetScale for development or testing purposes at this time, but not for a production blog unless it's behind a CDN or some other cache, and even then you may encounter issues as traffic ramps up on your blog.

[joshourisman]

Well, it's disappointing to not be able to use PlanetScale for this, would solve so many of the annoyances of working with Wordpress (which I also don't do much of, I'm primarily a Python dev…), but it's good to know that I wasn't just screwing something up!

I'll give it a try running in dev mode, just to see if it works, but I guess I'll be sticking with Google Cloud SQL for the production database for now. Being able to import the database into PlanetScale for testing in staging could still be pretty valuable, though, so at least I know it can work for that scenario!

[joshourisman]

So I've created a fresh Wordpress install at https://wordpress-test.fly.dev and a fresh PlanetScale database. I ran the Wordpress install process against the database in development mode, and then promoted the main branch to production mode, to try and match as closely as possible the process I was attempting before with migrating an existing site. This time, however, it seems to be working fine. Even after promoting the main branch to production, you can still visit the blog, and everything appears to be working as normal.

So now I'm wondering if maybe there's something about the specific Wordpress install I was trying to get running against PlanetScale that's causing the problem. Probably one of the plugins, or possibly the theme. it's a very old Wordpress site, that's been hacked on by a number of people over the years, so it's not unreasonable to think that might be the case.

I guess the next step is to install, one-by-one the plugins that are installed on the other site, to see if any of them trigger the same problem. Hopefully it's a plugin that's not actually needed, and I can just uninstall it, and then migrate the site's database to PlanetScale…

[fooforge]

I ran the Wordpress install process against the database in development mode, and then promoted the main branch to production mode, to try and match as closely as possible the process I was attempting before with migrating an existing site. This time, however, it seems to be working fine. Even after promoting the main branch to production, you can still visit the blog, and everything appears to be working as normal.

Yes, that will work for a brief moment. And no, it's not related to your other Wordpress installation.

The problem starts when there's a Wordpress upgrade or when you install a plugin requiring changes to the schema. That's when it will all start to fail again as production branches do not allow direct DDL operations such as CREATE, DROP, ALTER or TRUNCATE. The worst-case scenario here is for your Wordpress installation to not be able to upgrade itself automatically on a critical security release, increasing the chance for another hack similar to what you have described earlier.

I'm going to demote that branch again for you now to prevent that from happening.

For frameworks hiding away the underlying database layer and not giving the option to perform decoupled upgrades, PlanetScale is just not a good match yet. I think, by the time we will get there, but it's not in our immediate focus, so at this time I cannot say when that will happen.

[joshourisman]

Oh I see, I thought before you were saying that the checks Wordpress runs for those capabilities are run as soon as the code is loaded, so it was impossible to run a Wordpress install at all on a production branch. This is actually exactly what I wanted; this particular Wordpress site is using a very old, very fragile theme, that has a tendency to break when things are updated. I've been running it from a Docker container for years, and not allowing things to auto-update so they could be tested first anyway. So if I can find a way to get it to run against a PlanetScale production branch in its current state, that's actually ideal, as I'll then be able to use a development branch to test updates and roll them out in a controlled manner.