22

I was quite surprised when MySQL allowed me to insert a NULL into a field that was created with NOT NULL. I did some research and discovered how to enable strict mode. However, I am not quite sure what validation MySQL does when STRICT_ALL_TABLES is enabled.

The manual says:

Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. (emphasis mine) For example, it might have the wrong data type for the column, or it might be out of range.

I understand what it considers missing and how it handles that. I am unclear what it considers invalid. I've done some testing and discovered the following:

  • strings that are too long are invalid
  • numbers that are out of range are invalid
  • NULLs for a non-NULL column are invalid
  • TRUE and FALSE always seem to be valid (they become 1 and 0 respectively)
  • invalid dates are invalid
  • zero dates are valid (additional modes can be enabled to change this behaviour)
  • floats in an integer field are valid (they get rounded)
  • letters in a number field are invalid

Does MySQL do any other validation checks other than what is mentioned above?

The manual says 'wrong data type for the column', but the only situation I see where this actually comes into play is letters in a number field. Are there other examples of data type errors?

Is there a list somewhere of exactly what checks MySQL performs?

EDIT: For the record, my application already does extensive validation. I am using strict mode as a last-chance, just-in-case check. If I forget to check something, I want it to fail fast rather than 'silently mangle my data'.

toxalot
  • 11,260
  • 6
  • 35
  • 58
  • I get the feeling you are wanting the DB to do data validation for you. If that's the case, it's a bad path to go down. – siride Dec 10 '12 at 22:42
  • 3
    @siride: the DBMS ***is*** there to do validation (foreign keys, primary keys, check constraints, type validation). The fact that MySQL allows you to store `February, 31st` is a bug in my opinion. Or to put it the other way: it's a very bad path to go if the DBMS doesn't do any validation. –  Dec 10 '12 at 22:44
  • 4
    I already do validation in the application. I am *not* using strict mode as a *replacement* for good programming, but rather as a way to ***ensure*** good programming. – toxalot Dec 10 '12 at 22:49
  • I would agree it's a bit of a bad path to go down. This should really be enforced at the application level, as what happens a year down teh line when you move to a different database server and forget to enable strict mode in your DB config, or some other developer is working on your code doesn't have any idea strict mode is enabled, He would have no good way to change the validation logic in that the validation logic doesn't exist in your code. – Mike Brant Dec 10 '12 at 22:50
  • 2
    Just saw you latest comment. Sounds good. I will leave my comment here though for any future people looking at a Db-only validation solution. – Mike Brant Dec 10 '12 at 22:51
  • 4
    Holy crap. A high quality, researched question. – Jim Dec 10 '12 at 22:54
  • @MikeBrant I agree. Validation at the application level is a must and makes a much more robust application. It also has the advantage of being able to give user-friendly error messages. – toxalot Dec 10 '12 at 22:54
  • @Jim I was expecting some user with 10k+ – Kermit Dec 10 '12 at 23:03

2 Answers2

9

A good resource is to check the MySQL source, and read mysql-test/t/strict.test to see all the cases they test for after setting STRICT mode or TRADITIONAL mode (which is a superset of STRICT).

You did excellent research and testing, but there are a few more cases, such as:

  • Trying to insert an undefined ENUM value.
  • Trying to insert a default value for a NOT NULL column with no DEFAULT defined.
  • Trying to use CAST() to convert strings to integers, etc.
  • Conversion of VARCHAR to MEDIUMTEXT or LONGTEXT if you give a length greater than 65536.
  • Truncation of COMMENT strings for tables and columns.
  • Conversion of string to YEAR type.
  • Defining a SET or ENUM column with duplicate entries.

Also mysql-test/include/strict_autoinc.inc, because it tests for overflow when an auto-inc value grows too large.

There are a few other test files that use STRICT mode for specific tests, but I didn't examine them.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Another case is trying to insert a negative number to an UNSIGNED column. – toxalot Dec 12 '12 at 18:22
  • http://dev.mysql.com/downloads/mysql/#downloads, select "Source Code" as the Platform. – Bill Karwin Dec 12 '12 at 18:22
  • Yep, the negative number in an unsigned column is another case the tests cover, but I figured that was a case of numbers that are out of range for a column. – Bill Karwin Dec 12 '12 at 18:50
  • 'a negative number in an unsigned column is out of range' - You are right of course, but many people just don't get that. – toxalot Dec 12 '12 at 19:05
4

I grepped the source code of MySQL 5.5.28 (Community Server Edition) to find instances of STRICT_ALL_TABLES being used.

From what I saw, it looks like your list is already pretty complete, but below are a few more things I learned about the use of STRICT_ALL_TABLES. The first one is another validation, while the rest all deal with errors and warnings.

  • Geometry columns can't have a default value. (sql/field.cc:9394)
  • If a table comment is longer than 2048 characters, MySQL produces an error rather than truncating the comment with a warning. (sql/unireg.cc:231)
  • If a field comment is longer than 1024 characters, MySQL produces an error rather than truncating the comment with a warning. (sql/unireg.cc:739)
  • On an insert or update, if a duplicate value exists in a SET or ENUM MySQL produces an error rather than a warning. (sql/sql_table.cc:2503)
  • There are a bunch of operations that will abort on warning with STRICT_ALL_TABLES rather than proceeding with just a warning. These are too numerous for me to list, and the setting of the abort_on_warning flag is too far removed from the code that creates the warnings for me to easily document (or even understand) all of them. But if anybody wants to get their hands dirty in the source code, a few places to start would be sql/sql_update.cc:630 and sql/sql_insert.cc:841
Michael Righi
  • 1,333
  • 9
  • 11