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.
You can alleviate the problem sometimes:
mysql> set sql_mode=’traditional’;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 (id tinyint);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (300);
ERROR 1264 (22003): Out of range value for column ‘id’ at row 1
But this doesn’t help for the other issues you have mentioned.
As for the warnings turned into error, be careful of what you ask. MySQL trows a warning on “CREATE TABLE IF NOT EXISTS table_name” if the table did not exist.
I hope that Oracle stewardship will address the strictness issue more vigorously.
Giuseppe
SQL_MODE may help but it doesn’t do it all.
I think it would be helpful if the server prohibited writes to non-transactional tables while in a transaction. This means you can no longer have mixed transactions (which are un-binloggable if they rollback).
Also it would be helpful if the operations which do a silent commit just failed instead.
Clearly these changes would break legacy apps, which is why SQL_MODE exists. It sucks but it’s the only way to do it.
NEW applications (or new servers) get SQL_MODE=’TRADITIONAL’. Legacy ones cannot as it would introduce a vast number of bugs to the app.
Don’t forget about the fact that MySQL silently ignores CHECK constraints on all storage engines.
@Giuseppe,
Yes, there are many more such examples where warnings are thrown: STOP SLAVE on a stopped slave; EXPLAIN EXTENDED…
I think most automated application based SQL do not care about these.
@Mark
Thank you. Please see my two posts: Do we need sql_mode?, sql_mode: a suggestion
@Bill,
True! Thanks for noting this.
@Mark R:
“I think it would be helpful if the server prohibited writes to non-transactional tables while in a transaction. This means you can no longer have mixed transactions (which are un-binloggable if they rollback).”
This may look like a sensible thing but it’s not that simple. You are *always* either in a transaction or between two transactions, it’s just that in autocommit mode, the transaction spans one statement. And of course, engines that are not transactional, are simply unaware of transaction status.
I think the solution is pretty simple though: if you’re really that concerned about this, just don’t use non-transactional engines.