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.

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.

Daylight saving time has been extended. Effective this year it’s starting on March 11 and ending November 4. This means lots and lots of patching. For the most part it’s only the OS that needs to be patched. But there are a few applications out there, notably Java, that don’t make standard system calls to manipulate dates and times. At first I didn’t think this was a big deal. However, how many different databases embed JVM’s in them as a stored procedure language? Oracle, Informix, and DB2 come to mind. What other apps am I missing?

Something interesting that I found this week, with the GNU version of the date command you can do this:

$ date -d ‘yesterday’ +%Y%m%d
20070106

What’s special about that? Well, the standard *nix date command only supports three options (-a, -u, and -s) and optionally let’s you pass it a string dictating the format that you want it to display the date in. So, it makes it easy to find out what today’s date is, but how do we know what yesterdays date was? Because of that limitation I have this big library of functions that can be invoked in a shell script that perform some of the common time and date manipulations that we need in our scripts at work. The GNU version makes it too easy. For instance, let’s suppose that you need to pass the first and last days of the previous month in a script:

$ date -d “$(date +%Y-%m-1) -1 month” +%Y%m%d
20061201
$ date -d “$(date +%Y-%m-1) -1 day” +%Y%m%d
20061231

How about the start and end of next month:

$ date -d “$(date +%Y-%m-1) +1 month” +%Y%m%d
20070201
$ date -d “$(date -d “$(date +%Y-%m-1) +2 month” +%Y-%m-%d) -1 day” +%Y%m%d
20070228

While I’m talking about date and time stuff, nobody should forget that they moved up Daylight Savings Time this year in the United States to March instead of April. You need to make certain that your servers are patched to handle the new dates.