O'Reilly logo

MySQL Troubleshooting by Sveta Smirnova

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.



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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required