Database backups are not a difficult thing, but they are tedious and only important when there is a crisis at hand. Recently we ran into a situation where backups failed to protect us from some worst practices and we had to scrape together the binlogs and old dumps to resurrect the table.

What follows is a true story although the names and places have been changed.

At 13:45 a developer execute the following query

DELETE FROM table
return 159 rows effected


He intended to execute a SELECT * FROM table, and just remove the WHERE clause from a previous query but it turned out to be a DELETE FROM table WHERE ID = #.

As soon as the trigger was pulled he realized his mistake. I was brought in to resurrect the data because I previously performed all backups.

Off we go.

1. Stop replication
2. Flush logs
3. if you can do the processing on the server wonderful, other wise copy the binlogs to a recovery system.
4. mysqlbinlog -d mydb mysql-binlog.[0-9]* | sed ‘/table/,/;/!d’ > recovery_file
5. start replication

From the recovery_file we had a list of every statement issued against the table and we could replay all transactions to return the table to it’s original state with a little editing.

We are now reviewing our backup and recovery practices. I hope this was educational for you as it was for me.

I found the key point for the sed command in this document, and this tutorial helped greatly.

August 12th, 2008 | Programming | No comments

When we talk about modern web APIs we are mostly talking about REST(http://en.wikipedia.org/wiki/REST). In the last couple of years it has easily become more popular than similar layers like XMLRPC and SOAP. While these protocols work in a number of situations many of the popular Web Applications like Flickr have begun to look for methods that afford greater scalability than the those that use HTTP for data exchange.

Since HTTP is a stateless protocol it runs into problems when data changes occur. With HTTP the client must poll for new data, over, and over, and over, and over … Flickr threw up some numbers at the session that nailed home the challenges large web apps face. July alone Flickr had nearly 3 million requests from FriendFeed (a social content aggregator for your online presence), for 6000 users. Clearly this sort of traffic won’t fly as more people climb aboard.

The presenters, Evan Henshaw-Plath and Kellan Elliot proposed repurposing XMPP (typically an IM protocol) for the purposes of creating data streams for more efficient exchange. While REST is a constant request for new data, XMPP really is a subscription model, where a client establishes a session and listens for requested data. I don’t have their metrics handy, but the difference was remarkable, effectively shrinking the whole thing down to about 1% of the original traffic.

Their reason were:

• persistent connections
• stateful - less ID on each connection
• designed to be event streaming protocol
• natively frederated and asychronous
• identity, security, and presence built in
• jabber servers are built to do this

They also used PubSub (http://www.xmpp.org/extensions/xep-0060.html), a protocol extension, to achieve the subscription model, and OAuth for authentication.

While this had little to do with my work, it was fascinating to hear how larger enterprising handle the scaling issues that inevitably plague any popular web app. We have tried to do data streams ourselves in a couple of situations using Flash’s XML Sockets, which work well except 1. it’s in Flash and 2. it doesn’t route through firewalls easily. I don’t necessarily think Flash is bad, but it is difficult to support at times. I thoroughly enjoyed the talk. Evan and Kellan have a great presentation style that makes them easy to relate to.

A couple of miscellaneous notes:
The jid per user oauth authentication handles the identification of users.

XMPP Data service example

1. hello world
• jabber:simple
• xmpphp for php
• smack javae
2. jabber account
3. connect and send a message

Servers

• ejabberd
• djabberd
• openfire/wildfire java server
• tigase - java server

When setting up a server, turn off lots of features, for example turn off open subscribing. For this sort of data streaming there is little need for the multitude of features that XMPP offers to IM clients.

Build a component example

• use a compnent XEP-0114
• persistent talks over local socket
• YAGNI: rosters. presence
• Load balance

Starling - no idea what this was about

OAuth over XMPP - delgation of identification

August 2nd, 2008 | Programming | No comments