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';