Upgrading MySQL on OS 10.3.9 Server

Another day, another problem finally resolved. I've worked feverishly the past two weeks developing our Visions participant database. The product was right on sezzule until I discovered the MySQL 4.0.18 version running on my OS 10.3.9 Server did not support a TIMEDIFF function I used in a query on the participants' add session page. After debating and performing the upgrade in my test environment, I decided, rather than lose functionality in my application, that it would behoove me to upgrade from MySQL 4.0.18 to MySQL 4.1.21 on the production server to address not only this TIMEDIFF issue but others that will undoubtedly pop up in the future. This decision set in motion a series of curiously indecent events that I just brought under control at 2:43 p.m. (14:43:16 to be exact) PST this afternoon.

My first crack at the upgrade seemed to come off without a hitch. In fact, I even jotted down simple instructions to help me remember how I did the upgrade in case I ever found myself in a position where I needed to do it again:

Upgrading MySQL on 10.3.9 Server

1. Stop running version of mysql
sudo /usr/local/mysql/bin/mysqladmin –h localhost –u root –p shutdown

2. Download and install the new packaged version. it will create a symlink from /usr/local/mysql to /usr/local/mysql-standard-4.1.21-apple-darwin7.9.0-powerpc/

3. Ditto the data directory from the originial mysql directory to the new data directory in mySQL (I failed at this step originally, foolishly copying an outdated data directory from one of Nestor's older installations. stupid).

4. chown and chgrp –R the new data directory so owner and group is the mysql user.

5. Check to make sure a symlink was created on the original mysql directory to point to the new installation. if not use the ls –l source target command.

6. Start mySQL using sudo /usr/local/mysql-standard-4.1.21-apple-darwin7.9.0-powerpc/bin/mysqld_safe

Things looked peachy except the search feature on teacher exchange didn't work-- Huh? That was a problem. In fact, it was totally unacceptable. For a reason I'm still not entirely clear on I was not able to repair tables using phpMyAdmin (which would have probably fixed the search problem; at least it did on my test box) so I ended up taking the entire server down over the weekend, doing a clean (i think?**) MySQL install and recreating all databases from scratch using sql data dumps rather than "ditto-ing" the data directory like I attempted the first time I performed my upgrade. On a side note, I DID learn how to enable .htaccess on 10.3 Server in direct response to this foolishness because I was in need of a way to redirect people to a temporary alert page when I took down my server. The temp alert redirect didn't work BUT I did get server to recognize .htaccess files. Hooray for me. Read about it here in the Apple Server Discussion forums.

**Before the weekend install I didn't delete the pkg receipts found in /Library/Receipts left from my first mysql installation attempt. In hindsight I probably should have deleted those package files to ensure my second attempt was a clean install instead of an upgrade install.

Life 'waz butta' until I discovered that norman could no longer connect to the www3 box because of a mysql client authentication protocol problem directly related to a new password hashing algorithm implemented in MySQL v. 4.1.x. In addition to my server barking ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client at me I was also receiving a ERROR 1045: Access denied for user 'root'@'localhost' (using password: NO) complaint. I was tearing my hair out over this and eventually posted to a number of different discussions including this one on the www.thescripts.com developer forum to find an answer. Well, after R-T-F-3.23, 4.0, 4.1 MySQL-M (not the 5.0 or 5.1 manual) I finally figured out how to fix these little nasties and posted my resolution in the forum. I won't bother to repeat it here. If interested, click on the link above to read more about it.


>>>Update 10/14/06

Just a quick note that I needed to perform an additional tweak in my etc/my.cnf file to allow 3 character words to be found in my fulltext searches. I added ft_min_length=3 to the config file, stopped and restarted the server, and repaired my tables.

added to my.cnf file:
[mysqld]
ft_min_word_len=3

mysql command-line:
mysql > use database_name;
mysql> REPAIR TABLE tbl_name QUICK;

Documentation:
Fine Tuning Fulltext Searches & Indexing
Using Option Files