Beamtic's logo

Share via:

Does MySQL Error Codes Change?

While MySQL error codes are unlikely to change, there might still be reasons not to rely on them within your colde.

97 views

Edited: 2020-08-18 17:43

When we query MySQL databases from PHP and some kind of error occurs, it is very common practice to simply return false and check for this inside of our PHP application; there is just one problem — it is not enough!

If we were to perform an insert statement, and the statement fails because of a duplicate key error, then we will often need to be able to tell why the query failed in our PHP script. The alternative, a beginners solution, would be to first perform a select to test if a row with an ID already exists, and then perform the insert after; but this is actually dangerous, since something could still be inserted in-between us evaluating the result of that first query, and performing the final insert.

Instead, just inserting the data directly is both an atomic operation, and more efficient — we can then check whether a duplicate key error was encountered from PHP after attempting to insert the data!

I have not worked enough with MySQL yet to know all the different niche cases, but a quick check with perror from a terminal told me that there is at least more than a thousand different error codes — so I take that it is very likely that you need to know about many, many others of these from within your PHP applications.

There is just one question left to answer:

Do MySQL error codes ever change?

A short answer to this would be no, they will probably not change, and this is also implied by the documentation. The error message strings do change occasionally, so the documentation encourages us to rely on the error codes instead of checking the text strings. That is a strong indication that they do not expect error codes to change without something really big changing as well.

The error code is accessible via $stmt->errno when using MySQLi and $stmt->errorCode(); when using PDO.

Relying on MySQL error codes in code

While the error codes themselves might not change, there is still a good reason not to rely on them outside of your database class; a good reason is that relying on the numeric error codes, outside of the database class, will just introduce a not-so-obvious dependency to our code.

This is going to be a problem if suddenly we decide to switch databases, since all these error codes might no longer match, and then you may have an even larger refactoring project on your hands.

So, instead of returning the error code from our database client, we might want to handle the errors from the client class itself; and then only handle the specific error codes that our application needs to handle. The places in my code that I actually need to know the cause as to why a given query is failing are very few, and everything else can be handled simply by returning false and then checking for this in my code. I.e:

if (false === $stmt) {
  throw new Exception("The query failed for unknown reasons.");
}

Relying on constants instead of numeric error codes

In PHP we are encouraged to rely on predefined constants rather than error codes, at least when it comes to PHP's own errors, such as E_ERROR and E_WARNING, since the integer values might change. But this does not necessarily translate to MySQL or MariaDB, and PHP also does not have predefined constants for MySQL errors; so what can we do instead?

As I already discussed in the previous section, we do not really need our application to know about every single MySQL error — there are currently more than a thousand of them! No. Instead we will just define constants for the ones we need.

Some of these error codes do also not appear to be something that can be triggered from PHP applications anyway.

Defining global constants is probably a bad idea, since it will create unknown dependencies for your classes. Instead you might consider defining local constants, inside of the PHP database client class.

Defining constants inside of classes

Defining relevant MySQL error codes as constants has the advantage that you can just update them in one place, if they ever do change. But, instead of defining global constants, which is bad, we can just define local constants inside a PHP object; to do this, we use the const keyword:

class mysqli_client
{
    const ER_DUP_ENTRY = 1062;

    function example_method() {
        echo  $this::ER_DUP_ENTRY . "\n";
    }
}

We can use perror 1062 in a terminal to find a good name for our constant, as this hints at what MySQL is calling it themselves. For example, the 1062 code is returned when a unique or primary key is duplicated in a table; if we check it in a terminal we get:

$ perror 1062
MySQL error code MY-001062 (ER_DUP_ENTRY): Duplicate entry '%-.192s' for key %d

To access a constant from the outside of the class, we would write like this:

echo $class::ER_DUP_ENTRY."\n"; // As of PHP 5.3.0

So, in order to check if a query returned the ER_DUP_ENTRY code, we can do like this:

$stmt = $db->prepared_query("insert into some_database (id, some_column) values(?, ?)",
 [$id, $some_value]
);

if ($db::ER_DUP_ENTRY === $stmt) {
  echo 'The ID already exists in the database. Nothing was inserted.';
  exit();
} elseif (false === $stmt) {
  throw new Exception("The query failed for unknown reasons.");
}

Comments

  1. In this tutorial you can learn how to insert data in tables that has a foreign key constraint, and as such relies on a key provided from another table.
  2. How to create a MySQL Database with UTF-8 encoding.
  3. How to perform simple SELECT statements in SQL to communicate with SQL databases.
  4. How to create new users in MySQL and control their permissions for better security.

More in: MySQL