Switched WordPress database to SQLite

It’s a long trek for sure *sigh*

The beginning of this 3-day journey is a simple installation of SQLite Integration. After following the instructions to switch the back-end database from MySQL to SQLite, I found this plugin worked out very well. Then I decided to convert or import the data from MySQL to SQLite database, where the nightmare begins.

After a series simple googling, I found this question on Stack Overflow, and the following tools for conversion. I’m running Windows 10 on my laptop.

Prerequisite

To acquire and preserve the natural schema of SQLite database, I set up a blank site and activated all my installed plug-ins. Then I copied the back-end SQLite database to my PC and, after clearing all the rows, used it as the vessel to put my old data in.

Convert MySQL to SQLite

mysql2sqlite.sh

On GitHub: esperlu’s original script dumblob’s script

Basically, these scripts just need you to dump the whole database using mysqldump first, then use the script to convert the dump file into SQLite-compatible dump. I, however, just had no luck. There are Chinese and other non-ASCII characters in the dump file, and the converted dump file didn’t work well with sqlite3 client.

memory
Error: near line 162263: near "A▒": syntax error
Error: near line 163004: near "m": syntax error
Error: near line 165315: near "w": syntax error
Error: near line 165676: unrecognized token: ""

Then the client just got stuck. when I hit Ctrl+C, a lot of error messages exploded on the screen.

SQLite Data Wizard

I found SQLite Data Wizard 11.1 in a pretty old table of SQLite-related converters. Its UI looks awesome and it supports to import data to existing database. It can import various forms of data into SQLite database, including ODBC. After setting up ODBC data source on my PC for remote MySQL database, I just followed the instructions and tried to import the data. At first it worked fine, but it comes to error when performing some insertions. E.g.

Error while importing to table wpp_options:
SQL Error: wpp_options.option_value may not be NULL
Statement: INSERT INTO wpp_options ("option_id", "option_name", "option_value", "autoload") VALUES (504, 'te...
Source record number: 209

It seems that the importer cannot distinguish between NULL and empty (“”) fields in MySQL (via ODBC).

SQLite2009 Pro Enterprise Manager

It claims to be “the best GUI to manage your SQLite 3 databases”, and for sure it has the most powerful UI compared to other similar products, e.g. SQLite Manager, considering it’s a database manager dedicated to SQLite. It supports to import data from MS Access 2003 / MS SQL Server / MySQL / PostgreSQL to SQLite 3 database, however, instead of populating data into an existing database, it need to create a new database, which, as I’ve checked checked afterwards, will NOT keep the field constraints and table relations. Nonetheless, I succeeded in converting the database from MySQL to SQLite 3. Better than nothing.

I’ve got to say, here I committed a mistake by forgot to chown the uploaded SQLite database from root to www-data, and maybe the finale will be quite different if I’ve noticed my negligence. Anyway, after I had uploaded the converted database, I found the homepage had changed as expectation, but when I entered my account infromation and tried to login, I was kept redirected to the same login page. This is not good, and I decided that instead of creating a brand-new one, I need to populate the data to the existing SQLite database. But how? I need a database dump.

SQLite 2009 Pro does support database dump. You just need to hit F10 and choose a destination. The problem is, after I “successfully” (at least the message box told me so) imported the dump, nothing seems to be added to the existing database. I checked the dump file. At least I should see something were the SQL instructions really executed.

DROP TABLE IF EXISTS [wpp_commentmeta];
CREATE TABLE `wpp_commentmeta`
-- This table created by SQLite2009 Pro Enterprise Manager 
-- Osen Kusnadi - http://www.osenxpsuite.net 
-- Created date: 2017-01-27 14:02:39
( 
       `meta_id` TEXT,
       `comment_id` TEXT,
       `meta_key` TEXT,
       `meta_value` TEXT
);
-- …
INSERT INTO wpp_commentmeta  (meta_id,comment_id,meta_key,meta_value) VALUES('24','9', …
-- …

I cannot be defeated here. I need to import the dump. By any means.

I removed all the DROP and CREATE instrctions in the dump file, then tried to import the dump into the existing database with the most classical SQLiteManager. Still no luck. I received something like

Error on line: 398
11 values for 15 columns

This is the source

INSERT INTO wpp_comments  (comment_ID,comment_post_ID,comment_author,comment_author_email,comment_author_url,comment_author_IP,comment_date,comment_date_gmt,comment_content,comment_karma,comment_approved,comment_agent,comment_type,comment_parent,user_id) VALUES('1','1','Mr WordPress',NULL,'https://wordpress.org/',NULL,'2015-06-30','2015-06-30','Hi, this is a comment.
To delete a comment, just log in and view the post's comments. There you will have the option to edit or delete them.',0,'1',NULL,NULL,'0','0');

Damn. Line feed.

After a series of following attempts, I finally gave up. Until…

Navicat Premium

It should be noted that when importing from MySQL, the date time format options should be checked, if such options exist (I hadn’t check it by myself, unluckily), or you may witness something weird with post and comment dates. See the last section for detail.

Worked like a charm! Okay, this time I had no problem importing the database, still via ODBC, and after I uploaded the converted SQLite database file, I still faced the same infinite login redirects when I attempted to login. (reauth=1, I’ve got to say.) After some trail-and-errors, it finally struck me that perhaps I need to check the database file permission.

Ah ha! I forgot to change the owner of the database, and WordPress just couldn’t write it. That’s the answer.

Inherent limitations

Now that (after 3 days) the database has been set up. Time to check the dashboard!

According to the author, some plugins will not work with SQLite database. In addition to the plugins listed there, I found out that Wordfence cannot work very well, just as stated in this post, maybe it’s due to its INSERT OR UPDATE instruction is not supported by SQLite and there’s something wrong with the rewritten SQL instrcution.

And don’t forget to set permalink options in the dashboard AGAIN so that the virtual directory settings in .htaccess will be purged. This will solve some 404 errors no sooner after you’ve switched to SQLite.

Further problems

There’re still some other glitches, as you might see, such as the wrong archive dates on the bottom of the left side area, and wrong comment dates. Anyway, it’s still better than nothing. Maybe I’ll figure out a fix later.

Here I am. I finally figured this out. First of all, there’s no such type as DATETIME in SQLite, so all the date in the WordPress is virtually stored as TEXT SQLite database. Thus, it’s crucial to check whether the correct date and time format has been used. After a simple look up in the tables, alright, the format is surely not right.

Table: *_posts

ID	post_author	post_date	post_date_gmt	post_title	…
796	1	2016/12/10 下午 11:02:14	2016/12/10 下午 3:02:14	AsyncEnumerable 随笔
801	1	2016/12/20 下午 1:44:54		测试
803	1	2016/12/28 下午 3:51:09	2016/12/28 上午 7:51:09	Setting up a TypeScript + Visual Studio Code development environment
807	1	2017/1/25 上午 2:35:42		Auto Draft
808	1	2017-01-30 21:12:51	2017-01-30 13:12:51	Switched WordPress database to SQLite
809	1	2017-01-27 22:20:35	2017-01-27 14:20:35	Switched WordPress database to SQLite

See the point? Post 808 & 809 are created after I’ve migrate to SQLite, and the date time format is different from the older posts. So the rest work is to convert the imported dates into yyyy-MM-dd hh:mm:ss form. Fortunately, Navicat supports that; you can edit the cells just like in Excel, though the modification commit process is a little bit long.