Strict mode can still throw warnings

MySQL by default is vary lax with data validation. Silent conversions is a concept that is not a common practice in other databases. In MySQL, instead of throwing an error, a warning was thrown and many applications simply did not handle warnings. With the introduction of sql_mode=STRICT_ALL_TABLES (or TRADITIONAL), in MySQL 5, a better level of validation now exists.

My understanding was that Warnings are now thrown as Errors, therefore eliminating the need to do a SHOW WARNINGS to confirm any problems after every query (this is a performance overhead on a high volume system due to the round trip latency).

However I found an instance where MySQL in STRICT Mode still throws warnings, leading to the question, are there any other areas, and does the earlier statement “Warnings are now thrown as Errors” hold true.

When using a Strict Mode, a recommendation for all new systems, it is generally accepted that warnings are translated into errors, which implies your could should never have to consider checking for warnings.

Comments

Very interesting. However, INSERT IGNORE has good use: to solve in DB level issues of duplication.
What would otherwise be an application level check: “Is there a row with id XYZ? If not – let’s insert one” –
and I’ve seen so many of these on MyISAM, or on InnoDB outside transaction scope –
is easily solved by MySQL when simply instructed to add or ignore.
I find it useful and in fact having better performance at times.

On the ‘insert syntax’ manual page: “If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead.”

If you combine this rule with the rule of the strict mode, you’ll get a contradiction. It’s very well possible that the current behaviour is the intended behaviour. And I actually think this makes sense, because otherwise the whole insert ignore feature would have no purpose.