Wright This Way

MySQL issue with short password hashes...

Some time in the past, during an upgrade to the MySQL software here, I somehow missed a step and didn't upgrade privileges properly, and after upgrading to PHP 5.3, have now finally gotten the dreaded 'mysqlnd cannot connect to MySQL 4.1+ using old authentication' error when launching phpmyadmin. A lot of googling failed to identify an easy fix, but did yield a few clues.

Some time back, MySQL expanded the 'user' table of the 'mysql' database to allow for longer password hashes. But it seems that new hashes weren't generated, and the shorter hashes were still in place for those old user accounts So, ultimately, the fix was simple, just reset the passwords for the old user accounts, using the same passwords, which would update the hash to the new longer version.

If in doubt, the following MySQL commands will show the critical fields:

mysql> use mysql
mysql> select host, user, password from user;

This will show the above fields, the shorter password hashes are 16 bytes long, the newer version is 41 bytes in length. All you need to do is update any of the shorter ones and you're done!

Details on upgrades

To recap my recent upgrades here, I was transitioning my old web/mail server from a G4 box running OS 10.4 to a new Mac Mini running OS X 10.5. Funny that I wrote about using a Mini as a server back in 2005, and I'm only now finally getting around to putting one in here...

So, the basic process here was shutting down Postfix, then using Carbon Copy Cloner to clone my existing server to the Mac Mini (booted in Target Disk Mode), then rebooting the Mini into the Mac OS X 10.5 Installer. The Installer had absolutely no problems upgrading a PPC version of OS 10.5 to an Intel OS running 10.5, which was great. I really did not want to do a clean install, which would have been more of a hassle in converting mail files and other lower level items.

The next necessary step after installing 10.5 was to install Xcode 3.0, in order to compile all the apps I needed. Once that was done, I was finally able to start getting things up and running.

From prior dry runs, I had done a lot of testing of various packages to make sure that things would compile properly, and run without errors. There was a good bit of trial and error, and lots of googling. And thanks to someone else googling and finding an earlier entry of mine, a helpful tip out of the blue (Thanks to Paul S.) that helped massively. I had partitioned my drive so that I had a nice workspace partition to hold files between attempts at cloning and upgrading, and I had saved a few helpful notes there as well, which was very handy.

As I had mentioned a few days ago, the unix system accounts for postfix, mysql, www, and others, now for some reason all begin with an underscore character, so I had to edit a few config files where these accounts were specifically used to make sure that they reflected the current users. Also, 10.5 now runs Apache 2.2.x and not Apache 1.x, so I had to do some reading up on how this gets configured in order to migrate my config files, there were few surprises there, once I paid attention to the sample config files. Having saved copies of my working config files from earlier runs, it was a simple matter to copy these over before starting other work.

In retrospect, I should have worked on getting the mail server up and running before the web server, I didn't lose any mail, but I just hated it being offline as long as it was...

Starting with the web side, I installed MySQL 5.0.45 using a pre-build package, I used the one built for 10.4 Intel, and plan to upgrade that to a 10.5 specific build once one is available. After installing this, I simply copied my data folder over, ran the mysql_upgrade script, and all was well. Next, I compiled DBI-1.601 and DBD-mysql-4.005. For some reason I wasn't able to track down, DBD insisted on looking for mysql/lib files in /mysql/lib/mysql, even though my install never mentioned this path anywhere. Some googling finally revealed that the easiest fix was simply to fake it with some symlink trickery:

cd /usr/local/mysql/lib
sudo mkdir mysql
cd mysql
sudo ln -s ../*

DBI compiled fine, DBD threw up an error about incompatible pointers, which I was stuck at for a day or two before finding out that this was just a warning and could be ignored. Sure enough, it ran just fine, and I found that MovableType was now working fine. During the final install of everything, I discovered that I had to reset access privs for my web folder in order for MT to be able to write files, but after doing that, it worked fine again. I'm saving my upgrade to MovableType 4.x for another day.

Compiling Postfix was fairly straightforward, as before, I built Postfix according to the standard install docs to include MySQL and PCRE support, but this time included SASL in the mix. It is very important to read the SASL docs, there was a bit of needing to create symlinks and make sure that header files were in the right locations, but once I followed all the steps outlined, it compiled fine.

The Courier-IMAP pieces drove me nuts for several days. Courier-IMAP 4.2.1, the latest build, just couldn't be made to work here, I eventually tried building an older version, 4.1.3, and that worked just fine. Courier-Authlib 0.60.2 compiled but had problems running, the trick mailed in my Paul S. was to enter the following before doing the compile:

export MACOSX_DEPLOYMENT_TARGET=10.5

This handy command has been around for a few OS releases now, and forces some settings that apparently don't get set otherwise, a quick google search found many packages needing this to compile properly. Once set, AuthLib compiled properly and more importantly, ran properly.

Despite doing the 'migrate' steps, though, my old Courier settings never made it over, and so I had to edit the authmysqlrc and some other Courier files by hand using my older versions as templates, but this work was done in short order.

One site that helped a lot in checking over some of my steps was this one:

http://switch.richard5.net/isp-in-a-box-v2/building-the-mail-server-components/building-the-courier-imap-server/

The versions used there weren't current, but helped to validate what I was trying to do here, and setting the proper CFLAGS and compile arguments. His setup there was very similar to mine, virtual domains, MySQL authentication, etc, which was a great help.

With this done, I was now actually able to check mail the last necessary step, which made a good stopping point for the night with a fairly functioning server.

The next day, I tested a few more functions of the system, and found that one of the web packages I had installed was having problems with MySQL. This turned out to be a PHP issue connecting to MySQL, it was looking for the mysql.sock file in /var instead of /tmp. The easiest fix here was to create a /etc/php.ini file, consisting of the following:

; Default socket name for local MySQL connects.  If empty, uses the built-in MySQL defaults.
mysql.default_socket = /tmp/mysql.sock
; Default socket name for local MySQL connects.  If empty, uses the built-in MySQL defaults.
mysqli.default_socket = /tmp/mysql.sock

The second section for mysqli was required for version of MySQL 4.1 and later, once this was in place and Apache stopped and started, this problem was now history.

The last hurdle I had was getting policyd running, this is the greylisting package I use with Postfix. I had been struggling for some time to get newer builds of this running. I had somehow hacked the 1.7.x version into running previously, and was never able to duplicate my success with later builds. Thanks to some outstanding work by the developers, the final fixes to this are now available in the latest SVN builds, and I was able to get the 1.9.x experimental build to compile successfully, and more importantly, to run successfully as well.

In closing, what I'd like to say here is that when you're rolling your own code, patience is your best friend. Take things one step at a time, make sure you have a backup, and when you hit a wall, do searches and ask questions on mailing lists until you find the answers you need. If all else fails, post about your failures, and someone else might stumble across your post and supply the answers you need, it's amazing how things like that work out sometimes.

Server upgraded successfully

The server here has been successfully upgraded to Mac OS X 10.5. A few tips rolled in earlier this week that resolved the last of my compile issues (details to follow soon), so last night I cloned everything over to the new box and started the upgrade process.

One important tip, installing Xcode is kinda important. It's the little things you forget to do... :)

Upgrades... Hmmm.

A relatively sleepless weekend, and not in a good way. Here's an interesting tidbit, the common unix accounts such as postfix, mysql, www, and others, are now prefaced by an underscore character in OS X 10.5. Why, I have no idea, but when configuring scripts, make sure to change the usernames.

The switch to Apache2 for the most part went pretty well, it took a bit of trial and error to get my virtual domains working, but once I went back and poured over the sample configs, it all started to make sense. Just copying and pasting relevant bits from my old config files was not the way to go. :)

Minor issues compiling the DBD::mysql module, I had to use a slightly older version and it worked fine.

Courier-IMAP is what drove me absolutely nuts, I finally thought I had it all going, and then discovered that the auth module was throwing errors in the log, and I'm still trying to track that down. Also, despite attempting to migrate my older Courier settings, this apparently didn't happen, best thing may be to build it all up from scratch.

Leopard Day...

Mac OS X 10.5 (Leopard) is available in stores, and of course I've been playing with it for a bit. The new tabbed Terminal is great, having several terminal sessions all open in separate tabs instead of multiple windows is great, it really helps keep things organized.

Anyway, my first pass at upgrading my mail/web server from PPC 10.5 to Intel 10.5 went pretty well, everything actually seemed to launch and run correctly, web services worked, postfix was running, etc. Of course, I want to compile Intel binaries and not use the PPC codes, so I've been testing some installs. So far, most of them are going well, a few minor snags though, but I hope to have things working on the new hardware by the time the weekend is done.

Upgrade prep

I had forgotten what a pain in the ass a major upgrade can be... Made even worse by switching platforms, going from a PowerPC based Mac to an Intel based one.

I did a number of 'test compiles' on the Mac Mini just to make sure that things looked like they'd be working come Leopard day, and figured out that in order to clone my old server over to the new box, I'd have to format the drive with a GUID partition map, something not done by default when formatting from a G4 system... It's the little things that get ya.

So, quick checklist of things to do:

Format Mini's drive as GUID
Download latest MySQL 5, MovableType, Courier-IMAP, Courier AuthLib, Postfix, PCRE, PHP, Cyrus SASL, policyd
Shut down all services
Clone drive from G4 to Mac Mini
Boot 10.5 Installer
Upgrade system
Install MySQL, PHP (built for 10.4.x, will upgrade to 10.5 versions when available)
Build/Install PCRE, Cyrus SASL, Courier AuthLib, Courier IMAP, then Postfix (order probably important)
Build/Install policyd
Keep fingers crossed

Hopefully I haven't forgotten anything that's a dependency, if so, I'm sure I'll find out about it.

I'll probably try for a dry run this weekend, and see what happens.

Major Upgrades

I'm in the beginning stages of some major upgrades to the machine running the web/mail system here, every major piece of software on the back end is being upgraded, and the hardware is seeing some major changes as well Gone will be the old G4 system, in favor of a new Intel Mac, and a whole host of software upgrades for the new hardware to bring everything current with the latest releases (MySQL 5, MovableType 4, Courier-IMAP 4.1.3, Postfix 2.4.5, pcre 7.3, PHP 5.x, etc, etc). Oh, and of course, Mac OS X 10.5, when it ships, hopefully next month.

The plan at this point is to prep the new hardware, install all the software, then migrate the data from the old server, followed by much testing to make sure that everything is working as it should be. If all goes well, shortly after Mac OS X 10.5 ships, I'll be able to wipe the drive and install all the latest bits and be able to go live in early November. There should be no noticeable difference (unless I play with MovableType a bit!), but my UPS will have a lighter power load to deal with, at least. :)

OS X Firewall

Out of necessity, I've been playing around a bit with the built in firewall in OS X, ipfw. It definitely isn't as full featured as iptables, the firewall built into many linux distros, but it's very easy to configure. One feature not controlled by the Firewall GUI settings in System Preferences is the ability to block a specific IP or range, and my old Netopia router didn't provide this either. Thanks to some kiddie trying to hack my system, I found that I now needed this ability.

I came across a slightly dated article at MacDevCenter.com that gave some excellent background on using ipfw, including setting it up as a Startup Item so that you can customize whatever settings you want, and have them take effect at each startup.

One change that I made here was instead of setting up my config file in the /etc directory, I saved it into the Firewall folder in StartupItems, and referenced it there, it seemed a much more logical place to keep this.

Upgrade to 10.4.5

The server here has been upgraded from 10.4 to 10.4.5. I was a little apprehensive about this update as I didn't have a chance to test prior to performing it, but everything went well, Postfix kept chugging, web services uninterrupted, other miscellaneous non-standard compiled code went well. Looks like Apple didn't stomp on anything that I'd upgraded. ;)

The big hangup was an issue with Carbon Copy Cloner, under 10.4, it wasn't able to clone my drive due to an authentication issue, normally I'd clone my drive and test out the upgrade first, so I just had to chance it. The main reason I needed to upgrade was 10.4 apparently had some issue that would keep log files from rotating properly, and possibly other scheduled tasks from running, so it was high time to get that issue fixed.

Serving multiple domains with OS X

So, after first thinking I had no use for more domain names, I then decided what the heck and registered two .info domain names for myself to play around with. After some hunting for info on getting OS X to make use of these, I came across this article, which was more geared towards OS X Server, but the steps seemed to work just fine with regular OS X.

I still have my main site served, and currently have one of my new domains returning a dummy holder page. I think I might use this to test out an upgrade to Movable Type, see how things go. Hopefully that info might help some folks out there.

Free .info domain names

Want up to 25 free .info domain names? Then check out the offer from Domainsite, you can get up to 25 .info domain names for free. This appears to be a single year offer, it's assumed that come renewal time you'll need to fork over some $$ to keep the name, but still, not a bad deal.

If you need DNS hosting, I'm still recommending Sitelutions. Can't beat the price of free. ;)

New version of MSU posted

I've posted a new version of MacSitelutionsUpdate, a little AppleScript for folks using the Sitesutions Dynamic DNS service.

I now have it talking to the CGI script that I wrote so it can keep track of the current version, plus some other enhancements.

Serving a WAN IP page

I've just posted another example page , this one showing how to set up server side includes under OS X and create a web page that will show a user's WAN IP address.

Basically, this is just an example to show that server side includes are working, but it can also be a useful page to serve, should you wish to offer such a service.

AppleScript CGIs

The last two days I've been working off and on to get a new CGI working on the web server here, built using AppleScript. And as with most oddball projects I think up in the middle of the night, it wasn't going as planned.

Turns out the magic key to making it all work is a slick piece of software called acgi Dispatcher, by James Sentman.

What this application does is allow Apache to pass AppleEvents to your CGI, actually an ACGI, which then allows the script to actually do something other than just sitting there.

An AppleScript CGI, like the one described here basically lets you use AppleScript to write some routines that can process information, return a web page, or even interact apps on your web server (potentially dangerous if you don't know what you're doing).

In the case of this sample app, it was returning a web page based on some info passed to it, but though the information on the page indicated that it was 'fully supported' with Apache under OS X Server, that isn't entirely true, and certainly isn't true for standard OS X. Apparently OS X Server 10.3 and up no longer included a necessary component for making these scripts function, but the official Apple workaround (according to their Knowledgebase) for OS X Server 10.3, and apparently for OS X 10.1 and later, is to run the acgi Dispatcher application, which acts as an interface between Apache and AppleScript.

Dispatcher has a 30 day demo, and is $15 for home/educational use, and $35 for commercial use.

Server Side Includes w/OS X

After stumbling around with this problem for a few days, trying to get server side includes working, I came across this article that solved my problem. This person was having the issue under 10.2.6, and it apparently still applies under 10.3.4.

Specifically, what I did in the httpd.conf file was to scroll down to where the web directory was actually defined, and added in the 'Includes' option there. Of course this was also after uncommenting the two AddHandler lines mentioned in that article.

I now have something like this:


#
# This should be changed to whatever you set DocumentRoot to.
#


#
# This may also be "None", "All", or any combination of "Indexes",
# "Includes", "FollowSymLinks", "ExecCGI", or "MultiViews".
#
# Note that "MultiViews" must be named *explicitly* --- "Options All"
# doesn't give it to you.
#
Options Indexes FollowSymLinks MultiViews Includes



A test document with a .shtml extention worked as intended.