When I started looking into managing hierarchical data I found a lot of short articles that provided bits and pieces of the big picture, but nothing gave a good in-depth review of everything I needed to know to handle hierarchies.
One trend that began to develop was that the best information on the subject was coming from Joe Celko, a rather prolific author/speaker on all things SQL.
Joe Celko has a book in publication called SQL For Smarties, and this book had some basic information on handling hierarchies, which he followed up with Trees and Hierarchies in SQL for Smarties, a truly excellent book on the subject.
This book is not for the faint of heart, but it really is a must-read for those who are looking to handle hierarchies in SQL.
The book only addresses MySQL once, and while in that section it repeats Joe’s opinion of MySQL not being a real database, users of MySQL 5 can rest assured that the stored procedure examples in the book work without modification (thanks to Joe and MySQL 5 both using the standard PL/PSM stored procedure syntax).
Overall this is an excellent book for those looking to implement hierarchical data in SQL.
I ran into an interesting issue when installing WordPress on my re-installed server, I could not get a database connection during installation. I added some debugging and discovered that I had a “Can’t connect to MySQL server on” error returned after the call to mysql_connect() in PHP.
To check the source of the issue I then tried to connect on the command-line using the mysql client, which occurred successfully, confirming that I was using the correct credentials and host address (this was a remote MySQL server).
I next created a test PHP script with a simple mysql_connect() call, and executed it with “php test.php” from the command-line, which was also successful.
Finally I accessed test.php through a browser, where again the connection failed.
So I was dealing with a situation where it was Apache in particular that was unable to connect to the remote MySQL server. Thanks to Wez Furlong‘s ideas I was able to narrow this down to SELinux blocking outgoing communications by Apache.
So, if you’re having issues with Apache and specifically connecting out, you may want to disable SELinux for Apache.