But I DO want MySQL to say “ERROR”!

MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data.

• Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though).
• Calling CREATE TEMPORARY TABLE? You get silent commit.
• Issuing a ROLLBACK on non-transactional involved engines? Have a warning; no error.
• Using LOCK IN SHARE MODE on non transactional tables? Not a problem. Nothing reported.
• Adding a FOREIGN KEY on a MyISAM table? Good for you; no action actually taken.
• Inserting 300 to a TINYINT column in a relaxed sql_mode? Give me 255, I’ll silently drop the remaining 45. I owe you.

Warnings and errors

It would be nice to:

• Have an auto_propagate_warning_to_error server variable (global/session/both) which, well, does what it says.
• Have an i_am_really_not_a_dummy server variable which implies stricter checks for all the above and prevents you from doing with anything that may be problematic (or rolls back your transactions on your invalid actions).

Connectors may be nice enough to propagate warnings to errors – that’s good. But not enough: since data is already committed in MySQL.

If I understand correctly, and maybe it’s just a myth, it all relates to the times where MySQL had interest in a widespread adoption across the internet, in such way that it does not interfere too much with the users (hence leading to the common myth that “MySQL just works out of the box and does not require me to configure or understand anything”).

MySQL is a database system, and is now widespread, and is used by serious companies and products. It is time to stop play nice to everyone and provide with strict integrity — or, be nice to everyone, just allow me to specify what “nice” means for me.