Blog updated to 3.4, sqlite database fixed
I just upgraded my blog to WordPress 3.4.
My blog’s database backend is not MySQL but instead (for portability reasons) I am using PDO for WordPress, a plugin which uses PDO (a PHP data access abstraction layer) to allow the use a sqlite database; i.e. a simple file.
Unfortunately this sqlite backend is not 100% compatible with WordPress installations starting with version 3.3. You can not even create a new blog using the PDO plugin with WordPress 3.3 and higher. The blog has to be created using wordpress-2.2.x, and then upgraded to 3.3 or later.
It looks like the maintainer of the PDO plugin will not be able to give adequate support for the sqlite backend. Not because he does not want to, but because it is difficult to create the proper glue between WordPress SQL code and generic SQL backends (WordPress developers have stated that they are not interested in supporting anything else than MySQL).
The problems with the PDO plugin show when creating a new post… the blog will be “dead” for at least five minutes and the apache log full of SQL errors. I am considering to move “back” to a supported backend and wanted to check if I could export the full blog so that I could import it to a fresh installation with a MySQL backend.
I quickly discovered that this was not possible. Somewhere along the line, WordPress has added a new table to the database (wp_commentmeta) and the PDO plugin apparently has not been able to add that to my sqlite database. An attempt to export the blog to an XML file (one of the standard tools in WordPress) would lead to a lot of errors in the apache log about “Problem preparing the PDO SQL Statement. Error was no such table: wp_commentmeta“.
I compared my blog’s sqlite database to a fresh MySQL-backed blog database and thus found the new table’s properties. I upgraded the database on the fly using the sqlite3 command-line program as follows:
$ sqlite MyBlog.sqlite > CREATE TABLE wp_commentmeta ( meta_id integer NOT NULL PRIMARY KEY AUTOINCREMENT, comment_id integer NOT NULL DEFAULT '0', meta_key text DEFAULT NULL, meta_value text); > .quit
After that modification, exporting the blog to XML took only seconds to create a 4.3 MB file instead of the previous 400 KB partial dump which took painstakingly long. I still have not tried to import that XML dump into an empty blog. Also, this new post will be a test to see how the blog recovers… will it still take many minutes before it is available again after I press “Publish”?