Download - All IT eBooks

Unfortunately, not all syntax errors are so trivial. I once worked on a trouble ticket
concerning a query like this:
SELECT id FROM t1 WHERE accessible=1;
The problem was a migration issue; the query worked fine in version 5.0 but stopped
working in version 5.1. The problem was that, in version 5.1, “accessible” is a reserved
word. We added quotes (these can be backticks or double quotes, depending on your
SQL mode), and the query started working again:
SELECT `id` FROM `t1` WHERE `accessible`=1;
The actual query looked a lot more complicated, with a large JOIN and a complex
WHERE condition. So the simple error was hard to pick out among all the distractions.
Our first task was to reduce the complex query to the simple one-line SELECT as just
shown, which is an example of a minimal test case. Once we realized that the one-liner
had the same bug as the big, original query, we quickly realized that the programmer
had simply stumbled over a reserved word.
■ The first lesson is to check your query for syntax errors as the first troubleshooting
step.
But what do you do if you don’t know the query? For example, suppose the query was
built by an application. Even more fun is in store when it’s a third-party library that
dynamically builds queries.
Let’s consider this PHP code:
$query = 'SELECT * FROM t4 WHERE f1 IN(';
for ($i = 1; $i < 101; $i ++)
$query .= "'row$i,";
$query = rtrim($query, ',');
$query .= ')';
$result = mysql_query($query);
Looking at the script, it is not easy to see where the error is. Fortunately, we can alter
the code to print the query using an output function. In the case of PHP, this can be
the echo operator. So we modify the code as follows:
…
echo $query;
//$result = mysql_query($query);
Once the program shows us the actual query it’s trying to submit, the problem jumps
right out:
$ php ex1.php
SELECT * FROM t4 WHERE f1 IN('row1,'row2,'row3,'row4,'row5,'row6,'row7,'row8,
'row9,'row10,'row11, 'row12,'row13,'row14,'row15,'row16,'row17,'row18,'row19,'row20)
If you still can’t find the error, try running this query in the MySQL command-line
client:
2 | Chapter 1: Basics
www.allitebooks.com