Setting Back Up

Getting this thing spun back up was a bit of an adventure – as you are no doubt unfortunately aware: software – and I wanted a few things out of my new setup, among those being “self-hosted WordPress backed by SQLite”, “https”, “cleanly importing the old data” and a few other modest housekeeping improvements. I’m starting with a network-facing Linux instance here, and I’m going to gloss over a few pretty hairy steps like “set up Apache2”, for which I apologize. I’ve been doing this for a long time; if you’ve found your way here in the weeks or months after this has been published and need more detailed instructions, email me.

Once you’ve got your Linux instance and Apache stood up straight, the rest of the preparation steps are straightforward:

  • Get yourself WordPress, install it and drop the db.php file from WP-SQLite-DB.
  • Set up SQLite3, PHP 7.whatever’s-current and Certbot on your instance via your usual package manager, in my case Apt, and
  • Have Certbot do its thing.

“Have Certbot do its thing” was actually remarkable: I am just a simple unfrozen caveman and your “hosted services” confuse and frighten me, and it’s been probably a decade since I’ve looked into this space so being able to just… cause a cert to occur? Without needing to put a credit card into anything or fax things to companies whose software you wouldn’t run with a gun to your head? Amazing. The Certbot+LetsEncrypt process was so fast and painless that I was actually startled, and “pleasantly startled by software” is a rare feeling I deeply mistrust. But it worked right on the first try, pretty wild.

Getting WordPress working right with SQLite was largely the opposite of that. Who doesn’t love debugging other people’s PHP for db-constraint correctness problems, or hand-editing XML? Well, everyone. That’s who doesn’t love it. You, me, everyone.

Before turning on Apache you’re going to want to take WP-SQLite-DB up on their offer to put the DB somewhere sensibly non-default, you might need to toss it and start fresh later, but fortunately if your database is SQLite that’s “mv” and not “oh god now I need to re-learn how MySQL works again”, and that on its own will make the whole effort worthwhile.

Anyway, let’s get into the two bits of unpleasantness.

The first thing you’re going to need to do is manually clean up your old WordPress backup. I don’t know when this bug was introduced, but WordPress-current will not import its own backups correctly. Specifically, when there is a line break and tabs between <content:encoded> tags and the <![CDATA[[ tags that follow it, the importer will assume that tab-tab-tab-tab-<![CDATA[[ is the part of the content you’re importing rather than an indication that it’s encoded. This manifests itself in – if you’re lucky! – all your old blogposts losing most of their formatting on import. But that’s not the best part.

No, the best part is that once you import that backup, if you open thus-afflicted posts in the WYSIWYG editor they’re all blank. Fun race condition: if you touch the post with the classic text-editor option first, it assumes it’s all text, and escapes all those brackets for you when you switch to visual. But if you touch it with the visual editor first, it assumes everything in those CDATA brackets is invisible, and helpfully scrubs it clean for you. Super helpful either way, but opening the editor for a post you know exits, if imperfectly, on your sites and discovering that the editor believes that page to be blank? You need to have a certain mental model of this class of malfeasance to figure that out, I think, and I’m not sure if I should be proud I can connect those dots easily or annoyed that this is the person I’ve spent my life becoming.

Like I said, I’ve been doing this a long time. Get you a man who can do both.

Anyway: I’m sorry I don’t have a regression range for you, but if you’re importing old WordPress backups into WordPress 6 you will definitely need to inspect that backup XML file, get your regex on and strip all the whitespace between the <content-or-whatever:encoded> tags and the <![CDATA[[ bits or you’re gonna have a bad time dot gif.

At this point you’ve got a WordPress instance that you can stand up, data that you can import, and superficially this will appear to work. The problem you’re going to hit when you’re trying to configure WordPress through its usual menus – for example, disabling comments – is that saving those options gives you a dialog saying your changes have been saved, but they won’t have changed.

WordPress won’t show any errors in the UI, but will instead inform you of the problem by dumping a web page into your Apache logs. It looks something like this:

[Tue Jun 21 17:18:07.151358 2022] [php7:notice] [pid 12535] [client IPADDR:57288] WordPress database error <div style="clear:both">&nbsp;</div><div class="queries" style="clear:both; margin_bottom:2px; border: red dotted thin;">Queries made or created this session were<br/>\r\n\t<ol>\r\n\t\t<li>Raw query:\nUPDATE `wp_options` SET `option_value` = NULL WHERE `option_name` = 'default_ping_status'</li>\r\n\t\t<li>Rewritten:\nUPDATE wp_options SET option_value = NULL WHERE option_name = 'default_ping_status'</li>\r\n\t\t<li>With Placeholders:\nUPDATE wp_options SET option_value = NULL WHERE option_name = :param_0 </li>\r\n\t\t<li>Prepare:\nUPDATE wp_options SET option_value = NULL WHERE option_name = :param_0 </li>\r\n\t\t<li>Executing:\narray (\n 0 => 'default_ping_status',\n)</li>\r\n\t</ol>\r\n</div><div style="clear:both; margin_bottom:2px; border: red dotted thin;" class="error_message" style="border-bottom:dotted blue thin;">Error occurred at line 1713 in Function execute_query. <br/> Error message was: Error while executing query! Error message was: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: wp_options.option_value </div><pre>#0 WP_SQLite_DB\\PDOEngine->get_error_message() called at [/opt/wordpress/wp-content/db.php:2736]\n#1 WP_SQLite_DB\\wpsqlitedb->query(UPDATE `wp_options` SET `option_value` = NULL WHERE `option_name` = 'default_ping_status') called at [/opt/wordpress/wp-includes/wp-db.php:2476]\n#2 wpdb->update(wp_options, Array ([option_value] => Array ([value] => ,[format] => %s,[charset] => ,[length] => )), Array ([option_name] => Array ([value] => default_ping_status,[format] => %s,[charset] => ,[length] => ))) called at [/opt/wordpress/wp-includes/option.php:496]\n#3 update_option(default_ping_status, ) called at [/opt/wordpress/wp-admin/options.php:322]\n</pre> for query UPDATE `wp_options` SET `option_value` = NULL WHERE `option_name` = 'default_ping_status' made by update_option, WP_SQLite_DB\\wpsqlitedb->query, WP_SQLite_DB\\wpsqlitedb->print_error, referer: SITE/wp-admin/options-discussion.php

… which I think we can agree is less than helpful, but with some distillation we discover:

Error message was: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: wp_options.option_value

This is the second part of the unpleasantness, and this took quite a while to figure out; that SQLite has some database constraints that WordPress doesn’t meet and isn’t interested in meeting. I’m not an expert – I think the general thrust of the problem is that where SQLite defaults to strict, MySQL defaults to sticking a crayon in its nose – but some precision chicanery in options.php seems to sort us out. In wp-admin/options.php at around the 320th line or so:

if ( isset( $_POST[ $option ] ) ) {
$value = $_POST[ $option ];
if ( ! is_array( $value ) ) {
$value = trim( $value );
$value = wp_unslash( $value );
// You probably shouldn't do this, but it makes WordPress work on SQLite.
$value = $value ?? '' ;
// If this breaks anything I am very sorry. It works on my machine. -mhoye

update_option( $option, $value );

A single line of code. The precise, surgical elegance of a scalpel taped to a brick. But it works. For me, on my machine.

Shut down Apache, rename the DB file and restart it, and there you are. A clean install of WordPress running (far as I can tell…) correctly backed by SQLite3.

Nothing to it.