Monday, November 14, 2011

How to add up all numbers, one per line in a file

cat /tmp/foo | awk '{sum+=$1}END{print sum}'

(From Mike Masters, of course)

Friday, November 11, 2011

How to output the first line of each file in a directory

$ head -n 1 *

Sample output:
 $ head -n 1 *
==> Desktop <==

==> Development <==

==> Documents <==

==> Downloads <==

==> Dropbox <==

==> Environment <==

==> Library <==

==> Movies <==

==> Music <==

==> Pictures <==

==> Public <==

==> Sites <==

==> bin <==

==> current.html <==
Current IP CheckCurrent IP Address:

==> databases <==

==> my.cnf <==

(That's the directory structure of my home dir on my work machine.)

Wednesday, November 2, 2011

Notes on GROUP BY in MySQL

Here is a query I wanted to run, but I was concerned that the value of fat.rowsprocessed would not come from the same fat row as min(fat.processeddate).

select m.domain, ma.merchantacctid, ma.createddate, 
fat.rowsprocessed, min(fat.processeddate)
from merchant_account ma
join merchant m on m.merchantacctid = ma.merchantacctid
join ftp_audit_trail fat on fat.merchantacctid = 
where fat.processeddate > ma.createddate 
and fat.rowsprocessed > 0
and ma.createddate > '2009-12-31'
group by fat.merchantacctid
order by domain;

My buddy Spencer pointed out that in standard SQL, if you use an aggregate function, then you have to include all the other fields you are selecting in the group by. It turns out that there is an extension to GROUP BY, and to HAVING, in MySQL that enables you to use them on a single field:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.
11.15.3. GROUP BY and HAVING with Hidden Columns

I was afraid that the db would pick any value of rowsprocessed, that it would not come from the same row that the min(processeddate) is from.

select m.domain, ma.merchantacctid, ma.createddate, 
fat.rowsprocessed, fat.processeddate
from merchant_account ma
join merchant m on m.merchantacctid = ma.merchantacctid
join ftp_audit_trail fat on fat.merchantacctid = 
where fat.processeddate > ma.createddate 
and fat.rowsprocessed > 0
and ma.createddate > '2009-12-31'
group by fat.merchantacctid
having min(fat.processeddate)
order by domain;

HAVING is what I wanted to use. It's also not standard SQL legal but the same MySQL extension enables this.

I ran both, exported the csv's, and diff'd them, and they gave identical results. But I suspect that was luck in this case, and that the first query would not be dependably unarbitrary. I am more comfortable with the second query, using HAVING.

Monday, August 8, 2011

Set vim status line to show file name, format, column#, line#

:set statusline=%t\ %y\ format:\ %{&ff};\ [%c,%l]
Sample output: .vimrc [vim] format: unix [2,3].

Wednesday, July 13, 2011

How to restore Java 1.5 on Snow Leopard

Apple is so annoying. This morning I upgraded my Mac OS (a non-restart-required, supposedly low impact upgrade) and then discovered that my codebase, which requires Java 1.5, would no longer compile in IntelliJ. The upgrade had removed my install of 1.5 and replaced it with a symlink to 1.6. Why does Apple so badly want to force users into Java 1.6? It's extremely irritating to have to stop everything and remind myself how to do this all over again. I looked in here to see if I could find my notes and I could not.

On the command line:
cd /System/Library/Frameworks/JavaVM.framework/Versionssudo rm 1.5sudo rm 1.5.0
Open the file JavaForMacOSX10.5Update6.dmg with Pacifist.

Navigate inside the Pacifist display to /System/Library/Frameworks/JavaVM.framework/Versions.

Select 1.5.0 and Install to Default Location.

On the command line:
sudo ln -s 1.5.0 1.5

Some links:

UTA: I found my notes. In case these provide any additional context.

Restoring Java 1.5.22 to the machine

For some reason Apple saw fit to remove all versions of Java other than 1.6 in Snow Leopard. In the dir /System/Library/Frameworks/JavaVM.framework/Versions there are entries for "1.5" and "1.5.0" but they are symlinks to "CurrentJDK", which itself is a symlink to "1.6". In order to restore Java 1.5.22 I followed the suggestions of this blog page:

In a nutshell (in case the page goes away) I used Pacifist ( to open the Java for Mac OS X 10.5 Update 6 ( package, and selected only the 1.5 and 1.5.0 elements for install, rather than running the whole update. Before doing this I had to delete the empty symlinks "1.5" and "1.5.0".

It's possible - the blog author notes that this happened to him - when installing the latest Java update for OS X 10.{?} it will change the frameworks dir and rename the "1.5.0" folder to "1.5.0 1", installing the symlink to "CurrentJDK" in "1.5.0"'s place. If this happens, just jettison the new "1.5.0" and rename "1.5.0 1" back to "1.5.0".

Thursday, May 19, 2011

how to fix "javac: invalid target release: 1.6" error

For reasons not worth going into, we are still running our project code under Java 1.5, and this has caused no end of interesting scenarios, including today's issue when attempting to run a unit test in debug mode. I received the following error in Compile Messages in IntelliJ (v9, also an involved story):
Information:Usage: javac  
Information:where possible options include:
Information: -g Generate all debugging info
Information: -g:none Generate no debugging info
Information: -g:{lines,vars,source} Generate only some debugging info
Information: -verbose Output messages about what the compiler is doing
Information: -deprecation Output source locations where deprecated APIs are used
Information: -classpath Specify where to find user class files
Information: -cp Specify where to find user class files
Information: -sourcepath Specify where to find input source files
Information: -bootclasspath Override location of bootstrap class files
Information: -extdirs Override location of installed extensions
Information: -endorseddirs Override location of endorsed standards path
Information: -d Specify where to place generated class files
Information: -encoding Specify character encoding used by source files
Information: -source Provide source compatibility with specified release
Information: -target Generate class files for specific VM version
Information: -version Version information
Information: -help Print a synopsis of standard options
Information: -X Print a synopsis of nonstandard options
Information: -J Pass directly to the runtime system
Information:Compilation completed with 1 error and 0 warnings
Information:1 error
Information:0 warnings
Error:javac: invalid target release: 1.6

I googled the last line of this and got a bunch of recommendations regarding pointing JAVA_HOME at Java 1.6, but because of our constraint that solution is not for us. Also, there was no mismatch between the Java version being used by IntelliJ and that being used by Maven (you can do mvn -version to check).

Finally I discovered that IntelliJ has a hidden little default setting in the compiler that was effing it up. I discovered it via the Maven tab -> properties, but it's just a project setting. I used the top left search field to search for Java. That gives several results, including one for the Java Compiler:

As you can see, there's this secret little config that is the cause of the problem:

Additional command line parameters: -target 1.6


When I removed this parameter and reran my debug unit test, everything worked just fine.


Thursday, April 14, 2011

Perl girl, she's a Perl girl (sort of)

God, yesterday's setup of Perl was hairball. Due to the company requirement that the script I am working must run on Perl 5.8.x (not 5.10.x as standard in later Mac installs) and 32-bit rather than 64-bit, it was a true fustercluck. It took all day and many internet references, and a lot of help from awesome *nix/Mac guru Mike Masters, to get the script to run. Since this was so burly, and there wasn't enough info about on it, I thought I'd post the results of my efforts.


At some point I had to completely uninstall MySQL in order to reinstall it. This was helpful.

I went to and downloaded the Mac OS X ver. 10.6 (x86, 32-bit), DMG Archive. It's critical that if you use 32-bit Perl, you use 32-bit MySQL.

Since I had to reinstall MySQL, I had to start from scratch:

mysqladmin -u root password "<password>"

Then I logged in to mysql as root.

mysql -u root -p <password>

Once in mysql, I used the following to create myself a user - the same username that my Mac knows me as. This will be used by CPAN later in installing the Perl DBI/DBD module.

mysql> grant all on *.* to '<username>'@'localhost' identified by 's3kr1t';

CPAN will use the current username on the machine and this 's3kr1t' password for its DBD::mysql tests.

After you install Perl DBI/DBD, you can change the password to something actually secret for the MySQL account with:

$ mysqladmin -u <username> -ps3kr1t password "<new password>"


I needed a specific (earlier) version of Perl, and it had to be 32-bit. If you're using Snow Leopard you may notice that it's been upgraded to Perl version 5.10.0. It appears that at this point in time there is also a 5.8.x version included. In my case it's 5.8.9. If you look in /System/Library/Perl/ you can see the various versions installed.

$ cd /System/Library/Perl/
$ ll
total 0
drwxr-xr-x    6 root  wheel   204 Nov 19  2009 .
drwxr-xr-x   63 root  wheel  2142 Jan 10 11:23 ..
drwxr-xr-x  145 root  wheel  4930 Aug 17  2010 5.10.0
drwxr-xr-x  134 root  wheel  4556 Aug 17  2010 5.8.9
drwxr-xr-x    5 root  wheel   170 Aug 17  2010 Extras
drwxr-xr-x    4 root  wheel   136 Jun 26  2009 lib

To use the earlier version you need to set the following env variable:


Also, 5.10.0 defaults to 64-bit Perl which can lead to "byte order incompatible" errors, so even if you plan to use 5.10.0, but you want 32-bit, set this env var:


I added these to my .bash_profile but they only seem to apply within Perl. For the 32-bit setting, that's not a problem. But by default, CPAN installs (at the moment, on Mac) into /Library/Perl/5.10.0, which means any CPAN-installed libraries will be unreachable from Perl 5.8.9. Fortunately, the version setting can also be applied system-wide using the "defaults" command (I added this to .bash_profile, and also kept the "VERSIONER" ones):

defaults write /Library/Preferences/ Version 5.8.9

This will let CPAN know to install into /Library/Perl/5.8.9 instead of 5.10.0.

For more info on this, see:


I did this:
$ sudo cpan
cpan[1]> install DBI
cpan[2]> install DBD::mysql
cpan[3]> exit

That's it. There is a page of instructions I tried following, at first - Install MySQL and DBD::MySQL on Mac OS X - it was helpful, but some of the stuff in there isn't necessary, I found. They describe using CPAN to get the source and then installing "by hand". They do this to set up a custom user & password in the config that the installer will then use, as a workaround since CPAN uses the machine username and the password "s3kr1t" for MySQL to run its tests of DBD::mysql before the final make install.

However, if you use the trick of creating a user in MySQL with the machine username and the password "s3kr1t" as described above, you should be able to use CPAN to install without a problem. It worked perfectly for me.

Environment Variables

Since I use MacPorts to install some stuff (it gets installed in /opt/local/lib) and CPAN to install others (in /Library/Perl/), I want to make sure that Perl sees all of it. To do so, I included this env var in my .bash_profile:

export PERL5LIB=/opt/local/lib/perl5/site_perl/5.8.9:/opt/local/lib/perl5/site_perl/5.8.9/darwin-2level:/Library/Perl/5.8.9:/Library/Perl/5.8.9/darwin-thread-multi-2level:.

Binding Mismatch

So the most frustrating part of the whole day was when I installed everything and tried to run my script and I got this:

dyld: lazy symbol binding failed: Symbol not found: _Perl_Istack_sp_ptr
 Referenced from: /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle
 Expected in: flat namespace

dyld: Symbol not found: _Perl_Istack_sp_ptr
 Referenced from: /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle
 Expected in: flat namespace

If you get this or a variation on it, it's an indication that there is a mismatch in software being referenced. In my case, it was because I had this error in my .bash_profile:

export PERL5LIB=/opt/local/lib/perl5/site_perl/5.8.9:/opt/local/lib/perl5/site_perl/5.8.9/darwin-2level:/Library/Perl/5.10.0:/Library/Perl/5.10.0/darwin-thread-multi-2level:.

This had been left over from before I discovered that you can apply the Perl version system-wide and make CPAN install to your desired location. So it was trying to use 5.8.9 Perl, but then pointing to 5.10.0 libraries, and it was NOT HAPPY. Once I changed these to the 5.8.9 location, the dyld error went away.

Thursday, April 7, 2011

MySQL madness from MM

MYSQL> use information_schema
Database changed

MYSQL> show tables;
| Tables_in_information_schema          |
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
17 rows in set (0.01 sec)

MYSQL> select distinct grantee from user_privileges;
| grantee            |
| 'root'@'localhost' |
| 'root'@'dbro101'   |
| 'rc'@'localhost'   |
| ''@'dbro101'       |
| ''@'localhost'     |
| 'rc'@'%'           |
| 'redcarpet'@'%'    |
| 'sdc_updater'@'%'  |
8 rows in set (0.01 sec)

MYSQL> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
MYSQL> GRANT ALL ON *.* TO 'jeffrey'@'localhost'; 

Wednesday, March 9, 2011

I want to be like Mike (Masters)

Mike gave me some crib notes on unix:

df -h
reports file system disk space usage

du -h
shows disk usage by directory
-h is human readable

du -h --max-depth=1
summarizes all the du under a dir at a certain depth. this takes a long time at root level, so you wouldn't do it there

cat /proc/cpuinfo (no /proc dir on a Mac)
summarizes the cpu info for the machine

/bin is scripts and things
/etc is configs & default settings
/dev is devices
/lib is header files for c libraries and such
/var/log is usually where logs are
/usr/local for installing new software (many install scripts look for /usr/local)
/var/lib is another place software may be installed

sudo su
allows you to emulate root