[OT] Re: Another Endless Loop

[crashtest]

I am astounded that MySQL silently truncates your data if the column isn't wide enough. I had to see it to believe it, so I connected to the instance running on my machine and tried a little example. Sure enough:

mysql> describe x;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| y     | char(3) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into x values ('abcd');
Query OK, 1 row affected (0.00 sec)

mysql> select y from x;
+------+
| y    |
+------+
| abc  |
+------+
1 row in set (0.03 sec)

I am particularly offended by the message Query OK, when it clearly isn't. In what universe is it acceptable for a database - an application centered on the storage of data - to design an interface that makes it possible for the data you input to be different from the data it outputs?

I am honestly curious if other databases exhibit this type of behavior. Is there some sort of precedence for this in older RDBMSs? I can't quite wrap my mind around it. My understanding was that although MySql started from humble beginnings, it was sufficiently advanced to see use in many production settings, and was quite popular.

To soothe my nerves, I tried the equivalent operations in Oracle, and to my relief saw

ERROR at line 1:
ORA-01401: inserted value too large for column

I don't use MySql for production use (just for personal tinkering around), and think this has probably scared me off.

[almut]

... I am particularly offended by the message Query OK, when it clearly isn't.

I agree with you that this is questionable default behaviour, but at least it's documented, and (as of version 5.0.2) it seems you can enable a "strict mode" to have it error out:

"If you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit. If the truncated characters are not spaces, a warning is generated. For truncation of non-space characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.2.6, "SQL Modes"."

[samtregar]

Awesome. I was hoping strict mode would do that. We're going to v5.x soon and I'm definitely going to want to turn that on!

-sam

[dragonchild]

There are a ton of strictnesses you can enable. You should read up on them here

[hobbs]

MySQL became "good enough for production use" through a process of redefinition -- lots of people use MySQL in production, so obviously it's good enough for them, right? Seriously though, it has improved, a lot, over the years, but there are still places where not only does it do something stupid and harmful, but it doesn't even consider it important enough to tell you that it happened. You've found one of those. Hopefully the "strict mode" business eliminates most of those problems, but you know as well as I that the people who need it most won't use it at all. :|