mysql_upgrade woes

Aah, here we are again after a long 4 day weekend, relaxed and full of energy. Some of us got a true holiday, but not me. To sum up my 4 day weekend, it was 18 hours in the car (Harry Potter books on tape are a surprisingly good time), one day of staring at a really adorable 4 month old and 7 hours of grueling yard work to prepare for a 4 PM Memorial Day cook-out which should have been spread out over the previous 3 days, but wasn’t because we had to take a car trip. Surprisingly, I am actually full of energy and feeling good… a little sore from the yard work, but good. In fact, I feel so great, I actually woke up this morning on my own, without my alarm clock. Considering the alarm clock did not go off, had I not woken up when I did I would have been late for work.

So, naturally after a long weekend, I started up my computer expecting quite the garboil. Luckily for me, only two distressing emails hit my inbox first thing. Of course this took me off the track of my blog, which I like to do first thing, but I got through the error messages and the responses I needed before setting on to the task at hand. One of the tasks I had to complete was to set up a data source for one of our latest clients so that one of our programmers could get to work on the shopping cart. Previously I had come across an issue where phpmyadmin was telling me:

Warning: Your privilege table structure seems to be older than this MySQL version!
Please run the script mysql_fix_privilege_tables that should be included in your MySQL server distribution to solve this problem!

As I tried to set up a new user for this database, the same error appeared. Now, a week ago I had sent a task on to one of our programmers to run an update to our mysql tables with an executable file “mysql_upgrade” which should apparently be run after every upgrade (it is said to do the same thing as mysql_fix_privilege_tables). I had thought that it was part of the install, but considering our last ‘upgrade’ was simply installing mysql and then loading a giant database dump from the previous server, I can see why the issue may have come about. I have come to find that it’s not as simple on our version of mysql on the server. Apparently the version of mysql I tested on locally is different from the version on our server and it does not run correctly, or moreso, not at all. The first error I found was “Can’t find data directory. Please restart with –datadir=path-to-writable-data-dir”. So I checked the help (myslq_upgrade –help) and found the switches to try to throw in the paths. So, our server being on windows, I grudgingly typed in the full data path with spaces and fun things all in: >mysql_upgrade –password –datadir=C:/Program Files/MySQL/MySQL Server 5.0/Data/ –basedir=C:/Program Files/MySQL/MySQL Server 5.0/ and of course I get the error message again, Can’t find data directory. Please restart with –datadir=path-to-writable-data-dir. So after trying a few ways I eventually put the directories in double quotes, which seemed to work… at least for a split second.

C:/Program Files/MySQL/MySQL Server 5.0\bin\mysqlcheck.exe: Got error: 1045: Access denied for user ‘root’@'localhost’ (using password: NO) when trying to connect
Error executing ‘”"C:/Program Files/MySQL/MySQL Server 5.0\bin\mysqlcheck.exe” “–check-upgrade” “–all-databases” “–auto-repair” “–user=root”"‘

So it looks like our little executable script then calls the mysqlcheck.exe file, and bombs there because it’s not pushing the password through. Even with the actual password in the password switch, it dies. So further research shows there does seem to be some issue with our version of mysql_upgrade, so sometime soon we will have to take down the database and upgrade to the latest version, and try all over again. Of course I’m sure that as soon as we do that another release will be available, and we’ll again be behind the times.

So, since I can’t properly add users using phpmyadmin, and the gui version of things doesn’t work as well, I have to resort to straight up sql:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON mynewdb.*
TO 'mynewdb'@'localhost'
IDENTIFIED BY 'mynewpassword';

I’ll let everyone know if the upgrade works or not.