Sunday, September 24, 2006

Advanced Features as Crutches

I am glad to see all of the advanced features in MySQL. They all have their place, whether it's making things possible that weren't before, or improving ease-of-use, or improving thru-put and performance. However I find that just because you can use an advanced feature in place of the old way doesn't necessarily mean that you should.

If there are several ways to accomplish the same thing, my first inclination is to go with the one that is simplest to code. My philosophy is that if I arrange the syntax in a logical order with each link in the chain in its proper place, that this will make it that much easier for the server to optimally execute it.

I think that some people will code a subquery in preference to the equivalent join syntax mostly from a basic lack of understanding of how joins work. My personal preference is to code something as a join and avoid use of subqueries wherever possible. If the subquery functionality did not exist (and it still doesn't for a lot of users, but that's an entirely different issue) then people would perforce use joins.

Subqueries are not bad things. They do make things possible that are not possible with joins alone. They can eliminate the need for temporary tables, although a properly-defined temporary table may out-perform a straight subquery. People may code using all the bells and whistles available, saying that it's the software's job to properly optimize the execution. But until the optimizer rivals the human brain in terms of complexity, nothing can beat some good old-fashioned skull sweat in terms of convincing a computer to put out its best performance.

Plus, once you've coded the same sort of problem a time or two, it gets easier to wrap your brain around a scenario and crank out the code using the optimal correct aproach. Trying to convince a newbie that there is a method to the madness, there is a reason for doing things a certain way, that it's not a bug that one approach will outperform another— now there's a challenge.

Some features I see as going further to enhance security than to increase performance; views seem to fall into that category, and to a certain extent, stored routines. Triggers and cursors make many things possible, yet it's also possible to use these to code stuff that looks perfectly nice on paper, yet there is a perfectly simple solution using only basic SQL statements.

It's certainly worth exploring all the functionality, to find out what it can and cannot do, but also to find out what it does well, and what is better done the 'old way'.

Saturday, September 23, 2006

Too Many RSS Feeds

Okay, so maybe I went a little overboard. But having switched to RSS in order to better interact with the MySQL forums, I seem to have embraced this new way of having content delivered to me whole-heartedly. Of course I added all of the available forum feeds immediately, but then also I added feeds to Planet MySQL as well as some of the constituent blogs directly, also a few NASA blogs and some from family&friends, well, before I knew it the list of feeds has stretched the entire height of my monitor, and I have a 21.3" monitor turned into portrait orientation.

I have to admit, I like staying on top of things and having my feeds polled at 15 minute intervals. I may have to figure out how to subcategorize my feeds so I don't have to look at the entire list always but roll up portion of it. I have already trimmed the list a little, removing some feeds that I had more of curiosity than anything else. Some things are outside of my area of expertise, but it is interesting sometimes to try and follow along, I might learn something.

Monday, September 18, 2006

PHP and MySQL Stored Procedures

Amid recent discussions about calling MySQL stored procedures from PHP or similar API-type interfaces, and mentions of difficulties in doing so, I thought I would post some notes about my experiences. When I first tried to use stored procedures from PHP, I discovered that it worked some of the time, but I did not take the time to delve into the whys or the wherefores. It was also mentioned by some that the mysqli interface was necessary in order to properly interact with stored procedures, but I have since determined that that is in fact not the case. I now conclude that the mysql interface works with stored procedures just fine, and that is fine with me since I am perfectly at home with this interface.

The only issue appears to be when returning a resultset. It's not at all intuitive, but in order to return a resultset from a stored procedure to PHP, one must have either the multiple-statements connect option or the multiple-results option (or both) enabled. If the routine does not return a resultset, neither option is required.

Since the defined names are not available from PHP, one must use the numeric equivalents. You can grep these from mysql_com.h. I used option masks of 65536, 131072, and 199608 with equal success. I confess I do not understand why both these options perform equally well, and why a single resultset from a stored procedure evidently falls into the 'multi' category, but no matter, it works.

Sunday, September 03, 2006

MySQL Error Checking in PHP

So often it seems that I am seeing someone struggling to find out what is wrong with their PHP script. They see the following error message:
Warning: mysql_fetch_assoc():
supplied argument is not a valid MySQL result resource
in /var/www/htdocs/somefile.php on line 18
and have no clue what this means. The line number has no bearing on where the real problem is, and there may be a whole cascade of these messages. Let's assume the code looks something like this:
mysql_connect ("server", "user", "password");
mysql_select_db ("dbname");
$result = mysql_query ("SELECT something FROM mytable");
while ($row = mysql_fetch_assoc ($result))
Where did the error occur? The answer is it could be any number of places. Let's make a list:
  1. The server name is wrong.
  2. MySQL is not running on the server
  3. The script is being run on a host that is not allowed to connect to the MySQL server
  4. Incorrect username
  5. Incorrect password
  6. The database does not exist
  7. The username does not have permission to access the database at all

    And finally:

  8. There is an error in the query
So we can play the guessing game trying to figure out where the error is. Or we can do error checking. The next mistake is to do some error checking, but not in all potential places for error. This can be more confusing than no error checking at all. Let's say that we checked for errors after the call to mysql_query but nowhere else, and the connect call failed. We put in the password wrong perhaps. So the call to mysql_connect fails. Then the call to mysql_select_db fails. Finally the call to mysql_query fails. We actually do check the error message here and what we get is the following:
Access denied for user 'nobody'@'localhost' (using password: NO)
At this point our user is scratching his head, because he knows he put in the right credentials, he's double-checked. This error message gives a complete different set of credentials: wrong username, no password, maybe even a diffrent server name. The reason for this, which is not always obvious, is that a connection did not get established via mysql_connect, so PHP reverts to a default set that is in its config file. Chances are nobody has edited it to put valid credentials in, and I wouldn't personally recommend it for security reasons.

So we can see how it is essential to perform error checking every step of the way to eliminate any guesswork as to what went wrong, where it went wrong, and why. The remaining item to discuss is how to check for errors. There are numerous different styles and techniques, each with their own pros and cons. My personal preference is to do several things:
  1. Store the credentials in a separate script. This might even be outside of the htdocs directory tree entirely, so there is no chance of someone referencing it maliciously.
  2. Write a small script that references the credentials script, connects to the database and selects the database, using proper error checking of course.

    (At this point we have eliminated the possibility of any connection error being mistaken for a faulty query)

  3. Display a compound error message with detailed information about a failing query.
I prefer a compound error message since it provides complete details, but any of the following will work. I recommend aborting the page load by calling die since it can print a message and do the abort in a single call. Plus it can be piggy-backed onto the mysql function calls using the short-circuit boolean logic which says that if the left-hand operand of an or is true, the right-hand side is ignored. So simply append one of the following forms:
  • or die()
  • or die("Query failed!")
  • or die ("Query failed: " . mysql_error())
  • or die ("Query failed: " . mysql_error() . " Actual query: " . $query)
I prefer the latter with its complete display of pertinent information. If you have more than one query in a particular document, it may be helpful to add some indication of which on failed to further eliminate confusion.