I was recently on holiday up in Vancouver, BC. Unfortunately my GPS unit had not been preloaded with Canada maps, so in the interest of not getting lost, I elected to open a map once or twice on my smartphone. I knew that this would be charged as “roaming” data, and as a smart person I had done my research, and established that AT&T would be charging me the exorbitant price of… $0.015 per kB. Didn’t seem soooo terrible – I figured that opening a map would use a handful of kB tops. If I was unlucky I was looking at a few dollars worth of usage. How wrong I was.
Total usage: 4.6MB. Total charge: $69. FML.
Let’s put that in perspective. I pay $33 per month for internet at home. I can use up to 250GB on my plan without repercussion, although let’s say I average around 100GB. That means I pay around $0.000000315 per kB.
The upshot of this, is that the data I used in Canada cost me more than 47,000 times as much per byte than my home internet plan. Forty. Seven. Thousand. For those keeping score, that means that if the roaming charge was the standard rate, I would be getting a 99.9979% discount on my internet bill every month. You can’t make these numbers up, we’re talking mind-bogglingly disparate numbers here.
Now don’t get me wrong, I understand the implications of roaming. AT&T has to negotiate with third-party providers to use their networks and their bandwidth when I’m out of the country. I understand that there is a significant difference between the cost of transferring data over a cable connection and a 3G connection. But if you think that all of those issues justify a 4.7 million percent markup, you have got to be pulling my leg. Canada is a first-world country, with (presumably) first-world cell networks and internet access. AT&T can give me an unlimited smartphone data plan for $20/mth, but can’t negotiate a better rate than $60 for 4MB of data?!
Give me a fucking break.
Lately I’ve been getting the occasional error from my WebFaction instance – alerting me that a running MySQL query had been shut down because it had been running for too long and was slowing the system. Here’s the offending SQL query:
SELECT /*!40001 SQL_NO_CACHE */ * FROM `phpbb_search_wordmatch`
Very mysterious. The offending table is one relating to my phpBB instance. I did not have any further information at hand to work out what was going on, and so my first port of call in attempting to resolve the issue was to adjust my phpBB search settings. However, a few days later, the error occurred again – back to the drawing board.
My next step was to inspect the source code of phpBB. However, I could not find anything that would produce SQL like the above statement.
And then it occurred to me. I started receiving the error messages about the same time as I set up a Cron database backup job. Sure enough, all the errors were coming through at around the same time of day, coinciding with when the backups were scheduled to run. A quick web search confirmed that running mysqldump against very large tables was a typical culprit for long-running SQL statements.
Once I knew the source of the error, the fix was very simple: Because the table in question is just an index table for the purposes of searching posts (i.e. not essential data to backup), it was just a case of adding –ignore-table=db.phpbb_search_wordmatch to the mysqldump command line in my cron job. Now, the search index table is skipped in my backups, resulting in smaller, faster backups, and no more error messages!
I couldn’t find much information about this problem specific to phpBB or Webfaction, so hopefully this helps someone else out!
Monday, August 2nd, 2010 | Posted in Intarwebs | Comments Off on MySQL backups causing problems
Here’s a bit of a dud write-up. Dud because the risks are minimal, as I realised when I started looking into cross-domain iframe DOM scraping… But potentially interesting reading for web developers nonetheless.
If you have been browsing the internet lately, you have more than likely seen a Facebook Connect box. It looks like this:
This particular screenshot is taken from Gameplanet Forums, but Facebook Connect makes it easy for any website developer to embed the panel into their website. I could put one on tom.net.nz, if I was so inclined.
Now, Facebook would argue that this frame is a naive, harmless feature, because the information is never passed directly to the website in question, but rather the website just embeds a little piece of code, and Facebook generates the actual content of the pane. The code embedded is identical for any user visiting the website.
This is all well and good, however (!), the content that Facebook generates for this pane will differ depending on whether or not the user viewing it is currently logged into Facebook. If they are, then Facebook tries to show information more “relevant” to that user. For example, in the above screenshot, “Matt” is my friend on Facebook (and the only person in my friends who has “liked” Gameplanet on Facebook). The other people are generated randomly, but no matter how many times I refresh the page, Matt will always appear in the list. Do you see where this is going?
One might assume that Facebook has put some measures in place to stop the site from scraping this information, however the tech savvy can follow the following link which generates the box for Gameplanet’s Facebook Connect pane: link. If you view the source, there are all the names, in plain HTML, with links to both the photo, and (perhaps more disturbingly), the profile of each person. I was also able to scrape my own user ID from the HTML. Fortunately, in most modern browsers, XSS (Cross-Site Scripting) protection prevents the parent page from accessing the DOM of Facebook’s frame, but this is still a major potential security problem for older browsers which don’t have such protection built-in. By inspecting the list of users on a few page loads and looking for duplicate names, a malicious site could ascertain who is friends with the user browsing the page. The parameters passed to the frame source allow significant customization of the response, for example with a bit of tweaking I was able to come up with the following source, which now shows 100 users instead of the default 15. To take it a step further, a malicious page could potentially load this frame without even showing it, meaning the user would be completely unaware that the site was doing anything to do with Facebook, meanwhile it’s scraping the user’s private information.
Like more than 400 million other people on this planet, I belong to a little site called Facebook. I was what you might call an “early adopter”, at least in New Zealand. I joined when the site was only open to university/college students (requiring one to have a university email to register). I believe it had been running for a couple years before they added New Zealand universities to those eligible for registration. I was literally within the first hundred or so people to sign up for it at the University of Auckland, on the 26th of February, 2006.
That’s more than 4 years ago now, and it’s kind of saddening to see how Facebook has changed in that time. When I first joined, I immediately liked it for the following reasons:
- It did photos better than any other site I’d seen, with photo tagging and a simple interface (I was on MySpace, Friendster and Bebo at the time, none of which had photo tagging).
- You couldn’t do any customisation of page appearance. Everyone’s profile was clean and uncluttered. (BIG contrast with both MySpace and Bebo). There wasn’t any extra cruft, and you couldn’t add images and music and videos and so on into your profile. This alone made me love Facebook more than anything I’d used before. My eyes would no longer bleed when looking at peoples’ profiles. No more sparkly stuff, or god awful music that started playing as soon as you opened a page. Thank God.
- Privacy was the default, not a hidden option. You couldn’t see anyone’s stuff unless they opted to make it public, or you added them as a friend.
Overall, it just felt much nicer and cleaner than anything I’d used before. I promptly told all my university-attending friends about it, and a few months later, ditched the other three sites. Then started the slow and steady decline. You may or may not have been around to see some of these changes:
- The opening of registration to anyone and everyone. Suddenly the site was full of preteens and teenagers that (quite frankly) I was happy to have left behind on MySpace and Bebo. When it was only open to university students, it had an “exclusive” feel to it, which was nice.
- The introduction of applications. This one particularly pissed me off. I was getting constant spam from other people “inviting” me to use the applications they were using, and my news feed was full of junk about other peoples’ application activity. Worst of all, the thing that originally had me most excited (the clean, uncluttered appearance of profiles) was now pretty severely compromised. While you still couldn’t change the appearance of your page in terms of background colours/images, and while the layout was still roughly the same, pages were now full of “fishtanks” and “DO U LIEK ME?” questionnaires, and other such junk. This is now better under control, with applications appearing either in the small column to the left of profiles, or in the “Boxes” tab, but it was certainly annoying at the time.
- The gradual erosion of user privacy over the years. This has almost happened so slowly that it’s been unnoticeable, but Facebook is (at its current stage of development) trying to stick its finger in too many pies. It is pandering to its partners and developers, by giving them more access to user information, at the cost of user privacy. With the likes of Beacon (now shut down, two years after its inception), Connections and Open Graph now making your public information available to third parties, it has now reached the point where it seems they no longer care about their users as anything other than a revenue source. The information being shared with third parties notably includes information that you can set the “Visibility” of to “Only Friends” in your privacy settings. Apparently Visibility only refers to other Facebook users, and some third parties are exempt from such restrictions. Users essentially have no control at all over where this information could end up.
- Along with the occasional reduction in default privacy settings (or removal of privacy completely from certain items), Facebook seems to be deliberately making it difficult for you to opt out of these settings.
Perhaps even more worrying, is how few people are actually even aware of this erosion of privacy. Facebook have done a good job of keeping it pretty well hidden, or glazed over as “enhancing the experience”. Of course, most people (myself included) don’t read privacy policies (often pages and pages worth) every few months.
Of course, a social networking site is only useful if there are a good proportion of the people you care about using it. With that in mind, Facebook is still good from the perspective of sharing photos, organising events, and communicating with friends. However, I have stripped most of my personal information from the site (bar photos), and will gladly move to a new platform if it can grab me in the same way that Facebook did when it first appeared on the scene.
In particular, I’ll be keeping an eye on Diaspora, an intriguing project about to start development, which promises to deliver on the concept of an “open source” social network. In a nutshell, it’s a network of personal “nodes”. Each person on the network owns their own node, to which they can add whatever information they like, and access it from anywhere. In turn, they have fine-grained control over who can see that information. Think about it like your own personal house that contains your personal information (basically everything that you’d otherwise be sharing on Facebook/Flickr/LastFM/Twitter/etc), and you can open the door two whomever you (and only you) choose. Because you own the house, you can demolish it at any time, or add and remove furniture as you please. It’s also being developed by a bunch of super nerds, so I can totally get behind it:
Hopefully it amounts to something! My biggest concern is that it’s very technical and geeky at this point in time, so they will need a good marketing team with a pitch for the masses before it gains any real ground.
Hopefully if I keep this up, I’ll be inspired to write some actual code…