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.
here’s a related bug report i opened:
http://bugs.mysql.com/bug.php?id=47771
(return a warning or ER_NOT_SUPPORTED_YET for unsupported syntax)
Shlomi, are you sure with this statement?
> Calling CREATE TEMPORARY TABLE? You get silent commit.
My testcase:
mysql> use test
Database changed
mysql> CREATE TABLE test_commit(col INT) ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test_commit SET col = 1;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TEMPORARY TABLE tmp1 (col INT);
Query OK, 0 rows affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test_commit;
Empty set (0.00 sec)
@Vojtech,
Thank you; I did mean normal CREATE TABLE; have updated the text.
I have not tested rollbacks on temporary tables; however, according to documentation, the rollback will not apply to the CREATE TEMPORARY table itself.
Will further look into it.
If you are serious about your data, use something mature than MySQL. I don’t see a reason why one would run MySQL instead of a more SQL compliant DB. There are quite a few out there, but my personal favorite is Postgres.
Added by shlomi, two days later:
Gurjeet, you are most welcome in this blog. I hope you find it interesting and would like you to visit often. However, this comment does take the form of troll-speech. Since you work at EnterpriseDB, you cannot be completely naive or innocent.
Let me know if you want this comment to be altogether removed.
@Gurjeet,
A bit far fetched in th context of this post. There are plenty of reasons to use MySQL, and very large companies run very popular products on MySQL, successfully. Seems like something’s going right?
I think the web if full of database wars; but eventually, both MySQL and Postgresql (as example) are *good* database systems, and both have their pros and cons.
I think saying “there’s no reason to use MySQL, and PostgreSQL is far better” is not too serious.