SQL and MySQL Debugging Techniques


The most common SQL errors are caused by the following issues:

  • Unbalanced use of quotation marks or parentheses

  • Unescaped apostrophes in column values

  • Misspelling a column name, table name, or function

  • Ambiguously referring to a column in a join

  • Placing a query's clauses (WHERE, GROUP BY, ORDER BY, LIMIT) in the wrong order

Furthermore, when using MySQL you can also run across the following:

  • Unpredictable or inappropriate query results

  • Inability to access the database

  • Difficulty starting MySQL

Since you'll be running the queries for your dynamic Web sites from PHP, you need a methodology for debugging SQL and MySQL errors within that context (PHP will not report a problem with your SQL).

Debugging SQL problems

To decide if you are experiencing a MySQL (or SQL) problem rather than a PHP one, you need a methodology for finding and fixing the issue. Fortunately, the steps you should take to debug MySQL and SQL problems are easy to define and should be followed without thinking. If you ever have any MySQL or SQL errors to debug, just abide by this sequence of steps.

To debug your SQL queries

1.

Print out any applicable queries in your PHP script (Figure 6.16).

Figure 6.16. Knowing exactly what query a PHP script is attempting to execute is the most useful first step for solving SQL and MySQL problems.


As you'll see in the next chapter, SQL queries will often be assigned to a $query variable, particularly as you use PHP to dynamically write them. Using the code echo $query (or variations on this) in your PHP scripts, you can send to the browser the exact query being run. Sometimes this step alone will help you see what the real problem is.

2.

Run the query in the mysql client or other tool (Figure 6.17).

Figure 6.17. To understand what result a PHP script is receiving, run the same query through a separate interface. In this case the problem is the lack of quotes around mypass, so MySQL tries to treat it as a column (and such a column doesn't exist).


The most foolproof method of debugging a SQL or MySQL problem is to run the query used in your PHP scripts through an independent application: the mysql client, phpMyAdmin, or the like. Doing so will give you the same result as the original PHP script receives but without the overhead and hassle.

If the independent application returns the expected result but you are still not getting the proper behavior in your PHP script, then you will know that the problem lies within the script itself, not your SQL or MySQL database.

Tips

  • Another common MySQL problem is trying to run queries or connect using the mysql client when the MySQL server isn't even running. Be sure that MySQL is available for querying!

  • As an alternative to printing out the query to the browser, you could print it out as an HTML comment (viewable only in the HTML source), using

     echo "<!-- $query -->"; 

3.

Rewrite the query in its most basic form, then keep adding dimensions back in until you discover which clause is causing the problem.

Sometimes it's difficult to debug a query because there's too much going on. Like commenting out most of a PHP script, taking a query down to its bare minimum structure and slowly building it back up can be the easiest way to debug complex SQL commands.

Debugging access problems

Access denied error messages are the most common problem beginning developers encounter when using PHP to interact with MySQL. These are among the common solutions:

  • Reload MySQL after altering the privileges so that the changes take effect. Either use the mysqladmin tool or run FLUSH PRIVILEGES in the mysql client. You must be logged in as a user with the appropriate permissions to do this (see Appendix A for more).

  • Double-check the password used. The error message Access denied for user: 'user@localhost' (Using password: YES) frequently indicates that the password is wrong or mistyped. (This is not always the cause but is the first thing to check.)

    PHP's mysql_error() Function

    Error handling, which is important in any script, is even more of an issue when dealing with databases, since the probability for errors will increase dramatically. Printing out your queries and then running them through the mysql client is a key debugging technique but is certainly not the only tool.

    To have your scripts give informative reports about errors that occur, make use of the mysql_error() function. It will return the error that MySQL had with the previous database interaction. Whether an error occurs in trying to connect to MySQL, in selecting the database to use, or in running a query (like that in Figure 6.17), the mysql_error() function will report on any problems, just as if the command was run in the mysql client. In the next and subsequent chapters you'll use this function extensively.


  • Do not forget to use the PASSWORD() function when setting privileges or updating a password.

  • The error message Can't connect to… (error number 2002) indicates that MySQL either is not running or is not running on the socket or TCP/IP port tried by the client.

Tips

  • MySQL keeps its own error logs, which are very useful in solving MySQL problems (like why MySQL won't even start). MySQL's error log will be located in the data directory and titled hostname.err.

  • The MySQL manual is very detailed, containing SQL examples, function references, and the meanings of error codes. Make the manual your friend and turn to it when confusing errors pop up.




    PHP and MySQL for Dynamic Web Sites. Visual QuickPro Guide
    PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    ISBN: 0321336577
    EAN: 2147483647
    Year: 2005
    Pages: 166
    Authors: Larry Ullman

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net