Skip to content

# But I DO want MySQL to say “ERROR”!

MySQL Configuration Data Types sql_mode Syntax March 12, 2010March 12, 2010

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
18
Leave a Reply

18 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
9 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Giuseppe Maxia

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

Guest
Mark R

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.

Guest
Bill Karwin

Don’t forget about the fact that MySQL silently ignores CHECK constraints on all storage engines.

Author
shlomi

@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.

Guest
Roland Bouman

@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… Read more »

Guest
sbester

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)

Guest
Vojtech Kurka

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)

Author
shlomi

@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.

Guest
Gurjeet Singh

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.

Author
shlomi

@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.

Guest
Bill Karwin

@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. 🙂

Guest
Roland Bouman

Feeding time is over trolls, let’s get back on-topic.

Author
shlomi

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.

Guest
Shatnanu Oak

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:… Read more »

Author
shlomi

@Shatnanu

Weehee! Material for more posts!

Log Buffer #183, a Carnival of the Vanities for DBAs | The Pythian Blog

[…] Are you interested in MySQL Clustering? I am. Andew Morgan has a new post introducing a tutorial to Build MySQL Cluster 7.1 from source – including MySQL Cluster Connector for Java. With Alex Fatkulin maybe running into an as yet possibly, sort of , kinda non-discovered bug, it’s only fair we also get some vision into the dark side on the MySQL front. Shlomi Noach states But I DO want MySQL to say “ERROR”!. […]

Pythian Group: Log Buffer #183, a Carnival of the Vanities for DBAs | Weez.com

[…] Are you interested in MySQL Clustering? I am. Andew Morgan has a new post introducing a tutorial to Build MySQL Cluster 7.1 from source – including MySQL Cluster Connector for Java. With Alex Fatkulin maybe running into an as yet possibly, sort of , kinda non-discovered bug, it’s only fair we also get some vision into the dark side on the MySQL front. Shlomi Noach states But I DO want MySQL to say “ERROR”!. […]

Those oversized, undersized variables defaults | code.openark.org

[…] to solve otherwise difficult problems. And in most cases, 1024 was just too low, resulting in silent (Argh!) truncating of the result, thus returning incorrect results. It is interesting to learn that […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.