How to Detect and Handle MySQL Warnings with PHP

Can I detect and handle MySQL Warnings with PHP?

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.

How do I display a MySQL error in PHP for a long query that depends on the user input?

Use this:

mysqli_query($this->db_link, $query) or die(mysqli_error($this->db_link)); 
# mysqli_query($link,$query) returns 0 if there's an error.
# mysqli_error($link) returns a string with the last error message

You can also use this to print the error code.

echo mysqli_errno($this->db_link);

Take a look here and here

how do i get mysql warning messages in php (not error)

The best is to use the mysqli extension instead of the old mysql extension, as mysqli supports all of MySQL's features and offers the mysqli_get_Warnings() function. Without mysqli you can do mysql_query("SHOW WARNINGS"); (see SHOW WARNINGS docs) and then read the warnings from the result set.

Show WARNINGS in MySQL + PDO

When constructing your PDO instance, pass an array of options, containing this key-value pair:

array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)

To force PDO to throw exceptions (PDOException instances) when an error occurs. Also, when using prepepared statements, you don't add quotes of your own... if the value is a string,

WHERE value = :value

Is just fine, PDO/MySQL will quote the value correctly for you.

PS: You can always change the error mode later on, by calling setAttribute:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);//raise warnings, don't throw errors

Here's a list of attributes

Here, you'll find examples where options are set via the constructor

example from the doc pages, only extended a bit:

$pdo = new PDO('mysql:host=myhost;dbname=mydb', 'login', 'password',
    array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'',
          PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_ORACLE_NULLS       => PDO::NULL_NATURAL)
);

Edit:

After a second glance at your question, I noticed the actual warning you got. That warning will evidently not throw an Exception. Prepared statements check the datatypes upon statement preparation, after that job is done, the statement gets executed. In your case, the double is cast to a varchar, and then the query is executed, comparing the varchars in value with the varchar in the query. No warnings, no fuss, it just does the job.

Here's a related question

And here's a more detailed explanation on how prepares work

Catch mysql *warning* (not exception) while using Laravel DB Builder?

You can do another statement without catching an Exception (A warning is not an Exception). The syntax:

SHOW WARNINGS [LIMIT [offset,] row_count]

SHOW COUNT(*) WARNINGS

After your insert query you may do the following:

---

Count warnings

DB::statement('SHOW WARNINGS');
$warning = DB::select('SELECT @@warning_count');

If a warning occurs it returns:

array(1) {
  [0]=>
  object(stdClass)#412 (1) {
    ["@@warning_count"]=>
    string(1) "1"
  }

or

["@@warning_count"]=>
    string(1) "0"

if no warning occurs.

---

Warning messages & codes

To get the warning messages:

$warning_messages = DB::select('SHOW WARNINGS');

Returns:

array(1) {
  [0]=>
  object(stdClass)#412 (3) {
    ["Level"]=>
    string(7) "Warning"
    ["Code"]=>
    string(4) "1264"
    ["Message"]=>
    string(79) "Out of range value for column 'quantity' at row 1"
  }
}

See the docs about mySQL Warnings

PHP error handling working in MySQL & MySQLi but not PDO

It's a very common fallacy, that one needs a dedicated error handling code for PDO or Mysqli (or whatever else module for that matter). Least it should be even more specific, such as "Mysqli connection" handler, as it seems with your old mysqli code.

If you think of it, you don't really care whether it was exactly a database error that prevented the code form being executed correctly. There can be any other problem as well.

Admittedly, one hardly can expect any other problem from such a simple code but still, the code may grow, become more modular, perform more tasks - and therefore error out in any other part as well. Like, writing database credentials in the every file is a bit of waste. So it's natural to put them in a file and then just include it in the every other script that requires a database interaction. So this file may get corrupted which will has the same effect as a database error. And will need to be fixed as well.

Or, if you're handling only the connection error, the problem can happen during the query execution as well (especially in your case, as the way the query is executed it will error out even if a customer will simply enter fish'h'chips for example).

What you really care for is whether the data has been stored correctly (and probably whether emails were sent as well) or not, no matter what could be the possible failure. This is being the exact reason, why I wrote in the article this warning against wrapping some specific part of code in a try-catch in order to report this particular error. As error reporting handler must be common for the entire code.

Admittedly, the simplest exception handling method is simply wrapping the entire code in a try catch block where the most generic exception type, namely Throwable, must be checked for. Not very reliable but simplest.

The key here is to wrap the entire code, not just some random part of it. But one shouldn't forget to set the exception mode for PDO, in order let the query execution errors to be caught in this block as well.

<?php
try {
    require 'pdo.php'
    ...
    $sql = "INSERT INTO other_choices (order,foods) VALUES (?,?)";
    ...
    $stmt= $db_connection->prepare($sql);
    $stmt->execute([$order, $foods]);

    // send emails, etc
} catch (Throwable $e) {
    // do your handling here
}

Note that I substituted actual variables in the query with question marks, which is being correct way of using prepared statements, that otherwise become useless and render all your transition from mysqli fruitless (especially given that mysqli supports prepared statements as well).

Unfortunately, PHP has two kinds of errors - exceptions and errors proper. And try-catch can catch only the former. In order to handle all kinds of errors, an error handler can be used. You can see a very basic example of one in my article on PHP error reporting.

The last note: sending an email every time an error occurs on the site is not the wisest move. Although in your case it could be justified, given PHP is only involved when a user submits a form, but on a regular site, where PHP is used to handle every page, it can lead to thousands emails. Or even in your case, spammers may target your forms and send thousands requests as well (which itself may cause some overflow error and therefore thousands emails in the inbox). Instead of sending emails manually, consider using a dedicated error monitoring software, such as Sentry. It will send only new errors, as well as aggregated error info.

---

Related Topics