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"

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).