A script to fix Geo Mashup update

On one of my web logs, I use a map to display the route of my travels. Each post is geo-tagged, so places can be connected with lines. It’s a very nice feature for a travel blog, thanks to the Geo Mashup plugin for WordPress. I recently updated the Geo Mashup plugin to a new version. For this version, the locations had to be converted to the new format. The result of this upgrade was that my route was no longer correct: posts seemed randomly connected to each other.

After some investigation, I found that Geo Mashup uses a special table to link posts and location to each other. This link also has a date and time connected to it. The results are ordered using this date. During the conversion, each link had the date of the conversion. Therefore, the results were displayed random.

I wrote a quick PHP script to fix this problem. This script looks at the time the original post was made, and updates the Geo Mashup table.


// CONFIG

// username for the database
$USERNAME = 'username';

// password for the database
$PASSWORD = 'password';

// name of the database containing WordPress
$DATABASE = 'wordpress';

// host for the database (99% chance it will be localhost)
$HOST = 'localhost';

// END OF CONFIG

$link = mysql_connect($HOST,$USERNAME,$PASSWORD) or die('Cannot connect to database '.$HOST);
$x = mysql_select_db($DATABASE,$link) or die('Cannot select database '.$DATABASE);

$result = mysql_query("SELECT * FROM wp_geo_mashup_location_relationships");
while($row = mysql_fetch_assoc($result)
{
$result2 = mysql_query("SELECT post_date FROM wp_posts WHERE id=".$row['object_id']);
mysql_query("UPDATE wp_geo_mashup_location_relationships SET geo_date=\'" . $row2['post_date'] . "\'");
}

After running this script, the problem was fixed, and my map displayed the correct route.

WP-super-cache on lighttpd

Because my web server does not have a lot of processing power, dynamic web pages can sometimes be a bit slow. Especially if the server is busy. Serving static HTML pages is a lot better than serving PHP pages. In fact, most of the blogs on this site are just static “copies”. Using a web site downloader, such a HTTrack, I’ve made an exact copy of the web sites. This copy is served, instead of the dynamic content.

This server blog is a live blog, so I cannot make a static copy. But with the WordPress plugin WP-super-cache you can make caches of all pages. If a specific page is available in the cache, the cached paged in served. This saves a lot of executing time; no need to go to the database and get all the content. The cache has a time-out, so at regular intervals the caches are refreshed. And if a new post or comment is made, the cache is refreshed too. So users of the site won’t see any difference.

WP-super-cache supports two modes: the mod_rewrite and PHP. PHP will always work, but pages are served via PHP. The PHP script checks if a cache file exists, and serves that. This is still a lot faster than when PHP has to contact the database to build the page. But is still uses PHP. The other mode is mod_rewrite, but this is very specific to an Apache webserver. Because this site is running lighttpd, I had to search for the correct instructions. There are sites that describe a solution. However, after some debugging, I found out the solution doesn’t actually work (although it seems to work because the PHP mode is used). Also, when running WordPress as a multiblog site, images are also served using PHP. The solution below eliminates any executing of PHP when there is a static page available.

A special module, mod_magnet, in needed for lighttpd. First, install mod_magnet for lighttpd:
sudo aptitude install lighttpd-mod-magnet
Next, enable mod_magnet by runnuing:
sudo lighty-enable-mod magnet

Next, add a magnet rule to a site. In a previous post I explained how to add a site to the lighttpd configuration. To enable magnet for a site, a line needs to be added. The complete configuration then looks like this:

$HTTP["host"] =~ "server.vijge.net" {
server.document-root = "/home/www/wordpress"
var.wp_blog = 1
magnet.attract-physical-path-to = ( server.document-root + "/rewrite.lua" )
include "wpmu-rewrite.conf"
}

As you can see, a file called rewrite.lua is used. Create a file with this name in the WordPress installation directory. Paste the following code into the file:
[code lang=”lua”]
function serve_html(cached_page)
if (lighty.stat(cached_page)) then
lighty.env[“physical.path”] = cached_page
–print(“Serving cached page: ” .. cached_page)
return true
else
return false
end
end

function serve_gzip(cached_page)
if (lighty.stat(cached_page .. “.gz”)) then
lighty.header[“Content-Encoding”] = “gzip”
lighty.header[“Content-Type”] = “”
lighty.env[“physical.path”] = cached_page .. “.gz”
–print(“Serving gzipped page: ” .. cached_page .. “.gz”)
return true
else
return false
end
end

if (lighty.env[“uri.scheme”] == “http”) then
ext = “.html”
else
ext = “-https.html”
end

cached_page = lighty.env[“physical.doc-root”] .. “/wp-content/cache/supercache/” .. lighty.request[“Host”] .. lighty.env[“request.orig-uri”] .. “/index” .. ext
cached_page = string.gsub(cached_page, “//”, “/”)

attr = lighty.stat(cached_page)

if (attr) then
query_condition = not (lighty.env[“uri.query”] and string.find(lighty.env[“uri.query”], “.*s=.*”))
user_cookie = lighty.request[“Cookie”] or “no_cookie_here”
cookie_condition = not (string.find(user_cookie, “.*comment_author.*”) or (string.find(user_cookie, “.*wordpress.*”) and not string.find(user_cookie,”wordpress_test_cookie”)) or string.find(user_cookie, “.*wp-postpass_.*”))
if (query_condition and cookie_condition) then
accept_encoding = lighty.request[“Accept-Encoding”] or “no_acceptance”
if (string.find(accept_encoding, “gzip”)) then
if not serve_gzip(cached_page) then serve_html(cached_page) end
else
serve_html(cached_page)
end
end
end

Finally, add the following line to the wp-config.php file in the WordPress directory:
define('WP_CACHE', true);
Now you can go to the WordPress admin pages and configure everything. The mode setting in the admin pages does not have any effect. The rewrite.lua is always used. I recommend setting the mode to PHP, because setting it to mod_write will give an error that mod_rewrite is not enabled.

Running WordPress with lighttpd

In most of the tutorials about WordPress, it presumes you have Apache as a web server. I use lighttpd, however. Especially on a server with not a lot of power, this can make a big difference. Getting WordPress to run on lighttpd is not very difficult. You can follow the normal installation instructions. In fact, you only need to do something special if you want to use permalinks without index.php in it, and/or want to have the multisite feature in WordPress 3 (or the older WordPress MU). The instructions below are for WordPress 3 Multisite with multi blogs.

First, configure your site in the lighttpd config file. I use the (almost) default configuration file, /etc/lighttpd/lighttpd.conf At the end of this file, I placed
include "sites.conf"
This allows to have all information about the different sites in a separate config file. To add a WordPress site, simply add
$HTTP["host"] =~ "server.vijge.net" {
server.document-root = "/home/www/wordpress"
var.wp_blog = 1
include "wpmu-rewrite.conf"
}

The host is the address of the site. The document-root is the location where WordPress is installed. var.wp_blog is the ID of the blog. The ID can be found in the Network admin section of WordPress. Click on the top-right on the Admin drop down menu and choose Network Admin. Choose All sites from the left menu. Here, all blog sites are listed. The link for each site contains the ID of the blog.
wpmu-rewrite.conf is a file that does the rewriting for permalinks and files in a multisite setup. The contents of this file is:
url.rewrite-once = (
"^/(.*)?/?files/(.*)" => "/wp-content/blogs.dir/"+var.wp_blog+"/files/$2",
"^/(wp-(content|admin|includes).*)$" => "/$1",
"^/favicon.ico$" => "$0",
"^/xmlrpc.php$" => "$0",
"^/(.*)$" => "/index.php/$1",
)


Save the files sites.conf and wpmu-rewrite.conf in the /etc/lighttpd directory and restart the web server, and it should work. The trick with setting the ID for each blog allows loading images without the need of PHP. You can also use the following code, in which case you do not need var.wp_blog:
"^/(.*)?/?files/(.*)" => "wp-includes/ms-files.php?file=$2", With this code, for every image, a PHP script is executed.

Updating WordPress and plugins with SSH

WordPress allows upgrade of itself and of plugins via a nice web interface. By default, it offers the option between FTP and FTPS. That’s very nice except that my server does not use FTP, only SSH. Fortunately, WordPress can support SSH if the correct package is installed. On a Debian server, simply run:
sudo aptitude install libssh2-php
Then restart the web server. If you now go to the upgrade page, there is an option to use SSH. You can either use a password-based login, or a public/private key-based login.

Upgrading WordPress MU to WordPress 3.0

The various site on this server are powered by WordPress. Because there are multiple sites, I use WordPress MU (multi user). This fork of WordPress allows running multiple blogs from one installation, either in sub directories, or (in my case) on sub domains.

A couple of days ago WordPress 3 was released. WordPress MU is now integrated into WordPress. This is good news, because now there is only one WordPress, and I can easily upgrade to new versions, without having to wait for an updated WordPress MU version. But the first task, upgrade WordPress MU to WordPress 3.

There is a very handy auto update feature. You simply have to click a link and the update takes place, or so is the theory. That sort-of worked, but a few changes were needed. For the upgrade to succeed, the web server must have permission to write to the the directory where WordPress is installed. By default, I disabled this, and only allow writes to certain folder. Simple (temporarily) giving every one write access solved that problem:

chmod -R o+w wordpress/

After that, the upgrade went fine. A few things were needed to make everything work. First, I do not run Apache as a web server, but lighttpd. This makes permalinks and uploaded files a bit difficult. I have the following lighttpd config to make all this work:

url.rewrite-once = (
"^/(.*)?/?files/$" => "index.php",
"^/(.*)?/?files/(.*)" => "wp-includes/ms-files.php?file=$2",
"^/(wp-.*)$" => "$1",
"^/([_0-9a-zA-Z-]+/)?(wp-.*)" => "$2",
"^/([_0-9a-zA-Z-]+/)?(.*\.php)$" => "$2",
"(\?.*)$" => "index.php$1",
"." => "index.php"
)

And a few settings had to be added to the wp-config.php file. The following must be added:

define('MULTISITE', true);
define('SUBDOMAIN_INSTALL', true);

The first setting tells WordPress that I run a “multisite”, what used to be the “MU”. The second option tells WordPress that each blog has its own subdomain, as opposed to its own sub directory. Without this last line, permalinks do no work, and all links are redirected to the main site (with no subdomain).

Other that that, the upgrade went pretty fine. Actually, I’m quite impressed with how easy the upgrade of WordPress.

Introduction

NSLU2
The NSLU2, my previous server

I run my own web server at home. This little computer is my web server and mail server. I used to have a NSLU2 with Debian Linux. The NSLU2 (also known as Slug) is a little NAS that can also run some software. The NSLU2 has a 223MHz processor and 32MBRAM. That’s not a lot, but it was enough to run a simple web server, a mail server, and a DLNA server. There were some limitations though. The web server could only server static pages – things with server-side scripting (PHP), or databases (MySQL), are simple to heavy for that server. Having a big list and serving that through a DLNA server also caused problem.

So I decided to upgrade my little server. The requirements were a little, silent, and low-energy server. I decided on the SheevaPlug, a small plug-shaped computer. It has 1.2GHz processor, 512MB ram, 512MB internal flash memory, one USB port, a slot for memory cards, and a network port. This thing is a lot faster. It still runs Debian, with a web server, a mail server (IMAP and SMTP), it can download torrents, it is a DLNA media server, and it is powerful enough (sort of) to serve things like WordPress through PHP and MySQL.

SheevaPlug
And my new server, the SheevaPlug

While maintaining this server, I discovered a lot of things. Little tweaks that make the whole system run better. Or changes that I had to make the make the software run. I finally decided to publish all those things on a web site. In the first place, so I can’t forget, but also because other might run into the same problems.