Accessing your MySQL server: “Network is unreachable”

This has been a very sysadmin-y week for me, and I’ve mixed feelings about that. For now though, I’d like to tell the story of how I debugged why I couldn’t access my MySQL server.

My standard MO would be to have MySQL and the app running on the same machine. This time though, since I’ve built my app using Docker (which I’m deploying with Docker Cloud) I can’t have the MySQL server on the same box.

I do have a box already with MySQL running, however that box is smartly locked down with all sorts of iptables voodoo. Very few things are allowed to talk out from the server, and even fewer are allowed to talk to the server. Here are the steps I took while learning how to open that box up.

First, on the MySQL box figure out what port it’s running on. The default is 3306, but you can confirm that like so:

sudo netstat --tcp --listening --program --numeric-ports | grep 'mysql'

The number in the forth column, which looks a bunch like an IP address with a port, is the port you’re looking for.

Now we know that, we can check if our app server has access to the database server.

telnet yourhost.com 3306

Hopefully, you won’t get anything back but a quick message about it “Trying” to connect, and eventually “Network is unreachable”. If this command does actually connect you to your MySQL server then you should focus on locking that down as soon as you can.

Assuming that you’ve already got your server locked down via iptables though, you’ll want to open it up so that you app server (and only your app server’s IP) can get access to this port. I don’t know enough about system administration to get dirty with real iptables conf files though, so I much prefer to install webmin which will give you a lovely interface for it.

You want to be setting up rules which look like this:

  • Source address: [Your app’s IP address]
  • Source port: 3306

And then the defaults are largely good enough. Let me know in the comments if there are even more things that I could lock down – I think have the IP address locked to one I’m expecting should be safe enough though.

Once you’ve saved and applied those new rules, you may be able to telnet to your MySQL server now. You’ll see what definitely looks like a MySQL prompt.

If not, there’s another debug tool you can use: tshark. I’ve found this to be super helpful when trying to track down malicious looking traffic I had one time on a server of mine. In this case though, you can run it on your MySQL server and see if the server is even spotting the telnet request.

tshark -ta -n port 3306

This’ll show you data being sent to that port. Try and telnet again, you should see some traffic. If not, your iptables rules are wrong, or you’re mistaken about your IP address.

If all is going well though, you should see the traffic from your telnet request.

This is where I got stuck for a little while, but eventually found that MySQL doesn’t listen to the wider network – only internal network comms. You can fix this in your my.cnf file (likely /etc/mysql/my.cnf):

bind-address            = 0.0.0.0

Restart MySQL, and you should be able to access it all you need.

Question based debugging

This is a debugging technique which I imagine many developers just do in their head. I’m afraid I desperately lack the attention span though, and so find this method of debugging inscrutable anomalies really useful.

Here’s a simple breakdown, of what’s really a very simple system.

Ask a question to yourself, and write it down. The question should be about the problem you’re experiencing and should start very basic. It should question a premise which you expect to be true. More often than not the first question should be “does this bug still exist?” (Forcing yourself to see the bug is something that a surprising number of developer don’t do – jumping straight into the code instead.) Next to the question draw a checkbox. Later on the question might be “is the form sending the full file path?”

2016-03-17 21.49.25
Paper from Present and Correct, pen from Muji.

Do what you can to answer the question. This should be your only goal for the moment. Don’t get distracted by potential other avenues the bug could be hiding down. Even if you spotted something that looks worthwhile to follow. Just try to answer your question in the affirmative or otherwise. There’s no reason to skip over an obvious questions if it only takes a few moments to answer – you’ll just lower confidence in the rest of your debugging journey.

The reason to not get distracted is that you’ll forget how far you got with the first line of debugging. When the distraction turns out not to be correct, you’ll have no idea how to get back onto the track you were on ten minutes ago.

Write down the answer, and then tick the box. Writing down the answer is important for a number of reasons.

First off, if you’re debugging a particularly messy situation you’ll quickly forget the answer to the previous question, leaving you with fewer clues.

Second, and more importantly, is that writing is dog slow. You’ll be pausing, hands away from the computer, and giving yourself time to process what you just learned. Maybe you’ll realise that you’ve been thinking about it wrong, or that you’ve been debugging the wrong thing for the past half an hour. It’s very easy to get swept up in the flow of data when your head is keeping track or every bit of state.

This is also really important whilst pairing. Your partner may not be keeping up – or maybe you’ve decided something wrong. Having it written down is a lot clearer than “well the previous revision is overridden by the next revision because the author of the previous, uh, no, the next revision is the same as the first revision, see?” Write it down so that there’s always a checkpoint for your pair to catch up. If they’re a good pair they’ll ask you to explain anything that looks odd.

Ticking the box is important too! I’ve been lost on many two day long debugging sessions, which left me drained and stressed. Although slowly making progress, there’d be no specific point to celebrate. Instead, now that we have tiny units of work to complete, each tick is a mini-celebration.

Ask the next question.

Keep doing this until you realise, “dammit, it’s just a typo!”

At the end of this, not only will you have fixed your bug, but you’ll have learn a lot about your system, with some semi-decent notes.

Developing with Symfony 2 and Docker

I had a couple of aims for my most recent quick project, Dashli:

  • be able to avoid polluting my machine with PHP, MySQL, and those other services which quickly get littered around,
  • be able to really quickly deploy to a service, without having to ssh in and set everything up

Docker works well with both of these points.

There’s a simple Dockerfile which lets you write down the steps you’d take to build a box and set it up for your application. That means that during development, Docker knows how to build a virtual machine which’ll let me keep the entire server infrastructure hidden away from my “local” OS. No more worries about which PHP version I need for different applications and I don’t need to keep a MySQL server running for no good reason.

Together with tools like Docker Cloud (free with one private instance) and Digital Ocean (not free, but you can get $10 – enough for two months of a small server – when you sign up with this link), it’s super easy to have my project uploaded to a cloud instance and running without me even having to know how to SSH into the box.

I had this all set up and running brilliantly. The problem was when I came back to my machine to continue development.

Tiny change, rebuild. Tiny change, rebuild.

The most frustrating part of this new flow for me was finding a simple typo and then having to redo my docker build . step, which isn’t quick since it has to do a full composer install again.

The fix here was mounted volumes – let the virtual machine mount my project directory as nginx’s root directory. This is simple, actually. When running your docker, do this instead:

docker run --publish 80:80 --volume /local/project/path:/virtual/machine/path/to/project .

Now, when you update a file in your local project, it’ll be instantly reflected inside the docker. Refresh your browser and you’ll notice the updates.

composer files getting thrown away

You’re probably building your composer.phar install inside your Dockerfile. That will still happen. However, when Docker goes ahead and swaps out the VM’s /virtual/machine/path/to/project with your local one, it basically deletes all that is in there. That will include you vendor/ folder, which will now be empty.

That’s obvious why – you’ve never run composer locally, you don’t even have PHP installed locally so how could you! You need composer to be run within the Docker, and hopefully without having an affect on your local machine.

To do this, we can move our vendors outside of the project. I know, right. Weird. Composer has support for this though. In my composer.json you’ll seen this:

"config": {
"bin-dir": "bin",
"vendor-dir": "/tmp/dashli/vendor"
}

This tells composer to download the vendors in the /tmp/dashli/vendor directory (I chose /tmp/ as everyone can write to it – this might not be the smartest place to put it). I also had to edit my app/autoload.php so Symfony knows where the autoloader is.

$loader = require '/tmp/dashli/vendor/autoload.php';

Now your dependencies can be installed on the virtual machine, out of the way of your local machine.

Getting around root created files

The huge downside of this is that any file written by www-data user inside your container is actually going to be written locally by root. You’ll now find your local project’s var/cache/, var/session/, etc. full of files owned by root. You’ll have to sudo rm them to get rid of them, which is not something that is a part of a healthy development flow.

Symfony comes fully equipped to handle this problem though: in your app/AppKernel.php, you’ll have to change some of the overridden methods to point them to your /tmp/ directory, just like we did with composer dependencies above.

You can actually read more about this here on the Symfony docs.

Hopefully that leads you to an easy development experience with Symfony and Docker!

Past year or so in Notebooks

My first more-than-£1.50 notebook was quite recent. A few years ago I came across a baby blue Leuchtturm1917. You know the type. I felt almost guilty about spending almost £15 for a book of paper. I did ultimately buy it due to some of its fancier features: numbered pages and table of contents to fill out. Although I continued feeling guilty, I’m aware of the worth now.

The problem I have though was being unable to fold it over. A notepad with a wire binding was quite important for me, for practical reasons. How else are you able to comfortably use both sides of the book and have it lie flat?

Around this time I discovered Whitelines Link. Fancy paper which allows you to take a photo and archive the pages electronically with a mobile app. This is less and less useful now though as Evernote does a commendable with any piece of paper given. I am delighted by the grey paper and white lines of the pages though – less harsh on the eyes.

Until recently, the notebook you’d find in my bag would have been a wire-bound Oxford Black and Red or a more luxury nuco elite. Since they’re just A5 notebooks, they take up little space in my bag. The hard covers let them stand up for themselves in the midst of the warzone in my bag. The wire-bindings fold flat.

I can’t spot any Black & Red’s at the moment, but I do have a trusty, brown nu elite. The stock in it is a good paper, with a little too much gloss. This means that wet ink doesn’t bleed through, but is a lot more likely to bleed and a touch slower to dry. Looking through this book, I’ve got many ink smudges.

Christmas arrived this year, and with it my boyfriend brought me a Moleskine notebook. A4, soft shelled, section sewn, and rather somewhat thin paper. The opposite of what I usually carry.

To my surprise though, I’ve switched to this notebook almost exclusively. Writing in an A4 book seems a lot more pleasant. Less worry about when to break a line, and fewer interruptions to turn the page. Despite the larger size, my bag is more than capable. Even the soft cover helps out here – it molds into whatever curve I need it to.

The folding flat issue was a genuine one to start with, but I’ve fixed that with a change of habit. Why bother writing on both sides of the book? I’ve never found writing on the left side of the book particularly comfortable anyway. Thinking back, the only reason I do write on the back sides of each sheet is because it was drilled into me in a cost-saving high school.

The paper maybe thinner than I’m used to, but the quality certainly isn’t lacking. A blue fountain pen is visible on the opposite side, but it certainly doesn’t bleed.

This Moleskine is lovely.