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.
@Gurjeet: Are you the same Gurjeet Singh who works for EnterpriseDB — the commercial vendor of PostgreSQL? It would be good form to disclose that fact when you diss the competition. 🙂
Feeding time is over trolls, let’s get back on-topic.
Ouch! Have just been trolled.
Since two days passed with no comment back from Gurjeet, I’m editting his message to add a “troll alert” or something.
The worst thing about mysql is that there are times when it does not think it necessary to throw a warning (bug id: 43930).
For e.g.
mysql> CREATE TABLE `table1` (`number` int(20) DEFAULT NULL) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into table1 values (‘19.54’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from table1;
+——–+
| number |
+——–+
| 20 |
+——–+
1 row in set (0.04 sec)
Or while MySQL starts even with Fatal Innodb errors? (bug id: 19027) There seems to be no way to log warnings on import. (bug id: 8684) You will need to write another post for the bugs (id: 29898) where DATE() function returns inconsistent results.
@Shatnanu
Weehee! Material for more posts!