10. January 2015

Converting a decade-old Snitz forum to modern software

Categories: Internet,Linux – Ronny Schmatzler 09:02

iwar*sigh* Looks like I haven’t written anything for a long time. The cause of this may be the fact that I took ownership of a pretty cool website in the middle of December. Apparently their admin didn’t want to do the work of maintaining it anymore:



This thing was reasonably huge:

  • hundreds of files (HTML, ZIP & more)
  • various subpages that had been coded in ASP
  • a forum with “Snitz” software, which hasn’t been maintained since 2009 (ASP, too)
  • a forums database for Microsoft Access (.mdb)

How do you transfer such a sluggish, old beast to a Linux machine without major downtimes?

First of all, I wanted to get at least the static websites and all the files. The tool “WebHTTrack” was a very big help in this part. I was able to crawl the complete website and get all ZIP files, HTML pages and even some of the ASP sites, that were only used to deliver some static content.

The heart of this website is entirely different, though. It’s a forum with posts from which some were written over a decade ago. This one gave me a headache.

Step 1 – getting rid of Access

What was I supposed to do with an ugly .mdb file? That thing had to be gone and converted to MariaDB. I tried converting it with a linux tool, mdb-tools. This resulted in a segmentation fault. Great! Not much luck here, I guess.

My second approach was booting up a virtual XP machine and using Bullzip Access to SQL – that tool converted the database flawlessly, within mere seconds. I had a database which could be used as a starting point, now.

Step 2 – converting the database to phpBB3

I have a lot of experience with Joomla and one of it’s popular forum systems – Kunena (previously named Joomlaboard or Fireboard) – it was crystal-clear for me that this should be the target system for the new website.

Jumping directly from Snitz to Kunena was impossible, though. There wasn’t a tool for that purpose available and I didn’t have the time to fix one up just now.

So I had to do it the rough and hard way: Getting the database to work in phpBB2. I’ve found a converter for this purpose here. Surely a cool thing – but it needs phpBB 2.0.6. And that one runs only on PHP4. To be able to get that one, you need LAMPP 1.6.8a – and this has to be installed into /opt/lampp, otherwise it doesn’t run properly. (Additionally, all of the php.ini limits within LAMPP should be raised to the absolute maximum to get the conversion process started.)

After setting up and configuring that big bunch of software I had to remove all the sweat from my face. I really wanted to run the converter and be done with it. Of course – it broke down with an error message in forums.php. Apparently it couldn’t insert all columns into the new database, because there weren’t enough of them. After a lot of brainstorming, I commented out the lines 54-55 and it ran through. Great Scott!

Finally! I had a database file for phpBB2!

I continued with migrating from phpBB2 to phpBB3. Nothing extraordinary had to be done here. There is an official migration tool – I started it up, it did it’s job and I was done with it. Awesome!

Step 3 – there’s Kunena at the end of the tunnel


I was very happy and full of enthusiasm. I’ve managed to get around all of the obstacles and obtain three databases! Snitz SQL, phpBB2 and phpBB3 – they were all complete, too. A dream! :-)

My happiness vanished very quickly, though, when I read that there wasn’t an official migration tool anymore and therefore no official way to migrate directly to Kunena. Back in the days when Fireboard was the hot sh*t such a script had been maintained. Nowadays, nobody really seems to care anymore.

I discovered an SQL-Skript on github that had been written for a very specific usecase. As I already suspected that thing didn’t work completely, because some columns in the phpBB3 database weren’t there, but they should’ve been. It looks like these columns don’t get created if a user migrates his forum instead of completely creating it from scratch.

However, these missing columns were indeed available in the phpBB2 database, so I could copy together those two databases with a wild mix of commands and the strange thing that came out at the end of it all was – my Kunena database. I finally had it after uncountable hours of brainstorming. It worked, too. I browsed through my new forums and felt relieved.

Step 4 – Let’s clean up!

A beginner would tell me now: Fine Ronny, you did well. Keep it as it is.

But I wasn’t quite done yet. :( All the posts looked like shit. I had to continously improve the old data with these steps:

  • 1. Convert HTML to bbCode

This task was relatively easy. A little SQL command like this did it:

UPDATE kunena_messages_text SET message = REPLACE(message, '<center>', '[center]') 
WHERE `message` LIKE '%<center>%'
  • 2. Make garbled bbCodes pretty

Apparently some strange phpBB3 developers think that creating these bbCodes is awesome:


Of course, no other forum software is parsing that junk. MariaDB’s replace command isn’t able to deal with regular expressions, too. The only way for me to deal with this was exporting my SQL database and running sed over it to parse out that stuff:

sed -i 's/\[img:[^]]\{8}\]/\[img\]/g' db.sql
  • 3. Restore old URLs

The website is almost 15 years old. No wonder that in all of the time thousands of backlinks had been created by other users. And all of them should still work, because I want them to. Back in the days the URLs looked like this:


If you click on this link you’ll notice that you get redirected to a prettier one:


This possible because of some mod_rewrite rules in Apache2:

RewriteCond %{QUERY_STRING} ^TOPIC_ID=([^&]+) [NC]
RewriteRule ^forum/topic.asp$ /forum/missions/%1? [NC,L,R=302]
RewriteCond %{QUERY_STRING} ^TOPIC_ID=([^&]+) [NC]
RewriteRule ^forum/pop_printer_friendly.asp$ /forum/missions/%1? [NC,L,R=302]
RewriteCond %{QUERY_STRING} ^TOPIC_ID=([^&]+) [NC]
RewriteRule ^index.php$ /forum/missions/%1? [NC,L,R=302]
RewriteCond %{QUERY_STRING} ^FORUM_ID=([^&]+) [NC]
RewriteRule ^forum/forum.asp$ /forum/%1? [NC,L,R=302]
RewriteCond %{QUERY_STRING} ^CAT_ID=([^&]+) [NC]
RewriteRule ^forum/default.asp$ /forum/%1? [NC,L,R=302]
RedirectMatch 301 /forum/(.*).asp /forum

A visitor will be redirected to the subforum “missions” with the post id attached to the end of it. Kunena is changing that URL dynamically and making it pretty. It’s home time!

And the moral of this story: Don’t let your software rot, people, please! ;-)

9. December 2014

Access a website in bash and react to it

Categories: Linux – Ronny Schmatzler 22:47

nolf2Sometimes specific scripts I wrote run for years and suddenly I realize, that essential parts are missing in them, which breaks everything.

I wrote the article Host Windows games on a Linux server a while ago – the setup described in this article is running on this machine since a lot of years. A bash script is checking if the process of a gameserver is running (it doesn’t matter if it’s a wine process or a native one). If it doesn’t run, it will be started. This ensures that all my game servers are available for 99% of the year.

If a program is written fairly well, this isn’t even a problem. When a fault happens, these ones kill themselves and get restarted after some seconds.

The server executable of “No One Lives Forever 2″ was a bit more difficult. When I started it, people could play on the machine for some hours straight, until it lost the connection to the internet. The .exe was still running, so there was no way for my bash script to react to it.

But on this adress I have a web frontend running, which is displaying to me very reliably if the NOLF2 server is still running or not.

First I thought, I would only have to check if the phrase “OFFLINE” is appearing on that website and if it is, kill the server and restart it. This idea was very dumb, because when a player gives himself the name “OFFLINE” he can kill the server with that. Also, the program needs some time to load up the maps and the web interface is caching requests for some seconds, too.

I ended up with this solution – this script is checking if the server is online. If it is, it immediately aborts. When the server is offline, the scripts checks for the status again after 90 seconds. That’s simple enough to make it accessible most of the time:

# if online is NULL, it's down. May change maps, so wait and check again:
if [ -z "$(curl -sSf http://hirnschwund.net/?s=4 | grep "ONLINE")"]; then
echo "Shit is offline"
sleep 90
if [ -z "$(curl -sSf http://hirnschwund.net/?s=4 | grep "ONLINE")"]; then
echo "Shit is still offline"
echo "Killing process"
pkill NOLF2Srv.exe
sleep 2
echo "Starting server"
nohup xvfb-run -n 666 wine NOLF2Srv.exe >/dev/null 2>/dev/null &
echo "NOLF2 is running, leave."