Can I detect and handle MySQL Warnings with PHP?

Question

I'm dealing with a MySQL table that defines the JobName column as UNIQUE. If somebody tries to save a new Job to the database using a JobName that is already in the database, MySQL throws a warning.

I would like to be able to detect this warning, just like an error, in my PHP script and deal with it appropriately. Ideally I would like to know what kind of warning MySQL has thrown so that I can branch the code to handle it.

Is this possible? If not, is it because MySQL doesn't have this ability, PHP doesn't have this ability, or both?

Answer 1

For warnings to be "flagged" to PHP natively would require changes to the mysql/mysqli driver, which is obviously beyond the scope of this question. Instead you're going to have to basically check every query you make on the database for warnings:

$warningCountResult = mysql_query("SELECT @@warning_count");
if ($warningCountResult) {
    $warningCount = mysql_fetch_row($warningCountResult );
    if ($warningCount[0] > 0) {
        //Have warnings
        $warningDetailResult = mysql_query("SHOW WARNINGS");
        if ($warningDetailResult ) {
            while ($warning = mysql_fetch_assoc(warningDetailResult) {
                //Process it
            }
        }
    }//Else no warnings
}

Obviously this is going to be hideously expensive to apply en-mass, so you might need to carefully think about when and how warnings may arise (which may lead you to refactor to eliminate them).

For reference, MySQL SHOW WARNINGS

Of course, you could dispense with the initial query for the SELECT @@warning_count, which would save you a query per execution, but I included it for pedantic completeness.

Answer 2

It is possible to get the warnings, and in a more efficient way with mysqli than with mysql.

Here is the code suggested on the manual page on php.net for the property mysqli->warning_count:

$mysqli->query($query);

if ($mysqli->warning_count) {
    if ($result = $mysqli->query("SHOW WARNINGS")) {
        $row = $result->fetch_row();
        printf("%s (%d): %s\n", $row[0], $row[1], $row[2]);
        $result->close();
    }
}

Answer 3

Updated to remove the stuff about errno functions which I now realize don't apply in your situation...

One thing in MySQL to be wary of for UPDATE statements: mysqli_affected_rows() will return zero even if the WHERE clause matched rows, but the SET clause didn't actually change the data values. I only mention this because that behaviour caused a bug in a system I once looked at--the programmer used that return value to check for errors after an update, assuming a zero meant that some error had occurred. It just meant that the user didn't change any existing values before clicking the update button.

So I guess using mysqli_affected_rows() can't be relied upon to find such warnings either, unless you have something like an update_time column in your table that will always be assigned a new timestamp value when updated. That sort of workaround seems kinda kludgey though.

Answer 4

ini_set('mysql.trace_mode', 1)

may be what you are looking for.

The PHP errors can then be handled with a custom PHP error handler, but you can also just turn off displaying php errors as they are usually logged into a log file (depends on your php configuration).

Answer 5

Note on suppressing warnings: Generally, it is not a good idea to prevent warnings from being displayed since you might be missing something important. If you absolutely must hide warnings for some reason, you can do it on an individual basis by placing an @ sign in front of the statement. That way you don't have to turn off all warning reporting and can limit it to a specific instance.

Example:

 // this suppresses warnings that might result if there is no field titled "field" in the result
 $field_value = @mysql_result($result, 0, "field");

Answer 6

First, you should turn warnings off so that your visitors don't see your MySQL errors. Second, when you call mysql_query(), you should check to see if it returned false. If it did, call mysql_errno() to find out what went wrong. Match the number returned to the error codes on this page.

It looks like this is the error number you're looking for:

Error: 1169 SQLSTATE: 23000 (ER_DUP_UNIQUE)

Message: Can't write, because of unique constraint, to table '%s'