Why SQL_MODE is important

Today was another example of where a correct SQL_MODE saved customer data from being corrupted. By default, MySQL does not enforce data integrity. It allows what is called silent truncations where the result of what you INSERT or UPDATE does not represent truth. NOTE: I see very few customers ever have this correctly configured, those that do have actually listened to my advice.

If you do not read any further, your production MySQL environments should be running with at the bare minimum of SQL_MODE=STRICT_ALL_TABLES however I would also advocate for additional SQL_MODE settings.

For this example, some modified undesirable code attempted to reduce a counter by 1, however because of an UNSIGNED data type and a correctly set SQL_MODE, the application produced an error and data was not corrupted.

This is what should happen with your SQL.

mysql> update stats set loss_count=loss_count - 1 where user_id=42;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`db`.`stats`.`loss_count` - 1)'

It is interesting to note that the error message actually is misleading. The datatype for the column is SMALLINT however the error message prompted an unnecessary schema verification. Even with the calculation I would have assumed the inherited data type would be the column definition before the subtraction. If you try to set the value automatically to negative you get a better message.

mysql> update stats set loss_count=-1 where user_id=42;
ERROR 1264 (22003): Out of range value for column 'loss_count' at row 1

So why is this a big deal? If your MySQL instance is not using SQL_MODE then the result would have been a value of 0 and this would never highlight the bug in the code.

I should point out that only Drizzle of the MySQL forks and variants has tightened up this data integrity, however I have to agree to disagree with the decision to drop UNSIGNED. While I under internally the code simplification behind the decision, without check constraints in MySQL, UNSIGNED is a saving grace. In this example, Drizzle in this case would not have reported an error.

7 Responses to “Why SQL_MODE is important”

  1. Brian Aker says:

    Hi!

    Drizzle does have UNSIGNED. We added it back in the beginning of the year so that we could gain some compatibility that we needed in a couple of places.

    We treat it has a constraint though, not as a type (which is somewhat hairsplitting, but it is useful to know).

    Cheers,
    -Brian

  2. Shlomi Noach says:

    @Brian,
    Does that mean UNSIGNED only has half as much possible values? (Having limited a SIGNED integer?)

  3. Silvia says:

    It’s like the default MyISAM engine issue (until very recently). This is why using the out of the box configuration is never a good idea.

  4. Ovais Tariq says:

    Hi Ronald,

    I completely agree with you over the use of the SQL MODE, traditionally MySQL has been very forgiving and that stems from the fact that traditionally MyISAM (which is a non-transactional storage engine) is the default storage engine that is being used., and with MyISAM if a multi-row insert or update causes an error and you are using the strict SQL MODE then partial updates/inserts can happen if the error happens after the first row. Because MyISAM is not a transactional storage engine and there is no way it can rollback the updates/inserts. And it seemed better to avoid partial updates.

    So my recommendation is use InnoDB together with a strict SQL MODE, so that if there is any error that is encountered, then the changes can be rolled back and hence no partial update issue.

  5. Henrik Ingo says:

    Ronald, I think you are mixing two concepts here: data type and constraints. In your example it just happens that zero was the minimum value for the data range being stored. So using unsigned as data type also works as a constraint. But that’s not the case in general – for instance what if your minimum value would have been one (1)?