Recently in WebDev & Code Category

This post will (hopefully) remind me in the future how to do something very simple.

A crontab is normally running one command on each line. EG:

# m h dom mon dow command
01 00 * * * /home/cronscripts/vine-warn-when-large-user-profile
05 00 * * * /home/cronscripts/vine-get-remote-pc-backup.sh
01 01 * * * /home/cronscripts/vine-samba-users
01 02 * * * /home/cronscripts/vine-database-backups

So the above jobs run at 00:01, then 00:05, then 01:01am then 02:01am.

Instead, I can run these cron tasks one after the other by putting && between each task. EG:

# m h dom mon dow command
01 00 * * * /home/cronscripts/vine-warn-when-large-user-profile && /home/cronscripts/vine-get-remote-pc-backup.sh && /home/cronscripts/vine-samba-users && /home/cronscripts/vine-database-backups

So these jobs start running at 00:01am. When vine-warn-when-large-user-profile finishes, vine-get-remote-pc-backup.sh starts immediately, and so on.

Why is this useful?

I have several backup scripts for our linux servers. Each script creates a tar archive of a directory, compresses it, SFTP's it off site, then deletes the tar archive from the server.

If the scrips all run at the same time, the server runs out of disk space. I need them to run one after the other. I can't effectively guess when to start each one. The backup can't start until after midnight and must be finished by 7am when users are likely to start work again. Some directories can take minutes to archive, compress, send, delete. Others can take 2 or 3 hours. One server also collects backups from a couple of other places, so that script has to complete before others start too.

Running this scripts concurrently means no useful transfer time is lost and I don't have to guess the time each script will take to run.

In the hope I find this post next time I'm trying to monitor a SmartArray drive controller, A fresh install of Ubuntu 10.04, installing package cciss-vol-status didn't seem to work. I couldn't even find the file on the system using 'locate'.

For some reason, it installed as cciss_vol_status (using underscores instead of hyphens).

Sigh. Here I am at work on Tuesday morning. List of jobs to do being interrupted by our web server triggering over load alarms. Actually, it's been doing it for quite a while, but I've never sat down to analyse the logs to find what's happening to trigger the alarm (our gandi.net virtual server is more than powerful enough to cope, so fault finding has been low on my to do list). This morning as I walked to work I saw an overload message arrive in my email. The sun is up, the sky is blue, it's 8am. It feels a good day to fault find...

It didn't take long to find the problem. I used grep to pull out todays log entries from the apache log and put them into a temporary file

me@server4:/path_to_logs/rkbb.co.uk$ grep '06/Apr/2010' apache-log > check.txt

The bot causing the problem has a user agent of "Mozilla/5.0 (compatible; Purebot/1.1; +http://www.puritysearch.net/)", going to puritysearch.net I find a 'search engine' that doesn't appear to do anything but display adverts disguised as search results.

So, how to stop this bot. Nice bots read a file called robots.txt which tells them where they're allowed to go. Purebot didn't read the robots.txt so I couldn't excluded it there.

My next thought was to use apache to exclude the user agent. After an hour or so of trying I gave up with that (it is possible, I just didn't figure it out and took the easy for me approach). The site is running Coldfusion (actually BlueDragon) so in the Application.cfm I can check the user agent and stop processing requests from Purebot there.

<cfset useragenttest = find("Purebot",#cgi.http_user_agent#)>

<cfif useragenttest GT 0 >
  <p>Purebot banned</p>
  <cfabort>
</cfif>

The code isn't my most elegant but it works. Next time I come across a badbot (or Purebot changes it's name) I'll just updated this piece of code to ignore their requests.

I treated myself to a rails training course last weekend (with Well House Consultants, rather good, I'll write about it if I get around to it). Immediately I start to create my first ruby app and I forget all the sqltypes I can use in my Model and where to look them up. As I think I'll be looking them up quite a lot, I've put them here on my blog for my later reference

:primary_key,
:string,
:text,
:integer,
:float,
:decimal,
:datetime,
:timestamp
:time,
:date,
:binary,
:boolean.

Options that I can use in my migration
I must remember to specify the decimal precision I need!
:precision [1..63], :scale [0..30]. Otherwise Mysql Default is (10,0).

* :limit - Requests a maximum column length. This is number of characters for :string and :text columns and number of bytes for :binary and :integer columns.
* :default - The column's default value. Use nil for NULL.
* :null - Allows or disallows NULL values in the column. This option could have been named :null_allowed.
* :precision - Specifies the precision for a :decimal column.
* :scale - Specifies the scale for a :decimal column.

These came from: http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/TableDefinition.html

This is a note for my future self, because something tells me I'll come across the same problem again....

The old server ran Colfusion MX 6.01, the new server is running Open Bluedragon, the near equivalent to Coldfusion 7 or 8. (Colfusion MX6.01 doesn't work properly with Apache 2, or more recent linux operating systems and I couldn't justify the upgrade cost to the latest version of coldfusion. My next big project will be to rewrite the Roots web site into an open source language though as Open Bluedragon runs coldfusion code and is open source, I might be able to develop rather than re-write from scratch, anyway, enough of that background).

Things change between program versions and to my horror I found the shopping cart wasn't working yesterday. The blame lies with me, I didn't properly test and I should have spotted it before the server move. Hey ho, 3 hours to debug and fix, and the problem was with

<cfset this = val(evaluate(listgetat(
   form.FIELDNAMES, listcontains(
      form.FIELDNAMES,"_ID_"))))>

The Fieldnames are coming out as all lowercase, and the original code was written to handle the form.FIELDNAMES returning all uppercase.

If i've a bottle of white wine at home, I'll open it and have a glass to celebrate my satisfaction :-)

For some time I've had trouble installing Ubuntu Server to test things. Affecting 7.10 & 8.04, but only at work and not at home. Strange. Now though, I've just figured out what the problem was, at least, I thought I had.

At work, we have a gateway/DHCP box (say: 192.168.55.254). When handing out IP addresses, it gives a nameserver IP of 192.168.55.225 first, and should that not be working 208.67.222.222 second (www.opendns.com)

When booting, the Ubuntu box created it's /etc/resolve.conf with:
nameserver 192.168.55.225
nameserver 208.67.222.222

I edited and saved that file to read:
nameserver 192.168.55.254
nameserver 192.168.55.225
nameserver 208.67.222.222

so adding the gateway as the namesever (our gateway can be the name server but I had a particular reason for not having it that way).

Rebooted, tried my 'sudo apt-get install ...' again, everything resolved and the essential updates happened. However, when looking to write this post for when I next have the same problem, I've noticed the /etc/resolve.conf file has returned to it's earlier two entries.

I'd assumed that the Ubuntu server install expects the gateway to be at the same IP as the nameserver. This is true for my home (which is why it worked at home without trouble but at work it didn't). I'm not so sure now, maybe it was just rebooting a second time after the install that fixed it. At least it works.

The thing I hate most about learning a new programming language is the time it takes to do the simplest things... something goes wrong and it takes hours to figure it. Sure, it's worth it once you know but boy do I hate that time consuming learning process.

Meet today's problem.

NameError in StoryController#index
uninitialized constant StoryController::Story
RAILS_ROOT: C:/Documents and Settings/sroot/My Documents/NetBeansProjects/scaffoldlearning
Application Trace | Framework Trace | Full Trace
C:/InstantRails-1.7-win/InstantRails/ruby/lib/ruby/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:478:in `const_missing'
app/controllers/story_controller.rb:5:in `index'

Having spent 6 hours, googled as many variations as possible, I've finally tracked the problem is something to do with activesupport-2.0.2. I know this only because an earlier ruby program I created following a tutorial works without problem using the same code. When I purposefully break the earlier program, it's error reads:

NameError in StoryController#index
uninitialized constant StoryController::Storys
RAILS_ROOT: ./script/../config/..
Application Trace | Framework Trace | Full Trace
C:/InstantRails-1.7-win/InstantRails/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.2/lib/active_support/dependencies.rb:477:in `const_missing'
#{RAILS_ROOT}/app/controllers/story_controller.rb:7:in `index'

A similar error message but on a different version of activesupport. Unfortunately I have no idea how to fix this (or downgrade my rails install, or know if it's a good thing to downgrade my install - will that just break something else?), so that will now have to wait until tomorrow.

When learning PHP I spent hours fixing errors simply because I forgot to put a semicolon ';' at the end of a line

Creating my first Ruby on Rails application today I've just spent hours on one small part just because I forgot the equal '=' sign in the view file where I wanted to see things

<% can anybody hear me? %>
nope.
<%= We hear you now Steve! %>
hooray!

Backups are important. This tenet will be learned the first time you lose important files. Knowledge in itself is useless, it's the application knowledge that reaps rewards.

Having learnt many years ago the cost of losing data from a computer, I've become a dab hand at setting up backup routines for my laptop, desktops and servers. In the past I used a windows shell script to copy files from my laptop to a space on my office server. That server backs up again to an off site server just to make sure the data is kept. Unfortunately this script has been somewhat unreliable of late. At some point, on a never quite identified file, the copy action would fail and backup would stop. I needed a new solution, one that would be reliable, simple to set up and cost nothing but setup time. I found it in the shape of some linux software called rsync and a windows client to rsync called DeltaCopy. Actually, DeltaCopy is more than just an rsync client, it can be an rsync server for windows machines but I didn't need that. That would be very useful though if you are using an old windows PC as your file server though.

What's rsync?
From their web site: rsync is an open source utility that provides fast incremental file transfer. rsync is freely available under the GNU General Public License.

I've known of rsync for years, but never used it until now. Essentially the programme will compare files in two directories, if a file has been updated it will copy the updated parts and not the whole file. My script solution copied everything whether or not it needed to be. As I'm on the same network as my backup server bandwidth really isn't a problem. However, the rsync solution means I will be able to succesfully backup from home over the VPN.

Setup was really easy.
First set up our linux server to run rsync as a daemon. That means it runs all the time waiting for other rsync programs to connect to it. That's the same way a web server like apache works, sits there waiting until it has something to do, does it, then waits again. How to do that will depend on your server software but for my Trustix powered server it was simply "swup --install rysnc-server" and it was downloaded and installed automatically. Trustix has reached end of life now, so if you are looking for a new operating system you'll find rsync on most ready to go, including redhat and ubuntu.
Second set up my laptop to use an rsync client. That includes choosing which folders I want to synchronise with the server
Third, enable the rsync client to run as a scheduled task on my laptop.

This is where the DeltaCopy program is so useful. It's a windows point and click graphical interface. Installation was a breeze and I confess I didn't read the instructions to see how it worked it was so simple. You create a "profile" for each synchronisation task you want. For me there's only one, I called it "laptop backup". Then add all the folders (or specific files) you want backed up followed by the server details. At the bottom of the profile is a section called "schedule". DeltaCopy links seamlessly to the windows scheduler, so I set my backup to occur every day at 11am. Later I found settings that let me get an email on whether the backup worked and how well it went. If it works, the email includes the rsync result information too.

Today the first success email arrived at 11.07. In 7 minutes the folders had been synchronised and my backup completed. It's so fast because only changed files have been copied across the network. The full backup is a huge 21Gb.... perhaps that explains why the script would fail, 21Gb over a wireless network would take.... a long time.

The final step of any backup: Test it worked and test regularly. Testing is easy using this method, just open the file from the server over the network. To think of all those hours I used to spend waiting for a file to be recovered from my TR1 tape backups.

I've been investigating ways of improving our business systems to keep up with our growth.

One system I've been investigating is the open source "Web ERP" which is a system using PHP and MySQL. It looks good and I'm now at the stage of deeply investigating how it works and how moving onto it would affect our processes. In order to do this I needed to install it on a server. I really don't like installing things on our production server so VMWare comes to the rescue once more. VMWare is a piece of software that lets you have a virtual computer running on top of your regular operating system (Windows XP for my laptop). So, to test weberp I installed my favourite Trustix Linux operating system.

As Web ERP is open source, I've shared my Virtual Machine to save others the time of creating their own server to test on.

If you're here to get the Virtual Machine, here is the current link:
weberp.zip (84Mb)
The zip file includes some important instructions you should read before you virtually power on the server. Remember, you'll need VMWare player or equivalent from http://www.vmware.com (about 30Mb) in order to run the machine.

Let me know any feedback or questions. If demand is high I'll set up the FTP server, otherwise I expect the http service will suffice.

I should also warn you that the normal free software caveats apply (use at your own risk, no warranty etc).

I've been really busy since my last post (a whole month without a post!). Work is exceptionally busy and work comes first. Even my reading has slowed down a bit. I have several things waiting to be posted when I get time to write about them - including the first ever competition for this blog! You'll have to check back over the next week or two to find out what that's all about, in the mean time this post is all about...

"creating a new column in a MySQL select query"

So, here I am manipulating some data from a supplier to make it work with our internal business systems. Essentially I had to work out how much a kitchen cabinet would cost given an "assembly list", "component list", "decision list" and of course "cup of coffee".

Assembly list tells me that each unit needs a cabinet and a door. There are lots of door colours and lots of cabinet colours. These options are all in the component list which tells me the price of each door. The decision list is my own creation and says "build me a unit using a Maple door and a Maple cabinet". "cup of coffee" feeds my habit while I code. So far, so good.

Once I've created a list of units using maple doors and maple cabinets, I then want to create another list of oak doors and oak cabinets. Join them all together and I have a complete list of cabinets that customers can buy. I needed a way of identifying which cabinet was built with which options and for the life of me I couldn't remember how to do it. I needed to create an additional column in my query that would record what "decision list" row had been used for this build.

The answer is of course obvious once you know it:
Select "StevesDecision" as StevesColumnName, FirstBuildUpStaticTable.Material, SUM(`Gross weight`) As GWeight, and so on.

"StevesDecision" becomes the content of each row and "StevesColumnName" becomes the column name. All the rest of the select... line remains the same.

First lesson over! That was fun. Just like starting sixth form in so many ways. Meeting all the new people but with easier opening lines like "so, where are you from?". Thinking of which, I should put this in the Root Memory category as I remember starting my Business Studies A levels and meeting my friend Wendy...

Nice group, good sense of humour. There were 8 of us tonight, should be 10 next week. This was just a familiarisation night to check the software worked for all of us. We came from all over the world, or to be precise USA, Canada, Holland, Romania and me from the UK. The instructor is a chap called Ben Ramsey. We can hear him but we can only speak when he 'passes the mic' - students that can't answer back? It must be teacher heaven! He speaks clearly and I'm sure he knows his stuff.

I'm guessing, but I think he's the Ben Ramsey at http://benramsey.com/, in which case, he really does know his stuff!

I think I'm going to enjoy this course.

My return to education begins in just 2 hours. I say return but of course I've never stopped learning. However, I've signed up to a formal training course and paid a fair amount of money for the privilege.

The course is "Zend PHP Essentials". PHP is a programming language commonly used on web servers. It's core functionality is about generating web pages. It allows a programmer to generate those pages from a database, as well as many other things like create PDF files. As with many languages, you can use it to start other programs that aren't really related to PHP or web pages. It's one of the most popular programming languages for web site because
a) It's free (well, it 'open source' which means you can use the software for free but there are other obligations - like you can't alter it and then sell it)
b) It's now very popular (which means its on almost every server set up going and if you get stuck the chances are someone you know can help you fix it).

I have dabbled in PHP from time to time but I am a self taught programmer. I had a problem, I knew a computer could help me solve it, so I learnt what I needed to solve it and moved on. I have a big project coming up at work as I continue to improve our business systems to meet our growing needs. One of those is to move our MS Access based system that runs our invoicing onto a web based system. MS Access is fine in small groups, but we are pushing it's capacity to it's limit. I had considered Adobe/Macromedia Coldfusion but in order to use that in our office I would have to buy another licence (we already have one for our web server, the www.rootskitchens.co.uk site uses coldfusion). The licence isn't too expensive at around £900 but if ever I want to have a second server running (second site for Roots perhaps, or I create a website for another interest and need to host it on another server) then I would have to spend another £900. Instead, I chose to spend that money on a proper Instructor led course on PHP.

What did I mean by "...in a distant sort of way"? Well, I really wanted an instructor led course. I tried the local colleges and universities. I emailed some people mentioned on their web sites too, but there are no PHP specific courses they are running or plan to run. To be taught by an instructor I would have to go to London for a week. I thought fine, I'll find someone and pay them directly to teach me. Alas, I still didn't find anyone. Then I found the Zend course. It's instructor led but the instructor is California. Now that's what I call distant learning!

They have a web based (of course!) interface which I will be logging into to talk with the instructor and other students. I can see what he writes, the slides he shows, hear what he says, put my virtual hand up to ask a question, type a message and even interrupt him (using a microphone he can hear me too).

The downside? Well, I can't throw pen lids at my friends in class (sorry Fiona!).

The time zones worked well for me too. The lessons start at 6pm BST so it's just like Adult Education would have been. This type of online instructor led training could be a good thing - I'll let you know how it goes.

I just noticed my laptop was turned on 5 days and 4 hours ago (yes, it's Friday today, and yes, I do work Sundays). Now, if only I could work for that long without a rest....

Phew! What a Christmas. While many spent the time enjoying Christmas TV, I spent the time migrating from one web server to a new web server.

Easy? I was hoping so. Way before the move, I installed RedHat on my laptop to test the setup to ensure it went smoothly. It worked fine so my 3 days (24 hours) of set aside time [to set up new server, copy across all the web sites (approx 10Gb of data), transfer IP addresses from old server to new server] seemed an over estimate.

Unfortunately, a simple mistake in the file /etc/hosts stopped ColdFusion MX (the software used to run the business web site) from installing properly. It was slow, it wouldn't initialise Java. It gave me many many hours of grief. It worked perfectly when I installed on the laptop. The problem was simple the wrong IP address in /etc/hosts. That tells the server what it's address on the internet is (you can find out your address current internet address by going to http://whatismyip.com). Now, the server was set up BEFORE the firewall was put in place. So when set up it's ip address was something like 212.100.224.135. When the server has a firewall, the firewall takes the public IP address and forwards things on a private network to the server. So the address in /etc/hosts should have been change to 192.168.1.135.

I often find that things as simple as one line or dot out of place can create a computer nightmare. If only I knew what the problem was before I spent the 12 hours looking for it!

After that, most things went OK, although it took a few hours to get the server encryption working properly (I had to rewrite several parts of the ColdFusion code as RedHat ES4 has some different user names than RedHat ES2 did - which is what our previous server ran on) and some of the default PHP settings had changed which took me a few more hours to locate. The simple job of transferring IP's from old server to new server to far longer than expected.

Finally, it's all up and running. Though there are a few things I want to spend a little more time on.

The server upgrade was due to ColdFusion playing up. The new server has a faster processor (64bit AMD!) and 512Mb Ram. The old server had 256mb (the minimum recommended for ColdFusion). 512mb may not sound like a lot, but servers don't have monitors and don't need to display graphics. All they do is crunch data and deliver the results over a network connection. At the moment, all of the programs are running in Memory with no swap space being used. Therefore visitors should get to see pages slightly quicker.

Now that's all done, I can move onto my next project...