Unlocking MySQL table locks

I’ve built a PHP web app that’s used quite heavily for some moderately intensive MySQL queries.

There’s an issue with it: every so often, I see PDOExceptions with the message:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

I haven’t managed yet to trace exactly what the problem is, or what’s causing it… but I was Googling how to make MySQL drop locks, and found this StackOverflow post that I thought was quite useful:

# Log into MySQL
mysql -u your_user -p

# Show tables that are being used
mysql> show open tables where in_use>0;

# Show what processes are running
mysql> show processlist;

# Kill a process that's (presumably) locking a table
mysql> kill put_process_id_here;

Getting a local Git repo linked to a remote (Bitbucket) repo

Create a Bitbucket repo

I created a private git repo on Bitbucket.

SSH authentication

I created a SSH private/public key pair on my Mac:

$ cd ~/.ssh
$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/Users/me/.ssh/id_rsa): test_rsa
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in test_rsa.
Your public key has been saved in test_rsa.pub.

I left the passphrase empty because I didn’t want Bitbucket to prompt me for it on every push.

I uploaded the public key to Bitbucket:

  • In Bitbucket I navigated to My Account > Settings > Security > SSH Keys
  • I clicked Add SSH Key.
  • On the Mac I ran cat ~/.ssh/test_rsa.pub | pbcopy
  • I pasted the result into the Bitbucket screen, and saved the key pair

Initialise local repo

$ mkdir /path/to/your/project
$ cd /path/to/your/project
$ git init
$ git remote add origin git@bitbucket.org:myusername/myreponame.git

Create some content

$ echo "Hello world" >> test.txt
$ git add --all
$ git commit -am 'Initial commit'
$ git push -u origin master

For me, this FAILED.

I don’t know if it’s something very specific to my configuration, but none of the quickly-accessible Atlassian KB pages or Stack Overflow pages helped.

UNTIL I looked in an existing local repo’s .git/config file:

... some config...
[remote "origin"]
    url = https://username@bitbucket.org/username/reponame.git
    fetch = +refs/heads/*:refs/remotes/origin/*
...more config...

When I overwrote my “broken” local repo’s config, so it used a HTTPS version of the remote repo’s URL, I could interact with the remote repo without having to give a password.

Securing Apache: disabling SSLv3

A client of mine is seeking PCI compliance and one of the aspects they were failing on was that an Apache web server was allowing access over SSLv3.

To disable it, here’s what I did (as root user):

me:/etc/apache2#cd /etc/apache2
me:/etc/apache2#grep -i -r "SSLEngine" *
mods-available/ssl.conf:    SSLProtocol all
me:/etc/apache2#nano mods-available/ssl.conf

[Edits ssl.conf]
[Finds line SSLProtocol all; changes it to...]

SSLProtocol all -SSLv2 -SSLv3

[Saves changes to ssl.conf]

me:/etc/apache2#service apache2 restart

Ubuntu 14.04 shell script to pull updates from a Bitbucket repo

  • You’ll need to create a public/private RSA key pair (see here for details)
  • The eval and ssh-add lines make sure SSH Agent is active
  • git reset –hard is maybe a little bit strong, but there you go
#!/bin/bash

eval `ssh-agent -s`
ssh-add /path/to/rsa-key-file-name

cd /path/to/repo-root

git fetch
git reset --hard origin/master

#echo "Finished."

Cloning a private Bitbucket repo on Ubuntu via CLI

So I need to clone a private git repository hosted with Bitbucket, on a new Ubuntu 14.04 VPS. Here’s how I got it working…

Get Bitbucket to know and trust the VPS

First, I needed to create a public/private SSH key pair:

  • cd ~/.ssh
  • ssh-keygen -t rsa -b 4096 -C “myemail@mydomain.com”
  • [Enter name of file, EG key-file-name]
  • Enter/confirm passphrase [leave empty for no passphrase…]

Next, I had to add my key to SSH Agent:

  • eval “$(ssh-agent -s)” [to make sure SSH Agent is active]
  • ssh-add /path/to/key-file-name

Next I had to make Bitbucket aware of the key pair:

  • Got public key into clipboard. If you have Linux GUI going, or you’re on a Mac, you can use a special command for this, but I just ran cat key-file-name.pub and copied from the Mac terminal window to my clipboard.
  • In bitbucket’s site, I created a new SSH Key, named it (key-file-name) and pasted the contents of the public key into it.
  • Tested the connection with ssh -T git@bitbucket.org [it worked!]

Clone the repo

  • In bitbucket site, select the repo
  • Click the Actions icon (… ellipsis just below trash can)
  • Click the Clone link
  • Copy the CLI command to clone the repo – something like git clone git@bitbucket.org:username/reponame.git
  • Paste it into VPS CLI and hit ENTER
  • It clones…

Recovering MySQL after crash + InnoDB corruption

Ugh, so the Ubuntu 14.04 VPS crashed, corrupting MySQL as it went. The initial symptom was that web apps couldn’t connect to MySQL… the error message mentioned not being able to connect via a socket, but I knew the web apps were configured to connect to 127.0.0.1 – IE connect via TCP/IP, not over a socket.

Eventually I ran

tail -f /var/log/mysql/error.log

…and I could see entries appearing in the log that suggested something was wrong with the InnoDB tablespace: data corruption, causing MySQL startup to fail.

Here are the steps I took to get it working (…as the server’s root user):

Stop MySQL

This was surprisingly difficult – I think because mysqld_safe was running, and was restarting mysqld every time I ran a command like service mysql stop or kill -9 [mysqld PID goes here].

What seemed to work for me was the mysqladmin shut command but, from the tone of forum posts discussing similar problems, I suspect YMMV.

Start MySQL, forcing INNODB recovery

So you can set a MySQL configuration value that forces MySQL to start even a normal startup would abort due to data corruption issues. The configuration flag is innodb_force_recovery.

You need to write it into your MySQL configuration file – mine’s at /etc/mysql/my.cnf:

...
[mysqld]
user            = mysql
...
skip-external-locking
innodb_force_recovery = 1
# a value of 0 means do not force recovery at all; 6 means just start, no matter the risk

WARNING: Depending on the value (1 … 6) MySQL skips more and more safety-checking steps in order to start up. Once you get to 4, MySQL is taking significant risks as it starts – IE you risk making corruption worse, or causing more serious problems, if you start with innodb_force_recovery = 4 (or above).

I tried with a value of 1; that didn’t work; so I raised it to 2, and in my case that was enough to get MySQL started.

Backup MySQL data as SQL dump

Next I ran the command

mysqldump --skip-lock-tables -A > alldb.sql

Backup old MySQL data files

First, check where MySQL is keeping its data:

mysql -NBe "SELECT @@datadir"
/var/lib/mysql/

Now, you can move the existing files to back them up and, from MySQL’s point of view, disappear them at the same time:

mv /var/lib/mysql /var/lib/mysql.old

Create new version of MySQL data file directory

mkdir /var/lib/mysql
mysql_install_db

Restart MySQL and import your SQL

/etc/init.d/mysql start
mysql < alldb.sql

These steps, I think, sorted out the data itself. But I still needed to reset the MySQL root password; and the web apps still couldn’t access the database – although the errors now were to do with permissions, not actual connection to the DB.

Resetting MySQL root password

mysqladmin shut
mysqld_safe --skip-grant-tables &

mysql -uroot
mysql>use mysql;
mysql>update user set password=PASSWORD("mynewpassword") where User='root';
mysql>flush privileges;
mysql>quit

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

I think that last step (MySQL flush privileges command) may have helped all user accounts, because having done all that, the web apps could reach the database again… and they seemed to work OK.