John Beales

Here’s the brick book that’s been keeping me from reading some other design & HTML books since Christmas, and I’m finally done! Despite its size, I wanted to read High Performance MySQL from cover to cover as I’ve been working a lot recently with some databases that could stand to go faster, and, while it was a long read, (made longer by taking breaks and reading other books), it was very useful, and will never be too far from my desk, especially when I’m working on server-side code and database design and tuning.

Even before I finished the High Performance MySQL my work was seeing the benefits of what I had learned partway through the book. There are a lot of quirks in MySQL that I wasn’t aware of, especially when it comes to joining tables and applying indexes. In one instance I was able to take a query that had been taking several minutes to execute and bring its execution time down to just a few seconds simply by rewriting some joins in such a way that, when thinking only about how data is related, appears to be quite bizarre, but when thinking about how a query will be executed by MySQL makes good sense.

I learned about how indexes are used, not just in theory but actually how MySQL looks for something within an index, and I learned about how I can use indexes to make it so MySQL doesn’t have to touch the underlying table data for some SELECT queries, (quite the speed enhancement there). I learned more about InnoDB than I thought I would know for quite some time. There’s a great appendix about the Sphinx search server, which is a product that I’ve been interested in for quite a while. The appendix gave me the introduction that I needed, and I hope to start using Sphinx to power some searches soon.

If you work with MySQL databases a lot, and especially if you are involved in designing them, then High Performance MySQL is a very good book to have on hand. Even if you don’t read it from cover to cover like I did it’s a great reference and will help you speed up your MySQL instances.

Want to read it yourself? Get it from one of these places and I’ll receive a small kickback:

Amazon.com (USA) Amazon.ca (Canada) Amazon.co.uk (UK)

After upgrading to Snow Leopard today I tried to access my localhost versions of the websites I have under development and was met by this great message, (impatient? skip to the step-by-step solution):

Forbidden

You don’t have permission to access / on this server.

Great. I couldn’t even access http://john-mbp/ – my computer’s name, and the URL that the System Preferences’ Web Sharing pane tells me I can access. However, all is not lost.

Part 1: Get the Virtualhosts Online

The first place I looked was my httpd-vhosts.conf file, located in /var/apache2/extra, (this has not changed since 10.5), and as I suspected it had been reset to the Apache defaults, (thanks Apple). I suspected this might happen though, and had made a backup of the file, (actually, I backed up my whole hard drive, then made a separate, easier to find, copy of the file). Replacing the new httpd-vhosts.conf with the old one and doing an sudo apachectl graceful got the virtualhosts responding to requests again.

Part 2: Establish a MySQL Connection

While my virtualhosts were up and running at this point they did not have a MySQL connection. Maybe this is because my MySQL installation is from MacPorts so the socket is in a different place, or maybe not. Either way it had to be fixed.

Upon running a phpinfo(); I discovered a couple of things. First, the included version of PHP is now 5.3.0 and second, PHP was not loading any php.ini file, so I went on a php.ini hunt and found 2 files: php.ini.default and php.ini.default-5.2-previous, both located in /etc. Copying php.ini.default to /etc/php.ini and doing a sudo apachectl graceful brought my MySQL connections back online, leaving one final problem.

Part 3: Getting rid of the timezone warnings

Since I usually code with an error reporting level of E_ALL, and PHP 5.3 doesn’t seem to like not having a timezone set in the php.ini file, (it’s not set by default, at least not in Snow Leopard), I was getting a bunch of warnings whenever I used the date(); function in PHP, so, I needed to set a default timezone in php.ini. If you don’t already know the PHP name for your timezone, go to the PHP timezones page and find it, then open php.ini, (sudo vi /etc/php.ini the sudo is important), and search for “timez” to find the timezone section of the file. Now, on the line that says “;date.timezone = ” add the name of your timezone after the equals sign and remove the semicolon from the beginning of the line. Once you’ve done that, save the file, (it’s set as read-only for some reason so in vi you need to do :w! to make it save), then quit your text editor and do one more sudo apachectl graceful and you should be good to go.

Step by Step

1. before installing, back up your computer
2. before installing, make a copy of /etc/apache2/extra/httpd-vhosts.conf and put it somewhere safe
3. install Snow Leopard
4. replace the new /etc/apache2/extra/httpd-vhosts.conf with the old one you saved in step 2. If you don’t have a backup you’ll have to re-enter your virtualhosts in the new httpd-vhosts.conf file which isn’t the end of the world
5. in Terminal type sudo apachectl graceful and enter your password if prompted to restart apache with the new configuration. Your virtualhosts should now be online
6. rename or copy /etc/php.ini.default to /etc/php.ini (sudo cp /etc/php.ini.default /etc/php.ini)
7. Find the name of your PHP timezone, (look on the PHP timezones page). Write it down.
8. Edit the new php.ini file to have the correct timezone.
   1. open the new php.ini file (sudo vi /etc/php.ini)
   2. find the timezone section, (type /timez and press enter).
   3. move your cursor to the line that says “;date.timezone = ” using the arrow keys
   4. press the i key to edit the text
   5. add the name of your PHP timezone after the equals sign
   6. remove the semicolon from the beginning of the line
   7. press the escape key to stop editing the text
   8. type :w! to save the file
   9. type :q to quit the text editor
9. One final sudo apachectl graceful and you should be back up and running.

As I mentioned yesterday, I’m doing some work in WordPress right now, and a few minutes ago I tweeted that my custom code is messing with WP’s wp_get_archives() and wp_list_categories() functions, well, I found the problem.

I am including the 4RoadService.com header & utilities files in my WordPress theme, and I am using the same user here on my test server for both the main 4RoadService.com database and the WordPress database. It turns out that when the 4RoadService.com database connection was initialized, since it uses the same connection info as the WordPress database, the existing connection was just reused, (this behaviour, by the way, is well described in the PHP documentation), then when the 4RoadService.com connection was told to use the main 4RoadService database it did, thus switching our one and only connection away from the WordPress database, and making WordPress think that there were no posts on the blog.

Fortunately, there is a quick workaround, just add one more attribute to the mysql_connect() function so it looks like this:

$dblink = mysql_connect($host, $user, $pass, true);

This way a new connection is established, and the WordPress connection is left alone.

I am left wondering why, in the loop, WordPress was able to see my posts, perhaps it establishes a second database connection in there. However, I’m not going to spend the afternoon poking through the guts of WordPress.