Can I detect and handle MySQL Warnings with PHP?

问题:

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?

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

回答2:

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

回答3:

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'

回答4:

depending on what (if any) framework you're using, I suggest you do a query to check for the jobname yourself and create the proper information to user in with the rest of the validations for the form.

Depending on the number of jobnames, you could send the names to the view that contains the form and use javascript to tell use which is taken.

If this doesnt make sense to you, then to sum my view it's this: dont design your program and / or user to try to do illegal things and catch the errors when they do and handle it then. It is much better, imho, to design your system to not create errors. Keep the errors to actual bugs :)

回答5:

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.

回答6:

You can detect Unique key violations using mysqli statement error no. The mysqli statement returns error 1062 , that is ER_DUP_ENTRY. You can look for error 1062 and print a suitable error message. If you want to print your column (jobName) also as part of your error message then you should parse the statement error string.

  if($stmt = $mysqli->prepare($sql)){
            $stmt->bind_param("sss",
            $name,
            $identKey,
            $domain);


            $stmt->execute();
            if($mysqli->affected_rows != 1)  {
                        //This will return errorno 1062
                trigger_error('mysql error >> '.$stmt->errno .'::' .$stmt->error, E_USER_ERROR);
                exit(1);
            }
            $stmt->close();
        } else {

            trigger_error('mysql error >> '. $mysqli->errno.'::'.$mysqli->error,E_USER_ERROR);
        }

回答7:

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();
    }
}

回答8:

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");