Main menu:

Sponsoring

Please consider a small donation:

 

 

Or you can donate bitcoin:

 

Thanks to TekLinks in Birmingham, AL, for providing colocation and bandwidth.

Page Rank

Fame

FOSS Force Best Blog--2013 Award

Recent posts

Recent comments

About this blog

I am Eric Hameleers, and this is where I think out loud.
More about me.

Search

My Favourites

Slackware

Calendar

October 2014
M T W T F S S
« Sep    
 12345
6789101112
13141516171819
20212223242526
2728293031  

RSS Alien's Slackware packages

RSS Alien's unofficial KDE Slackware packages

RSS Alien's multilib packages

Meta

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”?

Eric

Comments

Comment from alienbob
Posted: June 16, 2012 at 14:28

Edit:

Indeed the blog is not going “down” anymore. Right after publishing this post, I could already access it again and write this comment.

Eric

Comment from alienbob
Posted: June 16, 2012 at 14:31

Obviously, adding the missing table fixed other issues as well. Suddenly several old pingback posts are appearing on the blog.

Eric

Comment from Didier Spaier
Posted: June 16, 2012 at 15:35

Congrats Eric.

I consider using pdoSQLite as backend to write my own _more_simple_than_any_other_ blog engine, so these news encourage me.

As I expect far less readers than you have, there shouldn’t be any performance issues ;)

Didier

Comment from Willy Sudiarto Raharjo
Posted: June 22, 2012 at 01:10

i noticed that if i posted a comment on this blog, it would not appear automatically. I have to refresh the page to see my own comment.

Does anyone experience this as well?

Comment from Armin
Posted: June 22, 2012 at 09:46

Funny to see this post, recently I’ve been looking for nice and light alternatives to wordpress which use sqlite (especially since sqlite support is usually in there with php). I’ve found lightblog which seems to be unmaintained and the PDO SQLite for WordPress which seems to be broken for new versions.

So, like every other coder I decided to write my own alternative. What would be interesting to me is to know what features of WP do you use and how? Once I have some code smacked up together I’ll make sure to drop a comment here.

Comment from alienbob
Posted: June 22, 2012 at 10:27

Hi Armin

There is not a lot of WP functionality I do not use… especially the extensions and theming model allows me to have site stats, adsense, spam protection, ban lsits etc.

The PDO plugin actually works pretty well as long as you create the sqlite database for a new blog using a wordpress release prior to 3.0.

What was not entirely clear to me is, did you write an alternative PDO driver for WordPress, or did you write your own blog software (with sqlite backend)?

Cheers, Eric

Comment from Armin
Posted: June 22, 2012 at 12:02

Hi Eric,

I gave a shot at writing patches for wordpress which use PDO with SQLite but that turned out to be a huge undertaking because of mysql-specific functionality which sqlite doesn’t have. So I’d have to rework almost all of wordpress which I don’t have time for.

So the answer is I’m writing my own (very light) blog software with an sqlite backend but my requirements are far less then yours so it might not be a good fit.

Cheers,
Armin.

Comment from Jack
Posted: June 28, 2012 at 22:18

Hi Eric

I was looking somebody who updated Wp 3.4 to sqlite. Could you upload somewhere clean version on WP 3.4 with sqlite ?

Thanks
Jack

Comment from Andrew Evans
Posted: November 15, 2012 at 21:23

Thanks for writing this, and how to set up pdo for wordpress! I only had to make one additional change to get it running on 3.4.2 — in wp-includes/user.php:

function count_users($strategy = ‘time’) {
global $wpdb, $wp_roles;

return array(‘total_users’ => 1, ‘avail_roles’ => array(‘administrator’ => 1));

This effectively disables user counts, but I don’t need them for my purposes.

Cheers!

Pingback from WordPress3.6をSQLiteで使う 【pdo-for-wordpress,動作したプラグイン】 | 某氏の猫空
Posted: February 9, 2014 at 08:25

[…] driver pdo-for-wordpressの修正方法に関しては、この辺りも参考になるかも ・Alien Pastures » Blog updated to 3.4, sqlite database fixed wp_commentmeta […]

Write a comment