Peter's Blog

I'm going to blog this so I can remember it. How to get Rails/ActiveRecord to wrap an existing table in a database, i.e. one that you don't want created or manipulated via migrations:

ruby script/generate model my_table --skip-migration

where my_table is the name of the table you are wrapping. This assumes you are already connected to the database (easy: edit config/database.yml).

It may be possible to simply derive a new class from ActiveRecord but the above is probably ensuring that everything is done properly.

In my case the database is being stuffed from some complex python code that I don't have the time/inclination to port to ruby/rails and I would rather the creation of the tables was still done through python. ActiveRecord being the wonder that it is will pick up the schema of the table and create wrappers automatically so you still don't need to tediously reiterate the column names/functions as you would in lesser frameworks.

It should be entirely possible to create views in MySQL and wrap them for Rails in this fashion although I found that creating views on a 20,000 record table causes the mysqld to take 99% cpu time for ten minutes afterwards. I know what you're saying, 'check your indexes', well I have.

This blog was offline for a while yesterday. One of the perils of shared hosting is that you have to share the mysql database with other people who tend to use up all the available connections etc and an admin has to sort it all out.

The blog was left broken and visitors were advised that the drupal sessions table was corrupted and it needed repairing. How nice of drupal to blab secrets like the name of the database. Repairing the database through the site5 interface didn't work, it didn't even list the sessions table in the results. Fixing it turned out to be quite simple, log into mysql and go:

REPAIR TABLE SESSIONS;

The sessions table had 25,000 entries in it! Since this is old session data and not at all essential (I have the only active account) I zapped it:

DELETE FROM SESSIONS;

Fixed.

Apart from server maintenance, problems with mysql seem to be the main reason for downtime on this blog. Should I move it across to my new server where all the connections are mine and mine alone? If I did I would probably want to make sure I was backing the blog up: I wouldn't want to lose three years and 43 weeks, 1480 articles, a detailed history of my World of Warcraft adventures and the internets primary cuprin0l fence sprayer vitriol page. Site5 do automatic backups, backing up on the new server would be totally down to me. Fortunately I can back it up to... my site5 account!

After a couple of weeks of neglect I ran an update on the packages on my debian server. It gave me a few surprises:

• it upgraded mysql from 4.1 to version 5
• it uninstalled the webmin mysql module
• it uninstalled my vim-python version 6.3 and installed plain vim version 6.4

The new version of mysql seems to be running smoothly (or you wouldn't be reading this). Must see if it supports cascade delete, my all-time favourite sybase sqlanywhere feature.

I manually installed vim-python using dselect and was happy. Installer glitch. I haven't addressed webmin-mysql yet, it wasn't obvious from what dselect showed me whether what it had would work with mysql 5. Maybe it doesn't say because it doesn't matter? I find webmin-mysql can do all the mysql database administration I need (create database, add users, set permissions, view table etc) with a much simpler interface than phpmyadmin, which is slightly ott.

I think vim 6.4 is a bug fix release. Upgrading on windows involves recompiling it with python support, not a big deal but it takes time that could be spent doing other things. Sometimes I contemplate hosting vim-python for windows downloads as a public service. I'll contemplate it some more.

I have developed my strategy for putting drupal database dumps into subversion every day. This was slightly more complicated than I though it would be. In principle I could just use mysqldump to dump the sql and put that in subversion but the problem is that mysqldump by default will output the insert statements to reconstruct a table in a single line in the file, even of that line grows to 600k long. If subversion does a line-by-line diff on the files it stores it will end up writing all 600k to it's transaction log.

To minimise the sizes of each diff I dump the mysql in two parts, the data and the structure. For the data dump I do the following:

• use --skip-opt to ensure that each row is inserted on a seperate line: this limits the line length. The --opt option is the default for my version of mysql (4.1.13) so I have to turn it off.
• remove comments
• strip out data for the 'accesslog', 'cache', 'search_index' and 'sessions' tables. I don't think these need backing up.
• sort the remaining lines into alphabetical order as I am not sure the sql dump is guaranteed to always dump the rows in the same order

The structure is dumped straight, I certainly don't sort the lines in the file!

This creates sql data files that should diff pretty optimally.

Here is the bash script that does this for me:

   1  #!/bin/bash
   2  
   3  function SqlDumpData {
   4      mysqldump -u secret -psecret --no-create-info --skip-opt --comments=0 $1 | \
   5        egrep -v "INSERT INTO \`(accesslog|cache|search_index|sessions)\`" | sort >$2
   6  }
   7  
   8  function SqlDumpStructure {
   9      mysqldump -u secret -psecret --no-data $1 >$2
  10  }
  11  
  12  cd /home/peterc/DatabaseDumps
  13  
  14  SqlDumpData petersblog petersblog_data.sql
  15  
  16  SqlDumpStructure petersblog petersblog_structure.sql
  17  
  18  svn commit -m "daily backup"
Toggle Line Numbers

This is all done on the server. Following this I can use my standard strategy to backup the subversion repository.

I have tested that the sql dumps can be reimported into mysql and give a functioning website.

I have implemented a daily backup from my oneandone dedicated server to my home desktop pc. I used rsync over ssh using the notes I wrote myself which work with ubuntu as well as windows. It is only backing up the websites, I'm not backing up the configuration yet, I could add a backup of /etc only that has to run as root.The PC is set up to turn itself on everyday via the BIOS, it will run the backup, do any other daily stuff I can think of, then switch itself off.

At the server end, just before the backup schedule, the mysql databases for the websites are all dumped. The sql for this site is now 9M although a lot of that appears to be server logs.

Once the backup has reached the desktop pc I'd like to create some form of historical backup and I am deciding whether to create incremental differences or chuck everything into subversion. I am leaning towards the latter.

One option for the servers daily activities could be to download podcasts and dump them onto my phone...

I have a few problems with my oneandone dedicated server:

• I don't know my way around Fedora Core and cannot find anything
• Plesk seems to hide most of the configuration deep in it's own directories, making finding anything even harder.
• It is Fedora Core 2 which is a year or two old. The packages available are a little long-in-the-tooth (Mysql 3.2, Subversion 1.0.9) and the versions are becoming deprecated. Plesk 7.5.3 only supports Fedora Core 2 so ultimately again Plesk is an annoyance.

So I decided to install debian. Ubuntu, my distribution of choice is derived from debian so I know my way around it. Also there is a complete debian mirror on oneandone's servers that I can access without impact on my bandwidth allocation.

I was inspired by this article about installing debian on a oneandone server which gave me the confidence to give it a try. However I didn't follow these instructions for a few reasons:

1. It requires building a kernel on a running debian system. I don't have one of those and didn't feel like making one. The instructions erroneously say that the network interface has to be built into the kernel but mine is ok as a module.
2. It sets up static ip addresses instead of using dhcp. The oneandone faqs seem very insistant on using dhcp. Getting this going was the most difficult part of this setup but I am glad I have done it this way: my server will not suddenly break if oneandone reconfigure their network.
3. I didn't want to clear out my entire hard disk, I wanted to keep the fedora/plesk system in case I wanted to return to it. The fedora installation allocates 27G to a /home partition. I decided to copy the little of this that is used to the root partition and use the home partition (/dev/hda7) for my new installation. I can always re-use the fedora partitions later, if 27G is not enough for me.

I am still grateful to the author of that article for making me believe it was possible.

These are essentially the steps I took:

Start of logged into the fedora system, copy the old home directory to the root partition:

mkdir /newhome
cp -a /home /newhome
umount /home

If the umount fails because files in /home are open then run

/usr/sbin/lsof | grep home

to see what has the files open and stop or kill the processes (lsof, list open files, damn useful).

Create new file system in partition where /home was:

/sbin/mkfs.ext3 /dev/hda7

Mount the new installation:

mkdir /mnt/new
mount /dev/hda7 /mnt/new

Download debootstrap from debian mirror on oneandone servers. This mirror is only visible to oneandone customers. Untar and try to run

tar xvfz debootstrap-0.3.1.5.tar.gz
cd debootstrap-0.3.1.5
export DEBOOTSTRAP_DIR=`pwd`
./debootstrap --arch i386 sarge /mnt/new http://update.pureserver.info/debian sarge

gives error:
E: no /home/peter/debootstrap-0.3.1.5/devices.tar.gz. cannot create devices

Hum, supposed to be in debootstrap archive from what I can tell. Installed debootstrap under ubuntu and copied the file up.

If you get:

W: Failure trying to run: chroot /mnt/new mount -t proc proc /proc

then log in properly as root to get path set up correctly:

su -l root

rather than

su

Looking good:

I: Base system installed successfully.

Do base configuration. This is essentially the debian setup program, it installs loads of packages, asks you what time zone you are in etc.

chroot /mnt/new
mount /proc
base-config

Mostly obvious stuff but:

• apt sources set to

  deb http://update.pureserver.info/debian unstable main contrib non-free
  

• Install the following
  • Web Server
  • Mail Server
  • SQL database
• Use readline interface for configuring packages: I find the dialog interface invasive, it pops up and stops you seeing anything else on screen which you sometimes need to understand what is going on.

For me the nfs package failed to install so I ran dselect and removed it. Installed openssh server and, most importantly, vim. sshd just works, needs no configuration.

Setup /etc/fstab:

/proc           /proc   proc    defaults                0       0
/dev/hda7       /       ext3    defaults,noatime        0       0
/dev/hda2       none    swap    defaults                0       0

Set up /etc/network/interfaces:

auto lo eth0
iface lo inet loopback
iface eth0 inet dhcp

Use dselect to install Linux kernel image for PPro/Celeron/PII/PIII/P4 machines.

Ensure network card is in /etc/modules (without this we are lost):

via-rhine

My box has via-rhine, you can check in /var/log/messages to see what yours might be and look in /lib/modules/*/kernel/drivers/net to see if there is a module for it.

Install lilo:

apt-get install lilo

Copy /etc/lilo.conf from fedora and modify it:

boot=/dev/hda
# for fedora
#root=/dev/hda1
root=/dev/hda7

install=/boot/boot.b
vga=normal
timeout=10
prompt
lba32

read-only

default=lx
append="console=tty0 panic=30"

#
# Debian install.
#
image=/vmlinuz
    initrd=/initrd.img
    label=lx

Run lilo to set this up:

lilo

Quit out of the chroot and copy the /etc/hosts from fedora to the new install.

exit
cp /etc/hosts /mnt/new/etc/hosts

Now go to oneandone control panel and reboot the system into the recovery system. This will have you box running an emergeny debian install. It's handy because we are installing debian. Once installed, mount your new install so that you can copy some more files to it:

mount /dev/hda7 /mnt
cp /etc/dhcp3/dhclient.conf /mnt/etc/dhcp3
cp /etc/dhcp3/dhclient-exit-hooks.d/local /mnt/etc/dhcp3/dhclient-exit-hooks
chmod 755 /mnt/etc/dhcp3/dbclient-exit-hooks/local

This last step took me hours to figure out, I think the dhcp3/dhclient-exit-hooks.d/local is the trick as it does not exist in a standard debian install. While I was messing around I managed to reset the execute bits on the files which broke dhcp completely, hence the chmod to make sure.

From the oneandone control panel, reboot server in normal mode and enjoy.

What is so damn cool about linux is that doing something like this is possible at all. Reninstall a different operating system on a pc in a different country (Germany) through a command line terminal? Try doing that in windows.

Set up personal, private (hopefully) microblog on dedicated server. No it won't be full of juicy details about my sex life, it's more for general notes not worthy of wide publication. I'll see how it pans out, it may be too much of a stream of consciousness thing to be much use to casual observers. If I recall correctly, James Joyce's drivel great literature is stream of consciousness stuff.

Access to dedicated server is much quicker than the site5 server, like it's on a local lan. This may be for two reasons:

• it's all mine
• it's hosted somewhere in Germany, i.e. on the same continent as me.

I've decided not to try upgrading it to Fedora Core 4 for now, I will try to keep it clean for as long as possible. I found the MySQL-Python rpm in the Fedora 2 stuff so I was able to get the Microblog running and now I can't think of any packages I want.

I'll fiddle some more before I migrate petersblog.org. I need to figure out how to use plesk to create user accounts with ssh access, I'm doing everything as root which is a no no.

Been reading bad comments about oneandone's tech support. Still no feedback on the problem with plesk I had after a day, don't care much. Bad tech support on an unmanaged server is no surprise.

I've been playing more with my Dedicated Server, getting to grips with Plesk. Plesk is a control panel for people who want to sell hosting packages. It allows you to:

• Create user accounts
• Create domains on those accounts
• Set disk quotas, limits etc
• Setup a list of applications that users can install (although the range supported by Plesk seems a bit limited, fantastico on site5 does better).

Plesk is a bit fiddly to use, go round in circles looking for things, it's buggy and the version I have (7.5.3) apparently only supports an old version of Fedora Code (2). This version of Fedora has an archaic version of MySQL (3.23?), python 2.3 and not a lot else. I've been spoilt by ubuntu's plethora of packages. Apperently Fedora core is called Fedora core because it is the core operating system. However, I don't really want to get into rpm hell, having to find them, find the dependancies, give up, install from source like I used to with SuSE. I like the way ubuntu has packages for everything I can think of.

I'm deciding whether to try upgrading to Fedora Core 4. It would probably kill plesk but do I care? Am I going to sell web hosting? Plesk would make that easy and it is arguably an easy way to administer multiple domains for myself.

My plesk is broken anyway: if I try to look in the 'Application Vault' I get:

ERROR
Up LevelUp Level
Unable to query database: Duplicate entry '28-Package name' for key 1
0: /usr/local/psa/admin/plib/class.ActionLog.php:638 psaerror(string "Unable to query database:
Duplicate entry '28-Package name' for key 1")
1: /usr/local/psa/admin/plib/class.ActionLog.php:515 logcomponent->submit(integer "28")
2: /usr/local/psa/admin/plib/class.ActionLog.php:450 actionlog->submitbuffer_()
3: /usr/local/psa/admin/plib/SiteApps/class.SiteAppPackageManager.php:395 actionlog->submit()
4: /usr/local/psa/admin/plib/SiteApps/class.SiteAppPackageManager.php:188 siteapppackagemanager::unregistersiteapppackage(object of type siteapppackage)
5: /usr/local/psa/admin/htdocs/server/site_app_pkgs.php:35 siteapppackagemanager->syncwithdatabase()

I've contacted oneandone's tech support but no reply. My gut feeling is that this one is not a simple thing and I doubt they will be able to help anyway.

Installed Bugzilla as an issue tracking system for support work. I've looked at many Issue/ticket/bug tracking systems, mostly php/mysql based. Many of them were awkward to install, amateurish in execution or otherwise lacking. I won't name names (mainly because this was over a few months and I've forgotten most of the names). A close candidate was the drupal project module but I decided that was a bit too fiddly. Bugzilla is closer to what I want.

First tried bugzilla through an ubuntu package. Like a lot of packages this was an old version (2.16) where the latest is 2.18. The installation package put the bugzilla files in an awkward mix of directories and symbolic links. It didn't work very well and I could only get it to run through the url /cgi-bin/bugzilla/index.cgi which was not very elegant. Then I had to poke around to find out how to customise it, working out where the package had put things.

When I installed my new server I decided to install the latest release version from the bugzilla site and this was very easy. It amounted to:

• untar the file
• move the contents to /var/www/bugzilla
• run the checksetup.pl script and fix the errors it threw up (mysql permissions, missing perl packages, not big problems).
• tweek apache to let /bugzilla run the appropriate index.cgi script.

Much nicer and more understandable installation than the package gave me, everything in one place.

I customised it by copying bugzilla/templates/en/default to bugzilla/templates/en/custom, editing the template files and running the checksetup.pl script again (which does some of the setup, it doesn't just check it).

With some tweeks to the templates and css I was able to:

• Change it to talk about 'issues' rather than 'bugs'.
• Change ugly black banner to navy blue.
• Change title from 'Bugzilla' to 'Issue Database'
• Change nasty huge serif font to subtle verdana.
• Change nasty picture of an Ant to something I found on a google image search for the word 'Complain' (which I am certain is not copyrighted).
• Remove the URL field from the bug entry form as it would never be required.

After this it was looking cool. Set up users (all user names are email addresses which is a bit naff), define projects and components and it's all ready to go.

Bugzilla is ok, it works, if I have any gripes they are:

• they could do with a good html designer on their team. The pages all look at bit slapdash.
• the emails it send out are not very clearly formatted
• why do I need to enter 'Priority' and a 'Severity'? Can I have a P5 priority (low?) and a critical severity?
• would be nice to have some user defined fields. I haven't looked into this but for our purposes it would be good to have a field for the person having the problem (don't want end users submitting bugs).

After fixing postfix installation all was looking good until I realised the web server running Drupal was only showing the home page: clicking on other pages kept giving the home page. I looked in /var/log/apache/error.log and found I was getting this error on each click:

/usr/sbin/apache: relocation error: /usr/lib/php4/20020429/mysql.so:
undefined symbol: php_sprintf

A nasty one. Some googling and forum browsing gave me the clue to the solution: I had an unholy mix of Apache 1.3 and Apache 2.0 installed on the box, apache 1.3 was running and finding php compiled for Apache 2.0 (or something like that).

The solution was:

• run rcconf and disable apache (1.3) and enable apache2
• stop apache 1.3
• install mod_php4 for apache2
• enable php in /etc/apache2/apache2.conf
• enable mysql.so and gd.so in /etc/php4/apache2/php.ini
• start apache2

and sanity was restored (if an intranet can be described as that).

I realised that a lot of my old postings were not tagged by awtags because I hadn't been through them to categorise them and, worst still, I couldn't be bothered. This meant that the postings weren't indexed unless someone went way back through the blog history.

I decided to create a new tag for them called untagged. I wrote a python script to look for awtags with no tags assigned and to assign them to this new tag. I used python because I am far more confident in it than I am in php. There is not much point in making this a module or anything because it only needs doing once if I am methodical about giving tags to new postings. I could also have done this in raw SQL but the version of MySQL on Site5 does not support nested selects.

Once the 'untagged' tag is in place the only chore is to remove this tag from postings that I generate new tags for using my search facility. This can be done through the awtags administration interface (e.g. search for tag 'whatever' and remove tag 'untagged'). Also, now I can easily list the untagged articles, it is much easier to see what tags need adding.

   1  #
   2  # Assign a tag to nodes with no awtags
   3  #
   4  import MySQLdb
   5  import DBTable
   6  
   7  o = MySQLdb.Connect( 'localhost', '<mysql user name', '<password>', '<mysql database name>')
   8  
   9  oAwNodeDB = DBTable.DBTable( o, 'awtags_node')
  10  
  11  oAwNodeDB.Select()
  12  
  13  oTaggedNodes = {}
  14  
  15  while 1:
  16      oRow = oAwNodeDB.FetchOne()
  17      if not oRow:
  18          break
  19      oTaggedNodes[oRow['nid']] = 1
  20  
  21  oNodeDB = DBTable.DBTable( o, 'node')
  22  
  23  oNodeDB.Select( "SELECT * FROM node WHERE node.type = 'blog'")
  24  
  25  oNodes = {}
  26  
  27  while 1:
  28      oRow = oNodeDB.FetchOne()
  29      if not oRow:
  30          break
  31      oNodes[oRow['nid']] = 1
  32  
  33  for nNid in oNodes.keys():
  34      if not oTaggedNodes.has_key( nNid):
  35          oDict = { 'nid': nNid,
  36                          'tid': 84}
  37          oAwNodeDB.Insert( oDict)
Toggle Line Numbers

This assumes the 'untagged' tag has a tid of 84: you should create your own tag and see what number it is.

This uses the DBTable module I wrote a while back. I discovered to my delight that the python odbc and MySQLdb modules had virtually identical interfaces so this module worked largely unchanged. I had to tweek it a bit because the field types were recorded as numbers instead of strings. Here is the modified version. It should work with odbc as well.

   1  #
   2  # Database wrapper class.
   3  #
   4  class DBTable:
   5      """
   6      Wrapper for database table
   7      """
   8      FIELD_TYPE = 0
   9  
  10      def __init__( self, oConnection, strTable):
  11          self.oConnection = oConnection
  12          self.strTable = strTable
  13  
  14          oCursor = oConnection.cursor()
  15          oCursor.execute( "SELECT * FROM %s" % strTable)
  16  
  17          self.oFields = [ oField[0] for oField in oCursor.description]
  18          self.oFieldDescription = dict( [ (oField[0],
  19                                   oField[1:]) for oField in oCursor.description])
  20  
  21      def Select( self, strQuery = None):
  22          """
  23          Select records from query
  24  
  25          Takes either SQL of select statement or a dictionary containing
  26          field names and values to find.
  27          """
  28          self.oCursor = self.oConnection.cursor()
  29          if strQuery == None:
  30              self.oCursor.execute( "SELECT * FROM %s WHERE 1" % (self.strTable))
  31          elif type( strQuery) == type(""):
  32              self.oCursor.execute( strQuery)
  33          else:
  34              #
  35              # assume query is a dict
  36              #
  37              self.oCursor.execute( "SELECT * FROM %s WHERE %s" % (self.strTable,
  38                                                       self.DictToWhere( strQuery)))
  39  
  40      def FetchOne( self):
  41          """
  42          Get next row of results
  43          Returns a dictionary holding field names and values.
  44          """
  45          oRow = self.oCursor.fetchone()
  46          if oRow:
  47              """
  48              Build a dictionary to map field name->value
  49              """
  50              return dict([(self.oFields[i], oRow[i]) for i in range(len(oRow))])
  51          else:
  52              return None
  53  
  54      def Insert( self, oDict):
  55          """
  56          Insert a row in the database
  57          Takes a dictionary holding field names and values.
  58          """
  59          strFields = oDict.keys()
  60          strValues = []
  61          for strField in strFields:
  62              strValue = oDict[strField]
  63              strType = self.oFieldDescription[strField][DBTable.FIELD_TYPE]
  64  
  65              strValues.append( self.FormatField( strField, strValue))
  66  
  67          strSQL = "INSERT INTO %s ( %s) VALUES(%s);" % ( self.strTable,
  68                                                          ", ".join( strFields),
  69                                                          ", ".join( strValues))
  70          print strSQL
  71          oCursor = self.oConnection.cursor()
  72          oCursor.execute( strSQL)
  73          self.oConnection.commit()
  74  
  75      def Update( self, oDictWhere, oDictNew):
  76          """
  77          Update a row in the database
  78          Takes a dictionary holding field names and values to find
  79          and dictionary to replace it with.
  80          """
  81          strFields = oDictNew.keys()
  82          strValues = []
  83          for strField in strFields:
  84              strValue = oDictNew[strField]
  85              strType = self.oFieldDescription[strField][DBTable.FIELD_TYPE]
  86  
  87              strValues.append( "%s = %s" % (strField, self.FormatField( strField, strValue)))
  88  
  89          strSQL = "UPDATE %s SET %s WHERE %s;" % ( self.strTable,
  90                                                    ", ".join( strValues),
  91                                                    self.DictToWhere( oDictWhere))
  92          print strSQL
  93          oCursor = self.oConnection.cursor()
  94          oCursor.execute( strSQL)
  95          self.oConnection.commit()
  96  
  97      def InsertOrUpdate( self, oDictWhere, oDictNew):
  98          """
  99          Seek record in database, add it if not found, update it if found.
 100          """
 101          self.Select( oDictWhere)
 102          if self.FetchOne():
 103              self.Update( oDictWhere, oDictNew)
 104          else:
 105              self.Insert( oDictNew)
 106  
 107      def Delete( self, oDict):
 108          """
 109          Delete row based on dictionary contents
 110          Takes a dictionary holding field names and values.
 111          """
 112          strSQL = "DELETE FROM %s WHERE %s;" % ( self.strTable, self.DictToWhere( oDict))
 113  #        print strSQL
 114          oCursor = self.oConnection.cursor()
 115          oCursor.execute( strSQL)
 116          self.oConnection.commit()
 117  
 118      def DictToWhere( self, oDict):
 119          """
 120          Convert dictionary to WHERE clause.
 121          """
 122          strFields = oDict.keys()
 123          strExpressions = []
 124  
 125          for strField in strFields:
 126              strValue = oDict[strField]
 127              strType = self.oFieldDescription[strField][DBTable.FIELD_TYPE]
 128  
 129              strValue = self.FormatField( strField, strValue)
 130  
 131              strExpressions.append( '%s = %s' % (strField, strValue))
 132  
 133          return " AND ".join( strExpressions)
 134  
 135      def FormatField( self, strField, strValue):
 136          """
 137          Format a field for an sql statement.
 138          """
 139          strType = self.oFieldDescription[strField][DBTable.FIELD_TYPE]
 140          if strType == 'STRING':
 141              return "'%s'" % str(strValue).replace( "'", "''")
 142          elif strType == 'NUMBER' or strType == 3:
 143              return '%d' % int( strValue)
 144          else:
 145              #
 146              # Treat as a string.
 147              #
 148              return "'%s'" % str(strValue).replace( "'", "''")
 149  
Toggle Line Numbers

Could not access this website, nor ssh into it, so decided to reboot the Linode that hosts it. It took a few minutes for the reboot to complete but this resolved the problem. Either:

• Linode sufferred a glitch
• My site sufferred a glitch

It's slightly worrying as Site5 never did this.

UPDATE: happened again. According to the Linode forums, the host my site is on (39) suffered from a power outage sometime sunday.

My statcounter stats indicate that the site has been down for some time, the number of hits is down.

On examination my linode was not dead but was running very slowly. I rebooted it again and it became responsive, however this site would not come up, I got a totally blank page. The apache2 error log said:

[client 1.2.3.4] PHP Fatal error:  Can't open file: 'sessions.MYI'. (errno: 145)\nquery:
SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid =
'61a28f0a34b8ebb9f26304c033219df0' AND u.status < 3 LIMIT 0, 1 in
/var/www/includes/database.mysql.inc on line 66

trying a command line query on the sessions table confirmed that mysql couldn't read this table. Corrupt? I had a quick google for repair options and very quickly came across the following sql command:

repair table sessions;

This did the trick and my site was back up again.

So did my reboot corrupt the sessions table, did the power outage do it, was the slowness related to this and in what way... so many questions. The bottom line is, do I trust Linode any more?

I've moved this site to my Linode. I have a couple more months before my Site5 account runs out so I have time to move back if necessary. I moved the site as follows:

• Dump the sql:

  cd ~/www
  mysqldump -u <user> -p drupal > pb.sql
  

• Zip the useful stuff:

  zip -r pb cron.php database/ fail.html favicon.ico files/ images/ includes/
  index.php misc/ modules/ pb.sql robots.txt scripts/ sites/ themes/ tmp/ xmlrpc.php
  

  Will I ever learn to love tar?
• copy the zip to new site by running sftp on new site.
• unzip the zip file in /var/www
• load the database into mysql:

  mysqladmin create drupal -u <user> -p
  mysql -u <user> -p drupal < pb.sql
  

• go to domain registrar and change DNS address of site from site5 to linode. It only took three hours or so for this to propogate enough for me to access the linode via the petersblog.org name.

And if you are reading this then you are reading the site from the Linode as I haven't posted it on the old site. I can leave the old site floating for a while until the old address is flushed from all the DNS caches out there. I'll leave bisiand.me.uk pointing there until the account expires: unless of couse something happens to convince me to stick with Site5.

I have no problems with Site5, I would recommend them to anybody, it's just that Linode is more technically challenging and hence more interesting and fun.

I updated my linode to Ubuntu hoary hedgehog. I followed the upgrade steps here except I didn't install the ubuntu-desktop package as its a GUI-less server (although I may regret that one day).

Notes:

• it only took a few minutes to upgrade 250 packages.
• installing one of the packages failed with:

  udev requires a kernel >= 2.6.8, upgrade aborted.
  dpkg: error processing /var/cache/apt/archives/udev_0.050-3ubuntu7_i386.deb (--u
  npack):
   subprocess pre-installation script returned error exit status 1
  Errors were encountered while processing:
   /var/cache/apt/archives/udev_0.050-3ubuntu7_i386.deb
  E: Sub-process /usr/bin/dpkg returned an error code (1)
  

  which appeared to be because I am running a 2.4 kernel. I am not sure how free I am to change kernels on a Linode. I ignored the error and life went on.
• libphp4 module installation failed because it could not find a php.ini file. I create one for it to fiddle with and this time it was happy.
• I did shutdown -r now to reboot but this only powered down the linode. I had to go into the Linode control panel to boot it again.
• It came up and I was happy. If it hadn't I would have to do a clean install. I MUST get around to adding a rescue partition on the virtual hard disk.
• It had lost the hostname and I had to use the hostname command to reset it.
• It had decided to upgrade me to apache2 and install the generic index.html which overrode my drupal index.php so I deleted it.
• I had to enable php in /etc/apache2/apache2.conf by uncommenting:

  AddType application/x-httpd-php .php
  

• I had to enable mysql and gd in /etc/php4/apache2/php.ini by uncommenting:

  extension=mysql.so
  extension=gd.so
  

• It has python 2.4
• It still has subversion 1.06 I wanted to try out 1.2 which is why I upgraded Ubuntu.

awTags was adding an entry to the navigation menu called 'My Tags'. This was irritating me because it was presented to anonymous users and was the only reason for the navigation menu to appear. Looking in 'awtags.module', there are no options to control it so I changed the source so it will only appear for logged-in users:

   1  /*
   2   * Implementation of hook_menu
   3   */
   4  function awTags_menu($may_cache) {
   5    global $user;
   6  
   7    $items = array();
   8  
   9    if ($may_cache) {
  10  
  11      // pcw: only logged in users can have 'my tags'
  12      if( $user->uid) {
  13         // /usertags/tags (my tags)
  14         $items[] = array(
  15         'path' => "usertags/$user->uid",
  16         'title' => t('my tags'),
  17         'access' => user_access('access tags'),
  18         'callback' => '_awtags_page',
  19         'callback arguments' => $user->uid,
  20         'type' => MENU_DYNAMIC_ITEM);
  21     }
  22  
  23     ... rest of function unchanged.
  24  }
Toggle Line Numbers

I tested this in IE where I am anonymous (like all IE users) and no change. Forgot to flush the damn Drupal cache for the umpteenth time: the menu's are cached. I took the time to knock up a php script to flush the cache for me so I don't have to fiddle with the mysql command line:

<?php
include_once 'includes/bootstrap.inc';
include_once 'includes/common.inc' ;

db_query('DELETE FROM {cache}');

echo( "Done");

?>

Save the above in a file called FlushCache.php on your server and just open it in a browser to flush the cache. It may be advisable to set up your .htaccess so that only you can access the file:

<Files "FlushCache.php">
  order deny,allow
  deny from all
  allow from [my ip address]
</Files>

Updated this blog to Drupal. Went smoothly enough, tried using the democratica theme as it is one of the few drupal themes that resizes horizontally to fit screen width but it had some problems:

• if main content was not long enough, there was an error whereby the background to the right side panel was not long enough and did not meet the footer.
• viewing in IE6 on my Dell inspiron 500m, the background to the page loaded horribly slowly and I had to edit the css to make it plan grey
• it's css is vastly complicated and spread over a number of css files. Does not strike me as a clean or efficient design.

so I went back to my theme although I am a little sick of it.

I have wanted to categorise my blog entries using tags for a while now but there is still no official drupal module to do it (an api for developers but no user level module). I had a google and found awTags which is exactly what I want. You can now see the nice tags block on my site.

So I had a nice tagging system and over 700 articles with no tags. asTags provides an admin page to add tags to nodes with selected existing tags but no more. Looking through the code it had a nice clean api and I was able to hack to to do a search on the database for a search term and add a tag to matching nodes. I used the mysql REGEXP operator so that I could match whole words:

   1  function awTagsAPI_AddToExistingTagSearch($search, $addTag) {
   2    $addTid = awTagsAPI_GetTagID($addTag);
   3  
   4    if ($addTid == FALSE)
   5      $addTid = awTagsAPI_AddTag($addTag);
   6  
   7      $strSearch = str_replace( "'", "''", $search);
   8    $result = db_query("SELECT nid FROM {node} WHERE body REGEXP " +
   9                       "'[[:<:]]%s[[:>:]]' OR title REGEXP '[[:<:]]%s[[:>:]]'", $strSearch, $strSearch);
  10    $nCount = 0;
  11    while ($nid = db_fetch_object($result)) {
  12      awTagsAPI_AddTagToNode($nid->nid, $addTid, TRUE);
  13      $nCount = $nCount + 1;
  14    }
  15  
  16    return $nCount; // return count to display in summary
  17  }
Toggle Line Numbers

In an ideal world I would have used drupals own search facility but the api for that is horribly mixed up with user interface code so I searched the database directly. The above allows an amount of regexp syntax to be used, e.g. search for (outlook|thunderbird|exchange|gmail) and tag with 'email'. However, the above does not search comments, only node title and body.

Still, it worked good enough for me and I've added lots of tags.

Linode looks interesting. It is a hosting service whereby you get a virtual linux box all of your own. It is on a server and it is shared with 40 other people but you get 64M of ram to yourself, 3G of disk space that you partition yourself as you see fit and a selection of linux distributions to choose from. You install linux, have root access and basically can install whatever software you like on it (even painful gentoo compilation). It is like having your own linux box out there on the web.

It can be used not just for a web server but ftp, mail, proxy, DNS server, backup server, you name it. It sounds more interesting than Site5 which gives plenty of power except there is no root access, cannot use wget to get packages, no compiler, two year old version of Python, no fastcgi or mod_python just slow cgi, etc etc. Linode costs $20 or £10.50 a month which is more expensive and the support would not extend to patching your kernel like Site5 would do. Then again, unlike a shared host, if some other tosser you share with uses up all the mysql connections with a flaky script your account does not suffer (happened for the second time to my knowledge this sunday).

I'd be tempted to go with this rather than renew my site5 deal.

Had a try with Python Database Objects (aka PDO) which is a simple python wrapper for a variety of SQL databases. I'm interested in using it to simplify some python MySQL stuff that I have in mind.

Here is a try with my Drupal database:

>>> import pdo
>>> c = pdo.connect( 'Module=MySQLdb;user=me;passwd=secret;db=drupal')
>>> rs = c.open( 'SELECT * FROM node')
>>> rs.fields.keys()
['status', 'comment', 'votes', 'moderate', 'uid', 'title', 'revisions',
'format', 'created', 'body', 'nid', 'sticky', 'teaser', 'score',
'changed', 'promote', 'type', 'users']
>>> rs.fields['body'].value
'<p>Released V0.0r475 yesterday, email to cjr &amp; floppy to mf.</p>'

It's pythonic in that the interfaces are simple and the results are reminiscent of Windows DAO, a windows database wrapper that I am most fond of. The source to it is very simple, just one file.

It is better than using the MySQLdb module directly: with that I could not find out how to get the column names and results were returned in a straight array of values. PDO returns the results for a row in a dictionary so it is easy to address them by name and not have to worry about column numbering.

I've been keeping an eye on my visitor logs to see how much my domain name problems have effected my traffic. According to Statcounter they had been climbing but yesterday there is a sudden dip. The Awstats logs provided by Site5 show no such dip.

I've seen a number of such dips in the Statcounter logs: their servers do not appear to be the most reliable. This is not a big complaint, I use them for free, more of a lamentation. Their professional service is too expensive for my simple ego brushing needs, $9 a month, but if I was paying that I would not want drop-outs approximately once a week.

The main advantage of Statcounter for me is that it counts visitors who have javascript enabled so it is essentially counting human beings rather than crawlers and referrer spam bots. It is also easy to set it up to ignore my own IP address. The Drupal statistics module does not have this feature but I could simply use phpmyadmin or another generic mysql database report generation tool to filter the drupal logs in any way I desire. The statistics module does list external referrers in reverse chronological order so it is useful for updating .htaccess referrer exclusion lists.

Created a new hosting account with site5. Impressed so far, 1.5G of storage, PHP, unlimited MySQL databases, auto-install of drupal, fast server, all for $7 a month. Sorry if this sounds like an advert.

My previous blog was hosted on Python Community Server and written using Python Desktop Server. I have a couple of reasons for changing, especially to something I have to pay for (!)

• My Python Desktop Server install was not the 'official' version, it was a Gentoo package installation and it suffered from a number of little problems that I didn't have time to fix.
• I've used Drupal at work and on another site and I really like it. Even when I have to drop into the php code I am not totally lost, the code is easy to follow.
• Drupal is better documented.
• All my blog entries are stored in a standard database format, not something obscure (heard of metakit ?).
• I (think) I can set things up so that I can moderate comments. I was getting comment spam and I hate it, it makes me feel violated.
• Drupal data entry has a cool 'preview' button, with Python Desktop Server it just gets published, complete with formatting errors.
• Drupal search facilities just work. I never did work out the hack required to add a search to my Python Cumminity Server. The only way to find old posts was using the calendar thing, and that was buggy and did not link every day that had postings.

I could go on and on but the fact is, here I am.

ToDo list:

• Create nice custom theme
• Upload old blog

I've been looking around the options for dynamic web content recently for various projects. One of them is to be hosted under IIS, the other I can do with as I please. The IIS based one required a CMS so that many people can add content. The original request was for something that used ASP, VBScript and Access databases and was not .NET. I searched around and found nothing suitable. Widening the search I came across Drupal which seemed to fit the bill except that it was PHP and MySQL.

At about the same time I was considering this I came across the PHP EasyWindows Installer which installs PHP as a CGI engine which is cool enough, I don't think I have to worry about high volume traffic.

So I installed it and MySQL on Windows XP and it runs a treat. The pages are server up quite swiftly and the package looks quite powerful. It looks nicely designed: a clear segragation between content (all stored in the database) layout (a single, nicely laid out template file that builds all the pages on the site, easily edited in HTML-Kit) and formatting (.CSS).

I'm still exploring it but it is looking suitable for my needs and the person I am doing it for has agreed to install PHP and MySQL on his windows box. I'm not reluctant to learn PHP although I find that after python all the $s in the code look like noise: not as bad as perl (from which it is distantly related) but nearly so. The drupal source that I have looked through is quite clean. There is not a great deal of documentation but that is par for the course. I might not even need to write any PHP, I've found modules to do most of the things I want.

One nice thing about Drupal that Python Desktop Server is lacking: a button to preview messages before submitting them.

For my other project I am looking into a way to present email archives on a web site. I've looked through various options, none of which appealed:

HyperLink

Crude presentation, no Gentoo ebuild.

MHonarch

Gentoo package failed to emerge

Macho

Nice presentation but an embarrasing name to search for on the net and also written in lisp (((ugh))). I don't really want to install yet another language and I certainly don't want to learn lisp.

Mailman

GNU list manager thing which includes a modern version of PiperMail which was what I wanted (python email->web code). I installed it but was boggled by the complexity so I uninstalled it quick. Life is too short. There are various other list managers I could hack on but it's a matter of finding the small part for presenting the archives amongst the rest of it. Plus I need to be sure they can handle multiple addresses, CC's, attachments, html mail etc.

Mod_Python

So I gave mod_python a whirl. It installed easily enough but I soon ran into a problem whereby if I generated too much HTML (two or three pages) then I'd get a segmentation error and no response from Apache. It's probably another Gentoo version mismatch problem but I didn't want to get involved so I ditched mod_python.

I finally decided upon python CGI. This was easy to set up and gives me total control. No learning curve beyond the cgi module and I can use the IMAP library to access my email.

I've been looking around the options for dynamic web content recently for various projects. One of them is to be hosted under IIS, the other I can do with as I please. The IIS based one required a CMS so that many people can add content. The original request was for something that used ASP, VBScript and Access databases and was not .NET. I searched around and found nothing suitable. Widening the search I came across Drupal which seemed to fit the bill except that it was PHP and MySQL.

At about the same time I was considering this I came across the PHP EasyWindows Installer which installs PHP as a CGI engine which is cool enough, I don't think I have to worry about high volume traffic.

So I installed it and MySQL on Windows XP and it runs a treat. The pages are server up quite swiftly and the package looks quite powerful. It looks nicely designed: a clear segragation between content (all stored in the database) layout (a single, nicely laid out template file that builds all the pages on the site, easily edited in HTML-Kit) and formatting (.CSS).

I'm still exploring it but it is looking suitable for my needs and the person I am doing it for has agreed to install PHP and MySQL on his windows box. I'm not reluctant to learn PHP although I find that after python all the $s in the code look like noise: not as bad as perl (from which it is distantly related) but nearly so. The drupal source that I have looked through is quite clean. There is not a great deal of documentation but that is par for the course. I might not even need to write any PHP, I've found modules to do most of the things I want.

One nice thing about Drupal that Python Desktop Server is lacking: a button to preview messages before submitting them.

For my other project I am looking into a way to present email archives on a web site. I've looked through various options, none of which appealed:

HyperLink

Crude presentation, no Gentoo ebuild.

MHonarch

Gentoo package failed to emerge

Macho

Nice presentation but an embarrasing name to search for on the net and also written in lisp (((ugh))). I don't really want to install yet another language and I certainly don't want to learn lisp.

Mailman

GNU list manager thing which includes a modern version of PiperMail which was what I wanted (python email->web code). I installed it but was boggled by the complexity so I uninstalled it quick. Life is too short. There are various other list managers I could hack on but it's a matter of finding the small part for presenting the archives amongst the rest of it. Plus I need to be sure they can handle multiple addresses, CC's, attachments, html mail etc.

Mod_Python

So I gave mod_python a whirl. It installed easily enough but I soon ran into a problem whereby if I generated too much HTML (two or three pages) then I'd get a segmentation error and no response from Apache. It's probably another Gentoo version mismatch problem but I didn't want to get involved so I ditched mod_python.

I finally decided upon python CGI. This was easy to set up and gives me total control. No learning curve beyond the cgi module and I can use the IMAP library to access my email.