currency exchange fixes

I have had some trouble recently with my Currency graph site. The data for this site comes from an email subscription I have to an daily feed. The emails were identified by Procmail on my server, and piped into a script which parses them and inserts the rates into the database.

The initial problem I noticed a couple of weeks ago was that the graphs hadn’t been updated for some time. On investigation it looked like something was core-dumping during the Procmail processing. However when I found the email and piped it into the script ‘by hand’ it worked ok. I couldn’t figure out what the core-dump issue was, so I worked around it by removing that rule from my procmailrc and setting up a separate cron job to find the emails and pipe them in.

The next problem I noticed was that the data hadn’t updated past when I had set up the cron job. It turned out my script was reading emails from the ‘cur’ (i.e. read) directory under my Maildir, but not the ‘new’ (i.e. unread) directory. A quick change fixed that.

A more serious problem was that the script appeared to be inserting duplicate records each time it ran. I had designed it to check for existing records, so didn’t think it should be doing that, and looking at the code it looked correct. I tried running the script with SQL debugging turned on, to see what SQL Rails was generating, and then ran the same query directly in mysql.

This showed that Rails was not formatting the timestamp correctly for mysql, and therefore was not getting a match when it should. Some web searching found that this was a known problem that had been fixed in Rails 2. So a simple upgrade was all that was needed, plus some quick SQL statements to clean up the duplicate data.

CREATE TABLE rates_bak AS SELECT * FROM rates;
INSERT INTO rates(currency_id, rate, reported_at) SELECT DISTINCT currency_id, rate, reported_at FROM rates_bak;

While I was looking at the application I also figured out a simple way to make the Y-axis scale a bit more usable – just ensure the maximum number is a multiple of 4, since there are four horizontal lines, this ensures they are each a whole number. It doesn’t do this when the maximum is less than 1 or 2, since those work ok with in between lines at 0.5 or whatever.

Post a Comment

Your email is never published nor shared. Required fields are marked *