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 ...