Good things happen when you subtract datetimes in MySQL

Of course, you know that “good things” and “MySQL” don’t go together. File this one under the category of “small ways in which MySQL is broken”.

Let’s fire up MySQL 5.1.72-2-log or 5.5.34-log.

mysql> create temporary table blah
    -> (alpha datetime, beta datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> describe blah;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| alpha | datetime | YES  |     | NULL    |       |
| beta  | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

OK, so we have two datetimes in a table. Let’s try adding a row:

mysql> insert into blah (alpha, beta)
    -> VALUES ('2014-01-01 03:00:00', '2014-01-01 03:00:37'); 
Query OK, 1 row affected (0.00 sec)

What happens if we try subtracting two datetimes?

mysql> select alpha, beta, beta - alpha from blah;
+---------------------+---------------------+--------------+
| alpha               | beta                | beta - alpha |
+---------------------+---------------------+--------------+
| 2014-01-01 03:00:00 | 2014-01-01 03:00:37 |    37.000000 |
+---------------------+---------------------+--------------+
1 row in set (0.00 sec)

So we got the number of seconds between the two datetimes. Let’s try that again with two datetimes a minute apart.

mysql> insert into blah (alpha, beta)
    -> VALUES ('2014-01-01 03:00:00', '2014-01-01 03:01:00');
Query OK, 1 row affected (0.00 sec)

mysql> select alpha, beta, beta - alpha from blah;
+---------------------+---------------------+--------------+
| alpha               | beta                | beta - alpha |
+---------------------+---------------------+--------------+
| 2014-01-01 03:00:00 | 2014-01-01 03:00:37 |    37.000000 |
| 2014-01-01 03:00:00 | 2014-01-01 03:01:00 |   100.000000 |
+---------------------+---------------------+--------------+
2 rows in set (0.00 sec)

So, 100 seconds in a minute? Yikes. Obviously, this isn’t how you’re supposed to subtract datetimes in MySQL. But the great part is that it kind of works! You get a number back that correlates to the actual interval of time between the two, and if you’re measuring lots of small intervals, you might not notice that your data is between 100% and 167% of what it should be. Excellent puzzle to drive a junior dev crazy!

Wait, any reasonable database would have known that we were making a mistake, right?

mysql> show warnings;
Empty set (0.00 sec)

tuntuntun – Combine Multiple Internet Connections Into One

GitHub repo: https://github.com/erjiang/tuntuntun (proof of concept status)

I was trying to play Minecraft by tethering over a Sprint data connection but was having awful random latency and dropped packets. The Sprint hotspot seems to only allow a limited number of connections to utilize the bandwidth at a time – a download in Chrome would sometimes stall all other connections. This was a huge problem in Minecraft, as loading the world chunks would stall my movements, meaning that I could teleport somewhere and die to an enemy by the time the map finished loading.

I’ve been seeing the idea of channel bonding here and there for a while, and it always seems like a cool idea without any popular and free implementations. Most of the approaches, though, were restricted to assigning different connections to different network interfaces. Essentially, a connection to YouTube might go over one link, while a software download might go out another. This works OK if you’re trying to watch YouTube while downloading updates, and it works great for many-connection uses like BitTorrent but in this case, I wanted to create a single load-balanced connection. So, I created tuntuntun.

Somewhat like a VPN

tuntuntun diagram

This requires the help of an outside server to act as the proxy for all of the connections, because most current Internet protocols require connections to originate from one address. The idea is to establish a connection to the proxy using a custom protocol that allows data to be split between two links. Tuntuntun works by encapsulating IP traffic in UDP packets, and does this in userspace using tun interfaces. A tun interface is a virtual network interface that has a userspace program as the other end. This means that any packets sent to the tun are read by the userspace program, and anything that the userspace program writes to it becomes “real” packets in the kernel.

Continue reading tuntuntun – Combine Multiple Internet Connections Into One