Tuesday, November 23, 2010

bash one liners to setup password-less SSH

ssh user@host "cat >> .ssh/authorized_keys2" < .ssh/id_rsa.pub

This will append the ssh public key on the local machine to the authorized_keys file in the remote machine so that the local machine will in the future be able to ssh to the remote without a password.

If you are setting up multiple machines this way, this one liner is faster than having to ssh into each remote to update the authorized_keys file.

You could build on this to setup multiple machines with a single command:

for m in host1 host2 host3 host4; do ssh user@$m "cat >> .ssh/authorized_keys2" < .ssh/id_rsa.pub; done

Monday, November 15, 2010

C2 A0 characters confusing bash?

Have you had a perfectly typed shell command fail on you, like this :

user@host:$ ps auxww | grep java
 grep: command not found

Here is the hex output of a correct "grep" and an incorrect "grep" command line:

user@host$ hexdump -C /tmp/x
00000000  70 73 20 61 75 78 77 77  20 7c 20 67 72 65 70 20  |ps auxww | grep |
00000010  6a 61 76 61 0a                                    |java.|
user@host$ hexdump -C /tmp/y
00000000  70 73 20 61 75 78 77 77  20 7c c2 a0 67 72 65 70  |ps auxww |..grep|
00000010  20 6a 61 76 61 0a                                 | java.|

The second output is the faulty one, notice the characters "C2 A0" cause the problem. A0 is the non-breaking space, and somehow, my keyboard at times produces these instead of "20" for the space character, thus confusing the shell.

This is on an ssh session to Linux 2.6, from a Mac.

Saturday, November 13, 2010

mysql deadlocks with concurrent inserts

It is possible to cause deadlocks in mysql (Innodb) on concurrent insert statements, without there being any transactions in progress. Deadlocks are possible even when the inserts don't collide on any key.

The deadlocks occur due to gap locking done by mysql. There are several reasons for gap locking, and in this particular case, it has to do with preserving a unique key constraint on an index. The situation presents itself to us this way: There is a unique key constraint on a column and we are doing an insert. Mysql has to make sure that the lock it takes is sufficient to prevent another concurrent insert from adding a record with the same key, thus breaking the unique key constraint.

Mysql innodb engine performs row locking on inserts. If column A has a unique key constraint, and we are adding the value "bbb" for column A in an insert statement, mysql needs to lock any gap in the index between the two current records where "bbb" will be inserted at.

To illustrate the deadlock, let us start with a table schema:

TABLE vegetable (
   id bigint(10) NOT NULL auto_increment,
   name varchar(255) NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY uk_name (name)

Let us assume the existence of these records in the table, and look at them in 'name' index order:

id name
10 ggg
05 jjj

Now, imagine two concurrent connections executing the following inserts in the following order:

Connection 1:

insert ignore into vegetable values(null, "ppp");

For this insert to proceed, connection 1 will lock the gap between "jjj" and "ppp" in the name index.

Connection 2:

insert ignore into vegetable values (null,"iii");

This will require locking the gap after "ggg", upto "iii". Since the lock from connection 1 does not span this, it will succeed.

insert ignore into vegetable values (null, "mmm");

This needs to lock the gap after "jjj" upto "mmm". Since connection 1 has a lock between "jjj" and "ppp", effectively spanning the lock connection 2 is attempting to take, this will block.

Connection 1:

insert ignore into vegetable values (null, "hhh");

This requires the gap lock between "ggg" and "hhh". This will block as it spans the the lock ["ggg" to "iii"] held by connection 2.

Thus we have both connections blocked on each other. This is the deadlock.

Here is a diagram. Transactions to the left are done by Connection 2. Transactions to the right are done by Connection 1. The sequence of transactions is donated by numbers 1) through 4).

Connection 2                         Connection1

---------------------------  ggg
G                                           G
A                                           AP
P                                            <------------------- 4) hhh
Lock                                                                  blocks (deadlock)
2) iii -------------------->

---------------------------  jjj 
G                                           G
P                                            A
3) mmm --------------->            P  
                                             <--------------------- 1) ppp

You can avoid this if you can order the inserts on each connection on the same direction. The deadlock happens as connection 2 inserts in ascending order of the index, while connection 1 inserts on descending order.

If you can't do this for practical reasons, you could retry the operation. Unless there is a high level of concurrency with a high load on the db where each transaction takes a heavy hit, a simple retry should work.

Monday, November 08, 2010

Bash math, command expansion and pipes

How do you add the number of lines in two files?

echo $((`wc -l /path/to/file1.txt|cut -f 1 -d ' '`+`wc -l /path/to/file2.txt|cut -f 1 -d ' '`))