MySQL in its standard configuration has this wonderful “feature” of
truncating your data if it can’t fit in the field.
mysql> create table foo (bar varchar(4)); Query OK, 0 rows affected (0.00 sec) mysql> insert into foo (bar) values ("12345"); Query OK, 1 row affected, 1 warning (0.00 sec)
In comparison, PostgeSQL does:
psql=> create table foo (bar varchar(4)); CREATE TABLE psql=> insert into foo (bar) values ('12345'); ERROR: value too long for type character varying(4)
You can make MySQL do the right thing by setting the SQL
Mode option to
include STRICT_TRANS_TABLES or STRICT_ALL_TABLES. The difference is that the
former will only enable it for transactional data storage engines. As much as
I’m loathed to say it, I don’t recommend using STRICT_ALL_TABLES, as an error
during updating a non-transational table will result in a partial
update, which is probably worse than a truncated field. Setting the mode
to TRADITIONAL includes both these and a couple of related issues
(NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO) You can set the
mode using:
-
On the command line:
--sql-mode="TRADITIONAL"
-
In /etc/mysql/my.cnf:
sql-mode="TRADITIONAL"
-
At runtime:
SET GLOBAL sql_mode="TRADITIONAL" SET SESSION sql_mode="TRADITIONAL"
Just say no to databases that happily throw away your data
on said:
Also just say no to non-ACID storage engines. Why are you even using that?
on said:
Just say no to MySQL. Seriously. MySQL is a toy.
on said:
It’s wrong to call it ‘silent truncation’ when in your example MySQL points out that there was a warning.
mysql> create table foo (bar varchar(4));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into foo (bar) values (“12345”);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+———+——+——————————————+
| Level | Code | Message |
+———+——+——————————————+
| Warning | 1265 | Data truncated for column ‘bar’ at row 1 |
+———+——+——————————————+
1 row in set (0.00 sec)
Perhaps it’s more accurate to call it a ‘rarely noticed truncation’…
Your point I guess is that you don’t like MySQL defaulting to not setting the TRADITIONAL sql mode, and there are arguments for and against that.
I wish it was set, personally, which would push it back as an error to the application, but in truth you should really be doing data validification at the application level already. Garbage in, garbage out, as they say.
on said:
It was not truncated “silently”, there was a warning. If you don’t like MySQL, don’t use it, but please stop spreading FUD.