MySQL Troubleshooting

Getting Information About a Query

As we saw in the previous section, the server returns some important information about each query, displaying some of it directly in the MySQL client and making some of it easy to obtain through commands such as SHOW WARNINGS. When SQL is called from an application, it’s just as important to retrieve this information and check to make sure nothing suspicious is going on. All programming APIs for MySQL support functions that retrieve the query information returned by the server. In this section, we will discuss these functions. I refer just to the C API because I had to choose one language, and most of the other APIs are based on the C API.^[4]

Number of rows affected

Let’s start with the simple output we saw earlier, which is displayed after each insert, update, or delete and shows how many rows were inserted, updated, or deleted:

Query OK, N rows affected

This means the query executed fine and changed N rows.

To get the same information in an application, use the call:

mysql_affected_rows()

This returns a positive number of rows if there were changes, 0 if nothing changed, or –1 in case of error.

For UPDATE statements, if the client flag CLIENT_FOUND_ROWS was set, this function returns the number of rows that matched the WHERE condition, which is not always the same as those that were actually changed.

Note

Using affected rows is turned off by default in Connector/J because this feature is not JDBC-compliant and will break most applications that rely on found (matched) rows instead of affected rows for DML statements. But it does cause correct update counts from INSERT ... ON DUPLICATE KEY UPDATE statements to be returned by the server. The useAffectedRows connection string property tells Connector/J whether to set the CLIENT_FOUND_ROWS flag when connecting to the server.

Number of matched rows

The string in the output that indicates this is:

Rows matched: M

which shows how many rows satisfy the WHERE conditions.

The following C function:

mysql_info()

returns additional information about the most recent query in the string format. For an UPDATE, it returns a string like:

Rows matched: # Changed: # Warnings: #

where each # represents the number of matched rows, changed rows, and warnings, correspondingly. You should parse the line for “matched: #” to find out how many corresponding rows were found.

Number of changed rows

The string in the output that indicates this is:

Changed: P

which shows how many rows were actually changed. Note that M (rows matched) and P (rows changed) can be different. Perhaps the columns you wanted to change already contained the values you specified; in that case, the columns appear in the “Matched” value but not the “Changed” value.

In an application, retrieve this information using mysql_info() as before, but in this case, parse for “Changed: #.”

Warnings: number and message

The string in the output that indicates this is:

Warnings: R

You get warnings if the server detected something unusual and worth reporting during the query, but the query could still be executed and the rows could be modified. Be sure to check for warnings anyway, because they will let you know about potential problems.

In your application, you can retrieve warnings in a few different ways. You can use mysql_info() again and parse for “Warnings: #”. You can also issue:

mysql_warning_count()

If there are warnings, run a SHOW WARNINGS query to get the text message that describes what happened. Another option is:

mysql_sqlstate()

This retrieves the most recent SQLSTATE. For example, “42000” means a syntax error. “00000” means 0 errors and 0 warnings.

Note

A value called SQLSTATE is defined by the ANSI SQL standard to indicate the status of a statement. The states are set to status codes, defined in the standard, that indicate whether a call completed successfully or returned an exception. The SQLSTATE is returned as a character string. To find out which values the MySQL server can return, refer to “Server Error Codes and Messages” in the MySQL Reference Manual.

Errors

It is also always useful to check for errors. The following functions report the error value for the most recent SQL statement:

mysql_errno()

This returns the MySQL number of the latest error. For instance, a syntax error will generate the number 1064, whereas 0 means no error.

mysql_error()

This returns a text representation of the latest error. For a syntax error, it would be something like:

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

This can be convenient for storing messages in a separate logfile that you can examine at any time.

Note

The official MySQL documentation contains a list of errors that the MySQL server can return and a list of client errors.

Retrieving Error Strings Through perror

The perror utility comes with the MySQL distribution and is a very useful tool for troubleshooting. perror provides the error string corresponding to an error code for both MySQL and the server host’s operating system. You can find these numbers in brackets after the error message received in either the MySQL command-line client, the information function, or the error logfile. Here are a few examples:

mysql> CREATE TABLE t2(f1 INT NOT NULL PRIMARY
    -> KEY, f2 INT, FOREIGN KEY(f2) REFERENCES t1(f2)) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150)

mysql> \q

$perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

$perror 2
OS error code   2:  No such file or directory

$perror 136
MySQL error code 136: No more room in index file

These codes are printed by the MySQL client when a command produces an error and can be retrieved by programs through mysql_error(). But in situations where you come across an error number and don’t know what it means, use this utility.

---

^[4] You can find a detailed description of the C API syntax at http://dev.mysql.com/doc/refman/5.5/en/c.html.