MySQL Troubleshooting

Chapter 1. Basics

When troubleshooting, you can generally save time by starting with the simplest possible causes and working your way to more complicated ones. I work dozens of trouble tickets at MySQL Support every month. For most of them, we start from trivial requests for information, and the final resolution may—as we’ll see in some examples—be trivial as well, but sometimes we have quite an adventure in between. So it always pays to start with the basics.

The typical symptoms of a basic problem are running a query and getting unexpected results. The problem could manifest itself as results that are clearly wrong, getting no results back when you know there are matching rows, or odd behavior in the application. In short, this section depends on you having a good idea of what your application should be doing and what the query results should look like. Cases in which the source of wrong behavior is not so clear will be discussed later in this book.

We will always return to these basics, even with the trickiest errors or in situations when you would not know what caused the wrong behavior in your application. This process, which we’ll discuss in depth in Localizing the Problem (Minimizing the Test Case), can also be called creating a minimal test case.

Incorrect Syntax

This sounds absolutely trivial, but still can be tricky to find. I recommend you approach the possibility of incorrect SQL syntax very rigorously, like any other possible problem.

An error such as the following is easy to see:

SELECT * FRO t1 WHERE f1 IN (1,2,1);

In this case, it is clear that the user just forgot to type an “m”, and the error message clearly reports this (I have broken the output lines to fit the page):

mysql> SELECT * FRO t1 WHERE f1 IN (1,2,1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'FRO
t1 WHERE f1 IN (1,2,1)' at line 1

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:

mysql> 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);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'row2,
'row3,'row4,'row5,'row6,'row7,'row8,'row9,'row10,'row11, 'row12,'row13,'row' at
line 1

The problem is that the closing apostrophe is missing from each row. Going back to the PHP code, I have to change:

$query .= "'row$i,";

to the following:

$query .= "'row$i',";

• An important debugging technique, therefore, consists of this: always try to view the query exactly as the MySQL server receives it. Don’t debug only application code; get the query!

Unfortunately, you can’t always use output functions. One possible reason, which I mentioned before, is that you’re using a third-party library written in a compiled language to generate the SQL. Your application might also be using high-level abstractions, such as libraries that offer a CRUD (create, read, update, delete) interface. Or you might be in a production environment where you don’t want users to be able to see the query while you are testing particular queries with specific parameters. In such cases, check the MySQL general query log. Let’s see how it works using a new example.

This is the PHP application where the problem exists:

private function create_query($columns, $table)
{
    $query = "insert into $table set ";
    foreach ($columns as $column) {
        $query .= $column['column_name'] . '=';
        $query .= $this->generate_for($column);
        $query .= ', ';
    }
    return rtrim($query, ',') . ';';
}

private function generate_for($column)
{
    switch ($column['data_type']) {
    case 'int':
        return rand();
    case 'varchar':
    case 'text':
      return "'" . str_pad(md5(rand()), rand(1,$column['character_maximum_length']),
      md5(rand()), STR_PAD_BOTH) . "'";
    default:
        return "''";
    }
}

This code updates a table defined in Example 1-1.

Example 1-1. Sample table of common troubleshooting situations

CREATE TABLE items(
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	short_description VARCHAR(255),
	description TEXT,
	example TEXT,
	explanation TEXT,
	additional TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now is time to start using the general query log. This log contains every single query the MySQL server receives. Many production applications don’t want to use it on a day-to-day basis, because it grows extremely fast during a high load, and writing to it can take up MySQL server resources that are needed for more important purposes. Starting with version 5.1, you can turn the general query log on temporarily to record just the query you need:

mysql> SET GLOBAL general_log='on';
Query OK, 0 rows affected (0.00 sec)

You can also log into a table, which lets you easily sort logfile entries because you can query a log table like any other MySQL table:

mysql> SET GLOBAL log_output='table';
Query OK, 0 rows affected (0.00 sec)

Now let’s run the application. After an iteration that executes the problem code, query the table containing the general log to find the problem query:

mysql> SELECT * FROM mysql.general_log\G
*************************** 1. row ***************************
  event_time: 2011-07-13 02:54:37
   user_host: root[root] @ localhost []
   thread_id: 27515
   server_id: 60
command_type: Connect
    argument: root@localhost on collaborate2011
*************************** 2. row ***************************
  event_time: 2011-07-13 02:54:37
   user_host: root[root] @ localhost []
   thread_id: 27515
   server_id: 60
command_type: Query
    argument: INSERT INTO items SET id=1908908263,
short_description='8786db20e5ada6cece1306d44436104c',
description='fc84e1dc075bca3fce13a95c41409764',
example='e4e385c3952c1b5d880078277c711c41',
explanation='ba0afe3fb0e7f5df1f2ed3f2303072fb',
additional='2208b81f320e0d704c11f167b597be85',
*************************** 3. row ***************************
  event_time: 2011-07-13 02:54:37
   user_host: root[root] @ localhost []
   thread_id: 27515
   server_id: 60
command_type: Quit
    argument:

We are interested in the second row and query:

INSERT INTO items SET id=1908908263,
short_description='8786db20e5ada6cece1306d44436104c',
description='fc84e1dc075bca3fce13a95c41409764',
example='e4e385c3952c1b5d880078277c711c41',
explanation='ba0afe3fb0e7f5df1f2ed3f2303072fb',
additional='2208b81f320e0d704c11f167b597be85',

The error again is trivial: a superfluous comma at the end of the query. The problem was generated in this part of the PHP code:

        $query .= ', ';
    }
    return rtrim($query, ',') . ';';

The rtrim function would work if the string actually ended with a comma because it could remove the trailing comma. But the line actually ends with a space character. So rtrim does not remove anything.

Now that we have the query that caused the error in our application, we can turn off the general query log:

mysql> SET GLOBAL general_log='off';
Query OK, 0 rows affected (0.08 sec)

In this section, we learned a few important things:

• Incorrect syntax can be the source of real-life problems.

• You should test exactly the same query that the MySQL server gets.

• Programming language output functions and the general query log can help you quickly find the query that the application sends to the MySQL server.