Tuesday, January 22, 2019

A subtle error when initializing multiple variables on one line in Python

From a code-style perspective, I don't often like to put multiple variable initializations on a single line, but once in a while it makes sense to do so:

foo = goo = None

Today, I ran into a subtle bug that can result from initializing multiple variables on one line, when the variables are of mutable types such as lists.

I was writing code to populate two lists, make some changes to the contents of each list, and then compare the two lists. Something like this:

list1 = list2 = []
list1.append(1)
list2.append(2)
print 'Are they equal?', list1 == list2  # True !!!

The output, to my surprise, showed that the two lists are equal. Upon changing the code to initialize each list on its own line::

list1 = []
list2 = []
list1.append(1)
list2.append(2)
print 'Are they equal?', list1 == list2  # False

... the lists are now shown to be unequal, as I expected. Here's why...

When the right-hand side of a Python statement is a mutable variable, nothing is created in memory. After the assignment, both variables refer to the already-existing object. (Thanks to https://medium.com/broken-window/many-names-one-memory-address-122f78734cb6 for this explanation).

In the assignment statement list1 = list2 = [], the right-hand side is list2 = [], which -- although it's also an assignment -- is a list and therefore mutable. This makes list1 an "alias" for list2, leading to the unexpected result.

If you'd rather never have to think about this, then it's reasonable to just avoid initializing multiple variables on the same line.

Monday, September 24, 2018

A more granular view of DynamoDB throughput using CloudWatch

DynamoDB, Amazon Web Services' NoSQL database in the cloud, provides guaranteed levels of read and write throughput, measured in units called RCUs and WCUs. The only catch is that you must specify the read and write capacity in advance. If the actual consumed capacity exceeds the provisioned capacity, throttling occurs, and read or write requests may be rejected.

You can set a CloudWatch alarm to alert you if consumed capacity comes close to exceeding provisioned capacity.

And it's easy to view the provisioned and consumed capacity, along with any throttled requests that may have occurred, on a table's Metrics tab in the AWS console. Below is a three-day graph for one of my tables, showing that some throttled reads occurred at 18:00 UTC on Sep 22.




By zooming in on the time range in question and setting the period to 1 Minute, I was able to get a more precise view of the read throughput at the time of the throttling.




But the console doesn't tell the whole story. In fact, it appears as if consumed capacity never exceeded provisioned capacity around 18:00 UTC. So why was there throttling?

To troubleshoot the cause of spikes in consumed capacity, I needed to view more granular metrics that are only available in CloudWatch. CloudWatch is accessible via the AWS API, command-line interface, or console. Here, we'll focus on using the console to see detailed CloudWatch metrics for a DynamoDB table.

Since I use multiple AWS services extensively, there are thousands of CloudWatch metrics. To find the relevant one, I started by filtering on my DyanmoDB table's name in the search box.





Next, I clicked to expand DynamoDB -> Table Metrics.



From the list of table metrics, I selected the relevant one, ConsumedReadCapacityUnits.






Next, I set the time range to the period from a few minutes before to a few minutes after the throttling occurred. I clicked custom, selected Absolute, and entered the start and end dates and times.




The resulting graph still didn't reveal the information I needed. It made it look as if there was only about one read per minute. My table's provisioned throughput was more than 600 reads per second, so why the throttling?



My next step was to click the Graphed Metrics tab.



That allowed me to use dropdowns to set the Statistic to Sum (rather than the default, Average) and the Period to 1 Minute (rather than the default, 5 Minutes).


With these settings, I could finally see a short-lived, but huge, spike in consumed read capacity. This spike of more than 32,000 reads in one minute -- invisible in the 5-minute average -- was now clearly displayed.

I hope this helps you if you're ever faced with isolating the nature and timing of an unexpected spike in read or write activity on one of your DynamoDB tables or indexes.

Friday, February 16, 2018

Copying an EC2 AMI between regions with boto 2

There are some good articles about copying an Amazon Machine Image (AMI) from one region to another, such as this one. It rightly states that copying can be accomplished using the console, command line tools, API or SDKs. I chose to use an SDK, specifically boto 2, but was unable to find clear instructions. I'm pleased to present a short Python script that shows how to do it.

The script is pretty basic -- no AWS authentication (so you'll need to provide an AWS access key ID and secret access key for an account with appropriate permissions), error handling, etc. But it works.

Here's the whole script. The comments explain some of the more interesting points.

#! /usr/bin/python2

from boto import ec2
from datetime import datetime
import time

COPY_FROM_REGION = 'us-east-1'  # Region to copy from. Change this if you like.
COPY_TO_REGION = 'eu-west-1'  # Region to copy to. Change this if you like.
AMI_ID = 'ami-XXXXXXXX'  # Change this to the ID of the AMI you wish to copy. This AMI must already exist.

ec2_conn = ec2.connect_to_region(COPY_TO_REGION)  # Boto 2 interface to EC2.

# Give the target AMI a unique name. Not truly necessary, but convenient. I chose seconds since epoch as a source
# of uniquness. The name can really be anything you want.
timestamp = int((datetime.utcnow() - datetime(1970, 1, 1)).total_seconds())
ami_name = 'eu-copy-of-{}-{}'.format(AMI_ID, timestamp)
print 'copying AMI to {}'.format(ami_name)
ec2_conn.copy_image(COPY_FROM_REGION, AMI_ID, name=ami_name)  # Initiate copying.

# Now we wait...
while True:
    # The image won't even show up in the target region for a while. Wait until it exists.
    images = ec2_conn.get_all_images(filters={
        'is-public': 'false',
        'name': ami_name
    })
    if images:
        image = images[0]  # Now the image exists in the target region.
        print image.id, image.state
        # Now wait for the image to be in the "available" state. This could take a few minutes, especially if it's big.
        while True:
            images = ec2_conn.get_all_images(filters={
                'is-public': 'false',
                'name': ami_name,
                'state': 'available'
            })
            if images:
                print 'available'
                break
            print 'not available'
            time.sleep(10)
        break
    print 'not found'
    time.sleep(10)


One final tip: I wasn't able to find documentation of the properties of the image objects returned by get_all_images. But Python offers an easy solution: just print out image.__dict__.

Happy copying!

Wednesday, September 13, 2017

A plumber's guide to software troubleshooting

The other day, my downstairs neighbor knocked on my door and delivered the alarming news that water was dripping from the ceiling of his laundry closet, that is, from my floor.

Bear with me. This story really does relate to software engineering...

I apologized to my neighbor and started investigating. In addition to the laundry closet occupied by a stacked washer and dryer, the bathroom includes a tub, a shower stall with a glass door, and a vanity with two sinks.

I found no visible sign of water in the laundry closet. I did notice some dampness and water damage inside the vanity cabinet under one sink. "Aha," I thought, "now it will be easy to find where the water is coming from!" Expecting to find a leaky supply or drain pipe, I ran the water, but there were no drips. Then it occurred to me to splash some water on the counter top, as might happen during shaving or face washing. Sure enough, a little water leaked into the cabinet. It appeared to be entering where the faucet passes through the counter. Looking more closely, I discovered the faucet's metal fitting inside the cabinet was corroded. "Aha!" I thought again.

I considered trying to repair it myself, but decided it was beyond my skill and called a plumber. When he arrived, I explained the situation with the neighbor, and pointed out the water damage and corrosion I had noticed. He ran the sink tap and replicated the problem to his satisfaction. He said the corroded part was beyond repair and I would need a whole new faucet set. I'd have to order it myself at a cost of several hundred dollars, plus more than a hundred dollars for a second visit by the plumber to install it.

Discouraged by the price, I decided to investigate further. It occurred to me to splash water onto the backsplash behind the counter. Sure enough, it leaked into the interior of the cabinet. "Aha!" I thought for the third time. This must be the real problem, and I could fix it myself with a simple tube of caulk. I caulked the gap between backsplash and wall, tested it by splashing lots of water up there, and found that the leak was gone. "Victory!" I assumed.

It did seem odd that a small amount of water in a cabinet several feet from the laundry closet could be the cause of my neighbor's complaint. But I've certainly heard of cases where water follows beams, pipes or wires and pops out in surprising places. And when I bumped into the neighbor a few days later, he said there had been no further leaks.

However...

A few days later, after taking a shower, my wife complained about a puddle on the bathroom floor, something we had never noticed in the past. Over the next few days, some -- but not all -- showers, resulted in a large puddle. These puddles started near the shower door and extended all the way to the laundry closet. Two things seemed obvious. First, water must be escaping around the edges of the shower door. This didn't surprise me, because the door was edged with a plastic gasket that didn't fit perfectly against the shower enclosure, and didn't extend all the way to the bottom of the door. Second, water from these puddles, extending into the laundry closet, might be the real cause of my neighbor's ceiling leak.

Again, I set out to replicate and isolate the problem, which was difficult because it didn't happen with every shower. First, I ran the shower unoccupied for about 10 minutes, to see if any water made its way out past the door. None did. I concluded someone needed to be inside the shower, with water splashing onto the door and door sill. By splashing water there, I was able to get a significant puddle to form.

But my test puddle was right next to the shower, whereas the puddles that had formed spontaneously were inexplicably a few inches away. I was forced to the conclusion that a leaky shower door was not the culprit. Perhaps a supply or drain pipe was at fault. I realized that in my previous tests, I had run a cold shower, so as not to waste hot water. Maybe there was a problem with the hot-water supply pipe. Sure enough, when I ran a hot shower for several minutes, a puddle appeared near the closet and spread toward the shower!

I figured there must be a leak in a hot-water supply pipe somewhere in the laundry closet. I was unable to move the 300-pound washer and dryer myself to inspect, and was again forced to call a plumber, a different one this time. When I described the problem on the phone, he diagnosed it without even seeing it! He agreed to come over, take a look, and hopefully fix it on the spot. His conclusion: Water was backing up from the shower drain and flowing out of a drain in the laundry closet floor. Snaking out the shower drain's U-trap would eliminate the backup and solve the problem. I was skeptical, but he arrived, reproduced the problem, snaked the shower drain, and verified that the problem was fixed. There have been no puddles ever since. "Aha" for real, at last!

So what does all this have to do with software engineering? It's a textbook example of many pitfalls that can mislead an engineer when trying to find the cause of an problem. Let's look at the situations and mental errors that confounded my plumbing investigation...

- It's easy -- but wrong -- to rationalize away things that don't quite fit the evidence. I was too ready to believe that a little water under a sink could travel several feet to the closet and soak through to the floor below. And I was also too ready to believe that a puddle near, but not adjacent to, the shower door was formed by water leaking around the door.

- Sometimes experts don't know best. The first plumber didn't correctly pinpoint the cause of the water leaking into my neighbor's apartment, or even the water dripping into the vanity. His proposed solution, at a cost of several hundred dollars, wouldn't have helped.

- Don't mislead the experts. Perhaps if I had simply told the first plumber water was leaking into the laundry closet downstairs -- and nothing else -- he would have investigated and found the clogged drain. However, it certainly didn't seem wise at the time to withhold the information I had discovered about water damage and a corroded plumbing fixture in the vanity.

- Sometimes experts DO know best. The second plumber knew that laundry closets have floor drains. He also knew that sometimes these drains are connected to the same waste pipe as another drain, in this case, the shower drain. That's just not something I, as a non-plumber, would ever have known. And I couldn't see under the 300-pound washer/dryer stack to find out for myself.

- The seemingly obvious cause of a problem might not be the real cause. The corroded faucet fitting seemed like an obvious source of leaks. So did the incomplete and poorly-fitting shower door gasket. But neither one was the real cause of the trouble,

- There can be more than one problem. There really was a small leak into the vanity cabinet, and it needed to be fixed, and was fixed. But that didn't solve the main problem, which was the result of a backed-up drain.

- It's easy to see patterns where none exist. I was convinced, after methodical testing, that the puddle formed only when running hot water in the shower. That wasn't true at all. Actually, the problem was sporadic: sometimes the shower drain backed up enough for water to flow out of the laundry closet drain, and sometimes it didn't.

I hope these tips will help you the next you need to find the cause of a software error. And I hope you don't experience any plumbing problems!

Friday, September 1, 2017

Using SQL date_trunc to group log records for troubleshooting

Did you ever notice a recurring error message in your log files, only to wonder if the problem has recently become more severe, or has actually been happening -- unnoticed -- for quite a while? If your log data is in SQL format, or any format you can query using SQL, a good technique is to group the records by time period to see if the error has become more prevalent over time.

Here's a real-world example. In a SQL table named log, which includes columns named timestamp (the time the record was written) and message (text giving details of what was logged), I stumbled across many records where message  had the value 'ConnectionError in application_cache'. I wanted to learn whether this error had suddenly become more frequent. This query gave me the answer:

select date_trunc('day', timestamp) as d, count(*) from log where message = 'ConnectionError in application_cache' group by d order by d

The date_trunc function takes a timestamp and "rounds it down" to the nearest specified unit -- the nearest day in my example. Instead of 'day', you can specify any unit from 'microseconds' all the way up to 'millennium'.

The results of my query, a count of the number of matching messages per day, looked like this:

That told me what I needed to know: This message happens many times per day, and the frequency hasn't increased recently.

My database is PostgreSQL 9.x, and the PostgreSQL date_trunc function is documented here. Whichever SQL database your using, a similar function is likely available, and may come in very handy.

Tuesday, August 15, 2017

Using old commits in GitHub to troubleshoot -- and fixing a common SQL error

By this morning, evidence was mounting that something was wrong -- very wrong. Support reps were reporting that customers' files weren't getting processed promptly. They had already mentioned a few days ago that some video clips uploaded by customers weren't appearing when expected. And this morning, our real-time dashboard showed that no video files had been uploaded in the last 24 hours, when this number is typically in the hundreds.

I realized that all the affected features had something in common: they depend on a scheduled job that processes CSV files for every customer several times per day. A check of audit logs revealed that this job was writing more than 10,000 log messages per day through Aug 11, but fewer than 200 per day starting on Aug 12. Clearly, something was not right with the scheduling!

Our software engineers note all deployments in a Slack channel, which showed we did a deployment on Aug 11. But how to figure out exactly how and where the problem was introduced? That's where GitHub's ability to compare commits came in handy. Here's how I used it:

1. Log in to GitHub, navigate to the repo, and click commits.



2. Page through the commits until one with a date shortly before the suspect deployment is found. I chose one from 5 days ago.



3. Copy the commit hash. Then use GitHub's compare route to compare that commit to master. This route requires two commit hashes or branch names, separated by three dots, e.g. https://github.com/<my account name>/<my repo name>/compare/23948e9f51d0b6c248193e450df0a7e2c3077037...master.


4. Click Files changed.


Now it's just a matter of scrolling through the diff to see which changes might be relevant to the problem. In this case, it was easy to spot: a SQLAlchemy query in the module that processes CSV files had been modified. With the problem isolated to a few lines of code, it was easy for me to figure out what I had done wrong when changing the query.

It turns out that, when I added a new condition to a SQL query, I made a common mistake. A comparison of any value to null is always false. That's true even if the comparison uses the not equals operator! So this query:
select count(*) from config
where ftp_format_ex != 'add_photo_urls_to_inbound_feed'

was finding only 370 rows -- the ones where ftp_format_ex not only was not equal to 'add_photo_urls_to_inbound_feed', but was also not null.

This mistake is so easy to make that I also made it back in 2014, and blogged about it then!

The null-coalescing operator -- in this case, converting nulls to the empty string -- gives the result I really wanted:
select count(*) from config
where coalesce(ftp_format_ex, '') != 'add_photo_urls_to_inbound_feed'

The above query finds 3,528 rows. In the module identified in GitHub, adding coalesce to the query caused all the expected CSV files to be processed and solved the problem.


The moral of the story? Keep good logs, isolate problems by comparing older commits to the current commit in GitHub, and be careful of nulls in SQL.

Monday, July 3, 2017

Using CloudFront access logs to investigate unexpected traffic

Amazon's content delivery network (CDN), CloudFront, has many benefits when serving web content to a large global audience. A small, but important, one of those benefits is detailed logging, which I recently took advantage of to investigate some unusual web traffic.

It all started with a CloudWatch alarm. I had configured CloudWatch to email me if my CloudFront distribution received more than a specified number of requests per minutes. I received such an email, clicked the link to view the alarm details in the CloudWatch console, and accessed a graph plotting requests per minute against time. Zooming out to a two-week scale, I immediately noticed sharp peaks occurring around 20:00 UTC most days.



How to find out where the excess traffic was coming from? CloudWatch and CloudFront offer a variety of graphs with resolutions as detailed as one minute. And CloudFront offers a report of top referrers, that is, websites from which the most requests to the CloudFront distribution originated. However, the shortest time unit the top referrers report covers is one day, not of much use for identifying the source of a traffic spike lasting only a few minutes.

The answer was to consult the CloudFront access logs, which, when enabled, are stored as text files on Amazon S3. Fortunately, logging was already turned on for my CloudFront distribution. If it's not, you can enable it in the AWS Console by selecting your distribution and clicking Edit on the General tab.






The log files can then be found in the specified bucket and folder in the S3 console. Focusing on the peak that occurred around 20:00 UTC on June 24, I typed in a prefix and sorted by Last modified to zero in on the relevant logs. (Tip: timestamps are in UTC on the CloudWatch graph and in the names of the log files on S3, but the last-modified time in the S3 console is in your local time zone.)



Each log is a zipped (.gz) file. Unzipping it yields a tab-delimited text file, which you can open in a text editor, view in your favorite spreadsheet program, or analyze by writing a script. Here are the first few lines of a typical log file:




Following two header rows, each row represents one request to a URL served from the CloudFront distribution. The relevant fields are time and cs(Referrer). The referrer is the URL of the page from which the event originated. I wrote a Python script to read the log files and output a CSV file with one row per request, where each row consists of the time (truncated to the minute) and the domain name.

It was then simple to sort the CSV file by minute and domain name. In this way, I found the domain that was responsible for the excessive traffic in the minutes shortly after 20:00 UTC on June 24. Armed with this knowledge, I was able to contact the owner of that domain and ask why their website was receiving such heavy use around 20:00 each day. (As of this writing, I'm waiting for their reply.)

If you prefer not to write a script, you might instead take advantage of Amazon Athena. Once you define an appropriate schema, Athena lets you query data on S3 without downloading it or writing code.

Many thanks to the engineers at the AWS Loft in New York for pointing me in the direction of CloudFront access logs. I hope you found this article informative and helpful.

Friday, June 9, 2017

Turning unstructured text into a SQL query

It sounds trivial, but querying a SQL database for records that match data provided in an unstructured format such as a text file can be a challenge. The goal is to construct a SQL query with a possible lengthy in clause, without a lot of manual copying and pasting. Consider this example: a colleague supplies a list of customer names in the body of an email message. (The principle is the same if the list is in a text file, a PDF document, or any similar format.) Something like this:

Hey Steve,
Just got this list of customers from Marketing. Could you look up the date of most recent login for each of them? Of course it's an emergency, has to be done before tomorrow's big conference.
Thanks,
Lenny Lastminute
-------------------------------
Acme Anvils, Inc.
Big Bertha's Balloons, LLC
Crazy Cars & Co
Dynamite Dog Food Enterprises
Excellent Eggs, Inc.

Since the list has only five customers, it wouldn't be hard to copy and paste to come up with a query like this:
select name, last_login_date from customer where name in (
    'Acme Anvils, Inc.',
    'Big Bertha''s Balloons, LLC',
    'Crazy Cars & Co',
    'Dynamite Dog Food Enterprises',
    'Excellent Eggs, Inc.'
) order by name

But now let's imagine that the list has hundreds of entries, and the customer table has millions of rows, so we don't just want to retrieve all of them and manually identify the relevant ones. Fortunately, there are some tricks we can do in LibreOffice Calc (or Excel or your favorite spreadsheet program if you're not a Linux geek).

Start by pasting the list into the first column of a blank spreadsheet:

Now place a formula like this in cell B1:
="select name, last_login_date from customer where name in ('" & A1 & "'"

Yep, those are single-quotes (which SQL requires) inside double quotes (which LibreOffice Calc requires).

And place a formula like this in cell B2:
=B1 & ", '" & A2 & "'"

Copy the formula from cell B2 to all the remaining cells in column B. You'll end up with something like this:

Then we just need to close the parens, and perhaps throw in an order by clause and a semicolon, which we can do with a formula like this below the last cell in column B:
=B5 & ") order by name;"

That cell now contains our SQL query:
select name, last_login_date from customer where name in ('Acme Anvils, Inc.', 'Big Bertha's Balloons, LLC', 'Crazy Cars & Co', 'Dynamite Dog Food Enterprises', 'Excellent Eggs, Inc.') order by name;

But we're not quite done. If you have sharp eyes, you may have noticed the SQL syntax error. One of the customer names, Big Bertha's Balloons, LLC, contains an apostrophe, which is the same as a single quote, which is a reserved character in SQL. We can escape it by doubling it. To automate replacing ' with '', change the formula from =B1 & ", '" & A2 & "'" to:
=B1 & ", '" & SUBSTITUTE(A2, "'", "''") & "'"

This results in a valid SQL query, like so:
select name, last_login_date from customer where name in ('Acme Anvils, Inc.', 'Big Bertha''s Balloons, LLC', 'Crazy Cars & Co', 'Dynamite Dog Food Enterprises', 'Excellent Eggs, Inc.') order by name;

I hope this saves you some time someday!

Wednesday, January 18, 2017

Installing Numpy on Ubuntu

I needed the numpy module in order to run one of my Python programs. I'm running Python 2.7.6 on Ubuntu 14.04.

I first tried pip install numpy. No luck. After a while, this error was displayed: UnicodeDecodeError: 'ascii' codec can't decode byte 0xe2 in position 72: ordinal not in range(128). The installation did not complete successfully.

Then I tried sudo apt-get install python-numpy. That did the trick. A tip of the hat to my colleague Collin Stocks for this advice.

A uWSGI Bug: A (Partial) Success Story About Logging and Testing

Users of SpinCar's mobile app recently started mentioning that they were seeing an error message. Not every user was affected, but it happened to enough users that it was clearly not an isolated problem. The error message was far from specific, indicating that something unexpected went wrong without giving any details.

The first place we looked to isolate the problem was MixPanel. We use this service to log events that occur in the app, including nonfatal errors. (We also use Fabric.io's Crashlytics, but that tends to be more useful for fatal errors that actually crash the app.) Fortunately, MixPanel did reveal details about at least one occurrence of the error. We could see that the app had made a particular call to the our back-end API, and that the API responded with an HTTP status of 502 (bad gateway). Furthermore, the full details of the offending HTTP request had been logged.

Our good luck in continued when we tried to replicate the problem. Simply by making the same HTTP request in a web browser, we provoked the same 502 response, using the production copy of the API. The next question was: Could we do so in the development environment, where we'd be able to troubleshoot and test potential fixes? The answer was yes! Running a local copy of the API and sending it the same request once again led to the 502 response.

In the development environment, we were able to see a detailed error message mentioning "invalid request block size." A Google search revealed that uwsgi, which use to serve our API, limits the size of requests, and the default maximum is 4,096 bytes. We corrected the problem by launching uwsgi with the buffer-size parameter to set a larger maximum. The bug was resolved within a few hours of the initial report, with no need to submit a new version of the iOS app for Apple's approval.

That's a success story, and the success was driven largely by two factors. First, adequate logging enabled us to get details about the error, even though the message displayed to users was uninformative, and the users themselves were non-technical customer personnel at distant locations. Second, the development environment closely mimics the production environment. In this case, it was a critical factor that uwsgi was used -- and used with the same settings -- in dev as well as on prod.

But, of course, the success is only partial. Real success would be avoiding the bug in the first place. How did this error slip through the cracks despite the fact that our app and API undergo extensive code review and automated testing? The answer is that the bug only occurs with very large requests, which only occur when users have used the app to create a great many videos -- far more than are created in our automated tests. This bug could have been revealed by the type of testing known as load testing or stress testing, in which actions are performed repeatedly and large data sets are used. There are a couple of reasons we haven't implemented this type of testing yet. One is that the open-source automated testing tools we rely on tend to crash during long test runs. But the main reason is that we haven't yet found the time to develop load tests. It's definitely on the to-do list!

Saturday, December 24, 2016

Amazon LightSail

Amazon Web Services recently announced LightSail, a low-cost service for quickly launching a virtual server with limited options. Fewer steps and less technical knowledge are required to get a simple website up and running than with traditional AWS services like EC2. LightSail seems intended to compete with low-cost virtual hosting services such as GoDaddy's.

Upon selecting LightSail from the AWS management console, it opens in a new tab with its own subdomain, lightsail.aws.amazon.com. This isn't true of most other AWS services, and may imply that Amazon views LightSail in a separate category.

At work, where scalability and reliability are key, I have no plans to use LightSail. However, I did have a chance to put it to the test for a personal project, a new website for my band, The Showoffs. Users with no technology background might not find it easy to get started with LightSail. But if you're familiar with the basics of DNS and virtual hosting, LightSail offers a quick and affordable setup. In about an hour, I went from nothing -- no domain registered, no server, and no content -- to a functioning WordPress site.

Amazon provides documentation here. For my project, the articles on getting started, static IP addresses, and using WordPress with LightSail were particularly relevant. (On the other hand, this DNS article led me astray. It turned out not to be pertinent; all I had to do was create an A record in Route 53.)

The main steps to create theshowoffsband.com were as follows:

  • In Route53, registered the domain name. This required replying to a verification email.
  • In LightSail:
    • Created an instance. I chose the WordPress 4.6.1 instance image and the $5/month instance plan.
    • Created a static IP address.
    • Created a DNS zone, but I think this step was unnecessary.
    • Connected to the server using SSH. A button in the LightSail opens a browser-based SSH connection with a singe click (screenshot below).
    • Obtained the WordPress credentials by running an Amazon-provided script.
  • Returned to Route53 and created an A record mapping theshowoffsband.com to the static IP address. I then waited several minutes for the DNS record to propagate.
  • Visited my new website's WordPress admin page, logged in, and started adding content.
The cost? $12 per year for the domain registration, and $5 per month for the virtual server. If you show up at the Showoffs' next gig, it will have been worth it!


SSH connection to the LightSail virtual server with a single click

Sunday, April 3, 2016

Automated testing of localhost URLs with Selenium, Pytest and Sauce Labs

Selenium WebDriver is a great way to create automated tests of web applications. It can simulate a user's interactions with the a web page and examine the results.

Although it's not required to use a test framework to organize and execute your Selenium WebDriver tests, a good test framework does make this task more convenient. As a Python programmer, I'm a fan of Pytest.

Selenium WebDriver supports a wide variety of web browsers, but is limited to those browsers installed on the computer where WebDriver runs. That's where a service like Sauce Labs comes in. Sauce Labs runs Selenium WebDriver on virtual machines in the cloud, offering many combinations of device, operating system and browser.

So the combination of Selenium WebDriver, Pytest and and Sauce Labs enables testing of a web application across many platforms. There's just one catch. While our web application is under development, we likely wish to run it on our local machine, with a localhost URL. A Sauce Labs virtual machine can't access the local machine to run such tests -- unless we install Sauce Connect. Sauce Connect is free software from Sauce Labs that creates a secure tunnel between Sauce Labs' servers and our machine.

What follows is a step-by-step guide to creating and running tests using Selenium WebDriver, Pytest, Sauce Labs and Sauce Connect. This guide refers to Python 2.7 running on Ubuntu 15.04, but the same principles apply to any programming language and operating system.

Let's start by creating a web app to test. There are many ways to do this. Our example uses a "hello world" Python Flask app, based on the sample from the Flask Website. Here's the code, which saved as hello.py:

# Source of hello.py

from flask import Flask
app = Flask(__name__)

@app.route("/")
def hello():
    return "Hello World!"

if __name__ == "__main__":
    app.run(port=8080)


Once we run


python hello.py

we can visit http://localhost:8080 in our browser and see a web page that displays "Hello World!" Now we can write a test to verify that the web application is working as expected. Selenium WebDriver supports  almost any programming language and web browser with Selenium. This example uses Python and Firefox. Let's make a directory named tests and create a file in it named test_hello.py:

# Source of test_hello.py

from selenium import webdriver
driver = webdriver.Firefox()
driver.get('http://localhost:8080/')
body_text = driver.find_element_by_css_selector('body').text
assert body_text == 'Hello World!'
driver.quit()

As long as Firefox is installed and hello.py is still running, we can execute

cd tests
python test_hello.py

Selenium WebDriver will open Firefox, navigate to http://localhost:8080, and verify that the text "Hello World!" is displayed.

Let's introduce Pytest to make the test a little more convenient to run. Pytest's discovery feature automatically finds tests based on naming conventions, such as files and functions whose names start with test_. We'll make a small change to test_hello.py, placing everything inside a function named test_1:

# Source of test_hello.py

def test_1():
    from selenium import webdriver
    driver = webdriver.Firefox()
    driver.get('http://localhost:8080/')
    body_text = driver.find_element_by_css_selector('body').text
    assert body_text == 'Hello World!'
    driver.quit()

Now, as long as Pytest is installed, we can simply run


py.test

Our test will run, and we'll get a handy summary of the results:



This test ran on Firefox. Suppose we'd like to test our web application on an operating system and browser that aren't installed locally, such as Windows 8.1 with Internet Explorer 11. For such remote testing with Selenium WebDriver running on a virtual machine, we'll need a Sauce Labs account. We'll need to supply our Sauce Labs username and an access key in order to connect to a Sauce Labs virtual server. We start by changing test_hello.py, replacing driver = webdriver.Firefox() with several lines of code that connect to a remote WebDriver.

# Source of test_hello.py
# Using a remote WebDriver

import os


def test_1():
    from selenium import webdriver
    desired_capabilities = {
        'platform': "Windows 8.1",
        'browserName': "internet explorer",
        'version': "11.0",
        'screenResolution': '1280x1024'
    }
    sauce_url = 'http://%s:%s@ondemand.saucelabs.com:80/wd/hub' %\
                (os.environ['SAUCE_USERNAME'], os.environ['SAUCE_ACCESS_KEY'])
    driver = webdriver.Remote(
        desired_capabilities=desired_capabilities,
        command_executor=sauce_url
    )
    driver.get('http://localhost:8080/')
    body_text = driver.find_element_by_css_selector('body').text
    assert body_text == 'Hello World!'
    driver.quit()

The code above assumes the Sauce Labs username and access key are stored in environment variables named SAUCE_USERNAME and SAUCE_ACCESS_KEY, respectively.

Once again, run the test with

py.test

The test runs, but doesn't pass:


Why? Because the Selenium WebDriver instance running on a Sauce Labs server can't access http://localhost:8080, which is running on our local machine. The error message provides a hint how to fix this: install Sauce Connect, which we can download here. This example uses Sauce Connect v4.3.14 for Linux. Versions for other operating systems are also available.

Installation on Linux is as simple as unzipping the downloaded file and ensuring that the resulting bin/sc file is on the path and has execute permission. Don't forget that, as above, our Sauce Labs credentials must be stored in the environment variables SAUCE_USERNAME and SAUCE_ACCESS_KEY.

Prior to running the test again, we run sc, which creates a secure tunnel between Sauce Labs' servers and our local machine. The tunnel remains available until we terminate sc (e.g by pressing Ctrl+c). Leaving sc running (either in the background or in a separate terminal window), we run py.test once more. This time, our test should pass.

At this point, we have everything we need to run remote tests of localhost URLs. However, there are some best practices we can implement to make such tests more convenient and reliable. Sauce Labs recommends that a tunnel be established and terminated for each test run, rather than leaving the tunnel open indefinitely. We'll accomplish this in two steps. First, we'll pass command-line arguments and add a few more lines of code to run sc as as daemon (that is, in the background). Second, we'll use a Pytest fixture to ensure the tunnel is ready before any test runs.

The full list of Sauce Connect command-line arguments is available here. The ones we want are:
  • -t: Controls which domains are accessed via the tunnel. For improved performance, we'll tell sc to use the tunnel for localhost URLs only.
  • --daemonize: Runs sc as a daemon.
  • --pidfile: The name of a file to which sc will write its process ID (so we know which process to kill when we're done).
  • --readyfile: The name of a file that sc will touch to indicate when the tunnel is ready.
  • --tunnel-identifier: Gives our tunnel a name so we can refer to it.
We could place the code to launch sc at the top of function test_1() in test_hello.py, and the code to terminate the tunnel at the bottom of the same function. However, this has a drawback. Presumably, we plan to write many test functions, not just one. We need to tunnel to be created before the first test runs, and terminated after the last test finishes -- regardless of the order in which the tests might be executed. Pytest provides a powerful feature known as fixtures that can accomplish this.

We could create a module-scoped fixture that applies to every test function in our test_hello.py file. But we can do even better. Let's create a session-scoped fixture. By defining our fixture in a specially-named file, conftest,py, and using a decorator to give the fixture session scope, the fixture will automatically apply to every test function in every test file we choose to create. The comments in the source code below provide more details.

# Source of conftest.py

import os
import pytest
import signal
import subprocess
import time


@pytest.fixture(scope="session")  # Session scope makes the fixture apply to any test function in any test file
def tunnel(request):
    sc_pid_file_name = '/tmp/sc_pid.txt'  # File where sc will store its PID
    sc_ready_file_name = '/tmp/sc_ready.txt'  # File sc will touch when the tunnel is ready
    sc_pid = None

    def fin():  # Function that executes when the last test using the fixture goes out of scope
        if sc_pid:
            os.kill(int(sc_pid), signal.SIGTERM)  # Kill sc's process, terminating the tunnel

    try:
        os.remove(sc_ready_file_name)
    except OSError:
        pass

    # Sauce Connect reads credentials from environment variables SAUCE_USERNAME and SAUCE_ACCESS_KEY
    subprocess.call([
        'sc',
        '-t', 'localhost',                            # Use tunnel for localhost URLs only
        '--readyfile', sc_ready_file_name,            # Name of the "ready" file
        '--tunnel-identifier', 'my_tunnel',           # Name of the tunnel
        '--daemonize', '--pidfile', sc_pid_file_name  # Run as daemon; store PID in specified file
    ])

    with open(sc_pid_file_name) as sc_pid_file:
        sc_pid = sc_pid_file.read()  # Read the PID
    request.addfinalizer(fin)  # Register the finalizer function

    # Wait for the tunnel to be ready
    start_time = time.time()
    while True:
        if os.path.exists(sc_ready_file_name):
            break
        if time.time() - start_time > 30:
            raise Exception('Timed out waiting for Sauce Connect')

Now we just need to make two small changes to test_hello.py, passing the name of the fixture function as an argument to the test function, and including the tunnel identifier in the desired_capabilities dictionary that controls Selenium WebDriver's behavior.

# Source of test_hello.py
# Using a remote WebDriver
# Using a Pytest fixture

import os


def test_1(tunnel):
    from selenium import webdriver
    desired_capabilities = {
        'platform': "Windows 8.1",
        'browserName': "internet explorer",
        'version': "11.0",
        'screenResolution': '1280x1024',
        'tunnelIdentifier': 'my_tunnel'
    }
    sauce_url = 'http://%s:%s@ondemand.saucelabs.com:80/wd/hub' %\
                (os.environ['SAUCE_USERNAME'], os.environ['SAUCE_ACCESS_KEY'])
    driver = webdriver.Remote(
        desired_capabilities=desired_capabilities,
        command_executor=sauce_url
    )
    driver.get('http://localhost:8080/')
    body_text = driver.find_element_by_css_selector('body').text
    assert body_text == 'Hello World!'
    driver.quit()

That's it! We now have a framework for running any number of test functions, with a tunnel automatically created before the first test, and terminated after the last test.

Sunday, July 19, 2015

Python dictionary comprehensions

Python programmers may be familiar with list comprehensions, a compact syntax for defining a list. Here's a typical example that creates a list containing the squares of the first five positive integers:

print [n*n for n in range(1,6)]
[1, 4, 9, 16, 25]

But suppose we want to create a dictionary, rather than just a list, mapping each integer to its square. We can use a dictionary comprehension. It differs from a list comprehension in using curly braces instead of square brackets, and in specifying two expressions separated by a colon to the left of the
for.

print {n: n*n for n in range(1,6)}
{1: 1, 2: 4, 3: 9, 4: 16, 5: 25}

This is equivalent to:

d = {}
for n in range(1,6):
    d[n] = n*n
print d

Dictionary comprehensions are a great of example of how we can do a lot in Python with a single, very readable, line of code.

Thursday, January 1, 2015

Avoiding inaccurate results when using SQL joins with aggregate functions

Using JOIN in a SQL query is a powerful way to combine data from two or more tables. However, if a join is used incorrectly in combination with an aggregate function such as COUNT or SUM, it may not give the output you intended. With a little care, you can get the right result.

As an example, suppose our database represents a group of students. Each student has zero or more email addresses. So we'll have a table named STUDENT, as well as a table named EMAIL that includes a STUDENT_ID column. An email address can be marked inactive, meaning the student doesn't currently use it.

Our goal is to count the number of email addresses for each student.

Here's the SQL to create the two tables:

create table student
(
  id integer,
  name character varying
);

create table email
(
  student_id integer,
  address character varying,
  is_active boolean
);


Let's populate our STUDENT and EMAIL tables with some data:

insert into student values(1, 'Steve');
insert into student values(2, 'Dana');
insert into student values(3, 'Jason');
insert into student values(4, 'Mark');

insert into email values(1, 'steve@gmail.com', true);
insert into email values(1, 'steve@yahoo.com', true);
insert into email values(2, 'dana@gmail.com', true);
insert into email values(2, 'dana@gmail.com', false);
insert into email values(4, 'mark@gmail.com', false);


Here are the resulting contents of the two tables:


















Notice that Jason has no email address, and Mark's only email address is inactive.

As a first attempt at counting email addresses, we might try this:

select s.name, count(e.address)
from student s
join email e on e.student_id = s.id
group by (s.name)


 
The counts for Dana, Mark and Steve are correct, but Jason is missing. We forgot to use a LEFT OUTER JOIN, which will include every record from the STUDENT table even if there is no corresponding record in the EMAIL table. So let's try:

select s.name, count(e.address)
from student s
left outer join email e on e.student_id = s.id
group by (s.name)










As we wanted, Jason shows up with a count of 0. So far, so good. Next, suppose we want to count only active email addresses. We expect the count for Dana to be 1, and for Mark to be 0. Let's try this query:

select s.name, count(e.address)
from student s
left outer join email e on e.student_id = s.id
where e.is_active
group by (s.name)








Jason and Mark have gone missing. To understand why, consider the output of this simple JOIN query:

select * from student s join email e on e.student_id = s.id:











Now condition the query on e.is_active, and only three rows will remain:

select * from student s join email e on e.student_id = s.id where e.is_active








It's these three rows that are considered by the aggregate function COUNT: two for Steve, one for Dana, and none for Jason or Mark.

How can we avoid this problem and make sure results are displayed for all students? The answer is to apply the filter e.is_active as part of the JOIN condition, not in the query's WHERE clause. Now we get the desired result:

select s.name, count(e.address)
from student s
left outer join email e on e.student_id = s.id and e.is_active
group by (s.name)










This type of mistake can be easy to overlook. When dealing with a large database, if you use the wrong query, the results may still look reasonable: you might not notice that a few students are missing. The moral of the story is, when using JOIN together with aggregate functions, always consider whether you want any filter conditions to be part of the JOIN condition, or part of a WHERE clause.

Tuesday, November 11, 2014

Null values can cause unexpected results in SQL subquery

I was recently using a SQL query to examine data in my PostgreSQL 9.3 database, and the results were not what I expected. It took a while to figure out that this due to using a subquery (also known as an inner query or nested query) that involved NULL values.

I have a table users, like this:


And a table addresses, like this:

In addresses, the user_id column indicates the user -- if any -- to whom the address belongs. I want to find any users who don't have an address. In this example, that would be betty: her user_id, 4, doesn't appear anywhere in addresses. I thought this query would do the job:

select * from users where user_id not in
(select distinct user_id from addresses)

But that query returns no rows! Here's why. Note that the row in addresses with address_id = 5 has a NULL user_id. Therefore, the query is equivalent to:

select * from users where user_id not in (1, 2, 3, NULL)

This, in turn, is equivalent to:

select * from users where user_id <> 1 and user_id <> 2 and user_id <> 3
and user_id <> NULL

In SQL, no comparison to NULL is ever true when using the = or <> operator. So no row matches the query. (The IS operator is proper to use for comparisons to NULL.)

We can avoid this problem by excluding NULL values in our subquery:

select * from users where user_id not in
(select distinct user_id from addresses where user_id is not null)

Or by using COALESCE to convert any NULL values to something we can compare against an integer:

select * from users where user_id not in
(select distinct coalesce(user_id, -1) from addresses)

Either solution will cause the query to output betty's row of the addresses table, as desired.

Thursday, July 17, 2014

Installing a Belkin N150 wireless network adapter on Ubuntu 14.04 LTS

I'm a longtime Windows user who also has some experience with Linux. This week, I'm taking the plunge and configuring a desktop system with Ubuntu that I plan to use daily for software development and management tasks. My first challenge was getting connected to the network. I purchased a Belkin N150 wireless network adapter. It came with a driver installation CD for Windows, but my computer has neither Windows nor a CD drive! Luckily, I found some good instructions thanks to theharrylucas. I'll just add a couple of points here:
  • The instructions are from 2011, so -- like most of the other info I found on this topic -- they pertain to older versions of Ubuntu. However, they worked fine for 14.04.
  • The instructions refer to the device ID 050d:935a. My device ID is slightly different, 050d:945a. Nonetheless, the instructions worked as is.

Friday, June 6, 2014

Handling null values in a SQLAlchemy query - equivalent of isnull, nullif or coalesce

SQLAlchemy allows you to use the equivalent of SQL's COALESCE function to handle NULL values. I didn't find the documentation for this easy to understand, so here's a quick tutorial...


Depending on which database management system you're familiar with, you might have used ISNULL, NULLIF or COALESCE in a query to return a specific value in case the database column contains NULL.

My example concerns a PostgreSQL database with a table named CONFIG. I want to find a record that was least recently processed, according to its LAST_PROCESSED_AT column, or was never processed, indicated by LAST_PROCESSED_AT = NULL.

Here's the result of the query
select name, last_processed_at from config:


Note that the third row has a null value for LAST_PROCESSED_AT. Here's a revised query using SQL's coalesce function to map NULLs to the earliest date recognized by PostgreSQL:
select name, coalesce(last_processed_at, to_timestamp(0)) from config

Note the effect on the third row:

Armed with COALESCE, I can find the record I'm looking for with this query:
select name from config order by coalesce(last_processed_at, to_timestamp(0)) limit 1

The question is how to do the same thing with Python and SQLAlchemy. And the answer is this:
import datetime

from sqlalchemy.sql.functions import coalesce

my_config = session.query(Config).order_by(coalesce(Config.last_processed_at, datetime.date.min)).first()

Friday, March 14, 2014

Python Flask - passing JSON to a template in a variable

I ran into a problem in my Python Flask app. I wanted to store some JSON-formatted data in a Python variable, and use JQuery's parseJSON function to consume that data inside a Jinja template. When I tried to parse the JSON in the template, a JavaScript error resulted. I figured out why, and how to work around it.

By the way, the same problem occurs, and the same workaround applies, whether you pass the variable in the call to render_template, or use a session variable.

Here's a copy of my first attempt at the code...

Python:

def index():
        return render_template('viewer_type.html', myVar='{"abc": "123", "def": "456"}')

JavaScript:

<script src='jquery-1.10.0.min.js></script>
<script>
        var myVar = "{{ myVar }}";
        var myJSON = $.parseJSON(myVar);
        prompt("abc", myJSON.abc);
</script>

This failed. prompt never executed, and Chrome's JavaScript console reported this error.

Uncaught SyntaxError: Unexpected token &

I realized that the contents of the variable were being encoded when Flask passed them to the template. Adding this line of JavaScript...

prompt("myVar", myVar);

... revealed that my JSON had turned into:

{&#34;abc&#34;: &#34;123&#34;, &#34;def&#34;: &#34;456&#34;}

Thanks to this helpful post on stackoverflow, I found a convenient way to decode the encoded text in JavaScript. I encapsulated that in a function:

function decodeJSON(encodedJSON) {
            var decodedJSON = $('<div/>').html(encodedJSON).text();
            return $.parseJSON(decodedJSON);
}


Then I just needed to amend my original JavaScript to call decodeJSON, so the whole thing looks like this:

<script src='jquery-1.10.0.min.js></script>
<script>
 function decodeJSON(encodedJSON) {
  var decodedJSON = $('<div/>').html(encodedJSON).text();
  return $.parseJSON(decodedJSON);
 }


 var myVar = '{{ myVar }}';
 prompt('abc', decodeJSON(myVar).abc);
</script>


Success! The expected output, 123, was displayed, and there were no more errors in the JavaScript console.

Wednesday, January 29, 2014

Iptables settings can interfere with apt-get update

I ran into a problem on my Ubuntu 12.04.3 box today. I issued the command sudo apt-get update. This usually works without a hitch, but today, it obtained a few of the updates successfully but gave 404 errors for many others. Here's an example:

Err http://security.ubuntu.com precise-security/main Sources
  404  Not Found [IP: 91.189.92.200 80]


After a little thought, I realized how this Ubuntu box differed from others I had worked on: I had used iptables to facilitate running Apache Tomcat on ports 80 and 443. (Because these port numbers are below 1024, Tomcat would have to run as a privileged user to access them, and iptables provides a well-known workaround.)

The particular iptables rule that caused the problem was this one:

sudo iptables -t nat -I OUTPUT -p tcp --dport 80 -j REDIRECT --to-ports 8080


By temporarily deleting that rule (which can be done by replacing -I with -D in the above command), I got sudo apt-get update to work.

Tuesday, January 28, 2014

Running Tomcat 7 on port 80 on Ubuntu 12.04.3

The goal:
  • Commission a new Amazon EC2 Instance running Ubuntu 12.04.3.
  • Install Apache Tomcat 7 and everything Tomcat requires to run.
  • Make Tomcat respond to requests on port 80.
How this differs from other tutorials you might have encountered:
  • It's for Ubuntu, not some other flavor of Linux where you can just set AUTHBIND=yes in an /etc/defaults/tomcat7 file.
  • It's for Tomcat 7, not some earlier version.
  • It doesn't just tell you to edit Tomcat's server.xml file, ignoring the fact that Ubuntu won't let a non-privileged user bind to ports below 1024.
  • It doesn't suggest running Tomcat as root, ignoring any resulting security concerns.
How to do it:
  • Create a new AWS EC2 Instance.
    • Select the AMI Ubuntu Server 12.04.3 LTS, 64-bit.
    • Use or create a security group that enables (at least) ports 22 and 80 for your IP address.
  • Use an SSH client to connect to the Instance. Most of the remaining steps will be performed in the SSH client.
  • Install Tomcat 7: 
    wget http://apache.mirrors.lucidnetworks.net/tomcat/tomcat-7/v7.0.50/bin/apache-tomcat-7.0.50.tar.gz
    tar -xzvf apache-tomcat-7.0.50.tar.gz
    rm apache-tomcat-7.0.50.tar.gz
    export CATALINA_HOME=/home/ubuntu/apache-tomcat-7.0.50
    export CATALINA_BASE=$CATALINA_HOME
  • Install JRE 7. Unfortunately, Oracle requires you to click to accept a license agreement, which you can't do from a headless server. So use another computer to visit http://www.oracle.com/technetwork/java/javase/downloads/server-jre7-downloads-1931105.html, download server-jre-7u51-linux-x64.tar.gz. Find a way to get this file to your home directory on the EC2 Instance. One possibility is to install an FTP or SFTP server on the Instance. Once the file is in your home directory:
cd ~ tar -xzvf /sftp/stevetest/incoming/server-jre-7u51-linux-x64.gz

export JAVA_HOME=/home/ubuntu/jdk1.7.0_51  
  • Use iptables to redirect requests on port 80 to port 8080
sudo iptables -t nat -I PREROUTING -p tcp --dport 80 -j REDIRECT --to-ports 8080

sudo iptables -t nat -I OUTPUT -p tcp --dport 80 -j REDIRECT --to-ports 8080
  • Start Tomcat:
cd ~/apache-tomcat-7.0.50/bin ./startup.sh

  • Now if you type the EC2 Instance's IP address into your browser's address bar, you should see the Tomcat welcome page. No need to specify port 8080!
A tip of the hat goes to Tomcat: The Definitive Guide by Jason Brittain and Ian F. Darwin for a clear explanation of how to use iptables.

In future installments, I hope to cover setting up the server to automatically export the environment variables and start Tomcat when booted, and to enable SSL on port 443.