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 =
ma.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 =
ma.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.
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.
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:
http://codethought.com/blog/?p=233
In a nutshell (in case the page goes away) I used Pacifist (http://www.charlessoft.com/) to open the Java for Mac OS X 10.5 Update 6 (http://support.apple.com/downloads/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".
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
DERP!
When I removed this parameter and reran my debug unit test, everything worked just fine.
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.
MySQL
At some point I had to completely uninstall MySQL in order to reinstall it. This was helpful.
I went to http://dev.mysql.com/downloads/mysql/ 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:
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:
VERSIONER_PERL_VERSION=5.8.9
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:
VERSIONER_PERL_PREFER_32_BIT=yes
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/com.apple.versioner.perl 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: http://developer.apple.com/mac/library/documentation/Darwin/Reference/ManPages/man1/perl.1.html
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:
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:
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.
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
As you can see, there's this secret little config that is the cause of the problem:
As you can see, there's this secret little config that is the cause of the problem: