I’ve been trying to write a bunch of scripts that perform some database administration tasks in Groovy. Groovy offers a several advantages over most of my other options. It runs practically everywhere. There are JDBC drivers available for every database that I have to work with. Most importantly to me though, SQL programming in Groovy is really simple.

At first I thought it was a Groovy limitation that was causing my scripts to run out of heap space when I ran them against large tables. It turned out to be the default behavior of the MySQL JDBC driver though.

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

The solution is to override three default settings. The first two override the default settings of the ResultSets that will be returned by the statement.
sql.setResultSetType( java.sql.ResultSet.TYPE_FORWARD_ONLY )
sql.setResultSetConcurrency( java.sql.ResultSet.CONCUR_READ_ONLY )

This one adds a closure that gets called every time a statement gets created.
sql.withStatement{ stmt -> stmt.setFetchSize( Integer.MIN_VALUE ) }

If I could only take two pieces of software with me onto a deserted island, the first would be Vim, the second would be Screen. Screen provides two very important features that make my life quite a bit easier.

1. Persistence. If the network has an error, or I just happen to click the silly little x on my terminal window, then all I have to do is ssh back into the server and restart screen (screen -r). It will be like I never left. This means that I don’t have to worry about running everything with a nohup, and if I start a job at work I can go home and reconnect (screen -Dr) to my terminal session in order to continue monitoring the job.
2. Multiple windows. Ssh into the server, start up screen, start a job in that shell, type ‘Ctrl-a c’, and voila, a new shell in a new window. Now I can go and adjust the size and position of the windows. For instance, let’s say that I need to tail five different log files. I can start up five windows, resize and position them all on the screen, and then I’m monitoring all those files in one terminal session. I can switch back and forth between the windows by typing ‘Ctrl-a #’ where # is the number of the window, ‘Ctrl-a n’ for next, ‘Ctrl-a p’ for previous, or ‘Ctrl-a “‘ to get a list of all the windows that you can arrow up or down through. You can also rename the window you are in by typing ‘Ctrl-a A’.

Unfortunately, screen sucks straight out of the box. I recommend creating a .screenrc with these lines at a minimum:

startup_message off
shell bash

termcapinfo xterm "ks=E[?1lE:kuE[A:kd=E[B:kl=E[D:kr=E[C:kh=E[5~:kH=E[F"

caption always "%{= bb}%{+b w}%n %h %=%t %c"
hardstatus alwayslastline "%{-b gk}%-w%{+b kg}%50>%n %t%{-b gk}%+w%<"

" screen starts at window 0. These two lines make the first window 1, and
" binds 0 to window 10.
bind c screen 1
bind 0 select 10

After starting screen you should now have two lines at the bottom of your screen. The first should be blue and have the window number, name, and system time. The second should be green and have a listing of all the current windows and their names. I prefer a dark background and these colors work really well on top of black. You should review the screen manpage for more things that you can add to those status lines. One of my favorites is %l which displays the current load on the system.

Another thing that you can do with screen is create custom screenrc files for different situations. I have several that I use which automatically open certain windows for me. For instance, if I “cp .screenrc .mysqlscreenrc” and then add the following lines to the bottom of .mysqlscreenrc:

screen -t "database 1"   1 mysql -h hostname -u username -ppassword data1
screen -t "database 2"   2 mysql -h hostname -u username -ppassword data2
screen -t "localhost"    3 bash

Then, add this alias “alias mysqlscreen=’screen -S mysql -c ~/.mysqlscreenrc’”. Now all you have to do is type mysqlscreen when you log in, and it will automatically open three windows for you. Two of them will open up mysql monitor sessions, and the third will open up a shell. The -S part of the screen command names the screen session. That way if you become disconnected then a ’screen -ls’ will be more meaningful, and it will be easier to know which session is which when you do your ’screen -r’.

These two lines will create and display a custom status line. The status line will include the name of the file, the ascii and hex values for the character under the cursor, and the location of the cursor.

set statusline=%F%m%r%h%w\ ASC=[\%03.3b]\ HEX=[\\x\%02.2B]\ %l,%v\ %p%%
set laststatus=2

One of my servers at work hit a milestone last night:

4:32pm up 730 days, 22:25, 231 users, load average: 0.60, 0.63, 0.69

Update: Stupid Murphy and his stupid laws. Just spent an hour working on two other machines. Only solution appeared to be rebooting both machines.

Was reading this article on customizing Ubuntu today. Nothing really new in there for me except for this nugget:

There’s an old practical joke where people pry the keys off a keyboard and replace them out of order. A user who is not a touch typist can get really confused when they look for the right letters. The xmodmap command can be used for an excellent alternative to this prank. For example, you can add the following commands into the victim’s .bashrc file:xmodmap -e ‘keycode 91 = t T’
xmodmap -e ‘keycode 92 = s S’

This prank swaps the s and t keys on the keyboard without requiring any physical keyboard modifications. Alternately, if you can pry up and replace the keys on the keyboard, follow it up by remapping the keyboard so it actually works correctly!

Never thought of that before. Now I have no choice but to try it out.

This morning Bruce Schneier posted a fascinating article on the psychology and economics of buying bad products. The example that he uses is a “secure” USB keychain drive called Secustick that was, supposedly, commissioned by the French government, tested and approved by a French intelligence service, and is apparently in use all over the world by governments and major corporations in the financial services industries.

A week ago a review of the Secustick was posted on Tweakers.net. They loaded the software on the stick into a debugger and learned that all someone had to do was place a breakpoint after a function called VerifyPassWord(), alter the return code from a 0 to a 1, and voilà! (Pun intended.) Anybody could get access to the contents of the drive.

It’s generally my belief that the free market is efficient. However, Bruce’s essay brings up a good point. Shoddy products like the Secustick have an advantage in the marketplace. Namely, they cost less to produce. Normally this isn’t an issue because the consumer has the skillset and the ability to evaluate that the clothing they are buying from Walmart isn’t as high a quality as what they would buy from JC Penney’s. How are you supposed to know when it comes to something like the Secustick? Or a firewall? Or a virus scanner? Or a <insert any suitably complicated technology>?

Important rule, MySQL only supports using one index per join.

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index finds fewer rows and using that index to fetch the rows.

To demonstrate this I went and got some historical stock price data from this site. After that, I created a test table and added some indexes in order to test some concepts.
Read the rest of this entry »

It dawned on me this morning, following a conversation with Casey, that I don’t know nearly enough about MySQL database administration. The vast majority of my DB experience is with Informix and Oracle. So, I did a little reading up and performed some experiments.
Read the rest of this entry »

From the Energy Policy Act of 2005:

SEC. 110. DAYLIGHT SAVINGS.
(a) AMENDMENT.—Section 3(a) of the Uniform Time Act of 1966 15 U.S.C. 260a(a)) is amended—
(1) by striking ‘‘first Sunday of April’’ and inserting ‘‘second sunday of March’’; and
(2) by striking ‘‘last Sunday of October’’ and inserting ‘‘first Sunday of November’’.
(b) EFFECTIVE DATE.—Subsection (a) shall take effect 1 year after the date of enactment of this Act or March 1, 2007, whichever is later.
(c) REPORT TO CONGRESS.—Not later than 9 months after the effective date stated in subsection (b), the Secretary shall report to Congress on the impact of this section on energy consumption in the United States.
(d) RIGHT TO REVERT.—Congress retains the right to revert the Daylight Saving Time back to the 2005 time schedules once the Department study is complete.

If these two economists are right, then there’s a good chance we get to do the last few weeks over again in order to put everything back.

The other day I needed a quick and dirty solution to lock down an account so that it could only use scp or sftp into the machine. I could have installed something like rssh, but I needed something very quickly and wasn’t certain that I could get it installed/configured in time. Instead I added a shell to /etc/shells called /bin/noshell and I made it the users default shell. Then I created a file called /bin/noshell and put the following code in it:

#!/bin/bash

if [ -t 0 ]; then
        echo 'You are not allowed to login to this shell.'
else
        /bin/bash ${1} "${2}"
fi
exit 0

After making the script executable, it has the following results:

> scp filename.txt username@machine:
username@machine's password: filename.txt
100% 9695     9.5KB/s   00:00
> ssh username@machine
username@machine's password:
You are not allowed to login to this shell.
Connection to machine closed.

The reason that this works is that both scp and sftp work by initializing an ssh connection and starting scp or sftp on the server side. Basically, the command

scp filename username@machine:

is, initially at least, the same as sending the command

ssh username@machine ${SHELL} -c "scp -t ."

By changing the user’s shell to my script, I am effectively running that test “-t 0″ before giving them the opportunity to start the command that they are sending over. The test checks to see if the command is being run on a terminal or not.

Now, there are several issues with the approach that I took. The first is that a user who understands what I’m doing could very easily get around this. There’s nothing to stop them from sending their own commands instead of scp or sftp. This could be fixed rather easily though, all that someone would need to do is test ${2} to make certain that the string begins with either scp or sftp. The other issue that I have is that a user could easily write to files outside of their home directory. After testing to see if the command is scp or sftp all someone would have to do is replace ${2} with the appropriate command followed by a ” -t .” That would effectively close those two holes.

Here’s a perl version that I just found from the Snail Book. The author addresses the first issue that I have with my bash version in basically the same way I described above.