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?

Thanks in advance.

KN

Answer 1

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'

Answer 2

Thanks Kyle. I do have warning turned off, that is why it is important to be able to detect warnings programatically. Also, I am using mysqli for transaction support. I don't know if this behavior is only with mysqli,but mysqli_query() does not return false for warnings, only full-blown errors.

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

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

Answer 5

Violating the UNIQUE constraint issues a MySQL WARNING not an error. I know I can detect errors all day long, but I want to know about warnings when they occur. I think that having an INSERT statement fail because of the UNIQUE constraint should be treated as an error, but MySQL is returning it as a warning, which is not picked up by mysqli_errno()

Answer 6

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

edit

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

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

Answer 8

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 9

http://bugs.mysql.com/bug.php?id=50757