How to set SQL modes for MySQL in a CircleCI container

In circle.yml you can add comma-separated sql-modes to /etc/mysql/my.cnf and then restart the mysql service, e.g.:

database:
  pre:
    - echo "[mysqld]" | sudo tee -a /etc/mysql/my.cnf
    - echo 'sql-mode="ALLOW_INVALID_DATES"' | sudo tee -a /etc/mysql/my.cnf
    - sudo service mysql restart

OS X Setup Guide

Recently I got a new work laptop. Today I configured it and documented the process. If I make any changes to these instructions I’ll update them in this Gist.

Update: I recently had to set up a new computer, so I took the opportunity to automate a lot of this stuff: https://github.com/bi1yeu/new-macos

Here are the programs and tweaks I first made on a fresh install of OS X 10.11:

Homebrew / Cask

$ /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
$ brew tap caskroom/cask
$ brew tap caskroom/versions
$ brew tap caskroom/fonts
$ brew tap tldr-pages/tldr

Rearrage PATHs

This is so that Homebrew-installed tools load instead of built-in ones. git, emacs, etc.

$ sudo vi /etc/paths

Put /usr/local/bin above /usr/bin in the list. Save file and restart terminal.

Install with Cask

$ brew cask install alfred                # https://www.alfredapp.com/
$ brew cask install flux                  # https://justgetflux.com/
$ brew cask install font-source-code-pro  # https://github.com/adobe-fonts/source-code-pro
$ brew cask install google-chrome         # https://www.google.com/chrome/browser/desktop/
$ brew cask install iterm2-nightly        # http://iterm2.com/
$ brew cask install java                  # https://java.com/en/download/
$ brew cask install keepingyouawake       # https://github.com/newmarcel/KeepingYouAwake
$ brew cask install lunchy                # https://github.com/eddiezane/lunchy
$ brew cask install middleclick           # http://rouge41.com/labs/
$ brew cask install osxfuse               # https://osxfuse.github.io/
$ brew cask install spectacle             # https://www.spectacleapp.com/

Install with Homebrew

$ brew install aspell                     # http://aspell.net/
$ brew install git                        # https://git-scm.com/
$ brew install gnupg2                     # https://www.gnupg.org/
$ brew install homebrew/fuse/sshfs        # https://github.com/libfuse/sshfs
$ brew install httpie                     # https://github.com/jkbrzt/httpie
$ brew install jq                         # https://stedolan.github.io/jq/
$ brew install leiningen                  # http://leiningen.org/
$ brew install markdown                   # http://daringfireball.net/projects/markdown/
$ brew install pandoc                     # http://pandoc.org/
$ brew install task                       # http://taskwarrior.org/
$ brew install the_platinum_searcher      # https://github.com/monochromegane/the_platinum_searcher
$ brew install tldr                       # http://tldr-pages.github.io/

launchctl / lunchy

build locate database:

$ lunchy install /System/Library/LaunchDaemons/com.apple.locate.plist
$ lunchy start com.apple.locate.plist

Create plist to periodically run brew update (replace <username> below):

  <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
  <plist version="1.0">
  <dict>
      <key>Label</key>
      <string>com.<username>.brew-update</string>
      <key>ProgramArguments</key>
      <array>
          <string>/usr/local/bin/brew</string>
          <string>update</string>
      </array>
      <key>StartCalendarInterval</key>
      <dict>
        <key>Hour</key>
        <integer>2</integer>
        <key>Minute</key>
        <integer>0</integer>
      </dict>
      <key>RunAtLoad</key>
      <true />
  </dict>
  </plist>

And start it:

$ lunchy start ~/Library/LaunchAgents/com.<username>.brew-update.plist

Git configuration

$ git config --global user.name '<your-proper-name>'
$ git config --global user.email <email-address>
$ git config --global core.editor 'vim'

Spacemacs

http://spacemacs.org/

$ brew install --with-cocoa emacs
$ ln -s /usr/local/Cellar/emacs/24.5/Emacs.app /Applications
$ git clone https://github.com/syl20bnr/spacemacs ~/.emacs.d
$ git clone https://github.com/bi1yeu/dotfiles
$ cd ~/dotfiles/ && ./link-dotfiles.sh
$ emacs --daemon && emacsclient -c &

Bash prompt

$ brew install bash-git-prompt

Add to .bash_profile:

if [ -f "$(brew --prefix)/opt/bash-git-prompt/share/gitprompt.sh" ]; then
    source "$(brew --prefix)/opt/bash-git-prompt/share/gitprompt.sh"
fi

GIT_PROMPT_ONLY_IN_REPO=1

Link Google Account to iCal

Prefs

• Map Caps Lock key to Control modifier in Keyboard prefs
• Adjust key repeat in Keyboard prefs
• Enable Remote Login in Sharing prefs
• Prevent computer from sleeping automatically when the display is off in Energy Saver prefs
• Put display to sleep in Mission Control > Hot Corners prefs

Generate SSH key and add it to ssh-agent

$ ssh-keygen -t rsa -b 4096 -C "<email-address>"
$ chmod 700 ~/.ssh && chmod 600 ~/.ssh/*
$ eval "$(ssh-agent -s)"
$ ssh-add ~/.ssh/id_rsa

Edit iTerm2 settings to bind alt+arrow keys to move forward/backward

https://coderwall.com/p/h6yfda/use-and-to-jump-forwards-backwards-words-in-iterm-2-on-os-x

Auto-incremented values can appear out of order if inserted in transactions

Last week I was surprised to learn that auto-incrementing field values in MySQL don't always necessarily appear in sorted order to clients. That is, it's possible for a count of rows in a table to increase while the max of an auto-inc col remains unchanged. Auto-incremented values are created when the record is inserted, not necessarily when the transaction during which the record is inserted is committed.

One of our systems at work can replicate updated rows from a table given that a column in it contains strictly incrementing values denoting the order of updates. I.e., if our system observes a row where the replication column has value X, subsequent replication jobs will only query for rows with a replication column value >= X. This is useful for efficiently replicating tables with a modified_at column or an append-only table with an incrementing ID. Most of the time...

We have a user who was inserting rows very rapidly into one such source database. They were using an AUTO_INCREMENTing column as a replication key for our system. In general there's no issue with doing this. We found, however, that in this case, rows were being inserted in batches as part of transactions happening parallel.

I discovered that auto-incrementing values are created at insertion time, not at commit time. In the case of relatively long-running transactions that happen simultaneously, this can result in auto-inc field values being visible in an unpredictable order.

It's fairly easy to illustrate this problem. Here I use two MySQL connections to show what can happen when there are multiple transactions inserting into a table simultaneously.

First, create a table with an auto-inc field and insert a row.

mysql> CREATE TABLE animals (
    ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->      name CHAR(30) NOT NULL,
    ->      PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into animals (name) values ('aardvark');
Query OK, 1 row affected (0.02 sec)

Observe that a simple SELECT query returns the row, as expected. The id field starts at 1.

mysql> select * from animals;
+----+----------+
| id | name     |
+----+----------+
|  1 | aardvark |
+----+----------+
1 row in set (0.00 sec)

Now, start a new transaction and insert some more records:

mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into animals (name) values ('bear'), ('cow'), ('dinosaur');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

In another terminal, connect to the database and select from the table. You'll see only the original row is visible. The three records inserted during the transaction have not yet been committed.

mysql> select * from animals;
+----+----------+
| id | name     |
+----+----------+
|  1 | aardvark |
+----+----------+
1 row in set (0.00 sec)

Now, still in the second terminal, start a separate transaction and insert three more records. This time, though, commit the transaction changes.

If you query the database after that commit, you'll see that the three most recently committed rows are visible and are returned by the query. Also note, importantly, that auto-incrementing id field values have incremented past the values that were inserted but not yet committed.

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into animals (name) values ('elephant'), ('frog'), ('groundhog');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | aardvark  |
|  5 | elephant  |
|  6 | frog      |
|  7 | groundhog |
+----+-----------+
4 rows in set (0.00 sec)

Back in the first terminal, you can commit the first transaction. Now all the records will be returned by a SELECT.

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | aardvark  |
|  2 | bear      |
|  3 | cow       |
|  4 | dinosaur  |
|  5 | elephant  |
|  6 | frog      |
|  7 | groundhog |
+----+-----------+
7 rows in set (0.00 sec)

I think the moral of the story is: don't rely on the order in which values in an AUTO_INCREMENT column appear. There could be rows in transactional flight with smaller values than the max of the auto-inc column.

Online courses

I recently completed the Machine Learning Foundations course offered through Coursera by the University of Washington. I found it to be more accessible and more of a survey class than Andrew Ng's Machine Learning course. It was eye-opening to learn how powerful machine learning algorithms are relatively easy to begin working with. Thinking about what kinds of breakthroughs the present AI summer will lead to is exciting. I'll probably continue to take a hobbyist's interest in ML and deep learning--luckily there's an endless torrent of free or inexpensive material on the subject.

After that I decided to take another course that was more applicable to my regular SE job--Functional Programming Principles in Scala. So far the class is challenging and fun. And it's nice to have an excuse to work with IntelliJ IDEA. I have a decent grasp of practical functional programming from doing Clojure development at work, but I wanted to get a more formal exposure to it.

Started using Spacemacs

For a long time I'd been using Sublime Text as my primary code/text editor, and I loved it very much. A few months ago, though, I decided it was time for a change. Connecting to an nrepl via Sublime wasn't supported, I had some concerns about the long-term support of a closed-source editor, and I started hearing about an editor called Spacemacs.

As someone who doesn't find the endless text editor war particularly interesting, and who just wanted a modern, powerful all-purpose editor, Spacemacs appealed to me. Longtime users of either Vim or Emacs will likely scoff at Spacemacs' unholy marriage of the two. Spacemacs is essentially a bunch of configuration on top of Emacs with Vim keybindings as the default ("The best editor is neither Emacs nor Vim, it's Emacs and Vim!"). Whenever I tried to pick up Emacs in the past, I found the keybindings unintuitive. I liked Vim keybindings more (and had been using ST's Vintage Mode to approximate that), but both Emacs and Vim lacked for me the seamlessness of ST's Package Control and its aesthetic.

Spacemacs seems to be the sweet spot for now. Its "layers" system is almost as simple as ST's package manager and the keybindings are logical and consistent across modes. And it looks good with very little customization.

It's not without warts--some mode buffers don't support evil keybindings and will fall back to Emacs bindings. The community-configuration is a double-edged sword: you get a lot of nice stuff out of the box, but when something goes wrong it can be hard to know how to fix it, although the community I've found to be helpful.

Some of my coworkers seem to have expressed worry that I'm investing time into what they see as a dead-end editor--that ten years from now I'll have accumulated a bunch of useless muscle memory. I'm not too concerned.